CS 4250: Database Management Systems

Fall 2016 - Mini-Homework 1

Due Wednesday, 11/30/2016, at start of class

This is a GROUP assignment. Work in groups of 3. (Groups of 2 or 4 are acceptable, but 3 is ideal. Enough people for a variety of viewpoints, not so many that anyone will feel left out of discussions.)

You may email a plain text, MS Word or PDF document to the instructor with subject line "cs4250,mini1", or turn in a hardcopy. I would prefer it if the SQL was typed, but will understand if the hardcopy is handwritten, because of the relational algebra. (Please write as neatly as you can.) Put the names of all group members on what you submit.

  • Suppose a database has the following three relations.
    Movie (mid: integer, title: string, director: string, releaseDate: date)
    PlaysAt (theaterID: integer, movieId: integer, showDate: date)
    Theater (tID: integer, name: string, phone: string, screencount: integer)
    "movieID" in PlaysAt is a foreign key referencing "mid" in Movie. "theaterID" in PLaysAt is a foreign key referencing "tID" in Theater.

    Write the following queries in both relational algebra and SQL.

    1. Find the names and theater IDs of theaters with more than 5 screens.
    2. Find the titles of movies playing on 3 / 3 / 2016 and the IDs of the theaters they are playing at. (You may assume that a simple "=" works on the date data type.)
    3. Find the names of theaters that are playing a movie titled "Pride and Prejudice" and a movie titled "Sense and Sensibility."
    4. Find the phone numbers of theaters where the movie titled "The Last Man on the Moon" is playing on 3 / 3 / 2016.
    5. Write the following queries in SQL only.

    6. Find the titles and movie IDs of movies whose titles start with "S" and end with "s".
    7. For each movieID, find the number of different theaters that are playing the movie on 11 / 24 / 2016. (List both movieIDs and counts of theaters in output.)
    8. For each theaterID, find the number of different movies that are playing at that theater on 11 / 24 / 2016. The user is only interested in information about theaters that are playing more than three movies on 11 / 24. (List both theater IDs and counts of movies in output.)