Question

Referring to the relational model in slide 15 in"ER-mapping" (about author, publisher, shopping-basket, provide the algebraic pressing for the following queries Q1: Report the book title, and year for the books that have been written by exactly 2 authors, one of them is Mark Smith" Q2: For each customer, we need to report the email and the number of books this customer have bought across all shopping baskets Q3: Report the unique (distinct author names who have written books in both 2010 and 2011.

Author(name, address, URL)

Book(ISBN, title, year, price,

        publisher_Name)

WrittenBy(name, address, ISBN)

Publisher(name, address, phone, URL)

Warehouse(code, phone, address)

Stocks(ISBN, WH_code, number)

Shopping-Basket(basketID, email)

basketContains(ISBN, basketID, number)

Customer(email, name, address, phone)

Answer 1

Q1. SELECT Book.title , Book.year

FROM Book, Author, WrittenBy, Publisher

WHERE Author.name = WrittenBy.name AND WrittenBy.name = "Mark Smith" AND Book.publisher_name = Publisher.name;

Q2. SELECT Customer.email , count(*) AS 'Number of Books'

FROM Customer, Book, , Shopping-Basket, basketContains

WHERE Customer.email = Shopping-Basket.email

GROUP BY Customer.name;

Q3 . SELECT DISTINCT Author.name

FROM Author, Book

WHERE Book.year ="2010 " AND Book.year= "2011" ;