Database Reference
In-Depth Information
NOTE
Because attribute names within a single table must be unique, it is not permissible to assign the attribute name LastName to
both the faculty and student last names. Thus, the attributes that store the last and first names of a faculty member are named
FacultyLastName and FacultyFirstName, respectively. Similarly, the attributes that store the last and first names of a student
are named StudentLastName and StudentFirstName, respectively.
329
Note that you have not yet indicated the primary key. To identify a given class within a particular
semester requires the combination of a department code, course number, and section letter or, more simply,
the schedule code. Using the schedule code as the primary key, however, is not adequate. Because the infor-
mation from more than one semester will be on file at the same time and because the same schedule code
could be used in two different semesters to represent different courses, the primary key must also contain
the semester code. When you remove the repeating group, this primary key expands to contain the key
for the repeating group, which, in this case, is the student number. Thus, converting to first normal form
yields the following design:
ClassList (DepartmentCode, DepartmentName, SemesterCode,
CourseNum, CourseTitle, NumCredits, SectionLetter,
ScheduleCode, Time, Room, FacultyNum, FacultyLastName,
FacultyFirstName, StudentNum, StudentLastName,
StudentFirstName, ClassStanding, Grade)
Converting to third normal form yields the following collection of tables:
Department (DepartmentCode, DepartmentName)
Section (SemesterCode, ScheduleCode, DepartmentCode, CourseNum,
SectionLetter, Time, Room, FacultyNum)
Faculty (FacultyNum, LastName, FirstName)
StudentClass (SemesterCode, ScheduleCode, StudentNum, Grade)
Student (StudentNum, LastName, FirstName, ClassStanding)
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
NOTE
Because the last name of a faculty member is now in a separate table from that of the last name of a student, it is no longer
necessary to have different names. Thus, FacultyLastName and StudentLastName have been shortened to LastName. Simi-
larly, FacultyFirstName and StudentFirstName have been shortened to FirstName.
Q&A
Question: Why was the grade included in the StudentClass table?
Answer: Although the grade is not actually printed on the class list, it will be entered on the form by the
instructor and sent to the records office for posting. The grade verification report differs from the class list
only in that the grade is printed. Thus, the grade will ultimately be required and it is appropriate to deal with
it here.
