Abraham Silberschatz, Henry F. Korth, S. Sudarshan
Chapter 2
Entity-Relationship Model - all with Video Answers
Educators
Chapter Questions
Explain the distinctions among the terms primary key, candidate key, and superkey.
Check back soon!
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.
Jon Southam
Numerade Educator
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.
Check back soon!
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.
Check back soon!
Consider a database used to record the marks that students get in different exams of different course offerings.
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.
Check back soon!
Construct appropriate tables for each of the E-R diagrams in Exercises 2.2 to 2.4.
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 statistics should be modeled as derived attributes
Kris Bright
Numerade Educator
Extend the E-R diagram of the previous question to track the same information for all teams in a league.
Kris Bright
Numerade Educator
Explain the difference between a weak and a strong entity set.
Sisi Gao
Numerade Educator
We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets?
James Kiss
Numerade Educator
Define the concept of aggregation. Give two examples of where this concept is useful.
KT
Kristen Tumminia
Numerade Educator
Consider the E-R diagram in Figure 2.29, 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 collection. The same music item may be present in cassette or compact disk format, with differing prices. Extend the E-R diagram to model this addition, 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.
Check back soon!
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?
Check back soon!
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 include or not to include each of the additional entity sets.
Check back soon!
When designing an E-R diagram for a particular enterprise, you have several alternatives from which to choose.
a. What criteria should you consider in making the appropriate choice?
b. Design three alternative E-R diagrams to represent the university registrar's office of Exercise 2.4. List the merits of each. Argue in favor of one of the alternatives.
Sarah Mccrumb
Numerade Educator
An E-R diagram can be viewed as a graph. What do the following mean in terms of the structure of an enterprise schema?
a. The graph is disconnected.
b. The graph is acyclic.
Check back soon!
In Section 2.4.3, we represented a ternary relationship (Figure 2.30a) using binary relationships, as shown in Figure 2.30b. Consider the alternative shown in Figure 2.30c. Discuss the relative merits of these two alternative representations of a ternary relationship by binary relationships.
Check back soon!
Consider the representation of a ternary relationship using binary relationships as described in Section 2.4.3 (shown in Figure 2.30b.)
a. Show a simple instance of $E, A, B, C, R_A, R_B$, and $R_C$ that cannot correspond to any instance of $A, B, C$, and $R$.
b. Modify the E-R diagram of Figure $2.30 \mathrm{~b}$ to introduce constraints that will guarantee that any instance of $E, A, B, C, R_A, R_B$, and $R_C$ that satisfies the constraints will correspond to an instance of $A, B, C$, and $R$.
c. Modify the translation above to handle total participation constraints on the ternary relationship.
d. The above representation requires that we create a primary key attribute for $E$. Show how to treat $E$ as a weak entity set so that a primary key attribute is not required.
Check back soon!
A weak entity set can always be made into a strong entity set by adding to its attributes the primary key attributes of its identifying entity set. Outline what sort of redundancy will result if we do so.
Check back soon!
Design a generalization-specialization hierarchy for a motor-vehicle sales company. The company sells motorcycles, passenger cars, vans, and buses. Justify your placement of attributes at each level of the hierarchy. Explain why they should not be placed at a higher or lower level.
Check back soon!
Explain the distinction between condition-defined and user-defined constraints. Which of these constraints can the system check automatically? Explain your answer.
KF
Kyle Findley
Numerade Educator
Explain the distinction between disjoint and overlapping constraints.
Lucas Finney
Numerade Educator
Explain the distinction between total and partial constraints.
Farnaz Mohseni
Numerade Educator
Figure 2.31 shows a lattice structure of generalization and specialization. For entity sets $A, B$, and $C$, explain how attributes are inherited from the higherlevel entity sets $X$ and $Y$. Discuss how to handle a case where an attribute of $X$ has the same name as some attribute of $Y$.
Check back soon!
Draw the UML equivalents of the E-R diagrams of Figures 2.9c, 2.10, 2.12, 2.13 and 2.17 .
Check back soon!
Consider two separate banks that decide to merge. Assume that both banks use exactly the same E-R database schema-the one in Figure 2.22. (This assumption is, of course, highly unrealistic; we consider the more realistic case in Section 19.8.) If the merged bank is to have a single database, there are several potential problems:
- The possibility that the two original banks have branches with the same name
- The possibility that some customers are customers of both original banks
- The possibility that some loan or account numbers were used at both original banks (for different loans or accounts, of course)
For each of these potential problems, describe why there is indeed a potential for difficulties. Propose a solution to the problem. For your solution, explain any changes that would have to be made and describe what their effect would be on the schema and the data.
Jennifer Stoner
Numerade Educator
Reconsider the situation described for Exercise 2.26 under the assumption that one bank is in the United States and the other is in Canada. As before, the banks use the schema of Figure 2.22, except that the Canadian bank uses the social-insurance number assigned by the Canadian government, whereas the U.S. bank uses the social-security number to identify customers. What problems (beyond those identified in Exercise 2.24) might occur in this multinational case? How would you resolve them? Be sure to consider both the scheme and the actual data values in constructing your answer.
Srikar Katta
Numerade Educator