Homework CS3431 5 (Fall 2007, B term)
Homework Out: Monday, Nov 26th, 2007 (in class) Homework Due: Monday, Dec 3rd , 2007 at 9:59 am (at start of class) Homework Submit: Complete in hardcopy at start of class Monday (and, SQL portion also via MYWPI )! Homework Maximum Points: 70 pts Problem 1. [20pts ] SELECTCandD ( Relation R ) = SELECTC ( Relation R ) INTERSECT SELECTD ( Relation R ). Here C and D are two arbitrary conditions on R. Does the above equivalence hold for SETS? Explain why you think it holds, or give a counterexample to show that it does not hold. SELECTCandD ( Relation R ) = SELECTC ( Relation R ) INTERSECT SELECTD ( Relation R ). Here C and D are two arbitrary conditions on R. Does the above equivalence hold for BAGS ? Explain why you think it holds, or give a counterexample to show that it does not hold. SELECTCorD ( Relation R ) = SELECTC ( Relation R ) UNION SELECTD ( Relation R ). Here C and D are two arbitrary conditions on R. Does the above equivalence hold for SETS? Explain why you think it holds, or give a counterexample to show that it does not hold. SELECTCorD ( Relation R ) = SELECTC ( Relation R ) UNION SELECTD ( Relation R ). Here C and D are two arbitrary conditions on R. Does the above equivalence hold for BAGS ? Explain why you think it holds, or give a counterexample to show that it does not hold. The antisemijoin R anti 1 S is dened to correspond to the bag of tuples in R that do not agree with any tuple in S in the attributes common to R and S. Give a relational algebra expression equivalent to R anti 1 S. Explain your answer briey. An unary operator f is said to be idempotent if for all relations R, f (f (R)) = f (R). That is, applying f more than once is the same as applying it once. Which of the following operators are idempotent? Either explain why or give a counter example. (a) S with S some new relation name, (b) the duplicate removal operator , (c) the project operator L with L some attribute of the relation, and (d) the sort operator L with L some attribute of the relation to be sorted on.
Problem 2. [30 pts] Consider the database schema below (the key elds are underlined). There are foreign keys connecting all three product-type relations to the Product relation. Product(manufacturer, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) The Product relation gives the manufacturer, model number and type (either PC or Laptop) of various products. The PC relation gives for each model number that is a PC the speed of processor (in megahertz), the amount of RAM (in megabytes), the size of hard disk (in gigabytes), the speed and type of the removable disk (CD or DVD), and the price (in $). The Laptop relation is similar except that the screen size is recorded in place of information about the removable disk. The Printer relation captures the color (yes/no), the type, and price for each printer model. Some sample data are shown below.
manufacturer A A A B B B C C C C model 1001 2002 2003 1002 1003 2004 5001 5002 6001 6002 type PC Laptop Laptop PC PC Laptop Printer Printer Printer Printer
Table 1: Sample data for Product
model 1001 1002 1003
speed 700 1500 700
ram 64 128 64
hd 10 60 10
rd 48xCD 12xDVD 8xDVD
price 799 2499 899
Table 2: Sample data for PC
model 2002 2003 2004
speed 600 800 850
ram 64 96 64
hd 5 10 10
screen 12.1 15.1 15.1
price 1488 2584 1999
Table 3: Sample data for Laptop
model 5001 5002 6001 6002
color no yes no yes
type InkJet InkJet LaserWriter LaserPrinter
price 300 350 600 1000
Table 4: Sample data for Printer
(a) Write the following queries in SQL. (b) Test them on our given data set (which we will be providing to you on the course web site), and submit your results for running each of your queries on our data set. (c) Design a second testing data set, explain what queries or cases you are testing for in your own data set, in particular, if there are cases in our data set that were missed. (d) Again submit your data set and matching results for the same queries now run on your second data set. You need to submit your SQL queries as usual (via MYWPI). You should put all your SQL statements into one le (say, myqueries.sql) and the output of sqlplus (using command spool in sqlplus) into another le say myresults.txt, illustrating the results you got on our test data. Note that we may decide to create our own separate testing le, when we grade your homework, which we then would use to further test out your queries. So make sure your queries work in general, and not just for some particular data. 1. Find the manufacturer of the product (PCs and Laptops) with the lowest price. (Note: there could be multiple products with this same lowest price). 2. Find the model number, speed and hard-disk size for all PCs whose price is below $2000, and rename the speed column to megahertz and the hd column to gigabytes. 3. Find the manufacturers that make at least three dierent models ...