Download Homework 2 Solved Questions - Database Systems | CS 411 and more Assignments Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Fall 2007
HW#
Due: 3:00pm CST, 10/10/
Note: Print your name and NetID in the upper right corner of every page of your submission. Handin your stapled homework to Donna Coleman in 2120 SC. In case Donna is not in office, slide your homework under the door.
To grade homeworks faster, the homework is partitioned into two parts. Please, submit each part separately. For each part, make sure to write down your name and NetID.
This homework is partitioned into two parts as follows:
- Part 1: Problem 1 - Problem 4
- Part 2: Problem 5 - Problem 8
There is a bonus credit of 2% if your homework is formatted correctly: Each part must be separable so that it can be independently graded. If you submit a paper copy then each part should i) be separately stapled and ii) include your netid.
Handwritten submissions will be graded but they will take longer to grade. For clarity, machine formatted text is preferable: Expect to lose points if your handwritten answer is unclear or misread by the grader.
Part 1
Problem 1 Relational Algebra (12 points; 4 points for each)
Consider the following relations for an online book warehouse:
books( isbn, title, authors ) stores( sname, href ) sell( sname, isbn, price ) coupons( cid, sname, discount, min expense )
Please give the queries using relational algebra to answer the following questions:
a) Find the authors of the book with title “The C Programming Language” or “The C++ Programming Language” (3 points)
b) Find all the names of stores (sname) which sell the book “The C Programming Language” (3 points)
c) Find all the coupon ids (cid) and the corresponding store names (sname) that we can apply when going to buy the book “The C Programming Language” (4 points) Note that a coupon can only be used in the specified store (sname) and when the items’ price is larger than or equal to the minimum expense (min expense).
Problem 3 Join (10 points; a) 3 points, b) 4 points, c) 3 points)
a) cyclic 3-join: Suppose we have a limited relational algebra that only supports natural join, but not theta-join. In order to do a theta join R onC S of two relations R and S on the condition C, we can introduce an additional relation T that retains the condition C, and perform a cyclic 3-join γ(R ∗ S ∗ T ), which natural-joins the three relations R, S and T (i.e., T is treated as the bridge relation). Now, let’s consider two specific relations R and S be:
R: b c 2 3 5 6 8 9
S: d e 3 1 6 2
We are going to join R and S under the condition R.b < S.d, i.e., R onb<d S. Can you sketch a specific relation T (b, d) so that γ(R ∗ S ∗ T ) is the same as R onb<d S? (Note that you only need to come out T (b, d) for combining R and S in this problem, but not necessary for any general purpose of onb<d)
b) lossless vs lossy joins: Suppose there are two relations R and S, where R and S are not empty. Let U be the relation of joining R and S. A join is lossless if the conditions πR(U ) = R and πS (U ) = S hold, where π is the projection operator, and πR(U ) means projecting U into relation R. A join is lossy if the above conditions may not always hold. Please indicate whether the following joins are lossless or lossy, and briefly explain your answers: (1) Cartesian product ×; (2) Natural join on; (3) Theta join onb<d; (4) Outerjoin
◦ on
c) Referring to Problem 1, when asked to write queries for “find the titles of books that J. Ullman wrote,” two programmers came up with the following two expressions: (1) πtitle(σauthors=J. Ullman(books)) (2) πtitle(σauthors=J. Ullman(books on sell)) Are these expressions equivalent, meaning that regardless of the instances of relations books and sell, the results of the two queries will be the same? Either explain why the two expressions always produce the same answers, or give a counterexample to show they can produce different answers.
Problem 4 Relational Algebra and SQL (12 points; 3 points for each)
Please use the relational algebra to rewrite the following SQL queries:
a) SELECT * FROM R, S WHERE R.b < S.d AND R.c > S.e
b) SELECT D.name FROM DEPT D WHERE D.floor in (SELECT floor FROM DEPT WHERE name=’shoe’)
Please use SQL to rewrite the following relational algebra queries:
c) πe(σd> 20 S)
d) πc(R − πR(R onR.b<S.d S)) Note that πR(U ) projects the join result U into relation R.
Problem 6 View (8 points, 2 points each)
Continue Problem 5,
a) Create a view BusyStudent that records the names of students who are enrolled in more than 5 classes;
b) Create a view BusyFaculty that records the names of faculties who teach more than 3 classes;
c) Using the views BusyStudent and BusyFaculty, find the names of “busy students” who are not enrolled in any class taught by a “busy faculties”.
d) Are the views BusyStudent and BusyFaculty updatable? Explain why.
Problem 7 Definition and Modification (15 points, 3 points each)
Consider the following relations:
Students(snum:integer, sname:string, major:string, level:string, age:integer) Faculty(fid:integer, fname:string, deptid:integer) Class(cname:string, meets at:string, room:string, fid:integer) Enrolled(snum:integer, cname:string)
Write the following declarations and modifications.
a) Write a SQL statement to create relation Students, Faculty, Class and Enrolled respectively; Declare a primary key and foreign keys (if any) on each relation. Note that snum and cname in Enrolled should be consistent with snum in Students and cname in Class respectively;
b) Write a SQL statement to insert into the database the fact that the 22 year-old senior CS student ‘Kobe Bryant’, with snum 111, is enrolled in class CS411. (hint: Both Students and Enrolled tables need to be updated.)
c) Write a SQL statement to delete all the classes taught by the faculty with fid 111.
d) Write a SQL statement to update the age of each student by one year. Note that only the rows where sname has value(i.e., not Null) should be updated.
e) Write a SQL statement to find the name(s) of the professor(s) that teach the course(s) with the highest enrollment, i.e., the course(s) with the most students enrolled.
