Practice Problems on E/R Diagrams

These are provided here for your convenience. It is not required that you solve these problems; but doing them is good preparation for your assignments, the midterm, and your project. Note that we won't be providing answers for all of the questions (although we might discuss a few of them in class).

In Example 4.18 on page 145 of your textbook, when Contracts is supposed to be a relationship among a star, a movie and any number of studios, why don't the authors use a normal ternary (three-way) relationship to connect the three entity sets (i.e., one without any arrows)?
Solve Exercise 4.2.5 on page 147 of your textbook (all three parts).
In Figure 4.20 on page 153 of your textbook, why doesn't Crews inherit from Studios? If we were to do so, then Crews would not be a weak entity set anymore.

(True/False) If R is declared to be a many-many relationship from Books to Authors, the following relationship set (for R) is illegal because each Book has only one Author and each Author has only one Book.


--------------------------------------
Books               | Authors
--------------------------------------
Jurassic Park       | Michael Crichton
Life of Pi	    | Yann Martel
God of Small Things | Arundhati Roy
--------------------------------------

(True/False) If a entity set has two keys (a primary key and a secondary key), then these two keys cannot have any attributes in common.
Draw an E/R diagram for the following situation: There are three entity sets - Leagues, Teams and Players. League names are unique. No league has two teams with the same name. No team has two players with the same number. However, there can be players with the same number on different teams, and there can be teams with the same name in different leagues.
(Courtesy Ullman). Provide an E/R diagram for the following situation: Trains are either local trains or express trains, but never both. A train has a unique number and an engineer. Stations are either express stops or local stops, but never both. A station has a name (unique) and an address. All local trains stop at all stations. Express trains stop only at express stations. For each train and each station the train stops at, there is a time.
(True/False) In the following E/R diagram, one of the three relationships is redundant.
(True/False) In the following E/R diagram, one of the three relationships is redundant.
(True/False) Referential integrity constraints are a weaker form of many-one constraints (or one-one constraints).
Suppose R(A, B) is a two-attribute relationships. The inverse of R is a relationship S(B, A) such that a tuple (b, a) is present in S if and only if the tuple (a, b) is present in R. Are the following two statements saying the same thing? (i) R is a relationship that is an inverse of itself, (ii) R is a relationship from one entity set to the same entity set with arrows entering the set in both directions.
When we do "pushing out" to remove a multiway relationship, i.e., we replace it by binary relationships, why do we introduce many-one relationships from the new connecting entity set to the original entity sets? Why can't these new relationships be many-many?
Give real-life examples for the following E/R situations: (i) one entity set E and a one-one relationship R from E to itself, (ii) one entity set E and a many-many relationship R running from E to itself, (iii) one entity set E and a many-one relationship R running from E to itself.
Consider the entity sets Students and Courses and the many-many relationship "Takes" from Students to Courses. (True/False) This means that every Student *has* to be enrolled in one or more courses.
Each State has exactly two Senators (neither more, neither less). A State has many attributes such as land area, capital city, latitude, longitude, altitude, population. A Senator has many attributes such as college (from which he/she graduated), age, name, address, car etc. How would you model this in an E/R diagram? (Hint: You can't make Senators to be an attribute of State because there is too much information to cram into State and a Senator can have existence by itself, regardless of the State to which he is `tied to'.)
(Courtesy Ullman). Provide an E/R diagram for the following situation: We wish to model cities, counties and states in the US. For states, we wish to record the name, population, and state capital (which would be a city). For counties, we wish to record the name, the population, and the state in which it is located. For cities, we wish to record the name, the population, the state in which it is located and the county/counties in which it is located. Names of states are unique. Names of counties are only unique within a state (e.g. 26 states have counties called "Washington"), and cities are likewise unique only within a state (e.g., there is a city called "Lafayette" in Lousiana as well as Indiana). Some counties and cities have the same name, even within a state (example: San Francisco). Almost all cities are located within a single county, but some (e.g., New York City) extend over several counties.
(Courtesy Ullman). Design an E/R diagram for the following situation: We wish to model crimes and punishments. crimes have a name and a degree (e.g., "murder in the first degree"); together, they form a key. A crime is either a felony or a misdemeanor. Punishments are either fines or jail sentences. A fine has an associated amount, and a jail sentence has a minimum and a maximum number of years. The punishment for a misdemeanor is always a fine. The punishment for a felony can be either a jail sentence, a fine or both.
(Courtesy Ullman). Design an E/R diagram for the following situation: Land masses are either islands or continents. All land masses have a name and an area; the name is the key. Some continents are connected to each other, e.g., Asia is connected to Europe. No island is connected to any other island or to a continent. Bodies of water are either oceans or straits. A body of water has a name (the key) and an area. Islands may be either located in one ocean (e.g., Hawaii is in the Pacific Ocean) or separated from a continent by a strait (e.g., Honshu is separated from Asia by the "Sea of Japan"). You should not assume that a strait is adjacent to only one continent or to only one island.
(Courtesy Widom) Suppose there are three entity sets E, E1 and E2, and there are many-one relationships from E1 to E and from E to E2. Prove that there exists a many-one relationship from E1 to E2. Recall that a many-one relationship means there is ``at most one,'' not that there is ``exactly one.''
(Courtesy Widom) The following E/R diagram is an attempt to design a database in which a store keeps a permanent record of customers (identified by social-security numbers) and the items they buy (identified by a unique item ID assigned by the store). However, there is a problem with this design, related to our ability to recover the history of, say, orders by a particular customer for a particular item. Your task is to identify the problem and to propose a solution.
(Courtesy Widom) Give an E/R diagram that describes the following kinds of objects. There are `people', with a name and an address. Some people are single, others are married. Married people have a spouse, which the database must indicate. Single people are either never-previously-married, widowed or divorced. For those who have been previously married but are now single, we want the database to indicate all their previous spouses. Do not include any useless subclasses in your design.
(Courtesy Ullman) Consider an E/R diagram involving a four-way relationship between four entity sets A, B, C and D. There are arrows pointing to only the sets C and D. Below are three possible relationship sets for this diagram:
```
-----------------
A  | B  | C  | D
-----------------
a1 | b1 | c1 | d1
a1 | b1 | c1 | d2
-----------------
```
```
-----------------
A  | B  | C  | D
-----------------
a1 | b1 | c1 | d1
a1 | b1 | c2 | d2
-----------------
```
```
-----------------
A  | B  | C  | D
-----------------
a1 | b1 | c1 | d1
a1 | b2 | c1 | d1
-----------------
```
You may assume that different symbols stand for different values, e.g., d1 is definitely not the same as d2. Which of the above could not be the relationship set?
- Only the first
- Only the first and second
- Only the second
- All of the three
(True/False) A ternary (three-way) relationship between three entity sets A,B, and C is equivalent to three binary relationships (between A and B, B and C, A and C) and can be so replaced whenever necessary.
(True/False) The textbook states that the supporting relationships for a weak entity set should be many-one. It is also required that these relationships be binary.
(True/False) If R is a one-one relationship from entity set A to entity set B, and R has an attribute (x) associated with it, we could move x to either of the two sets without creating a new entity set for it.
Consider that there are three entity sets A, B and C and two relationships - R1 and R2. R1 is one-one from A to B. R2 is one-one from B to C. (True/False) Then, B can be safely removed if it doesn't have any attributes and if it doesn't participate in any other relationships.
Consider three entity sets Courses, Students and Semesters. In this question, we would like to discuss the relative merits/demerits of the following three designs:
- Courses, Students and Semesters are connected by a three-way relationship R. R is many-many in all directions.
- Same as the first design, except that there is now an arrow from R entering Semesters.
- Courses and Students are connected by a relationship R1; Courses and Semesters are connected by a relationship R2. Both R1 and R2 are many-many.
Discuss their differences in English/practical terms such as "A student can only take one course at a time."
In the previous question, give two limitations of the third design that are not present in the first design.
Consider the following E/R diagram. Is this the same as using just a single one-one relationship run between the two entity sets? Why? Why not?

Last modified: Wed Feb 27 10:35AM EST 2012