CS 4250: Database Management Systems

Spring 2017 - Homework 1

Due February 16, 2017, at the beginning of class

This is an individual assignment. All work must be your own. You should not look at any other student's work (in whole or in part, on paper or on screen), nor allow anyone else to look at yours, during the course of this assignment.

Turn in as hardcopy or via email (MS Word or PDF or plain text only). Typed wherever possible.

When drawing ER diagrams, do not be subtle. Put a gap between double-lines. Draw subset symbols nice and large.

  1. Consider a database containing information about salad recipes. (This database might support a recipe web site, or a healthy dining smartphone app.) Create entities and relationships as appropriate for each situation.

    Information that is true for all of the following ER diagrams: Each salad recipe has a unique identifying number, and also a title and a block of text (a string) describing the recipe.

    For each of the following situations, draw an ER diagram that describes it. Consider each situation independently, unless told to do otherwise.

    1. Each salad recipe must have exactly one author. Authors have names, and are identified by author ID numbers. Authors may, of course, author more than one salad recipe.

    2. Each salad recipe may have any number of photographs of the resulting salad included in the database. The database stores the day and time the photograph was added to the database, plus the photo itself. For any particular salad, a photograph can be identified using the day / time the photograph was added to the database. However, for two different salads, different users might upload photographs at the same day and time, so day and time are not unique identifiers for photographs.
    3. For users, the database stores a username (unique identifier), personal description (i.e. "I love kale!", "I (heart emoji) cooking"), and the name of the area they live (i.e. "Turlock", "outer space"). The database also tracks which salad recipes each users has marked as a favorite recipe. Users can have many favorite recipes, and salad recipes can be favorites of many users.
    4. Some of the salad recipes are potato salad recipes, for which the database stores information about the recommended type of potato that should be used. (In general, for a potato salad, a chef can use their favorite type of potato. But the database can recommend a particular type, like Yukon Gold or Idaho potatoes.)

      Some of the salad recipes are grain salad recipes, for which the database stores information about the type of grain used in the salad. (Quinoa, wheat berries, brown rice, etc.)

  2. Translate the following image into relations. Write the SQL statements to create those relations. Write English explanations of anything in the ER diagram that is not captured in your SQL. Assume all keys are data type 'integer' and all other fields are data type 'date'. (Image updated on 2/14.)

  3. (Let me know if the diagram is too small and you want a bigger version of it.)