Imagine that you are hired as a database designer at one of the fastest-growing e-commerce companies called Atazon. Popularity of Atazon has increased manifold-times during pandemic and they want a new database to scale up their operations. They have hired you to create a new database schema for their e-commerce company. From the discussions at various meetings with the database manager, you have come to know the following:

 

1.         You have identified CUSTOMER, PRODUCT, and TRANSACTION as the three main entities of the database.

2.         Each customer can order multiple products from the e-commerce website, and a product is owned by only one customer.

3.         A customer can make multiple transactions; however, a transaction can belong to only one customer.

4.         A transaction can have multiple products, and a product can belong to multiple transactions, in case the product is returned by a customer.

After the requirements gathering, you have decided to take the first step towards designing a database, i.e., creating an ER diagram. Given the above description, please do the following - construct an ER diagram for Atazon using the following steps.

 

1.         Identify the entities and show them graphically.

2.         During your meeting with database manager, you have written down the attributes that you can think are important for the three entities. You have also assigned at least one unique attribute to each entity. Following is from your notes:

CUSTOMER has attributes such as customer_id, name, age, gender and zipcode. Customer_id is unique.
PRODUCT has attributes such as product_id, product_name and price. Product_id is unique.
TRANSACTION has attributes such as purchase_id, customer_id, product_id, date_of_purchase, if_returned. Purchase_id is unique.
3.         Define relationships between entities. Show them graphically.

4.         Assign cardinality (1:1, 1:N, N:1, M:N) to each relationship.

 

Submission guidelines: You have to submit a 1-page ER diagram in Microsoft Word. To insert shapes for entities, attributes, and relationships, go to Insert à Shapes à Rectangle for entities/Oval for attributes, add line for unique attributes/Parallelogram for relationships. You can add text to shapes using text boxes.