CS 4250: Database Management Systems

Fall 2019 - Mini-Homework 1

Due Wednesday, 9/25/2019, 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. (Please write as neatly as you can.) If solution is handwritten, turn in the original on hardcopy.

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 relational algebra. (Reference to SQL removed on 9/20, as it was a typo.)

    1. Find the titles and release dates of movies directed by a director named "Ryan Coogler."

    2. Find the titles of movies playing on 9 / 26 / 2019 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 "The Lion King" or a movie titled "Dora and the Lost City of Gold."

    4. Find the names of theaters that are playing a movie titled "Downton Abbey" and a movie titled "Abominable."

    5. Find the names and phone numbers of theaters where the movie titled "Spider-Man: Far from Home" is playing, but a movie titled "Aladdin" is not playing. (In other words, if the theater is playing 'Aladdin' then the theater should not be in the result set of the query.)