So, let's get to it. I have 6 tables:
I would like to create an ERD physical model of gradebook. This is how I pictured it:
- Every student goes to one class
- Every student has many subjects (like math, biology, geography,)
- Every student can have a large number of grades from different subjects.
- Every class has its class teacher
- In one class there are several students
- Every single subject is taught by only one teacher (there is only one for math, one for biology,)
- One class has several subjects.
- Every student can have a large number of grades.
- Grades are from different subjects
- Every class teacher is also a teacher (so is a member of Teacher table) but not every teacher is a class teacher
- One class teacher per class.
- One teacher teaches several students and several classes
- Teacher teaches only one subject
- One teacher can give a large number of grades
I hope I made myself clear. I have to create physical ER diagram and I have trouble connecting these tables with relationship lines. So, I have identifying, Non-identifying, M:N relationship and informative relation. If someone could help me connect these tables according to what's described above, that would be very nice. So far, I have just the tables. I am new at this, but I really like to get this right. Or if someone can just point me in the right direction for understanding the relationships. I have trouble deciding, what is in this particular example indentifying, non-identifying or M:N relationship.
Thank you in advance.