Question

Consider the following schema:

Sellers(SellerId, SellerName, address, phone)

Pieces(PieceId, PieceName, color, type)

Inventory(SellerId, PieceId, price, location)

Express following queries in relational algebra:

1. Retrieve the names of sellers who sale the black pieces.
2. Retrieve the SellerId of sellers who sale the black pieces or are at 19 Hussein Al Bouri Street.
3. Retrieve the sellersId of sellers who sale every piece.
4. Retrieve the SellersId of sellers who sale the white pieces and the black pieces.

Answer 1

1) Retrieve the names of sellers who sale the black pieces.

Ps (seller) Pp (Pieces) le (Invertory) 11 s.sellername o (SAIAP) (susellerid - I-sellerid and I. piecu.Id = P. PiccuId and P. color. "black")

SELECT s.SellerName

FROM Sellers s, Pieces P, Inventory I

WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId and P.color = "black";

2) Retrieve the SellerId of sellers who sale the black pieces or are at 19 Hussein Al Bouri Street

Ps (sellers) le (piece) PI (Inventory) o SAI AP) (s.sellerid = I seller Id and T. Piecu Id - P. piece Id and (P-color= black" or s address = "19 Hussein Al Bouril Street")) D s.sellerid

SELECT s.SelllerId

FROM Sellers s, Pieces P, Inventory I

WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId and ( P.color = "black" or S.address = "19 Hussein Al Bouri Street" );

3) Retrieve the sellersId of sellers who sale every piece.

es (sellers) (p (Picus) (I l Inventory) IT s-selleria MipMT) (s.seller Id - I-selleria and 1. pieuld - P. Pierced)

SELECT s.SelllerId

FROM Sellers s, Pieces P, Inventory I

WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId;

4) Retrieve the SellersId of sellers who sale the white pieces and the black pieces.

es (sellers), lp (pieus), P = (Inventory) ITs.selkuid - S x I x P) (s.sellerid - I-seller Id and I piece Id = p. pieu Id and p.color - "black" and P. color = "white"))

SELECT s.SellerId

FROM Sellers s, Pieces P, Inventory I

WHERE s.SellerId = I.SellerId and I.PieceId = P.PieceId and ( P.color = "black" and P.color = "white" );