For this assignment, we will be using the following relational schema for our qu

Question

For this assignment, we will be using the following relational schema for our queries. Customer(CID, Name, Address) Orders(Ordernum, CID, Order_date, Cardnum) Orderlist(Ordernum, ISBN, Qty, Ship_date) Book(ISBN, Title, Author, Qty_in_stock, Price) When an order is placed, there is one entry added to Orders which contains the customer information and order details. Then there is one entry per book entered into Orderlist with "NULL" in the ship date which will be replaced once that line item has been shipped. Each book item will only be shipped when all the copies are present (ie: we have sufficient Qty_in_stock for the Qty of the order, that way there is a unique ship_date for each line item). Note: you can also take advantage of renaming (RA) and views (SQL) to break down some of the queries into smaller parts.
Relational Algebra Queries 4. Write a query which will list the CID and name of all the customers who have ordered every book written by ‘C.S. Lewis’ which we have in the catalogue. For this assignment, we will be using the following relational schema for our queries. Customer(CID, Name, Address) Orders(Ordernum, CID, Order_date, Cardnum) Orderlist(Ordernum, ISBN, Qty, Ship_date) Book(ISBN, Title, Author, Qty_in_stock, Price) When an order is placed, there is one entry added to Orders which contains the customer information and order details. Then there is one entry per book entered into Orderlist with "NULL" in the ship date which will be replaced once that line item has been shipped. Each book item will only be shipped when all the copies are present (ie: we have sufficient Qty_in_stock for the Qty of the order, that way there is a unique ship_date for each line item). Note: you can also take advantage of renaming (RA) and views (SQL) to break down some of the queries into smaller parts.
Relational Algebra Queries 4. Write a query which will list the CID and name of all the customers who have ordered every book written by ‘C.S. Lewis’ which we have in the catalogue. For this assignment, we will be using the following relational schema for our queries. Customer(CID, Name, Address) Orders(Ordernum, CID, Order_date, Cardnum) Orderlist(Ordernum, ISBN, Qty, Ship_date) Book(ISBN, Title, Author, Qty_in_stock, Price) When an order is placed, there is one entry added to Orders which contains the customer information and order details. Then there is one entry per book entered into Orderlist with "NULL" in the ship date which will be replaced once that line item has been shipped. Each book item will only be shipped when all the copies are present (ie: we have sufficient Qty_in_stock for the Qty of the order, that way there is a unique ship_date for each line item). Note: you can also take advantage of renaming (RA) and views (SQL) to break down some of the queries into smaller parts.
Relational Algebra Queries 4. Write a query which will list the CID and name of all the customers who have ordered every book written by ‘C.S. Lewis’ which we have in the catalogue.

Explanation / Answer

The Query wil be:

select CID, Name from Customer where CID in (select CID from Orders where Ordernum in (select Ordernum from Orderlist where ISBN in ( select ISBN from Book where Author = “C. S. Lewis”; ); ); );

First we need to fetch the ISBN of all the books present in Book table for which author is C. S. Lewis?. We can do this by using equal operator as shown above. Then fetch the Ordernum from Orderlist table which belongs to the selected ISBN. Then we need to fetch CID from Orders which belong to the selected Ordernum. At the end, select all the CID and Name from Customer table. These records will consists of only those customers who have ordered books of C. S. Lewis.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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