Q.1 (70 points) Draw an ER diagram to design the database system for an online pet shop. Note that the pet shop does not sell pets, but only pet products. Each pet product has a unique product id, product name, description, price, and quantity. Each pet product belongs to at least one product category, and category can have many pet products. Each category has a unique category id and category name (e.g. dog, cat). Category can have sub-categories (e.g. food, supplies). Pet shop’s customers have a unique customer id, name, address, and birth year. There are two types of customers in the system: Repeat and new customers. Repeat customers are the ones who order products regularly, and the system keeps a promotion code for new customers. All customers can order pet products, and each order has a unique order id and order date. Each order is delivered by a driver, and delivery address is stored as well. Drivers have a unique id, name, and salary. Drivers have driving license, each licence having a licence number and expiration date. Driving licences can exist in the system only with drivers. The online system also provides a like button for each pet product; but only repeat customers can like pet products, and the system keeps the date when the user likes a pet product.
Q.2 (30 pts) Translate the E/R diagrams of the previous question into the relational model (i.e., give the relation schemas for each case specifying the table names, together with the attributes, and primary key and foreign key constraints).
