Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model
1
CHAPTER 3: DATA MODELING USING THE ENTITY-RELATIONSHIP (ER) MODEL
Answers to Selected Exercises
3.16 – Consider the following set of requirements for a UNIVERSITY database that is used to
keep track of students’ transcripts. This is similar but not identical to the database shown in
Figure 1.2:
(a) The university keeps track of each student’s name, student number, social security
number, current address and phone, permanent address and phone, birthdate, sex, class
(freshman, sophomore, …, graduate), major department, minor department (if any), and
degree program (B.A., B.S., …, Ph.D.). Some user applications need to refer to the city,
state, and zip of the student’s permanent address, and to the student’s last name. Both
social security number and student number have unique values for each student.
(b) Each department is described by a name, department code, office number, office phone,
and college. Both name and code have unique values for each department.
(c) Each course has a course name, description, course number, number of semester hours,
level, and offering department. The value of course number is unique for each course.
(d) Each section has an instructor, semester, year, course, and section number. The section
number distinguishes different sections of the same course that are taught during the same
semester/year; its values are 1, 2, 3, …, up to the number of sections taught during each
semester.
(e) A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3,
4 for F, D, C, B, A, respectively).
Design an ER schema for this application, and draw an ER diagram for that schema.
Specify key attributes of each entity type and structural constraints on each relationship type.
Note any unspecified requirements, and make appropriate assumptions to make the
specification complete.
Answer:
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
3.23 – Consider the ER diagram shown in Figure 7.21 for part of a BANK database. Each
bank can have multiple branches, and each branch can have multiple accounts and loans.
(a) List the strong (nonweak) entity types in the ER diagram.
(b) Is there a weak entity type? If so, give its name, its partial key, and its identifying
relationship.
(c) What constraints do the partial key and the identifying relationship of the weak entity type
specify in this diagram?
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model
7
(d) List the names of all relationship types, and specify the (min,max) constraint on each
participation of an entity type in a relationship type. Justify your choices.
(e) List concisely the user requirements that led to this ER schema design.
(f) Suppose that every customer must have at least one account but is restricted
to at most two loans at a time, and that a bank branch cannot have more than
1000 loans. How does this show up on the (min,max) constraints?
Answer:
(a) Entity types: BANK, ACCOUNT, CUSTOMER, LOAN
(b) Weak entity type: BANK-BRANCH. Partial key: BranchNo.
Copyright © 2016 Pearson Education, Inc., Hoboken NJ
3.24 – Consider the ER diagram in Figure 7.22. Assume that an employee may work in up to
two departments or may not be assigned to any department. Assume that each department
must have one and may have up to three phone numbers. Supply (min, max) constraints on
this diagram. State clearly any additional assumptions you make. Under what conditions
would the relationship HAS_PHONE be redundant in this example?
Answer:
Assuming the following additional assumptions:
– Each department can have anywhere between 1 and 10 employees.
The resulting ER Diagram will have the (min, max) constraints shown in Figure A.
Relationship HAS-PHONE would be redundant under the following conditions:
– Each employee is assigned all of the phones of each department that he/she works in.
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model
9
– An employee cannot have any other phones outside the departments he/she works is.
EMPLOYEE
PHONE
3.25 – Consider the ER diagram in Figure 7.23. Assume that a course may or may not use a
textbook, but that a text by definition is a book that is used in some course. A course may not
use more than five books. Instructors teach from two to four courses. Supply (min, max)
constraints on this diagram. State clearly any additional assumptions you make. If we add
the relationship ADOPTS between INSTRUCTOR and TEXT, what (min, max) constraints
would you put on it? Why?
Answer:
Assuming the following additional assumptions:
– Each course is taught by exactly one instructor.
3.26 – Consider an entity type SECTION in a UNIVERSITY database, which describes the
section offerings of courses. The attributes of SECTION are SectionNumber, Semester,
Year, CourseNumber, Instructor, RoomNo (where section is taught), Building (where section
is taught), Weekdays (domain is the possible combinations of weekdays in which a section
can be offered {MWF, MW, TT, etc.}). Assume tat SectionNumber is unique for each course
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model
10
within a particular semester/year combination (that is, if a course if offered multiple times
during a particular semester, its section offerings are numbered 1, 2, 3, etc.). There are
several composite keys for SECTION, and some attribute sare components of more than one
key. Identify three composite keys, and show how they can be represented in an ER schema
diagram.
Answer:
3.27 – Cardinality ratios often dictate the detailed design of a database. The cardinality ratio
depends on the real-world meaning of the entity types involved and is defined by the specific
application. For the binary relationships below, suggest cardinality ratios based on common-
sense meaning of the entity types. Clearly state any assumptions you make.
Entity 1
Cardinality Ratio
1.
Student
2.
Student
3.
ClassRoom
4.
Country
5.
Course
6.
Item (that can be
found in an order)
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model
13
b. Each department should be to able add or delete courses and hire or terminate
faculty.
c. Each instructor should be able to assign or change a grade to a student for a course.
Note: Some of these functions may be spread over multiple classes.
Answer:
COURSE
CourseName
Description
Department
SECTION
Instructor
Semester
Year
CourseNumber
SectionNumber: {1,2,…}
GRADE REPORT
StudentNumber
SectionNumber
LetterGrade: {A,B,C,D,F}
NumberGrade: {0,1,2,3,4}
PREREQUISITE
CourseNumber
PrerequisiteNumber
STUDENT
Name:
<Name>
StudentNumber
SocialSecNumber
CurrentAddress:
<Address>
CurrentPhone:
<Phone>
PermanentAddress:
<Address>
PermanentPhone:
<Phone>
Birthdate: Date
Sex: {M,F}
Class: {F,So,J,Se,G}
MajorDepartment
MinorDepartment
Degree: {BA,BS,…}
compute_gpa
add_major
drop_minor
DEPARTMENT
Name: {CS,…}
DepartmentCode: {Codes}
OfficeNumber
OfficePhone:
<Phone>
College: {Colleges}
add_course
delete_course
hire_faculty
fire_faculty
INSTRUCTOR
<Phone>
DepartmentCode
assign_grade
Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model
14