Databases Reference
In-Depth Information
Note that you have not yet indicated the primary key. To identify a given class within a particular semes-
ter 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 informa-
tion 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 semes-
ter code. When you remove the repeating group, this primary key expands to contain the key for the repeat-
ing group, which, in this case, is the student number. Thus, converting to first normal form yields the
following design:
327
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 nec-
essary to have different names. Thus, FacultyLastName and StudentLastName have been shortened to LastName. Similarly,
FacultyFirstName and StudentFirstName have been shortened to FirstName.
Q&A
Question:
Why was the grade included?
Answer:
Although the grade is not actually printed on the class list, it will be entered on the form by the instruc-
tor 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.
Q&A
Question:
Determine the tables and keys required for User View 4. Merge the result into the cumulative design
and draw the E-R diagram for the new cumulative design.
Answer:
Figure A-14 shows the new cumulative design.
