Scribd
Upload
11 views1 page

Supplier and Part Queries in SQL

The document outlines a schema for Suppliers, Parts, and Catalog relations, detailing the key fields and their domains. It then presents a series of queries to be expressed in relational algebra, tuple relational calculus, and domain relational calculus, focusing on suppliers and parts based on color and pricing criteria. The queries range from finding supplier names and IDs based on part colors to identifying parts supplied by multiple suppliers and pricing conditions.

Uploaded by

furnitech3
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
11 views1 page

Supplier and Part Queries in SQL

The document outlines a schema for Suppliers, Parts, and Catalog relations, detailing the key fields and their domains. It then presents a series of queries to be expressed in relational algebra, tuple relational calculus, and domain relational calculus, focusing on suppliers and parts based on color and pricing criteria. The queries range from finding supplier names and IDs based on part colors to identifying parts supplied by multiple suppliers and pricing conditions.

Uploaded by

furnitech3
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Consider the following schema:

Suppliers(sid: integer, sname: string, address: string)


Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The key fields are underlined, and the domain of each field is listed after the field name.
Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form
the key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Write
the following queries in relational algebra, tuple relational calculus, and domain relational
calculus:

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


2. Find the sids of suppliers who supply some red or green part.
3. Find the sids of suppliers who supply some red part or are at 221 Packer Street.
4. Find the sids of suppliers who supply some red part and some green part.
5. Find the sids of suppliers who supply every part.
6. Find the sids of suppliers who supply every red part.
7. Find the sids of suppliers who supply every red or green part.
8. Find the sids of suppliers who supply every red part or supply every green part.
9. Find pairs of sids such that the supplier with the first sid charges more for some part
than the supplier with the second sid.
10. Find the pids of parts supplied by at least two different suppliers.
11. Find the pids of the most expensive parts supplied by suppliers named Yosemite
Sham.
12. Find the pids of parts supplied by every supplier at less than $200. (If any supplier
either does not supply the part or charges more than $200 for it, the part is not
selected.)
Answer the below using RA refers to Relational Algebra, TRC refers to Tuple Relational
Calculus and DRC refers to Domain Relational Calculus and SQL

You might also like