Database Reference
In-Depth Information
Recall from Chapter 5 that you should separate repeating groups when a relation has more than one. If
you don
336
t, you will typically have problems with fourth normal form. Separating the repeating groups in this
example produces the following:
'
StudentMajor (StudentNum, (MajorNum, DepartmentCode, LastName, FirstName))
StudentCourse (StudentNum, (SemesterCode, DepartmentCode,
CourseNum, CourseTitle, NumCredits, Grade, GradePoints))
Converting these tables to first normal form and including FacultyNum, which is a determinant for
LastName and FirstName, produces the following:
StudentMajor (StudentNum, MajorNum, DepartmentCode, FacultyNum,
LastName, FirstName)
StudentCourse (StudentNum, SemesterCode, DepartmentCode,
CourseNum, CourseTitle, NumCredits, Grade, Grade Points)
The StudentCourse table is not in second normal form because CourseTitle and NumCredits depend only
on the DepartmentCode, CourseNum combination. The StudentMajor table is not in second normal form
either because DepartmentCode depends on MajorNum. Removing these dependencies produces the following
tables:
StudentMajor (StudentNum, MajorNum, FacultyNum, LastName, FirstName)
Major (MajorNum, DepartmentCode)
StudentCourse (StudentNum, SemesterCode, DepartmentCode,
CourseNum, Grade, GradePoints)
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
Other than the StudentMajor table, all these relations are in third normal form. Converting the
StudentMajor table to third normal form produces the following tables:
StudentMajor (StudentNum, MajorNum, FacultyNum)
Faculty (FacultyNum, LastName, FirstName)
Merging this collection into the cumulative design does not add anything new. (You can merge the
StudentMajor table with the Advises table without adding any new attributes.)
User View 10
Work version of the time schedule: This report is similar to the original time schedule
(see Figure A-3), but it is designed for the college
'
s internal use. It shows the current enrollments in each
section of each course, as well as each section
s maximum enrollment. The only difference between the work
version of the time schedule and the time schedule itself (see User View 6) is the addition of two attributes
for each section: current enrollment and maximum enrollment. Because these two attributes depend only on
the combination of the semester code and the schedule code, you would place them in the Section table of
User View 6, and after the merge, they would be in the Section table in the cumulative design. The cumula-
tive design thus far is shown in Figure A-17.
'
Search WWH ::




Custom Search