Course Schedule

Requirements
This example is from the Data Modeling book by Andy Oppel, chapter 6, problem practice "UTLA Academic Tracking" (pages 143-145).

Design
The following solution is authored by Scott Callahan. The solution has been revised by Mihaela Sabin and Rebecca Lee.

Part A - First Normal Form
The first step in normalization is to find a primary key for each relation. This is essential because all further normalization processes will reference this key.

STUDENT: STUDENT ID (PK), STUDENT NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE

In the STUDENT relation, I have designated the Student ID as the primary key. Prior to doing this, I transformed the given ID attribute listed in this exercise into a more specific unique identifier called “Student ID.” It appears already to be in first normal – no repeating data to move into a new relation. It should be noted that any first normal form relation that has only a single atomic attribute for its primary key is automatically in second normal form.

COURSE: COURSE ID (PK), TITLE, NUMBER OF CREDITS, DESCRIPTION

COURSE PREREQUISITE: COURSE ID (PK, FK), PREREQUISITE COURSE (PK, FK)

In the COURSE relation, conversion to first normal form required me to move the multivalued attribute of PREREQUISITE COURSES into the new relation I named “COURSE PREREQUISITE.” In this new relation, I chose a meaningful name which included a part of the original relation. I have gone on to copy the original primary key, the COURSE ID, into the COURSE PREREQUISITE relation, and have designated it as both a primary and foreign key. The multivalued attribute PREREQUISITE COURSE has been moved to this new relation and become a part of the primary key. Because the PREREQUISITE COURSE attribute has values that correspond to valid COURSE ID values (as generated for each new instance in the COURSE entity), PREREQUISITE COURSE is also a foreign key (FK) that references the COURSE entity.

INSTRUCTOR: INSTRUCTOR ID (PK), INSTRUCTOR NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE, OFFICE PHONE

INSTRUCTOR QUALIFIED COURSE: INSTRUCTOR ID (PK, FK), QUALIFIED COURSE (PK, FK)

From the INSTRUCTOR relation, I have specified INSTRUCTOR ID as the most unique primary key. Converting this relation to first normal form required the creation of a new relation called “INSTRUCTOR QUALIFIED COURSE.” In this new relation, I have copied the primary key from the original, and designated this attribute as both a primary and foreign key. In addition, adhering to first normal rules, I moved the multivalued attribute “QUALIFIED COURSE” into this new entity, and designated it as part of the primary key. Similar to the argument made in the case of the COURSE PREREQUISITE entity, QUALIFIED COURSE attribute is also a foreign key (FK) that references the COURSE entity.

SECTION: SECTION ID (PK), YEAR, SEMESTER, BUILDING, ROOM, DAYS, TIMES, INSTRUCTOR ID, INSTRUCTOR NAME, COURSE ID, NUMBER OF CREDITS

SECTION LINE ITEM: SECTION ID (PK, FK), STUDENT ID (PK), STUDENT NAME, GRADE

In the SECTION relation, I have elected to create a surrogate key dubbed “SECTION ID.” Conversion of this relation to first normal form makes it necessary to remove the repeating group (STUDENT ID, STUDENT NAME, GRADE) from this relation. The group has now been moved to the new entity called “SECTION LINE ITEM.” The new relation has the SECTION ID copied from SECTION REPORT, and designated as both a foreign and primary key. STUDENT ID has been added to SECTION ID to comprise a concatenated primary key.

Part B - Second Normal Form
A relation is said to be in second normal form if it meets both the following criteria:
 * 1) The relation is in first normal form.
 * 2) All nonkey attributes are functionally dependent on the ENTIRE primary key.

I will list out the first four relations that have been converted to first normal form: COURSE: COURSE ID (PK), TITLE, NUMBER OF CREDITS, DESCRIPTION

COURSE PREREQUISITE: COURSE ID (PK, FK), PREREQUISITE COURSES (PK, FK)

INSTRUCTOR:INSTRUCTOR ID (PK), INSTRUCTOR NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE, OFFICE PHONE

INSTRUCTOR QUALIFIED COURSE: INSTRUCTOR ID (PK, FK), QUALIFIED COURSES (PK, FK)

COURSE PREREQUISITE and INSTRUCTOR QUALIFIED COURSE are the new first normal form relations created out of COURSE and INSTRUCTOR. Their creation involved moving a single multivalued attribute into a new relation, and since no other attributes were included, the new relations are already in second normal form. This is because there are no nonkeys in the new relations: functional dependence of nonkeys on the entire compound primary key is a nonissue in this instance. Since this is the case with the new relations not having nonkeys, they are already in third normal form as well, which is concerned with transitive dependence among nonkeys.

However, looking at the first normal form of the SECTION LINE ITEM relation:

SECTION LINE ITEM: SECTION ID (PK, FK), STUDENT ID (PK), STUDENT NAME, GRADE

We see that the nonkey attribute of STUDENT NAME is not functionally dependent on the entire primary key of SECTION ID and STUDENT ID. The reason for this: at any point in time, there is only one value of the attribute STUDENT NAME associated with a given value of STUDENT ID; SECTION ID does not hold dependence over the STUDENT NAME nonkey. Therefore, in adhering to second normal form rules, I have opted to create a new relation called STUDENT INFO, and with this new relation, I have copied the newly designated FK attribute of STUDENT ID in the SECTION LINE ITEM relation to the STUDENT INFO entity as a primary key. The STUDENT NAME attribute has been removed from the original relation, and placed in the new one. Here is the result of the conversion:

SECTION LINE ITEM: SECTION ID (PK, FK), STUDENT ID (PK, FK), GRADE STUDENT INFO: STUDENT ID (PK), STUDENT NAME

We observe that STUDENT INFO is a shorter version of STUDENT. Therefore, I dropped STUDENT INFO and kept only the STUDENT entity:

STUDENT: STUDENT ID (PK), STUDENT NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE

Part C - Third Normal Form
So far, there are seven relations that are in second normal form. As discussed previously, relations 3 and 5 (COURSE PREREQUISITE and INSTRUCTOR QUALIFIED COURSE) are already in third normal form. Third normal form lends itself to eliminating transitive dependencies, nonkey attributes within a relation that depend on another nonkey. In fact, the only relation that isn’t already in third normal form is the SECTION entity. There is a transitive dependence on INSTRUCTOR ID of the attribute INSTRUCTOR NAME and a transitive dependence on COURSE ID with NUMBER OF CREDITS.
 * 1) STUDENT: STUDENT ID (PK), STUDENT NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE
 * 2) COURSE: COURSE ID (PK), TITLE, NUMBER OF CREDITS, DESCRIPTION
 * 3) COURSE PREREQUISITE: COURSE ID (PK, FK), PREREQUISITE COURSE (PK, FK)
 * 4) INSTRUCTOR: INSTRUCTOR ID (PK), INSTRUCTOR NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE, OFFICE PHONE
 * 5) INSTRUCTOR QUALIFIED COURSE: INSTRUCTOR ID (PK, FK), QUALIFIED COURSE (PK, FK)
 * 6) SECTION: SECTION ID (PK), YEAR, SEMESTER, BUILDING, ROOM, DAYS, TIMES, INSTRUCTOR ID, INSTRUCTOR NAME, COURSE ID, NUMBER OF CREDITS
 * 7) SECTION LINE ITEM: SECTION ID (PK, FK), STUDENT ID (PK, FK), GRADE

To transform the second normal form relation of SECTION into third normal form, I move any transitively dependent attributes to new relations where they depend only on the primary key. With the two new relations, I have kept the attribute in which they depend as foreign keys in the original relation. The result is as follows:

SECTION: SECTION ID (PK), INSTRUCTOR ID (FK), COURSE ID (FK), YEAR, SEMESTER, BUILDING, ROOM, DAYS, TIMES INSTRUCTOR INFO: INSTRUCTOR ID (PK), INSTRUCTOR NAME COURSE INFO: COURSE ID (PK), NUMBER OF CREDITS

Similar to the situation in which I created a STUDENT INFO relation, the INSTRUCTOR INFO and COURSE INFO relations are shorter versions of INSTRUCTOR and COURSE. Therefore, I keep the ‘longer’ versions and discard the ‘shorter’ ones.

Part D - Final Relation List and UML Diagram

 * 1) STUDENT: STUDENT ID (PK), STUDENT NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE
 * 2) COURSE: COURSE ID (PK), TITLE, NUMBER OF CREDITS, DESCRIPTION
 * 3) COURSE PREREQUISITE: COURSE ID (PK, FK), PREREQUISITE COURSE (PK, FK)
 * 4) INSTRUCTOR: INSTRUCTOR ID (PK), INSTRUCTOR NAME, STREET ADDRESS, CITY, STATE, ZIP CODE, HOME PHONE, OFFICE PHONE
 * 5) INSTRUCTOR QUALIFIED COURSE: INSTRUCTOR ID (PK, FK), QUALIFIED COURSE (PK, FK)
 * 6) SECTION: SECTION ID (PK), INSTRUCTOR ID (FK), COURSE ID (FK), COURSEYEAR, SEMESTER, BUILDING, ROOM, DAYS, TIMES
 * 7) SECTION LINE ITEM: SECTION ID (PK, FK), STUDENT ID (PK, FK), GRADE

UML Class Diagram

Implementation
SQL CODING:

Back to CIS520 Class Notes