(b) The relations given below are part of a database schema for a bookstore chai

Question

(b) The relations given below are part of a database schema for a bookstore chain which has several branches. Relation names carry the usual meaning and the underlined attribute is a primary key. Assume that the relations have been populated with suitable data as appropriate. Use these relations to write relational algebra expressions for retrieving data as required in (i) to (iii) below. BRANCH (branch#, name, location, number_of_employees) BOOK (book#. book_code, title, publisher, price) CUSTOMER (customer#, name, city, postcode, branch#) BOOKORDER (book#, customer#, order date) (i) Get all details of all customers in all branches, and place your result in a new relation. (ii) Create a new relation to hold the book title, publisher and price for books with a price of pound 50 or less. (iii) Get the contact details of all customers who placed a book order before 25th December, 2013. (c) Map each of the relational algebra expressions formulated in (b) to equivalent SQL statements.

Explanation / Answer

ANSWER:

Here NewCustomer,NEWBOOK,CUSTOMERCONTACTDETAILS are new relations.

SELECT CUSTOMER.customer, CUSTOMER.name, CUSTOMER.city,CUSTOMER.Postcode,customer.branch INTO NewCustomer
FROM CUSTOMER
INNER JOIN BRANCH ON CUSTOMER.branch=BRANCH.branch;

SELECT BOOK.TITLE,BOOK.PUBLISHER,BOOK.PRICE INTO NEWBOOK FROM BOOK WHERE PRICE<=50;

SELECT CUSTOMER.customer, CUSTOMER.name, CUSTOMER.city,CUSTOMER.Postcode,customer.branch into CUSTOMERCONTACTDETAILS FROM CUSTTOMER
INNER JOIN BOOKORDER ON CUSTOMER.CUSTOMER=BOOKORDER.CUSTOMER WHERE BOOKORDER.ORDER_DATE<='2013-12-23';

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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