Pekka Alaluukas /Databases

Database Exercises

Online Store database

Task: Database Design for an Online Store

Step1

Requirements	Relationship type
One Customer can made many Orders One Order is made by One Customer	One-to-Many -> OK
One Order can include many Products One Product can be included in many Orders	Many-to-Many -> Wrong

Step2

Requirements	Relationship type
One Customer can make many Orders One Order is made by one Customer	One-to-Many -> OK
One Order can include many OrderItems One OrderItem belongs to one Order	One-to-Many -> OK
One OrderItem is connected to one Product One Pruduct can be included to many OrderItems	One-to-Many -> OK
One Customer can have many ShoppingCarts One ShoppingCart is made by one Customer	One-to-Many -> OK
One Product can be included in many ShoppingCarts One ShoppingCart item is connected to one Product	One-to-Many -> OK
One Order can have one PaymentMethod One PaymentMethod can be included in many Orders	One-to-Many -> OK
One Review is connected to one Product One Products can be connected to many Reviews	One-to-Many -> OK
One Review is made by one Customer One Customer can make many Reviews	One-to-Many -> OK

Step3

Normalazing the database ->We need to add more tables

So in the final version we have all tables from Step2 and Step3,

Final ER Diagram

Customers

customer_id

Billing Address

address_id

customer_id (FK)

Orders

order_id

customer_id (FK)

Products

product_id

OrderItems

orderitem_id

order_id (FK)

product_id (FK)

Payments

payment_id

order_id (FK)

method_id (FK)

Payment Methods

method_id

Reviews

review_id

customer_id (FK)

product_id (FK)

Shopping Cart

cart_id

customer_id (FK)

product_id (FK)