Database Reference
In-Depth Information
Student schedule: After all students are assigned to sections, the system produces a student
schedule form, which is mailed to students to inform them of the classes in which they have been enrolled.
Suppose you had created a single unnormalized relation to support the student schedule. This unnormalized
relation would contain a repeating group representing the lines in the body of the schedule as follows:
User View 8
—
335
StudentSchedule (StudentNum, SemesterCode, LastName, FirstName,
LocalStreet, LocalCity, LocalState, LocalZip, PermStreet,
PermCity, PermState, PermZip, (ScheduleCode,
DepartmentName, CourseNum, CourseTitle, SectionLetter,
NumCredits, Time, Room) )
At this point, you remove the repeating group to convert to first normal form, yielding the following:
StudentSchedule (StudentNum, SemesterCode, LastName, FirstName,
LocalStreet, LocalCity, LocalState, LocalZip, PermStreet,
PermCity, PermState, PermZip, ScheduleCode,
DepartmentCode, CourseNum, CourseTitle, SectionLetter,
NumCredits, Time, Room)
Note that the primary key expands to include ScheduleCode, which is the key to the repeating group.
Converting this table to second normal form produces the following:
Student (StudentNum, LastName, FirstName, LocalStreet, LocalCity,
LocalState, LocalZip, PermStreet, PermCity,
PermState, PermZip)
StudentSchedule (StudentNum, SemesterCode, ScheduleCode)
Section (SemesterCode, ScheduleCode, DepartmentCode, CourseNum,
CourseTitle, SectionLetter, NumCredits, Time, Room)
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
Removing the attributes that depend on the determinant of DepartmentCode and CourseNum from the
Section table and converting this collection of tables to third normal form produces the following tables:
Student (StudentNum, LastName, FirstName, LocalStreet,
LocalCity, LocalState, LocalZip, PermStreet, PermCity,
PermState, PermZip)
StudentSchedule (StudentNum, SemesterCode, ScheduleCode)
Section (SemesterCode, ScheduleCode, DepartmentCode, CourseNum,
SectionLetter, Time, Room)
Course (DepartmentCode, CourseNum, CourseTitle, NumCredits)
Merging this collection into the cumulative design does not add anything new. In the process, you can
merge the StudentSchedule table with the StudentClass table.
User View 9
Full student information report: List complete information about a student, including his
or her majors and all grades received to date. Suppose you attempted to place all the attributes on the full
student information report into a single unnormalized relation. The table has two separate repeating groups:
one for the different majors a student might have and the other for all the courses the student has taken.
—
NOTE
Several attributes, such as name and address, would not be in the repeating groups. All these attributes are already in the
cumulative design, however, and are not addressed here.
The table with repeating groups is as follows:
Student (StudentNum, (MajorNum, DepartmentCode, LastName,
FirstName), (SemesterCode, DepartmentCode, CourseNum,
CourseTitle, NumCredits, Grade, GradePoints) )
