Submission guidelines: Submit the zipped folder containing all the files (database, an image of the database schema, and pdf containing screenshots of all the SQL queries) using the Google form. Rename your submission as Roll number_Name. Submissions in the incorrect format will not be accepted. Any late submission will be summarily given zero marks without any further discussion.

____________________________________________________________________________

As a Software Engineer, you are asked to design a database schema of an E-Commerce platform XYZ. XYZ is an online marketplace for three types of products books, electronics, and furniture.

Following functionalities are made available to the customers:

• Search for products.
• Browse through products available on sale.
• Add products to their cart and save for later.
• Buy products.
• Rate and add reviews for products they bought in the past.
• Browse through their past orders.
• [Functionality for Bonus marks] Products recommendation by the platform based on their purchase and search history.

A user can signup on the platform by using a valid email id and password.

XYZ has a fixed set of retailers in various cities from which it procures products.

Each product contains information about the Retailer from which it was purchased by the platform, along with other information like description, date of manufacture, etc. Product prices are dynamic and can be updated.

Create a database schema for the above application. Your database must include the name of the tables you have identified, the attributes of the table and the integrity constraints like Primary key, Foreign key, and NOT NULL.

[5 marks = 3 (table and attribute definitions) + 2 (defining integrity constraints)]

Write SQL queries for the following questions. Questions 1-20 carry 2 marks each. Question 21

(bonus question) carries 5 marks. No partial marks for questions 1-20.

1. Populate the tables with at least 10 dummy records. (You can use a dummy data

generator, but please ensure that the database constraints are satisfied.)

2. Delete a user from the database. After deleting the user update name of the user as Anonymous in all the ratings and reviews written by that user.
3. Decrement the price of all products priced above Rs. 5000 by 10%, which were viewed by less than 10 users in the last 3 months.
4. Add 2 delivery addresses for a user with userid = 101.
5. Find all retailers and their email addresses who operate from the city Delhi.
6. Find email addresses of all users who reside in the city Mumbai and have made a total purchase greater than or equal to Rs. 5000 in the past.
7. Find all products in the database whose name contains the string le . E.g. Apple, Oracle, etc.
8. Find the last 3 orders of the first user.
9. Find products in the cart of the second user.
10. List all books published after 2001.
11. List all electronics products in the price range 10k-20k.
12. List all furniture pieces by the retailer Ikea.
13. Sort all laptops according to the price in increasing order.
14. List all products that were added to the database after 27/07/2019.
15. List all books authored by Franz Kafka.
16. Print the UserId and EmailId of all users who have saved a product in the cart, whose quantity is less than 3.
17. Find the order with the maximum number of products.
18. List all products added to the database in the past 10 days.
19. List all retailer ids whose products, userid = 55 has purchased.
20. Write a query to update the discount on all new products by 5% and store it as a new table Diwali_Deals.
21. [Bonus Question] List the top 10 recommended products for the userid=101 based on the users purchase and search history.