Database Reference
In-Depth Information
COLLEGE
DEPARTMENT
CollegeName
DepartmentName
DeanName
Phone
Building
Room
Chairperson
Phone
TotalMajors
Figure 5-45
Data Model for the College
Report in Figure 5-44
college. These data suggest that the data model should have COLLEGE and DEPARTMENT
entities with a relationship between them, as shown in Figure 5-45.
The relationship in Figure 5-45 is nonidentifying. This relationship is used because
DEPARTMENT is not ID-dependent and, logically, a DEPARTMENT is independent of a
COLLEGE. We cannot tell from the report in Figure 5-44 whether a department can belong to
many colleges. To answer this question, we need to ask the users or look at other forms and
reports.
Assume that we know from the users that a department belongs to just one college, and
the relationship is thus 1:N from COLLEGE to DEPARTMENT. The report in Figure 5-44 does
not show us the minimum cardinalities. Again, we must ask the users. Assume we learn from
the users that a college must have at least one department, and a department must be as-
signed to exactly one college.
The Department Report
The Department Report shown in Figure 5-46 contains departmental data along with a list of
the professors who are assigned to that department. This report contains data concerning the
department's campus address. Because these data do not appear in the DEPARTMENT entity
in Figure 5-45, we need to add them, as shown in Figure 5-47(a). This is typical of the data mod-
eling process. That is, entities and relationships are adjusted as additional forms, reports, and
other requirements are analyzed.
Figure 5-47(a) also adds the relationship between DEPARTMENT and PROFESSOR. We
initially model this as an N:M relationship because a professor might have a joint appointment.
The data modeling team must further investigate the requirements to determine whether
joint appointments are allowed. If not, the relationship can be redefined as a nonidentifying
1:N, as shown in Figure 5-47(b).
Another possibility regarding the N:M relationship is that some attribute about the
combination of a professor and a department is missing. If so, then an association pattern is
more appropriate. At Highline, suppose the team finds a report that describes the title and
employment terms for each professor in each department. Figure 5-47(c) shows an entity for
such a report, named APPOINTMENT. As you would expect from the association pattern,
APPOINTMENT is ID-dependent on both DEPARTMENT and PROFESSOR.
A chairperson is a professor, so another improvement on the model is to remove the
Chairperson data from DEPARTMENT and replace it with a chairperson relationship. This
has been done in Figure 5-47(d). In the Chairs/Chaired By relationship, the PROFESSOR is the
Figure 5-46
Highline University Sample
Department Report
Information Systems Department
College of Business
Chairperson:
Phone:
Campus Address:
Brammer, Nathaniel D
236-0011
Social Science Building, Room 213
Professor
Jones, Paul D.
Parks, Mary B
Wu, Elizabeth
Office
Social Science, 219
Social Science, 308
Social Science, 207
Phone
232-7713
232-5791
232-9112
Search WWH ::
Custom Search