You are using the relational data model to design a database that will be used by an online book purchase and recommendation service

Subject:Computer SciencePrice:4.87 Bought7

Share With

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.