Entity-relationship Model and E-r Diagram

Only available on StudyMode
  • Download(s) : 179
  • Published : May 6, 2011
Open Document
Text Preview
Exercises

9

address driver-id person name owns license

model year car report-number location date

driver

participated

accident

damage-amount

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 superkey. Answer: A superkey is a set of one or more attributes that, taken collectively, allows 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 possible 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.1 2.3 Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted. Answer: See Figure 2.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 title. Further, the enrollment of students in courses and grades awarded to students 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

insurance date−admitted name ss# patients test−log Dr−Patient date−checked−out

test_id

test

performed_by

doctors

test_name

date

time

dss# result

name

specialization

Figure 2.2
sid name time

E-R diagram for a hospital.
secno room iid name

student

enrols year

course− offerings semester is offered

teaches

instructor

dept

title

program

grade

syllabus prerequisite requires maincourse credits course

courseno

title

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 exams of different course offerings.

Exercises

11

sid

name

time

secno

room

student

takes marks year

course− offerings semester

courseno

program

eid name

exam place

time

Figure 2.4

E-R diagram for marks database.

a. Construct an E-R diagram that models exams as entities, and uses a ternary relationship, for the above database. b. Construct an alternative E-R diagram that uses only a binary relationship between students and course-offerings. Make sure that only one relationship exists between a particular student and course-offering pair, yet you can represent the marks that a student gets in different exams of a course offering. Answer: a. See Figure 2.4 b. See Figure 2.5 2.6 Construct appropriate tables for each of the E-R diagrams in Exercises 2.2 to 2.4. Answer: a. Car insurance tables: person (driver-id, name, address) car...
tracking img