CS 4250: Database Management Systems

Spring 2016 - Mini-Homework 1

Due Tuesday, 3/8/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" or a movie titled "Sense and Sensibility."
    4. Find the names of theaters that are playing a movie titled "Pride and Prejudice" and a movie titled "Sense and Sensibility."
    5. Find the phone numbers of theaters where the movie titled "The Last Man on the Moon" is playing on 3 / 3 / 2016.
    6. Write the following queries in SQL only.

    7. Find the titles and movie IDs of movies whose titles start with "Star".
    8. Find the titles and movie IDs of movies whose titles start with "S" and end with "s".