CS 4604 Practice Problems (Chapter 2)

Practice Problems (Chap 2)

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.

In Example 2.18 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)?
In Figure 2.20, why doesn't Crews inherit from Studios? (if we were to do that, then it 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 data (for R) is illegal because each Book has only one Author and each Author has only one Book.


--------------------------------------
Books                      | Authors
--------------------------------------
Jurassic Park           | Michael Crichton
The Satanic Verses   | Salman Rushdie
God of Small Things | Arundhati Roy
--------------------------------------

(True/False) If a class/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).
Are the following two statements saying the same thing? (i) A is a relationship that is an inverse of itself, (ii) A is a relationship from one set to the same set with arrows entering the set in both directions.
When we do `pushing out' to remove a multiway relationship, 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 running 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.
How would you model courses and their prerequisite courses with an E/R diagram?
Consider the entity sets Students and Courses. Assume that there is a many-many relationship `takes' from Students to Courses. (True/False) This means that every Student *has* to be enrolled in one or more courses.
Exercise 2.2.5 (all three parts from your textbook).
Each State has exactly two Senators (neither more, neither less). A State has a lot of attributes like land area, capital city, latitude, longitude, altitude, population. A Senator has a lot of attributes liked college (from which he/she graduated), age, name, address, car etc. How would you model this in ODL and E/R? (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'.)

What are the mistakes in the following ODL schema?


interface Team
{
	attribute string name;
	attribute integer captain_index;
	attribute string color;
	attribute Player players[ ];
	relationship Set  team_teams
	inverse Fan: favourite_teams;
};

(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 Washington counties), and cities are likewise unique only within a state (e.g., there is a 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 and an ODL schema 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 ODL design 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&B, B&C, A&C) and can be so replaced whenever necessary (as in ODL, which does not allow ternary relationships).
(True/False) The textbook states that the identifying relationships from 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 like "A student can only take one course at a time" etc.
In Question 30, 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?