CS 4250: Database Management Systems

Spring 2017 - Mini-Homework 1

Due Thursday, 3/16/2017, at start of class

This is a GROUP assignment. Work in groups of 3. (Groups of 2 are acceptable, as well.)

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 / 9 / 2017 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 include "LEGO" somewhere in the middle (but not exactly at the start or end).
    7. For each movieID, find the number of different theaters that are playing the movie on 3 / 9 / 2017. (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 3 / 9 / 2017. The user is only interested in information about theaters that are playing more than three movies on 3 / 9. (List both theater IDs and counts of movies in output.)