address driver-id person name owns license
model year car report-number location date
E-R diagram for a Car-insurance company.
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 ofﬁce 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 identiﬁcation 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 ofﬁce. 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,
Entity Relationship Model
insurance date−admitted name ss# patients test−log Dr−Patient date−checked−out
sid name time
E-R diagram for a hospital.
secno room iid name
course− offerings semester is offered
syllabus prerequisite requires maincourse credits course
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.
takes marks year
course− offerings semester
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...