Please answer the questions using relational algebra. Customer ( CID , firstname
ID: 3590621 • Letter: P
Question
Please answer the questions using relational algebra.
Customer (CID, firstname, lastname, street, city, state, zip)
Product (PID, Pname, category, description, unit-price)
ActiveOrders (OrderNo, CID, dollar-amount, order-date, ship-date)
OrderHistory (OrderNo, CID, dollar-amount, order-date, delivery-date)
OrderDetails (OrderNo, PID, quantity)
Primary keys are in bold
ActiveOrders= information about the company currently active orders that are either being processed or being shipped
OrderHistory= past orders that have been delivered
OrderDetails= details each active and past order
1. List the id and name of the products for which David Jones has currently placed order(s).
2. Find the id and name of the customers that currently do not have any orders.
Explanation / Answer
1.PROJECTs.Pid,s.Pname ((([SELECTp.firstname='David',p.lastname = "Jones"(RENAMEp(customer))] JOIN p.CID = q.CID [(RENAMEq(ActiveOrders))]) JOIN q.OrderNo = r.OrderNo [(RENAMEr(OrderDetails))]) JOIN r.PID = s.PID [(RENAMEs(Product))]))
EXPLANATION:
The project,select, join are the relational operations. Alias are used with RENAME operation for the tables and are joined based on common tuples.
2.PROJECT CID, firstname, lastname(customer) - PROJECT CID (ActiveOrders)
EXPLANATION:
The set difference operation is denoted by "-". We have projected the required columns in question and removed the customers from table who placed orders.