Download Entity Relationship Model: Exercises and Examples and more Study Guides, Projects, Research Managerial Economics in PDF only on Docsity!
Exercises 9
person owns car
participated (^) accident
address
damage-amount
model
name license year
report-number date
location
driver-id
driver
Figure 2.1 E-R diagram for a Car-insurance company.
Exercises
2.1 Explain the distinctions among the terms primary key, candidate key, and su- perkey. Answer: A superkey is a set of one or more attributes that, taken collectively, al- lows us to identify uniquely an entity in the entity set. A superkey may contain extraneous attributes. If K is a superkey, then so is any superset of K. A superkey for which no proper subset is also a superkey is called a candidate key. It is pos- sible that several distinct sets of attributes could serve as candidate keys. The primary key is one of the candidate keys that is chosen by the database designer as the principal means of identifying entities within an entity set. 2.2 Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. Answer: See Figure 2.
2.3 Construct an E-R diagram for a hospital with a set of patients and a set of medi- cal doctors. Associate with each patient a log of the various tests and examina- tions conducted. Answer: See Figure 2.
2.4 A university registrar’s office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and ti- tle. Further, the enrollment of students in courses and grades awarded to stu- dents in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints. Answer: See Figure 2.3. In the answer given here, the main entity sets are student, course, course-offering ,
10 Chapter 2 Entity Relationship Model
specialization
doctors
test_name (^) date time result
ss#
name
name
patients Dr−Patient
insurance date−admitted
date−checked−out
dss#
test−log
test_id (^) test performed_by
Figure 2.2 E-R diagram for a hospital.
program
course− offerings
dept title
course
courseno
title
credits
syllabus
prerequisite
maincourse
requires
secno
is offered
student
name
grade
teaches
year semester
time room
enrols
sid
instructor
iid name
Figure 2.3 E-R diagram for a university.
and instructor. The entity set course-offering is a weak entity set dependent on course. The assumptions made are : a. a class meets only at one particular place and time. This E-R diagram cannot model a class meeting at different places at different times. b. There is no guarantee that the database does not have two classes meeting at the same place and time.
2.5 Consider a database used to record the marks that students get in different ex- ams of different course offerings.
12 Chapter 2 Entity Relationship Model
course− offerings
secno
courseno
program
exam
name place
time
marks examof
student
name
year semester
time room
takes
sid
Figure 2.5 Another E-R diagram for marks database.
c. University registrar’s tables: student (student-id, name, program) course (courseno, title, syllabus, credits) course-offering (courseno, secno, year, semester, time, room) instructor (instructor-id, name, dept, title) enrols (student-id, courseno, secno, semester, year, grade) teaches (courseno, secno, semester, year, instructor-id) requires (maincourse, prerequisite)
2.7 Design an E-R diagram for keeping track of the exploits of your favourite sports team. You should store the matches played, the scores in each match, the players in each match and individual player statistics for each match. Summary statis- tics should be modeled as derived attributes. Answer: See Figure 2.
2.8 Extend the E-R diagram of the previous question to track the same information for all teams in a league. Answer: See Figure 2.7 Note that a player can stay in only one team during a season.
2.9 Explain the difference between a weak and a strong entity set. Answer: A strong entity set has a primary key. All tuples in the set are distin- guishable by that key. A weak entity set has no primary key unless attributes of the strong entity set on which it depends are included. Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity
Exercises 13
date matchid^ stadium
match player
name age
played
season_score
opponent
own _score (^) opp_score score
Figure 2.6 E-R diagram for favourite team statistics.
match player
name age
played
season_score
date
matchid stadium^ score
team
score team_played player_of
result
name ranking
Figure 2.7 E-R diagram for all teams statistics.
set. Tuples within each partition are distinguishable by a discriminator, which is a set of attributes.
2.10 We can convert any weak entity set to a strong entity set by simply adding ap- propriate attributes. Why, then, do we have weak entity sets? Answer: We have weak entities for several reasons:
- We want to avoid the data duplication and consequent possible inconsis- tencies caused by duplicating the key of the strong entity.
- Weak entities reflect the logical structure of an entity being dependent on another entity.
- Weak entities can be deleted automatically when their strong entity is deleted.
- Weak entities can be stored physically with their strong entities.
2.11 Define the concept of aggregation. Give two examples of where this concept is useful.
Exercises 15
basketID
email
basket-of ISBN
code
name
URL
address name address phone
URL
publisher
written-by published-by
title
price
number
book
contains
phone
customer
address name phone
stocks (^) warehouse
address number
author
year
shopping-basket
Figure 2.10 E-R diagram for Exercise 2.12.
2.12 Consider the E-R diagram in Figure 2.10, which models an online bookstore.
a. List the entity sets and their primary keys. b. Suppose the bookstore adds music cassettes and compact disks to its col- lection. The same music item may be present in cassette or compact disk format, with differing prices. Extend the E-R diagram to model this addi- tion, ignoring the effect on shopping baskets. c. Now extend the E-R diagram, using generalization, to model the case where a shopping basket may contain any combination of books, music cassettes, or compact disks.
Answer:
2.13 Consider an E-R diagram in which the same entity set appears several times. Why is allowing this redundancy a bad practice that one should avoid whenever possible? Answer: By using one entity set many times we are missing relationships in
16 Chapter 2 Entity Relationship Model
ss# name
takes^ class
ss# (^) name dept
student
student
plays (^) sport
courseno
teamname
Figure 2.11 E-R diagram with entity duplication.
the model. For example, in the E-R diagram in Figure 2.11: the students taking classes are the same students who are athletes, but this model will not show that.
2.14 Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the single entity set exam , with attributes course-name , section-number , room-number , and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the exam entity set, as
- course with attributes name , department , and c-number
- section with attributes s-number and enrollment , and dependent as a weak entity set on course
- room with attributes r-number , capacity , and building a. Show an E-R diagram illustrating the use of all three additional entity sets listed. b. Explain what application characteristics would influence a decision to in- clude or not to include each of the additional entity sets.
Answer: a. See Figure 2. b. The additional entity sets are useful if we wish to store their attributes as part of the database. For the course entity set, we have chosen to include three attributes. If only the primary key ( c-number ) were included, and if courses have only one section, then it would be appropriate to replace the course (and section ) entity sets by an attribute ( c-number ) of exam. The reason it is undesirable to have multiple attributes of course as attributes of exam is that it would then be difficult to maintain data on the courses, particularly if a course has no exam or several exams. Similar remarks apply to the room entity set.
