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
When drawing ER diagrams, do not be subtle. Put a gap between double-lines.
Draw subset symbols nice and large.
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.
- 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.
- 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.)
- 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.
- 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
- 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'.
(Let me know if the diagram is too small and you want a bigger version of it.)