CS 4604 Homework #2

Date Assigned: September 18, 2002
Date Due: September 25, 2002, in class, before class starts
  1. (20 points) Do Exercise 2.1.3 (all parts, obviously) from your textbook but in ODL, i.e., even though the question appears in the E/R chapter, we are interested in seeing an ODL schema, not an E/R diagram. Notice, in particular the constraint that the team captain is one of the players themselves. Explain how you would enforce this in your design (In other words, what aspect of your design will prevent a non-player to be named as a captain?) Also notice that the favorite players of a fan might not belong to his/her favorite team(s). Finally, be sure to include inverse relationships!

  2. (10 points; 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.

  3. (10 points; from the Fall 2000 midterm) Design an E/R diagram for the following situation: We wish to model airports, airlines, cities, and flights in the United States. Each airport is located in a unique city. A city can have many airports (e.g., Washington DC has three airports). Each city must have at least one airport (we will model only major cities in our database, so this condition is realistic). Assume that airport names are unique and that city names are also unique. Airlines are the companies that run flights (e.g., United, Delta, etc.). An airline is uniquely distinguished by its name. Every flight is operated by a unique airline. Flights have numbers but these numbers are unique only within an airline (e.g., both Delta and SAS have flights numbered 3785). Flights run from one airport to another, perhaps with stopovers and brief landings (for fueling purposes). For each flight, we wish to store its airport of origin and airport of destination, and also the origin and destination times (assume that all flights fly on all days, so we don't have to record days). In addition, information needs to be stored about the airports where stopovers and brief landings are made (and the times these are made) for each flight. Try to model as much as you can and write Notes, if necessary. Failing to write Notes implies that you E/R diagram is exact.

  4. (10 points) Draw an E/R diagram for the following situation describing a database application for a manufacturing parts company: The company manufactures automobile parts, which are of two kinds - monolithic parts and composite parts. Monolithic parts are parts that are not made up of other parts (they are indivisible). Examples of monolithic parts are spokes, nuts, bolts, washers, and screws. Composite parts are those that are made up of other parts. An example of a composite part would be, for example, an engine that contains several parts such as pistons, cylinders, rods, links, and cranks, assembled into one big unit (notice that the piston itself could be composed of more parts, and so on).

    Each part has a name, and a unique number (assigned by the company for identification purposes). In addition, monolithic parts have a weight attached to them (in pounds), while composite parts have an assembly procedure attached to them (which identifies the steps needed to assemble the composite part). Underline key attributes, double border weak sets (if applicable), and do not forget to put arrows!

Return Home