Question

Consider the following schema:

SUPPLIERS (SID: integer, SNAME: string, STREET: string, CITY: string, ZIP: string) PARTS (PID: integer, PNAME: string, COLOR: string) CATALOG (SID: integer, PID: integer, COST: real)

The primary key attributes are underlined, and the domain of each attribute is listed after the attribute name. Thus, SID is the primary key for SUPPLIERS, PID is the primary key for PARTS, and SID and PID together form the primary key for CATALOG. Attribute SID in CATALOG is a foreign key that refers to the primary key SID of SUPPLIERS and PID in CATALOG is a foreign key that refers to the primary key PID of PARTS. The CATALOG relation lists the prices charged for parts by suppliers.

State what the following query computes.

0. πCITY (πPID (σ COLOR = 'green' (PARTS) ) * σ COST>50 (CATALOG) * πSID, CITY (SUPPLIERS) )

Write the following queries in relational algebra.

1. Find the names of parts for which there is some supplier

2. Find the names of parts supplied by suppliers who are at 1 Central Ave.

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

4. Find the SIDs of suppliers who supply some red or green part.

5. Find the SID of suppliers who supply some red part or whose address is '221 Packer Street'.

6. Find the SIDs of suppliers who supply some red part and some green part.

7. Find the PIDs of parts that are red or are supplied by a supplier who is at the city of Newark.

8. Find the PIDs of parts supplied by a supplier who is at the city of Newark and by a supplier who is at the city of Trenton.

9. Find the PIDs of parts supplied by every supplier.

10. Find the PIDs of parts supplied by every supplier who supplies at least one part.

11. Find the SIDs of suppliers who supply at least two different parts (you are not allowed to use a grouping/aggregation operation for this query).

12. Find the SIDs of suppliers who supply at least two different parts (you have to use a grouping/aggregation operation for this query).

13. For every part, find its PID, its PNAME and the number of suppliers who sell it.

14. List the PID, PNAME and average cost of all parts.

15. Find the average cost of red parts.

16. Find the average cost of parts supplied by suppliers named 'Yosemite Sham'.

Answer 1

1. Find the names of parts for which there is some supplier
Result<- Π PNAME ((π PID CATALOG) * (π PID,PNAME PARTS))

2. Find the names of parts supplied by suppliers who are at 1 Central Ave.
Result<- Π PNAME ((π SID (σ STREET=’1 Central Ave’ SUPPLIERS )) * (π SID,PID CATALOG) * (π
PID,PNAME PARTS))

3. Find the names of suppliers who supply some red part.
Result<-Π SNAME ((π PID (σ COLOR = 'red' )PARTS )* (π SID,PID CATALOG)*( π SID,SNAME SUPPLIERS))

4. Find the SIDs of suppliers who supply some red or green part.
Result<-Π SID ((π PID(σ COLOR = 'red' U COLOR = 'green' )PARTS)* (π SID,PID CATALOG)* (π SID SUPPLIERS))

5. Find the SID of suppliers who supply some red part or whose address is '221 Packer Street'.
Result<-Π SID ((π PID (σ COLOR = 'red' U STREET = '221 Packer Street') PARTS)* (π SID,PID CATALOG)* (π SID
SUPPLIERS) )

6. Find the SIDs of suppliers who supply some red part and some green part.
Result<-Π SID((π PID (σ COLOR = 'red' ^ COLOR = 'green') PARTS)* (π SID,PID CATALOG))

7. Find the PIDs of parts that are red or are supplied by a supplier who is at the city of Newark.

Result<-Π PID ((π PID (σ COLOR = 'red' U CITY = 'Newark') PARTS)* (π SID,PID CATALOG)* (π SID SUPPLIERS))

8. Find the PIDs of parts supplied by a supplier who is at the city of Newark and by a supplier who is at the city of
Trenton.
Result<-Π PID ((π PID (σ COLOR = 'Trenton' ^ CITY = 'Newark') PARTS)* (π SID,PID CATALOG)* (π SID SUPPLIERS))

9. Find the PIDs of parts supplied by every supplier.
Result<-Π PID , SID CATALOG ÷ Π SID SUPPLIERS

10. Find the PIDs of parts supplied by every supplier who supplies at least one part.
Result<-Π PID , SID CATALOG ÷ Π SID CATALOG

11. Find the PIDs of parts supplied by every supplier who is at the city of Newark or at the city of Trenton
(equivalently: find the PIDs of parts supplied by every supplier who is at the city of Newark and by every supplier
who is at the city of Trenton).
T1 <-Π SID (σ CITY = 'Trenton' U CITY = 'Newark' (SUPPLIERS)
T2<-Π PID,SID CATALOG
Result<- T2÷ T1
T1 <-Π SID (σ CITY = 'Trenton' ^ CITY = 'Newark' (SUPPLIERS)
T2<-Π PID,SID CATALOG
Result<- T2÷ T1

12. Find the PIDs of parts supplied by every supplier who is at the city of Newark or by every supplier who is at the
city of Trenton.
T1 <-Π SID (σ CITY = 'Trenton' (SUPPLIERS)
T2<-Π PID,SID CATALOG
T3<- T2÷ T1
T 4<-Π SID (σ CITY = 'Newark' (SUPPLIERS)
T5<-Π PID,SID CATALOG
T6<- T2÷ T1
T7<- T6 U T3

13. Which one of the queries 11 and 12 is more restrictive (if any)?
12 is more restrictive.

14. Find pairs of PIDs such that the part with the first PID is sold at a higher price by a supplier than the part with the
second PID.
RESULT<-Π PID,PID1 [CATALOG ∞ ∫(CATALOG)]
PID=PID1 PID->PID1
COST>COST1 COST->COST1

15. Find the SIDs of suppliers who supply at least two different parts (you are not allowed to use a
grouping/aggregation operation for this query).
T1<- ∫PID-> PID1 CATALOG
T2<- Π SID ( σ PID≠ PID1 (T1 * CATALOG))

16. Find the SIDs of suppliers who supply at least two different parts (you have to use a grouping/aggregation operation
for this query) .
T1<- SID FCOUNT(PID) CATALOG
T2<- ∫ FCOUNT -> NOS_OF_COUNT T1
RESULT <- Π SID (σ NOS_OF_COUNTS>2) T2

17. For every part supplied by a supplier who is at the city of Newark, print the PID and the SID and the name of the
suppliers who sell it at the highest price.
T1<- σ CITY = ‘NEWARK’ (SUPPLIERS)
T2<- Π SID (F(Max(cost)) CATALOG)
RESULT<- Π PID,SID,SNAME (T1*T2)

18. For every part, find its PID, its PNAME and the number of suppliers who sell it.
T1<- Π PID,PNAME,SID (CATALOG*PARTS) )
RESULT<- Π PID,PNAME,FCOUNT(SID) T1

19. List the PID, PNAME and average cost of all parts.
T1<- FAVG(COST) CATALOG
RESULT<-Π PID ,PNAME ,FAVG(COST) (PARTS *T1)

20. Find the average cost of red parts.
T1 <- σ COLOR = 'RED' (PARTS)
T2 <- F avg(COST) (ΠCOST (CATALOG*T1) )

21. Find the average cost of parts supplied by suppliers named 'Yosemite Sham'.
T1 <- σ SNAME = 'Yosemite Sham' (SUPPLIERS)
T2 <- F avg(COST) (ΠCOST (CATALOG*T1))