Exercise $3(30\%)$

Consider the following relational database schema that describes information about stores that sell

bags and shoes, as well as the suppliers of the products. The relational schema Supplier has as primary

key the supplier$-$id and stores also the name and the city of a supplier. The relational schema Store has

as primary key the store$-$id and stores also the name and the city of a store. The relational schema

Product has as primary key the barcode of the product and stores also the pname and the price of a

product, the id of its supplier, supplier$-$id$,$ as well as whether this product is shoes or a bag: if it is a

shoes then itsshoes has the value 'yes' and itsabag has the value $\text{'}no\text{'},$ whereas if the product is a bag

then itsshoes has the value $\text{'}$no$\text{'}$ and itsabag has the value 'yes'. The relational schema Has$\_$stock stores

pairs of the store$-$id of stores and the barcode of products that these stores sell as well as the quantity

of the product in stock.

Supplier$($supplier$-$id$,$ name, city$)$

Store $($store$-$id$,$ sname, city$)$

Product$($barcode$,$ pname, price, itsshoes, itsabag, color,

supplier$-$id$)$

Has$\_$stock$($store$-$id$,$ barcode, quantity$)$

Express in relational algebra the following queries:

A$.(9\%)$ Find the name of the stores that have at least $2$ different bag designs in black color. Do not

use aggregation.

B$.(7\%)$ Find the price of all products that are shoes in the store named 'LaFollie'.

C$.(7\%)$ Find the names of all suppliers that supply more than $5$ products with different barcodes,

whether these are shoes or bags. You can use aggregation.

D$.(7\%)$ Find the barcodes of the products that are sold by the store with store$-$id equal to $\text{'}1\text{'}$ and by

the store with store$-$id equal to $\text{'}2\text{'}.$ Do not use the intersection operator.