Personal Web Pages - UNC Charlotte FAQ - UNC Charlotte



Assignment #2 Solution

Tuple Relational Calculus

1. Find the names of suppliers who supply some red part.

{S.sname | Suppliers(S) ^ ((C)((P)(Catalog(C) ^ Parts(P) ^ (C.sid = S.sid) ^ (P.pid = C.pid) ^ (P.color = ‘red’))}

2. Find the sids of suppliers who supply some red part or green part.

{C.sid | Catalog(C) ^ ((P)(Parts(P) ^ ((C.pid = P.pid) ^ (P.color = ‘red’)) V ((C.pid = P.pid) ^ (P.color = ‘green’))) }

3. Find the sids of suppliers who supply some red part or are at 221 Packer Ave.

{S.sid | Suppliers(S) ^ ((S.address = ‘221 Packer Ave.’) v (((C)((P)(Catalog(C) ^ Parts(P) ^ (S.sid = C.sid) ^ (C.pid = P.pid) ^ (P.color = ‘red’))))}

4. Find the sids of suppliers who supply some red part and some green part.

{C.sid | (Catalog(C) ^ ((P)(Parts(P) ^ (C.pid = P.pid) ^ (P.color = ‘red’))) ^ (Catalog(C1) ^ ((P)(Parts(P) ^ (C1.pid = P.pid) ^ (P.color = ‘green’))) ^ (C.sid = C1.sid)}

5. Find the sids of suppliers who supply every red part.

{S.sid | Suppliers(S) ^ (((P)(Parts(P) ^ (P.color=‘red’))) ^ (((C)(Catalog(C) ^ (C.pid = P.pid))) ^ (C.sid = S.sid)}

6. Find the sids of suppliers who supply every part.

{S.sid | Suppliers(S) ^ ((P)(Parts(P)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)}

7. Find the sids of suppliers who supply every red part or supply every green part.

{S.sid | Suppliers(S) ^ ((((P)(Parts(P) ^ (P.color=‘red’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) v ((((P)(Parts(P) ^ (P.color=‘green’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) }

8. Find the pids of parts supplied at least two different suppliers.

{C.pid | Catalog(C) ^ ((S)(Suppliers(S) ^ (C.sid = S.sid) ^ COUNT(C.sid) >= 2)} or

{C.pid | Catalog(C) ^ ((C1)(Catalog(C1) ^ (C.pid = C1.pid) ^ (C.sid != C1.sid)}

9. Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.

{C.pid | Catalog(C) ^ ((S)(Suppliers(S) ^ (S.sid = C.sid) ^ (S.name = ‘Yosemite Sham’)) ^ MAX(C.cost))}

10. Find the sids of suppliers who supply every red or green part.

{S.sid | Suppliers(S) ^ ((((P)(Parts(P) ^ (P.color=‘red’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) v ((((P)(Parts(P) ^ (P.color=‘green’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) }

SQL Solutions

1. List the names and addresses of all guests in London, alphabetically ordered by name.

SELECT guestName, guestAddress

FROM Guest

WHERE guestAddress LIKE ‘%London%’

ORDER BY guestName;

2. List the bookings for which no dateTo has been specified.

SELECT *

FROM Booking

WHERE dateTo IS NULL;

3. What is the average price of a room?

SELECT AVG(price)

FROM Room;

4. How many different guests have made bookings for August?

SELECT COUNT(DISTINCT guestNo)

FROM Booking

WHERE dateFrom >= DATE‘2008-08-01’ AND dateTo ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download