Entity Relationship Diagram Assignment 2022

Part 2:
ER diagram:
Business Rules:
 One user can have only one account and hence the relation between accoutn and user is one
to one.
 One User can give many feedback so the relation between the feedback and user is many-to-
one
 When one buys the item and never receives it and the seller does not response then the buyer
can leave the negative feedback, one user can leave one or many feedback.
 If everything is fine then leaves a positive feedback.
 One supplier can sell many products and one product belongs to one supplier. The relation is
many-to-one
 For resolving the many-to-many relation between order and product one table order products
is used as bridge entity.
This Entity relationship diagram has 12 entities and relationship are shown with the help of crow
foot notation. The username and password is stored in the Account entity and the complete user
details are stored in user table. The address details are stored in the seperate entity. Orderlist table
has the orderid and corresponding to it there can be one or many products. When order is placed
then this orderid is referred and same is used for shipping.
Relational Model:

Conversion of Entity Relationship into Relational Model:
 Entities are converted into relations
 Primary keys are highlighted in bold and underline
 Foreign key are highlighted in italic
 Many-to-many are converted to one-many relation by using bridge relation
User (user_id, firstname, lastname,phonenumber, email, age, dob)
Account (user_id, user_name, user_phonenumber, user_email, user_address, user_password)
Feedback (feedback_id, user_id (fk), comment, typePositive, typeNegative)
Address (address_id, user_id (fk), user_city, user_street, user_country, user_postalcode)
Orderlist (Order_id, user_id (fk), order_date)
Order_product (Order_id, product_id, Quantity)
Product (product_id, prod_name, prod_brand, prod_price, supplier_id (fk))
supplier (Supplier_id, supplier_name, supplier_address, supplier_phone, user_id (fk))
Check_updates (updates_id, Supplier_id, product_updates, views)
place_order (p_order_id, order_id, address_id, user_id, payment_id, dateoforder)
Payment (payment_id, user_id, card_number, cvv)
Shipment (Shipment_id, p_order_id, grand_total, expected delivery date, actual delivery date,
product quantity, shipment_date)
Relational Schema:
User
Attribute Data type Size Key
user_id INT Primary
firstname varchar 20
lastname varchar 20
phonenumber varchar 20
email varchar 50
age INT
dob date
Account
Attribute Data type Size Key
user_id INT
Primary,Fore
ign
user_name varchar 20
user_phonenumber varchar 20
user_email varchar 20