The Hermit Hermit's Free Library  Database Design Cases

Adult Ed Center Database, Part B

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.

System Analysis

After successfully implementing the initial database to track courses and instructors:

Complete diagram of a relational database
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:

Extended Model Fields

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.

Field names in tables of the extended Adult Ed Center relational database model

Extended Model Tables

Grouped in such a manner, our database now consists of five tables.

Diagram showing the tables in the extended Adult Ed Center relational database model

Extended Model Key Fields

Each record in a table must be uniquely identified by its value in the table's key field.

Diagram showing the key fields of the tables in the extended Adult Ed Center relational database model

Extended Model Relationships

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.

Diagram showing the relationships between the tables of the extended Adult Ed Center relational database model

Extended Model Primary & Foreign Keys

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.

Diagram showing the primary and foreign keys in the tables of the extended Adult Ed Center relational database model

The Rationalized Extended Model

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.

A rational database model must satisfy these four rules and pass these three tests.



Diagram showing the complete extended Adult Ed Center relational database model