Transforming Data Models into Database Designs - Database Processing: Fundamentals, Design, and Implementation - page 252

COLLEGE

DEPARTMENT

PROFESSOR

CollegeName DepartmentName ProfessorFirstName

ProfessorLastName

Chairs/Chairs By

DeanFirstName
DeanLastName
Phone
Building
Room

Phone
TotalMajors
Building
Room

Building
OfficeNumber
Phone

APPOINTMENT

Major

Title
Terms

STUDENT

StudentNumber

Title
StudentFirstName
StudentLastName
HomeStreet
HomeCity
HomeState
HomeZip
Phone

Advises/Advised By

Figure 6-26
Data Model for Highline
University in Figure 5-52

Relational Representation of the highline University Data Model
Let's consider the data model we created for Highline University in Chapter 5. Our final data
model for Highline University is shown in Figure 6-26.
Using the principles we have discussed in this chapter, we can turn this into a relational
database design, and the resulting database design is a straightforward application of the
principles described in this chapter. The database design for Highline University is shown in
Figure 6-27.
You should review Figure 6-27 to ensure that you understand the representation of every
relationship. Note that there are actually two foreign key references to a DepartmentName
primary key column in STUDENT. The first is DepartmentName (FK), which is the foreign
key linking to the DepartmentName primary key in DEPARTMENT. This relationship has the
referential integrity constraint:

DepartmentName in STUDENT must exist in DepartmentName in DEPARTMENT

The second is ProfessorDepartment (FK), which is part of the composite foreign key
(ProfessorDepartment, ProfessorFirstName, ProfessorLastName). This foreign key links to the
primary key (DepartmentName, ProfessorFirstName, ProfessorLastName) in APPOINTMENT
and has the referential integrity constraint:

(ProfessorDepartment, ProfessorFirstName, ProfessorLastName) in STUDENT
must exist in (DepartmentName, ProfessorFirstName, ProfessorLastName) in APPOINTMENT

Note that we had to change DepartmentName in APPOINTMENT to ProfessorDepartment
in STUDENT because we cannot have two columns named DepartmentName in STUDENT
and we had already used DepartmentName as the foreign key linking to DEPARTMENT.
This illustrates that a foreign key does not have to have the same name as the primary key
it links to. As long as the referential integrity constraints are correctly specified, the foreign key
name can be whatever we want it to be.