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
When drawing ER diagrams, do not be subtle. Make bold lines significantly
wider than non-bold lines, and draw your arrows large.
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
For each of the following situations, draw an ER diagram that describes it.
Unless told to do otherwise, consider each situation independently.
- 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)
- 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.
- 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.)
- 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.)
- 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'.
- 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.
- Find the student ids and last names of students that have the first name "Finn".
- Find the titles and directors for movies that were released after 8 / 18 / 2015.
- Find the first and last names of students who like the movie with movie identifier
equal to 42.
- Find the first and last names of students who like the movie titled
"The Force Awakens".
- Find the last and first names students that like a movie directed by "J.J. Abrams".
- Find the last names of students with GPA > 3.0 that like a movie titled "The Empire Strikes Back".