You are using the relational data model to design a database that will be used by an online book purchase and recommendation service. Think of it like Amazon Books, where customers buy books, review and rate them. When a customer gives a high rating to a book, that means that they like that book. When a customer gives a low rating to a book, that means that they did not like that book.

Your task is to design a database that stores the customers' book purchases, reviews and ratings. This way, you can use this data to analyze what each customer likes to read, and recommend him/her other books accordingly.

Please not that the database design for this application is very simple and straight forward. Keep things simple. Do not try to overdo things.

Please read ALL the questions below before starting to answer them.

Question1: 
Using the information above, list three requirements for that online book purchase and recommendation service.
- Hints:  
-- The customer should be able to ....
-- The customer should be able to ....
-- The system should store .... about books
-- The system should store .... about customers

Question 2: 
List the entities in your data.

Question 3: 
List the attributes for each of the entities that you specified in Question 2.

Question 4: 
List the relationships between your entities. List any relationship-specific attributes.

Question 5: 
Convert your set of entities, attributes and relations into a database schema. Specifically, write down the table names, the columns in each table, and specify which columns are primary keys and which ones are foreign keys.

Question 6: 
Using your database design, write an SQL query to retrieve the customer ID's (or customer names, either is fine) of all customers that gave the book "Alice in Wonderland" a rating of 5.

Question 7: 
Using your database design, write an SQL query to insert a new purchase done by customer John Smith, whose ID is 1234, to the book "The Three Musketeers", whose ID is 5678.

Answer:

Question1

Online book purchase and recommendation service.

• The customer should be able to view a variety of authors and titles, not only the popular authors and book titles but also the lesser known authors and books titles.
• The customer should be able to easily order books from an online bookstore without the restrictions of specific store timings because Online book shopping stores are open round the clock of 24/7, long queues at the cash, and that too while sitting in the comfort of your home.
• The system should store stock up on e-books. When the customer buys an e-book, it immediately gets delivered on your e-book reader. (about book customers)
• The system should store the comments, feedback and book reviews posted by other customers. ( about book customers)

Question 2

The entities in the database design are as follows:

• Customer
• Book
• Rate_review

Question 3

The attributes for each of the entity are listed below:

1. The attributes of the Customer entity is as follows:

Cust_id , Cust_name, Book_ID

1. The attributes of the Book entity is as follows:

Book_id ,Book_name,Price,Author

1. The attributes of the Rate_review entity is as follows:

Cust_id, Book_id, Rating, Review

Question 4:

The relationship between the Customer, Book, and Rate_review entities are as follows:

• The customer purchases a book
• The customer gives rating/review to the book.

Question 5:

The following list specifies the table names, the columns in each table, and specifies the primary keys and the foreign keys.

Customer table:

Primary key: Cust_id      

Foreign Key: Book_ID of table book

Book table:

Primary key: Book_id

Rate_review table:

Primary key: { Cust_id ,Book_ID }          

Foreign Key: Book_ID of table book and Cust_id of table customer.

Question 6:

To select the customer ID from the customer table that gave the book "Alice in Wonderland" a rating of 5.

SELECT Customer.Cust_id "C_Id",Customer.Cust_name "C_Name",

Rate_review.Rating "Rating" FROM Customer, Rate_review

WHERE Rate_review.rating=5 AND book_name like 'Alice in Wonderland' AND C.cust_id= R.cust_id;

Question 7:

To insert a new purchase done by a customer John Smith, whose ID is 1234, to the book "The Three Musketeers", whose ID is 5678.

INSERT INTO Customer (Cust_id, Cust_name, Book_ID)

VALUES (1234,'John Smith',5678);