Question

C. Answer the following five (5) questions, based on the schema provided.

Consider the following schema:

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

Part(pid: integer, pname: string, , color: string)

Catalog(sid: integer, pid: integer, cost: real)

The relation Supplier stores suppliers and the primary key of that relation is sid. The relation Part stores parts, and pid is the primary key of that relation. Finally, Catalog stores which supplier supplies which part and at which cost (price). The primary key is the combination of the two attributes sid and pid.

Write the following queries in relational algebra:

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

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

3) Find the IDs of suppliers who supply some red part or are based at 21 George Street (You can simplify to '21GSt' in your queries. Also, you may assume " 21 George Street" is the complete information stored in the DB for that supplier.)

4) Find the IDs of suppliers who supply only red parts (not all red parts, only red parts)

5) Find the IDs of suppliers who supply all parts with cost <20.00 (the suppliers who sell all of the parts that cost <20, not suppliers who sell some items that are all <20 )

Answer 1

1. find the name of the suppliers who supply red part

Solution:

$\pi$_sname($\sigma$(Supplier)$\Join$_{Supplier.sid==supply.sid} ($\rho$(supply,($\sigma$_color=='red'(Part) $\Join$_{Part.pid==Catalog.pid}  $\sigma$(Catalog))))

Explanation:

a.We need to find the red parts so we use $\sigma$_color=='red'(Part) for finding red parts

b. We dont have any thing to join on condition with supplier so we first use join with catalog

$\sigma$_color=='red'(Part) $\Join$_{Part.pid==Catalog.pid}  $\sigma$(Catalog)

c.$\rho$ this is used to rename the expression

c.now we have sid so we can directly join with suppliers and project sid.

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

Solution:

$\pi$_sid($\sigma$(Supplier)$\Join$_{Supplier.sid==supply.sid} ($\rho$(supply,$\sigma$_{(color=='red' v "green)}(Part) $\Join$_{Part.pid==Catalog.pid}  $\sigma$(Catalog)))

Explanation:

a. First we need to select the red and green parts $\rho$(supply,$\sigma$_{(color=='red' v "green)}(Part) so we use this.

b.We dont have any thing to join on condition with supplier so we first use join with catalog

$\sigma$_{(color=='red' v "green)}(Part) $\Join$_{Part.pid==Catalog.pid}  $\sigma$(Catalog)

c.$\rho$ this is used to rename the expression

d.now we have sid so we can directly join with suppliers and project sid.

3.find the ids of suppliers who supply some red part or based at 21GST

Solution:

$\pi$_sid($\sigma$_{(address=="21GST")}(Supplier))

$\bigcup$

$\pi$_sid($\sigma$(Supplier)$\Join$_{Supplier.sid==supply.sid} ($\rho$(supply,$\sigma$_{(color=='red')}(Part) $\Join$_{Part.pid==Catalog.pid}  $\sigma$(Catalog)))

Explanation:

a. We need to select the red parts $\sigma$_{(color=='red')}(Part) this will give us red parts

b.We need to join this with catalog so that we can get sid to join with suppliers

$\sigma$_{(color=='red')}(Part) $\Join$_{Part.pid==Catalog.pid}  $\sigma$(Catalog)

c.$\rho$ this is used to rename the expression

d. We use union operator so that all the suppliers based of 21GST will be included

d.now we have sid so we can directly join with suppliers and project sid.

4.Supplier who supply only red Parts

Solution:

$\pi$_sid(Supplier) -  $\pi$_sid($\sigma$(Catalog) $\bowtie$_{(Part.pid==Catalog.pid)} σ_{colour !="red"} (Part)).

Explanation:

a. In this we use minus operator where we will select all the suppliers who supply other than red and eliminate them so now we will get only the suppliers who supply only red.

5 Find the ids of the suppliers who supply all parts with cost<20

Solution:

$\pi$_sid(Supplier) -  $\pi$_sid($\sigma$_cost>20(Catalog)).

Explanation:

a.In this we select parts which costs more than 20 and use minus operator to eliminate all the suppliers who supply parts cost more than 20

Answer 2

Plz give Mee solution of this query