CSE 444 Homework 1

Objectives:

To understand and be able to write simple SQL queries, including updates.

Reading Assignments:

6.1 - 6.6, 7.1

Number of points:

100

Due date:

Wednesday, October 15th.

Tools for the assignment

For this assignment, you will use your SQL Server account on IISQLSRV.  Login using the SQL Server authentication, and your normal UW login name.  Your password has been mailed to you separately.  We have uploaded the database for you, in your account. As you work on this assignment you need to update the database content, and even to alter the database schema. If you want to recover the original database, please upload it from the textfiles available here, using SQL Server's Import feature.

As an alternative to SQL Server, you are allowed to use a different database, e.g. MySQL. In that case, you need to load the database using the textfiles available here. There will be less support (or none at all) for other database systems.

What to turn in:

Prepare one text file containing, for each of the questions below, the SQL query (or queries) and the answer returned by the database server after you run that query. Write your name at the beginning of the file.  Call the file firstnameLastname.txt (e.g. if your name is John W. Smith then call the file johnSmith.txt; but remember to include John W. Smith at the beginning of the file). Then email the file to Michael.

Homework Description

Consider a database wtih the following schema:

movies(MovieCode, Title, Rating, Length)
movies2theaters(TheaterCode, MovieCode, MovieTime)
theaters(TheaterCode, Name, Address, Phone)
theaters2districts(DistrictCode, TheaterCode)
district(DistrictCode, Name)
districts refer to Seattle Districts (U District, Center City, etc).  theaters  lists movie theaters in Seattle, while movies contains the movies currently showing (notice that the data is a bit old: don't use it to plan your next weekend).  movies2theaters lists which theaters show what movies and when.  theaters2districts tells us what theaters can be found in which districts.  theaters2districts is NOT one-many: some theaters list themselves as belonging to several districts. The database currently loaded on IISQLSRV has been populated with real data from   http://www.moviefone.com/, about two years ago.  Before you start it is helpful to play with  SQL Server browsing the database and getting familiarized with the schema, the data, and SQL Server. Recall that for each of the questions below you have to turn in two things:

a. A SQL query (or queries) that answers the question.
b. The results you obtained by running that query on the database.

 
  1. [5 points] Find all theaters that show both "Girlfight" and "Place Vendome": print their names and phone numbers.
  2. [10 points] Find theaters that list themselves both under 'CAPITOL HIL' and under 'DOWNTOWN'. Print their names and addresses.
  3. [10 points] Find theaters that list themselves under the "CAPITOL HILL" District but not under the "THE U DISTRICT".
  4. [10 points] Find movie titles that are shown only after 7pm.
  5. [10 points] For each theater find the minimum and the maximum length of the movies it shows.
  6. [10 points] For each movie find the latest time it shows at any theater.
  7. [10 points] Find all theaters showing at least four distinct movies.
  8. [10 points] Insert two new movies, of your choice, into the database. Your first movie should show at two different theaters (of your choice), the second movie plays at one theater. Each of the new movies should play at two different times at each theater.
  9. [10 points] School started last week, and now there is a new law requiring all movies in "THE U DISTRICT" to start one hour earlier. Write a query that updates all show times for the movies in the University District, to comply with this new law. Of course, you should not update any of the movies times in the other districts.
  10. [10 points] Alter the schema by adding an attribute NewFeature to theaters: this is a foreign key into movies, which shows a selected movie featured at that theater. Then, modify the theaters table such that in each theater NewFeature points to the last movie in alphabetical order that shows at that theater.  
  11. [5 points] Please answer the following questions