Database Homework #3, Spring 2014

Guidelines for E/R diagrams: Make sure to indicate primary keys, multiplicity constraints (arrow types and/or labels on relationships), and weak entity sets (if any) with their supporting relationships. If you can't directly express a constraint in the diagram, include it in a note below the diagram. Also list any reasonable assumptions you make in constructing the diagram that are not specified in the problem.

Guidelines for database schemas: Make sure that each schema captures key constraints (primary keys and foreign keys) Identify any additional constraints or restrictions that you are not able to capture in the SQL itself but would have to be enforced in some other way. Explicitly note any situations where you combined relations involved in a many-one relationship.

1. (E/R diagram only)

   Do exercise 4.1.3 on page 139 of the textbook (make sure you have the third edition of the book). Assume that each fan can at have at most one favorite team, at most one favorite player, and at most one favorite color.

   Draw an E/R diagram for this database. Follow the E/R guidelines above.

2. (E/R diagram and schema)

   We want to design a database for a local garage that fixes cars. For each customer, we want to record their name (assume names are unique), their address, and their phone number. For each vehicle, we want to record the unique vehicle identification number (VIN), and the vehicle's make, model and year.

   The garage keeps track of each repair job, which involves exactly one car, a description of what was done to the car, the date, and the total cost. A repair job includes zero or more parts being replaced on the car (e.g., "windshield wipers", "battery", etc.). For each part we want to record its (unique) part number, the part name and its cost. In addition, note that:

   • Each vehicle may have 1 or more repair jobs.
   • Each customer may own 1 more more cars.
   • Every car has only one primary owner (we ignore co-owners).
   • No vehicle can have more than one repair job in any given day.

   Part A: Draw an E/R diagram for this database. Follow the E/R guidelines above.

   Part B: Translate your E/R diagram into a relational database schema (i.e. give the SQL CREATE TABLE statements). Follow the schema guidelines above.

3. (E/R diagram and schema)

   You are building a database to help students decide where to eat off campus. You decide to model students' preferences for the nearby restaurants and the dishes served at each restaurant. For example, the student Alice Smith may give the "pulled pork sandwich" at "Central BBQ" a 5 out of 5 stars, but also give the "pulled pork sandwich" at "Barbecue Shop" only 3 out of 5 stars.

   Include the following:

   • Each student has his/her SSN, name, and major.
   • Each restaurant has its name and mailing address.
   • The name of a restaurant may not be unique, but the mailing address is unique.
   • Each dish (item on the menu at a restaurant) has a name and price.
   • The price of a dish might differ at different restaurants. For example, the pulled pork sandwich at Central BBQ might be $7.99, but at the Barbecue Shop the same sandwich might be $6.99.
   • The name of a dish is unique within a restaurant, but multiple restaurants may have dishes with the same name (e.g., "pulled pork sandwich").
   • Each dish is offered by at least one restaurant, and each restaurant offers at least one dish.
   • Students assign ratings (1 to 5 stars) to a specific dish at a specific restaurant. Each student may rate each (dish, restaurant) pair only once, but may rate multiple dishes at a single restaurant, or the same dish at different restaurants.

   Part A: Draw an E/R diagram for this database. Follow the E/R guidelines above.

   Part B: Translate your E/R diagram into a relational database schema (i.e. give the SQL CREATE TABLE statements). Follow the schema guidelines above.