Speaker Notes to accompany more on relational databases including 1 to 1, 1 to many and many to many relationships:
This presentation provides additional information on designing a relational database. Specifically it deals with the concepts of 1 to 1, 1 to many and many to many.
The following slides will give more information about these relationships.
One to one relationship means one record on a table relates to one record on another table.
You can also talk about one to one relationships between the data on the table. One social security number has one name etc.
Note that you have Date on the Donation Table if you allow multiple donations to the Drive by a donor. If you do not think that you will have donors giving twice to a drive you do not need the date as part of the key.
The Vendor # is the link between the two tables that are in a one to many relationship. Note the relationship. An item has one vendor while a vendor can have many items.
The vendor # links the two tables. It is stored on the one side. I could not store the Item # on the Vendor Table because one vendor provides many items. However I can store the Vendor # on the Inventory Table because one item can only have one vendor.
Note that the only data you can carry in the bridge file is data that relates to both parts of the key - the salary for a particular star for doing a particular movie meets this criteria.
The first step in designing the relational database is to determine a list of the data that is needed.
a separate table would be needed. The key would be student idno combined with the date the student signed up for the major. The data in the table would simply be the major unless there is other information related to both parts of the primary key that needs to be stored.
Candidate keys are defined as things that could be used as the primary key. Social security number is an excellent example of this.
Major code on the student table is a foreign key because it relates to the primary key in the major table.
If we had just used student idno and course code as the primary key, we would have a problem if a student failed or withdrew from the course and wanted to take the course over again. That is why we need to make semester taken part of the key. We now have a primary key code for each student, each course they took and each time they took it.
In this system, I choose to ignore the problem of a course changing name and changing number of credits. This could be handled by including date in the course table.
Course code in the student course table is a foreign key because it relates to the course code in the course table.