So, let's get to it. I have 6 tables:
Students
Classes
Subjects
Grades
Class teacher
Teachers
I would like to create an ERD physical model of gradebook. This is how I pictured it:
Students:
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.
Classes:
Every class has its class teacher
In one class there are several students
Subjects:
Every single subject is taught by only one teacher (there is only one for math, one for biology,)
One class has several subjects.
Grades:
Every student can have a large number of grades.
Grades are from different subjects
Class teacher:
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.
Teacher:
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.
In general:
Identifying relationships add new Primary Key and Foreign Key to child tables.
Non-identifying relationships add new Foreign Key to child tables.
M:N relationships create new table and use indentifying relationships.
Informative relationships are just lines (don’t affect generated SQL code).
Now I will try to help you to understand relationship types, but please don’t consider the samples to be 100% correct for your gradebook model. To my regret, I do not have time to work on it in detail.
In general, I recommend you to think about data and queries you may need to execute to get correct values.
One student goes to one class.
Try to draw non-identifying relationship between tables Class and Student. In Student table you will see newly created ID_Class column. Why non-identifying relationship can be used? Probably because Student_ID is the only required identifier and is unique. There seems to be no need to use ID_Class column as a part of primary key and that’s why identifying relationship is not needed.
Every student has many subjects:
Try to draw M:N relationship and see what happens. TDM will create a new entity with identifying relationships. Think about data. “Mike” studies “Biology” and “Math”. “John” studies “Math” and “Geography”. With M:N relationship you can store John and Mike in Students table and Math, Biology in Subjects table. In the new entity you can store ID values for both Students and Subjects. Data in the third table should represent records:
Mike - Biology.
Mike - Math.
John - Math.
John - Geography.
(In this case ID values will be used instead of names/titles.)
As you can see, for M:N relationships identifying relationships are used. Why? Because you can add other columns to the table, e.g. final grade and then query the table using Mike - Bilology where conditions and get what you need, without the necessity to create new primary key and use some ID value as a unique identifier. (You could create ID column in the third table and use non-identifying relationships, but that will enlarge your table.)
You can run queries on that third table and get information about how many students study math, how many subjects John studies, what is average grade for all students that study Geography etc.
You need to know what reports should be generated out of your gradebook database
A few tips from me, which should help you in future. I saw various naming conventions over the web. I will try to point you to correct one.
letter case - it is better to use only one size of letters: I suggest lowercased. Even if some/most engines allow using mixed sized or even national characters, try to use only lowercase. Why:
some databases create objects (tables) on disk using given names. Depending on filesystem (linux vs windows) the lettercase does matter so may have to face problems while on one FS your system will work, on another won’t
object name comparision is sometimes depended on database settings (for example lower_case_table_names for mysql). It may again gives unexpected results on different db instances.
for some db engines, lowercase is default method, but they allow using uppercase if using additional syntax. For example postgresql requires double quote encapsed names to interpret it in case sensitive manner - otherwise names are automaticly converted to lowercase while comparing by internal engine. Personaly I hate additional doublequotes in SQL code and I believe it makes this code less readable.
As you can see, using lowercase always is better, more clear and strightforward. And you don’t have to press shift while writing
Try to name fields in unique way. Let’s take a look on your tables. A few tables has a field Postal_Code. When writing more complex queries (joins) you will be forced to use table names in front of field names due to name colission. It will require more time to write and is less readable as well. From my experience, the best way is to use name of table or it’s short. For example for table users fields may be as: id_user, user_fname, user_lname, user_adr_street, user_adr_postal etc.
When prefix name looks unaccepttale long, you can use acronym/abbreviation (for all names in a table): usr_adr_postal etc
I know those tips are not directly related to what do asked but I hope i will help.
