This is Part B of the eighth in a series of database design case studies. Here in Part B, the three-table database from Part A is expanded to a five table database.
After successfully implementing the initial database to track courses and instructors:
the board of the Adult Ed Center has decided to proceed with adding students, grades, and credits to the database.
In addition to information contained in the first database, the completed project will include:
Obviously, student personal information will go into a new table, but what about grades and credits? Should we include in the student database x number of fields to accommodate a hypothetical x number of courses that students could take?
That approach would be cumbersome and have a few serious problems. What about students who took only one or several courses before discontinuing their studies? For them the un-used fields (course, date, grade, credits, etc. etc.) would be a huge waste of disk space. And what if over time some students took a number of courses that exceeded the foreseen x capacity of courses in the student table?
By similar logic, it would be impractical to add grades and credits to the classes table.
The solution is to put grades and credits into another table and link that table to the tables for students and classes.
Again, fields have been simplified for brevity.
Grouped in such a manner, our database now consists of five tables.
Each record in a table must be uniquely identified by its value in the table's key field.
The next step is to identify the relationships created by the new tables. We have a new many-to-many relationship between Classes and Students: one student may take many courses and one course may be given to many students.
When two tables have a many-to-many relationship we create a lien table to complete the many-to-many relationship. In the present case we add the Grades table.
As we have seen previously, the relationship between both many-to-many tables and the lien table is one-to-many.
One-to-many relationships are completed by placing values from the "one" table's key field into a field of the related records in the "many" table. So, in this example the primary keys from both "one" tables (Classes and Students) are placed into the "many" table (Grades) as foreign keys.
The final result is a relational database model. When a database model satisfies all the technical requirements it is said to be rationalized.
The objective of rationalizing a database structure is to eliminate redundancy in order to save disk space and to simplify updates by insuring that there is only one instance of every piece of information. The only fields that repeat are the foreign keys.
Every rational database model must obey these four rules and pass these three tests.