TEST 1 Supplement

Name:

TEST 1—Supplement

CSCE 520 – Spring 2003

Name:

Major:

SSN (last 4 digits):

Answer the following questions. Be brief and precise, please. You have 1 hour 15 minutes to finish the test. Undergraduate students answer only questions for a total of 75 points and the bonus question if you want to. The bonus question is open for everyone.

1. 10 points

Consider the following relational database:

Student(ID, GPA)

Plays(ID, sport)

and the query

SELECT sport

FROM Student, Plays

WHERE Student.ID=Plays.ID

GROUP BY sport

HAVING min(GPA) < 2.0

Write an equivalent SQL query but does not have the “HAVING” clause.

2. 10 points

Consider the following relation schema:

Student(name, dorm)

Partner(name1, name2)

Write a relational algebra expression to find all pairs of students (by name) who are partners but do not live in the same dorm. You may assume that each partner pair is listed only once, i.e., if (A,B) is in Partners then (B,A) is NOT in Partners.
3. 15 points

Consider the following two SQL queries over table R(B,A) where B is a key.

Q1: SELECT *

FROM R

WHERE A >= ALL (SELECT A FROM R)

Q2: SELECT *

FROM R AS R1

WHERE A > ALL (SELECT A FROM R AS R2 WHERE R1.B <> R2.B)

Are these two queries are equivalent? That is, do they return the same answer on all possible instances of R?

YES / NO

If you circled YES, briefly justify your answer. IF you circled NO, give an example instance where the two queries return different answers.

4. 20 points

Consider the following relations:

Employees(eno, ename, zip)

Parts(pno, pname, price)

Customers(cno,cname,street, zip, phone)

Orders(ono, eno,cno, received, shipped)

Odetails(ono, pno,qty)

Zipcodes(zip,city)

Answer the following queries:

(a) (5)Get the total sales in dollars for each employee (also give the employees’ name).

(b) (5) Get the pairs of customer number values of customers having the same zip code.

5. 10 points

Consider the two relations:

R(A,B)={ (0,1), (4,5), (8,9)}

S(B,C)={ (1,2), (5,2), (5,6), (5,10), (13,10) }

(5) Give the output schema of the following expressions

· R x S

· R |x| S (natural join)

· pA,B(R x S)

· SELECT * FROM R, S

· SELECT * FROM R, S WHERE R.B=S.B

(5) Give the number of tuples returned by:

· R x S

· R |x| S (natural join)

· pA,B(R x S)

· SELECT * FROM R, S

· SELECT * FROM R, S WHERE R.B=S.B

6. 10 points

(5) Explain what database views are and why they important.

(5) Give an SQL example of creating views.

7. 10 points

Explain what the following code does:

CREATE TRIGGER trig1

AFTER INSERT ON T4

REFERENCING NEW AS newRow

FOR EACH ROW

WHEN (newRow.a <= 10)

BEGIN

INSERT INTO T5 VALUES(:newRow.b, :newRow.a);

END trig1;

8. 15 points

(5) Briefly explain why it is desirable to write application program to connect to the back-end database systems.

(10) Explain the concepts of JDBC connection, statements, statement types, and use of JDBC statements.

---------------------------------------------------------------------------------------------------

Bonus question: (5 points)

Let R(A,B) and S(A,B) be two relations over the same schema. Consider the following relational algebra queries:

Q1: pA(R) Ç pA(S)

Q1: pA(R Ç S)

Choose one of the answers:

1. Q1 and Q2 always produces the same answer.

2. The answer to Q1 is always contained in the answer to Q2.

3. The answer to Q2 is always contained in the answer to Q1.

4. Q1 and Q2 produce different answers.

Justify your answer: