Given the following relational schema, write queries in relational algebra to an

Question

Given the following relational schema, write queries in relational algebra to answer the English questions. The best approach is to use the online relational algebra tool.

Customer (cid: integer, cname: string, address: string, city string, state: string) Product (pid: integer, pname: string, price: currency, inventory: integer) Shipment (sid: integer, cid: integer, shipdate: Date/Time) ShippedProduct (sid: integer, pid: integer, amount: integer) 1. Return the product name and price for all products with a price less than $5.00. (2 marks) 2. Return the id and names of all customers that have ever received a shipment where a product quantity (amount) in the shipmment is greater than 20. (2 marks) 3, Return a list of all the customer names with addresses in the state 'CA' and have received a shipment prior to January 2,2014. (2 marks) 4. Return the product name and shipdate for all shipments that have an amount greater than the current inventory. (2 marks) 5. Return a list of product ids and names where the product has either a price >= $25.00 or has appeared in a shipment with an amount greater than 10, (2 marks)

Explanation / Answer

1. select pname, price from Product where price < $5;

2. select cid, cname from Customer where cid in (select cid from Shipment as s1, ShipmentProduct as s2

where s1.sid = s2.sid and s2.amount >20);

3. select cname, address from customers, shipment where state = 'CA' and shipment.cid = customer.cid and Date(shipmentdate) < Date('2014-01-02');

4. select pname, shipdate from Product, Shipment, ShipmentProduct where Product.pid = ShipmentProduct.pid and ShipmentProduct.sid = Shipment.sid and Product.inventory > ShipmentProduct.amount;

5. select Product.pid, Product.pname from Product, ShipmentProduct where Product.pid = ShipmentProduct.pid and (Product.price >$25 or (Product.price * ShipmentProduct.amount) > 10);

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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