Question
Consider the following schema: SUPPLIERS (SID: integer(pk), SNAME: string, STREET: string, CITY: string, ZIP: string) PARTS (PID: integer(pk), PNAME: string, COLOR: string) CATALOG (SID: integer(pk),
Consider the following schema:
SUPPLIERS (SID: integer(pk), SNAME: string, STREET: string, CITY: string, ZIP: string) PARTS (PID: integer(pk), PNAME: string, COLOR: string) CATALOG (SID: integer(pk), PID: integer(pk), 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.
Write the following queries in relational algebra.
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'.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started