Databases Reference
In-Depth Information
Because advisees and students are the same, rename the Advisee entity to Student and use the StudentNum
attribute as the primary key rather than AdvisorNum. Your efforts yield the following tables and primary keys:
321
Department (DepartmentCode,
Faculty (FacultyNum,
Student (StudentNum,
Next, add the remaining attributes to the tables:
Department (DepartmentCode, DepartmentName)
Faculty (FacultyNum, LastName, FirstName, Street, City, State,
Zip, OfficeNum, Phone, CurrentRank, StartDate, DepartmentCode)
Student (StudentNum, LastName, FirstName, LocalStreet,
LocalCity, LocalState, LocalZip, PermStreet, PermCity,
PermState, PermZip, (MajorNum, Description,
DepartmentCode, FacultyNum, LastName, FirstName) )
The DepartmentCode attribute is included in the Faculty table because there is a one-to-many relation-
ship between departments and faculty members. Because a student can have more than one major, the infor-
mation about majors (number, description, department, and the number and name of the faculty member who
advises this student in this major) is a repeating group.
Because the key to the repeating group in the Student table is MajorNum, removing this repeating group
yields the following:
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)
