Hello guys,
I found ER-modelling as the hardest topic in one of the subject I have, Databases. There is a lot of things that I don't understand, and I don't clearly see how people can manage to solve problem like this. We got a few tasks, and I'm struggling with the first one. Simply because I don't understand. I will post it here, and what I've done, and maybe you guys could help me, guide me, make me understand this? So I can solve the other problems by myself :D
Task:
"We will create a database to keep track of exams for a school.
The school has campuses in several cities. The subjects on the schoolplaces are the same, and all the schools uses the same exampapers/examtasks . Every subject got an unique course code, name, and number of credits.
An examination task consists of many tasks. The different tasks can be created by different teachers and a task can be included in more than one exam. We must find who made the various tasks and the institution assosiated with this teacher. The system will register when the exam is submitted, the examination date, registration deadlines, and who will be the censor(s).
Every censor got a name, address, qualifications and address stored in a table. History of the censors that have marked the different examination papers for each semester to be preserved. It is not possible to use internal sensors. There is only exam in a course once in each semester.
We will also be able to make out lists of schools with school name, location, address and telephone number.
It should also be possible to find out which teachers who have taught in the various subjects in recent years and the school places the various teachers have been assosiated with. Every teacher got a name, address and qualifications. Some of the teachers have worked at several campuses.
Make your own assumptions where necessary. Students are given an unique student number when they register as students, and this is this number that they use for exams. Students must register for the exam within a given date. It must be recorded whether the student is ill or has not met the exam. The exam will be phased character is detected. It should be possible to obtain lists of names and addresses of all students, organized by school location."
First we need to make an ER-model, and then a logical datamodel where we split many-to-many relations.
What I've done so far. I don't know if this is right, but my taughts:
My entities: Exam, Task, Person (which I will split into censor, student, teacher later), Subject and Campus.
Relations:
- Many to many from Person to Campus (A person has worked in one or more campuses. A campus consist of one or more persons)
- Many to many from Exam to Task (A exam consists of one or more tasks. A task can be given to one or more exams)
- One to many from Subject to Exam (A subject can have one or more exams over time, but there can only be one exam for each subject)
- Many to many from Person to Exam (A student can take zero or more exams in a semester. An exam is taken by one or more students)
- Many to many from Person to Exam (A censor can mark one or more exams. An exam is marked by one (or more?) censor(s)).
- Many to many from Person to Task (A teacher can make zero or more tasks for an exam. A task is made by one or more teachers.
- Many to many from Person to Subject (A teacher can teach in one or more subjects. A subject consists of one or more teachers)
I don't know if this is right. I feel something is wrong with my entities as well my relations. Did I miss something? Is something unecessary? I also feel I have to many "many-to-many"-relations.
What do you think about my relations so far? Could you guys help me/guide me?
Thanks!