For each entity, Jesse wants to see table designs in 3NF. Use standard notation format to show the primary key and the other fields in each table.

Question

What will be an ideal response?

Answer 1

The design for STUDENT, INSTRUCTOR, COURSE, and CORPORATE CLIENT are not difficult. Most students will be able to create the 3NF designs, where all fields depend solely on the primary key, which is represented by a single field. The following examples would be typical:

STUDENT (SSN, LASTNAME, FIRSTNAME, ADDRESS, PHONE, BIRTHDATE, CLIENT NO)

INSTRUCTOR (INSTRUCTOR NO, LASTNAME, FIRSTNAME, OFFICE, EXTENSION)

COURSE (COURSE NO, COURSE NAME, HOURS, TUITION FEE)

CORPORATE CLIENT (CLIENT NO, CLIENT NAME, ADDRESS, PHONE, CONTACT PERSON)

Some students might include a “scheduled course” as an entity. This is a refinement that shows a good understanding of the normalization concept. A scheduled course is analogous to a scheduled flight in an airline scenario. For example, Flight 333 might be listed as a record in a flight schedule table (just as a course might be listed in a course table), but Flight 333 on a specific date, such as 12/31/2014, is a specific flight that flies that day. Thus, the following might work well as a design. Notice that the course has a date and an assigned instructor and is in 3NF:

SCHEDULED COURSE (SCHEDULED COURSE NO, SCHEDULE DATE, INSTRUCTOR NO, LOCATION)

The most difficult task is to create the design for the training record that reflects the information about a specific student in a specific course. Fortunately, the analogy is very close to the school registration example that begins on page 417. The textbook example begins with an unnormalized design (Figure 9-29 on page 418) that resembles a paper registration form. The example then moves into 1NF by eliminating the repeating group, and later into 2NF and 3NF. Similarly, the training record for SCR Associates might initially look like this, where every nonkey field depends on the combination key that consists of two fields, SSN and SCHEDULED COURSE NO:

TRAINING RECORD (SSN, SCHEDULED COURSE NO, PAID STATUS, STUDENT ACHIEVEMENT)