Database Reference
In-Depth Information
Because the key to the repeating group in the Student table is MajorNum, removing this repeating group
yields the following:
324
Student (StudentNum, LastName, FirstName, LocalStreet,
LocalCity, LocalState, LocalZip, PermStreet,
PermCity, PermState, PermZip, MajorNum, Description,
DepartmentCode, FacultyNum, LastName, FirstName)
Converting this relation to second normal form produces the following tables:
Student (StudentNum, LastName, FirstName, LocalStreet,
LocalCity, LocalState, LocalZip, PermStreet, PermCity,
PermState, PermZip)
Major (MajorNum, Description, DepartmentCode, DepartmentName)
Advises (StudentNum, MajorNum, FacultyNum)
In this case, you must remove the following dependencies to create third normal form tables: OfficeNum
determines Phone in the Faculty table, and DepartmentCode determines DepartmentName in the Major table.
Removing these dependencies produces the following collection of tables:
Department (DepartmentCode, DepartmentName)
Faculty (FacultyNum, LastName, FirstName, Street, City, State,
Zip, OfficeNum, CurrentRank, StartDate, DepartmentCode)
Student (StudentNum, LastName, FirstName, LocalStreet,
LocalCity, LocalState, LocalZip, PermStreet, PermCity,
PermState, PermZip)
Advises (StudentNum, MajorNum, FacultyNum)
Office (OfficeNum, Phone)
Major (MajorNum, Description, DepartmentCode)
The DBDL representation is shown in Figure A-9.
Department (DepartmentCode, DepartmentName)
Student (StudentNum, LastName, FirstName, LocalStreet, LocalCity,
LocalState, LocalZip, PermStreet, PermCity, PermState, PermZip)
Office (OfficeNum, Phone)
Faculty (FacultyNum, LastName, FirstName, Street, City, State, Zip,
OfficeNum, CurrentRank, StartDate, DepartmentCode)
FK OfficeNum
Office
FK DepartmentCode
→
→
Department
Major (MajorNum, Description, DepartmentCode)
FK DepartmentCode
→
Department
Advises (StudentNum, MajorNum, FacultyNum)
FK StudentNum
→
Student
FK FacultyNum
→
Faculty
→
FK MajorNum
Major
FIGURE A-9
DBDL for User View 2
The result of merging these tables into the cumulative design is shown in Figure A-10. The tables
Student, Faculty, Major, and Department are merged with the existing tables with the same primary keys and
with the same names. Nothing new is added to the Department table in the process, but the other tables
receive additional attributes. In addition, the Faculty table also receives two foreign keys, OfficeNum and
DepartmentCode. The Major table receives one foreign key, DepartmentCode. The Advises and Office tables
are new and thus are added directly to the cumulative design.
