Download Homework #3 with Solutions - Database Systems | CS 411 and more Assignments Deductive Database Systems in PDF only on Docsity!
Homework 3
Due date: at the beginning of the lecture on Tue March 7.
Note : Please submit a hard copy of your homework. Bring it down to the lecture table (the one with the PC that I use to display the slides). The hard copy should be as clearly readable as possible. You may be subtracted points for unreadability and ugly presentation. Note that late homeworks will not be accepted, barring exceptional circumstances.
Off-campus students: You should e-mail your solutions to Yoonkyong Lee in the pdf format or in the Word document. Send the file as attachment with your email by 2 PM UIUC time (CST). Off-campus students in other time zones should note that the deadline is according to CST.
Goal of this homework: To get you to practice relational algebra, SQL queries, and views.
SQL Queries
Problem 1. (40 points) For this problem, you will need the database we have created. First, download http://www.cs.uiuc.edu/class/sp06/cs411/homeworks/hw3.sql to your CSIL account and do the following:
tcheng3|csil-linux33|~|[2]% /usr/dcs/software/applications/Oracle/bin/sqlplus SQL*Plus: Release 9.2.0.3.0 - Production on Tue Feb 21 16:44:42 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> [your_netid]@oracsil
Enter password: Connected.
SQL> @hw3.sql SQL> (now your are ready to formulate your SQL queries here)
Note that you need to load database only once. Once it is loaded, you should not load it again the next time you log into sqlplus. For further information on Oracle DBMS, please read http://www-courses.cs.uiuc.edu/~cs311/oracle/embeddedsql.html.
The file provided is loaded with real data taken from movies.yahoo.com with the following schemas:
theatres(name,city,state,zip,phone) movies(title,rating,length) shownat(theatres.name,movies.title)
On the data and schema provided, answer the following questions.
For each answer, please provide (1) the SQL query(s) you used and (2) query results you obtained by running that query on the database (that is, the table that you obtain after executing the query).
a) List all theatres playing the movie “Cabin Fever”. (6pts) b) List all the movies playing in theatres in Champaign city. (7pts) c) Find the number of theatres in each city. (7pts) d) Find the name of all the theatres that don’t show movies whose rating is ‘R’. (10pts) e) Find the length of the movie which is shown in the maximum number of theatres. (Hint: Use views.) (10pts)
Views
Problem 2 (20 points). Consider the following schema:
Suppliers ( sid :integer, sname :string, address :string) , Parts ( pid :integer, pname :string, color :string) , Catalog ( sid: integer, pid: integer, cost:real)
a) Create a sql-view that would have all the Parts (and their Suppliers) such that the color of the Part is Red and the cost of the Part provided by the Supplier is less than 20$.
b) Create a sql-view that would have the sid of all the suppliers who supplies more than 5 different parts and doesn’t supply parts with Yellow color.
Problem 5. (10 bonus points) Consider the following relation R containing generation information. R( name1, name2, relationship ) For example, a tuple means David is the Father of Mary; a tuple means Mary is the Mother of Smith; etc. Suppose we are interested in finding all the descendants of David. (from the two example tuples, Mary and Smith are all descendants of David) Such a query could not be expressed using relational algebra. Prove why it can’t be answered using relational algebra.
