CPS 430/542: Database Modeling (E/R and ODL) Practice Problems

Database Modeling (E/R and ODL) Practice Problems

Coverage: [FCDB] Chapter 2 and §§4.1-4.3

In Example 2.18 (pp. 43-44) from [FCDB], when connecting entity set [Contracts] is supposed to be a relationship among a [Star], a [Movie], and any number of [Studios], why do not the authors use a ternary (three-way) relationship without any arrows to connect the three entity sets?
In Figure 2.20 (p. 55) from [FCDB] from [FCDB], why does not [[Crews]] inherit from [Studios]? (if we were to do that, then [[Crews]] would no longer be a weak entity set)

(T/F) 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
God of Small Things        | Arundhati Roy
The Tipping Point          | Malcolm Gladwell
The Q and A                | Vikas Swarup
---------------------------------------------

(T/F) 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.
(courtesy Ullman and Widom; exercise 2.4.4b from [FCDB]) Provide an E/R diagram which models 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 which models 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.
(courtesy T.M. Murali) Provide an E/R diagram which models the following situation: Doctors prescribe drugs for patients. A given doctor can prescribe many drugs for a certain patient. Many doctors can treat a patient. Many doctors can prescribe the same drug to the same patient. A prescription can involve more than one patient (e.g., a mother and her baby), more than one drug, but is associated with a unique doctor.
(T/F) In the following E/R diagram, one of the three relationships is redundant.
(T/F) In the following E/R diagram, one of the three relationships is redundant.
(T/F) 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 `push 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 cannot 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?
[FCDB] Exercise 2.1.4 (p. 37).
Consider the entity sets [Students] and [Courses]. Assume that there is a many-many relationship <takes> from [Students] to [Courses]. (T/F) This means that every Student has to be enrolled in one or more courses.
[FCDB] Exercise 2.3.2 (p. 54).
Each State has exactly two Senators (neither more nor less). A State has a lot of attributes like land area, capital city, latitude, longitude, altitude, and population. A Senator has several attributes like college (from which s/he graduated), age, name, address, and automobile. How would you model this in E/R and ODL? (Hint: You cannot 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 <Fan> team_teams
   inverse Fan: favorite_teams;
};

(courtesy Ullman). Provide an E/R diagram which models 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 Louisiana 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). Provide an E/R diagram and an ODL schema which models 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 T.M. Murali) Provide an E/R diagram which models the following situation describing a database application for a manufacturing parts company: The company manufactures automobile parts, which are of two kinds: primitive parts and composite parts. Primitive parts are parts which are not made up of other parts (they are indivisible). Examples of primitive parts are spokes, nuts, bolts, washers, and screws. Composite parts are those which are made up of other parts. An example of a composite part is an engine which contains several parts such as pistons, cylinders, rods, links, and cranks, assembled into one big unit (notice that the piston itself could be a composite part, and so on). Each part has a name, and a unique number (assigned by the company for identification purposes). Primitive parts have a cost, while composite parts have textual assembly instructions.
(courtesy Ullman). Provide an E/R diagram which models 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.
Design an E/R diagram to depict the structure of the US Congress. The US Congress is bicameral meaning that it is composed of two houses: the House of Representatives and the Senate. Every state has exactly two senators (a junior and a senior member), but a variable number of representatives (exactly one per district). No senator can represent more than one state at a time. Likewise, no representative can serve more than one district at a time. Every state has a variable number of districts (dependent on population) and every state has at least one district (in some states, e.g., Delaware, the district boundaries are the state's borders). Districts have numbers (e.g., district 1). A given congressperson (senator or representative) cannot serve in both houses at a given time. Congresspeople have names and e-mail addresses. Every congressperson is a member of exactly one political party which has a name. Exactly one member of the House is designated as Speaker of the House. Lastly, congresspeople belong to congressional committees which have names and sponsor bills, which also have names.

Your E/R diagram should model as much of the information above as possible. Use `notes:' to capture any aspects and constraints which cannot be modeled using E/R notation.
(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 the `one' in a `many-one' relationship means `at most one,' and not `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) Provide an E/R diagram and ODL design which models 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?
1. Only the first
2. Only the first and second
3. Only the second
4. All of the three
(T/F) 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).
(T/F) [FCDB] states that the supporting relationships from a weak entity set should be many-one (see §2.4.2, pp. 56-57). It is also required that these relationships be binary.
(T/F) 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]. (T/F) Then, [B] can be safely removed if it does not have any attributes and if it does not 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:
1. [Courses], [Students], and [Semesters] are connected by a three-way relationship <R>. <R> is many-many in all directions.
2. Same as the first design, except that there is now an arrow from <R> entering [Semesters].
3. [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.'
In previous problem, give two limitations of the third design which are not present in the first design.