Assume a video shop has a video mail-order database with the following schema of

Question

Assume a video shop has a video mail-order database with the following schema of six relations: (Primary keys are underlined. Meaning of each attribute is self-explanatory by its name.)

EMPLOYEES: <ENO, ENAME, ZIP, HIRE_DATE>;
PARTS: <PNO, PNAME, QUANTITY_ON_HAND, UNIT_PRICE, LEVEL>;
CUSTOMERS: <CNO, CNAME, STREET, ZIP, PHONE>;
ORDERS: <ONO, CNO, ENO, RECEIVED, SHIPPED>;
ORDER_LINE: <ONO, PNO, QTY>;
ZIPCODES: <ZIP, CITY>;

Convert the following queries into relational algebraic expressions:

customers (names) the zip of whose address is either 49008 or 49009.
employees (names) who have customers with the zip code of either 49008 or 49009.
employees (names) who have both 49008-zipcode customers and 49009-zipcode customers.
customers (names and IDs) who live in Kalamazoo, excluding customer #1111 who's a special case.
customers (names) who've ordered products through the employee named 'Jones'.
customers (names) who've not ordered "Sleeping Beauty".
all the different possible pairs of videos (PNAMEs). (Don't, of course, include 2 of the same prodeuct in a pair).
all the different possible pairs of videos (PNAMEs) where the first has a price of 24.99 and the second has a price of 19.99.
customers (numbers) who ordered at least one item that customer #1111 ordered.
customers (numbers) who ordered all the items as customer #11111 ordered (although, they may have ordered additional items besides).

I want it to be written in relational algebraic expressions

Explanation / Answer

SELECT CNAME FROM CUSTOMERS WHERE ZIP = 49008 OR ZIP = 49009 SELECT EMPLOYEES.ENAME FROM EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.ENO = ORDERS.ENO INNER JOIN CUSTOMERS ON ORDERS.CNO = CUSTOMERS.CNO WHERE CUSTOMERS.ZIP = 49008 OR CUSTOMERS.ZIP = 49009 SELECT EMPLOYEES.ENAME FROM EMPLOYEES INNER JOIN ORDERS ON EMPLOYEES.ENO = ORDERS.ENO INNER JOIN CUSTOMERS ON ORDERS.CNO = CUSTOMERS.CNO WHERE CUSTOMERS.ZIP = 49008 AND CUSTOMERS.ZIP = 49009 SELECT CUSTOMERS.CNO,CUSTOMERS.CNAME FROM CUSTOMERS INNER JOIN ZIPCODES ON CUSTOMERS.ZIP = ZIPCODES.ZIP WHERE ZIPCODES.CITY = 'Kalamazoo' AND CUSTOMERS.CNO '#1111' SELECT CUSTOMERS.CNAME FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CNO = ORDERS.CNO INNER JOIN EMPLOYEES ON ORDERS.ENO = EMPLOYEES.ENO WHERE EMPLOYEES.ENAME = 'Jones' SELECT CUSTOMERS.CNAME FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.CNO = ORDERS.CNO INNER JOIN ORDER_LINE ON ORDERS.ONO = ORDER_LINE.ONO INNER JOIN PARTS ON ORDER_LINE.PNO = PARTS.PNO WHERE PARTS.PNAME = "Sleeping Beauty" SELECT PNAME AS Video1 {SELECT PNAME AS Video2 FROM PARTS WHERE Video2Video1} FROM PARTS SELECT PNAME AS Video1 {SELECT PNAME AS Video2 FROM PARTS WHERE UNIT_PRICE = 19.99} FROM PARTS WHERE UNIT_PRICE = 24.99 SELECT DISTINCT ORDERS.CNO FROM ORDERS INNER JOIN ORDER_LINE ON ORDERS.ONO = ORDER_LINE.ONO WHERE ORDER_LINE.PNO = {SELECT ORDER_LINE.PNO FROM ORDER_LINE INNER JOIN ORDERS ON ORDER_LINE.ONO = ORDERS.ONO WHERE ORDERS.CNO = '#11111'} SELECT DISTINCT ORDERS.CNO FROM ORDERS INNER JOIN ORDER_LINE ON ORDERS.ONO = ORDER_LINE.ONO WHERE ORDER_LINE.PNO = {SELECT ORDER_LINE.PNO FROM ORDER_LINE INNER JOIN ORDERS ON ORDER_LINE.ONO = ORDERS.ONO WHERE ORDERS.CNO = '#11111' GROUP BY ORDER.CNO} GROUP BY ORDER.CNO

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.