CS 4250: Database Management Systems

Fall 2016 - Homework 1

Due September 16, 2016, 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 cookbooks. Create entities and relationships as appropriate for each situation.

    Information that is true for all of the following ER diagrams: Each cookbook has an ISBN that uniquely identifies it, and also a title and number of recipes.

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

    1. Each cookbook must have one or more authors. Authors are identified by author ID numbers. The database should also store author first and last names, and the number of years the author has been cooking professionally.

    2. Some cookbooks include work by food photographers. The database stores the first and last names of the photographer, and an identifying social security number. The database should also store the number of photographs each photographer contributed to the cookbook. (Note: food photographer is a niche specialty and a good food photographer might work on many cookbooks over the course of his/her career.)
    3. Some cookbooks contain only recipes specific to a particular device. (Recipes for a slow cooker, or for a bread machine, or for a grill.) For those cookbooks, the database should store the name of the the device.
      Some cookbooks contain only recipes specific to a particular diet (vegetarian, gluten-free, sugar-free, meat-free, vegetable-free, flavor-free...). For those cookbooks, the database should store the name of the diet. Some cookbooks may belong to both of these groups, some cookbooks may belong to neither group.
    4. The database should store recipes. For each recipe, the database should store the name of the recipe, and the recipe itself (as an attribute -- a long string). The database should keep track of which recipe comes from which cookbook. (Note: The name of a recipe is not an identifier. There are many recipes named "chocolate cake".)

  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'.

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