This is Part A of the eight in a series of database design case studies. Part A is a three-table database. In Part B, the database will be expanded to five tables.
A fledgling small-town adult education center plans to expand its curriculum to meet the rising demand for its services. The board of governors has sent out a request for proposals for a relational database management system to track students, course offerings, and instructors.
The board knows that a properly designed database can be easily expanded, so they have decided to implement the system in two stages, beginning with courses and instructors, and later include students.
The system will be required to generate a range of reports which will initially need to include the following:
Design a suitable database and draw a diagram showing all tables, fields, key fields, and relationships.
The first step in database design is to determine all the necessary fields and segregate them into groups that mirror the natural order of the real world.
We have abbreviated the fields here for simplicity. In the real world, for example, the instructor name would be broken down into two fields: last, and first.
When the fields have been grouped into their natural order, each group represents a table.
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 between the tables. In this example there is a many-to-many relationship between Instructors and Courses: one instructor may teach many courses and one course may be taught by many instructors.
When two tables have a many-to-many relationship we create a third table, often referred to as a lien table because it completes the many-to-many relationship.
As we have seen previously, the relationship between both many-to-many tables and the lien is a one-to-many relationship.
One-to-many relationships are created 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 (Instructors and Courses) are placed into the "many" table (Classes) 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 every piece of information exists in only one place. The only fields that repeat are the foreign keys.