CS 4250: Database Management Systems

Spring 2016 - Homework 1

Due March 8, 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. Make bold lines significantly wider than non-bold lines, and draw your arrows large.

  1. Consider a database containing information about pet fish. Create entities and relationships as appropriate for each situation.

    Information that is true for all of the following ER diagrams: Each pet has a RFID tag ID that uniquely identifies it, and also a breed and favorite food.

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

    1. Pet fish must have a fish tank. Every fish tank has a size, a shape, and a unique manufacturer's ID. (30 gallon rectangular tank, 10 gallon round tank, etc)
    2. Some pet fish live in salt water, some pet fish live in fresh water. For salt water fish, the database must store whether the fish is reef-safe or not, and a minimum tank size. (Because large fish should not be kept in a tank that is too small for them.) For fresh water fish, the database must store the minimum and maximum temperatures the fish can live in.
    3. Pet fish may or may not have favorite hiding spots. Each hiding spot is uniquely identified by tank manufacturer ID, plus x, y, and z values for the hiding spot location with the fish tank. The database stores a string describing nearby tank decorations for each hiding spot. (Rock, pirate sculpture, leafy plant, etc) (This ER diagram situation is just about the pet fish and the hiding spots. Ignore information provided in other parts of this homework problem.)
    4. The database must keep track of any experiences with fish disease some of the pet fish may have had. For each fish disease experience, the database should store the date the disease was detected, the name of the medicine applied, and the name of the disease. A single fish may have survived many disease outbreaks over the course of its life, but each outbreak, for that one fish, can be identified by the date it was detected. As diseases frequently strike a whole tank of fish at once, the date of detection can not be used to uniquely identify fish disease experiences in the database. (It is possible for fish in a tank to get different medicines on the same day. A devoted tank owner can catch a sick fish and put it in a separate bowl for an hour's exposure to a strong dose of medicine, and then return it to the tank.)

  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. Suppose a database has the following three relations.
    Student (sid: integer, fname: string, lname: string, gpa: real)
    Like (studentId: integer, movieId: integer)
    Movie (mid: integer, title: string, director: string, releaseDate: date)
    "studentID" in Like is a foreign key referencing "sid" in Student. "movieID" in Like is a foreign key referencing "mid" in Movie.

    Write the following queries in relational algebra.

    1. Find the student ids and last names of students that have the first name "Finn".
    2. Find the titles and directors for movies that were released after 8 / 18 / 2015.
    3. Find the first and last names of students who like the movie with movie identifier equal to 42.
    4. Find the first and last names of students who like the movie titled "The Force Awakens".
    5. Find the last and first names students that like a movie directed by "J.J. Abrams".
    6. Find the last names of students with GPA > 3.0 that like a movie titled "The Empire Strikes Back".