Data Modeling with the Entity-Relationship Model - Database Processing: Fundamentals, Design, and Implementation - page 213

parent entity. A professor can be a chair of zero or one departments, and a department must
have exactly one professor as chair.
With the Chairs/Chaired By relationship, the attribute Chairperson is no longer needed in
DEPARTMENT, so it is removed. Normally, a chairperson has his or her office in the department
office; if this is the case, Phone, Building, and Room in DEPARTMENT duplicate Phone, Building,
and OfficeNumber in PROFESSOR. Consequently, it might be possible to remove Phone, Building,
and Room from DEPARTMENT. However, a professor may have a different phone from the official
department phone, and the professor may also have an office outside of the department's office.
Because of this possibility, we will leave Phone, Building, and Room in DEPARTMENT.

The Department/Major Report
Figure 5-48 shows a report of a department and the students who major in that department.
This report indicates the need for a new entity called STUDENT. Because students are not
ID-dependent on departments, the relationship between DEPARTMENT and STUDENT
is nonidentifying, as shown in Figure 5-49. We cannot determine the minimum cardinality
from Figure 5-48, but assume that interviews with users indicate that a STUDENT must have
MAJOR, but no MAJOR need have any students. Also, using the contents of this report as a
guide, attributes StudentNumber, StudentName, and Phone are placed in STUDENT.
There are two subtleties in this interpretation of the report in Figure 5-48. First, observe
that Major's Name was changed to StudentName when the attribute was placed in STUDENT.
This was done because StudentName is more generic. Major's Name has no meaning outside
the context of the Major relationship. Additionally, the report heading in Figure 5-48 has an
ambiguity. Is the phone number for the department a value of DEPARTMENT.Phone or a value
of PROFESSOR.Phone? The team needs to investigate this further with the users. Most likely, it
is a value of DEPARTMENT.Phone.

Figure 5-48
Highline University Sample
Department Student Report

Student Major List
Information Systems Department

Brammer, Nathaniel D
Student Number
12345
48127
37512

Phone: [phone]

Chairperson:

Major's Name
Jackson, Robin R.
Lincoln, Fred J.
Madison, Janice A.

Phone
237-8713
237-8713
237-8713

Figure 5-49
Data Model with
STUDENT Entity

COLLEGE DEPARTMENT PROFESSOR

CollegeName ProfessorName

DepartmentName

Chairs / Chairs By

DeanName
Phone
Building
Room

Building
OfficeNumber
Phone

Phone
TotalMajors
Building
Room

APPOINTMENT

Major

Title
Terms

STUDENT

StudentNumber

StudentName
Phone