Problem

Figure 4-32 shows a class list for Millennium College. Convert this user view to a set o...

Figure 4-32 shows a class list for Millennium College. Convert this user view to a set of 3NF relations using an enterprise key. Assume the following:

• An instructor has a unique location.

• A student has a unique major.

• A course has a unique title.

Step-by-Step Solution

Solution 1

Convert the user view to 3NF relations

Convert user view to a set of 3NF using enterprise key:

The class list of Millennium College for a semester is displayed as a view in the Figure 4-32.

Enterprise key:

• To strength the standards of a primary key in the database, experts recommend new key known as enterprise key.

• Enterprise key is type of primary key, primary is unique in a table, whereas enterprise key is unique across the database.

This user view is transformed into a set of 3NF relations by the use of an enterprise key are as follows:

OBJECT (OID, ObjectType)

INSTRUCTOR (OID, InstructorName, InstructorLocation)

COURSE (OID, CourseNumber, CourseTitle, InstructorName)

STUDENT (OID, StudentNumber, StudentName, Major)

Note:

• The highlighted attributes are foreign key attributes, and the underlined attributes are primary key attributes.

Explanation:

OBJECT (OID, ObjectType)

• In the above line, OBJECT is the relation; the attributes inside the relation are OID and ObjectType.

• Here, OID acts as a primary key as well as enterprise key, this key is unique across the database.

INSTRUCTOR (OID, InstructorName, InstructorLocation)

• In the above line, INSTRUCTOR is the relation; the attributes inside the relation are OID, Instructor Name and Instructor Location.

• Instructor Location is unique in the database, using the enterprise key OID, the instructor location and instructor name can be gathered.

COURSE (OID, CourseNumber, CourseTitle, InstructorName)

• In the above line, COURSE is the relation; the attributes inside the relation are OID, CourseNumber, CourseTitle and InstructorName.

• Using the enterprise key, OID, the data of course number, course title and instructor name can be retrieved from the database.

STUDENT (OID, StudentNumber, StudentName, Major)

• In the above line, STUDENT is the relation; the attributes inside the relation are OID, StudentNumber, StudentName and Major.

• Using the enterprise key, OID, the data from the student table such as student number, student name and the unique major can be retrieved from the database.