Activity (3 of 3): Relational Database Design
Recall the ERD from the student data.
Here is the first ERD we looked at when discussing datatypes. Now let's talk about the dotted lines connecting each entity. Each student had worries, images and distractions that they kept track of. The relationships between the student and worry, for example, is a one-to-many relationship. Each student had one or more worries and each worry belongs to one and only one student. The image and distraction entities are related the same way with student. Each student had many images and each image was taken by one student. Each student had one or more distractions and each distraction belonged to one student.
The other ERD looks like this.
There are only 3 entities with this ERD. They are also showing a one-to-many relationship between each entity. Each group in the class has many different students in it and each student belongs to one and only one group. Each student had many different images and each image was taken by one and only one student. The worry and distraction was boiled down to just one value of that student's top distraction or top worry and included as an attribute inside the student table instead of as a separate entity in this ERD.
Notice how the primary key of group_id from the student_group entity is being used as a foreign key of the student table. This shows the relationship of those entities. Group_id in the student_group table is the primary key and there for is unique for every row of that table (or in other words each group will have a unique primary key number representing their group).
The numbers 1 through 9 here are representing the primary keys for each group. There is no repeated numbers here. They are all unique.
Recall the student table.
Sam, Joshua, Lincoln, Andrew, and Buck are all in group #1. We can see that because the foreign key of group_id is referring back to the primary key of student_group. So, Sam, Joshua, Lincoln, Andrew and Buck all belong to the group called 'Zac'. Notice how the foreign key value can repeat again and again for that group because those 5 students were in that group. That is the many part of the relationship.
How does Referential Integrity play into this relationship? The relationship between the student_group and student table is enforced between the primary key of student_group and the foreign key of student. If I were to add a new student #26 and I entered all his information and then in the last column put a 10 as the group_id foreign key value; I have violated referential integrity because there is no group with the primary key of 10. The MySQL Workbench would give us an error and not let us to that. The foreign key must be a value that already exists as a primary key of the related table.
How does Referential Integrity play into this relationship? The relationship between the student_group and student table is enforced between the primary key of student_group and the foreign key of student. If I were to add a new student #26 and I entered all his information and then in the last column put a 10 as the group_id foreign key value; I have violated referential integrity because there is no group with the primary key of 10. The MySQL Workbench would give us an error and not let us to that. The foreign key must be a value that already exists as a primary key of the related table.
Also, if I wanted to delete a group, for example group 1 'Zac' and I ran a DELETE statement to get rid of that group, my system would again give me an error because I can't delete a group that has student's in it. Or in other words I can't delete an instance of an entity that has its primary key being used as foreign key values in another table. I would first have to go into the student table and delete every student that belongs to that group first and then I could do back to the student_group table and delete the group once it was empty or didn't have any students related to it. It's important to realize how the system will enforce these relationships.
Now let's look at the relationship between the student entity and the image entity. Each student recorded many different images and each image belongs to one student. Again, a one-to-many relationship.
Recall the image table.
Notice the foreign key of student_id is how it relates to the student table. Each student had a unique number that can then be repeated for each of their images in the image table. So images 1 to 20 were all from the same student, Sam Spencer. His primary key was #1 and that 1 that is repeated again and again tells us that those are all his images. When you get to images 21 through 29 those are Joshua Jones' images. His primary key was #2 and that 2 as a foreign key tell us those are his images.
Watch the Design A Ward Database video which shows steps to design ward data into an ERD.
Submission
Report your progress in the Learning Activities quiz on Canvas
Useful Links:
- Next Activity: Team Activity: Big Data
- Previous Activity: SQL Overview Hands-on
- Go to: Week Index • Course Home • Canvas