KIT712 Assignment 1: Database Design for eBay - ER Diagram & Model

This document details the design of a database for eBay, an e-commerce platform. It begins by outlining the business rules governing the platform's functionality, such as user accounts, feedback mechanisms, supplier-product relationships, and order processing. Following the business rules, an Entity-Relationship (ER) diagram is presented, illustrating the entities involved (user, account, feedback, address, orderlist, order_product, product, supplier, check_updates, place_order, payment, and shipment) and their relationships using crow's foot notation. The document then translates the ER diagram into a relational model, converting entities into relations and defining primary and foreign keys. Finally, it presents the relational schema, specifying attributes, data types, sizes, and keys for each table in the database, providing a comprehensive blueprint for the eBay database.

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,Foreign
user_name varchar 20
user_phonenumber varchar 20
user_email varchar 20
user_address varchar 100
user_password varchar 20
Feedback
Attribute Data type Size Key
feedback_id INT Primary
user_id INT Foreign
comment varchar 100
typePositive varchar 50
typeNegative varchar 50
Address
Attribute Data type Size Key
address_id INT Primary
user_id INT Foreign
user_city varchar 50
user_street varchar 50
user_country varchar 50
user_postalcode varchar 6
Orderlist
Attribute Data type Size Key
Order_id INT
PRIMARY,
FOREIGN
user_id INT
PRIMARY,
FOREIGN
order_date DATE
Order_product
Attribute Data type Size Key
Order_id INT
PRIMARY,
FOREIGN
product_id INT
PRIMARY,
FOREIGN
quantity INT

Product
Attribute Data type Size Key
product_id INT Primary
prod_name Varchar 50
prod_brand Varchar 50
prod_price FLOAT
supplier_id INT
Supplier
Attribute Data type Size Key
Supplier_id INT Primary
supplier_name Varchar 30
supplier_address Varchar 50
supplier_phone Varchar 12
user_id INT Foreign
Check_updates
Attribute Data type Size Key
updates_id INT Primary
Supplier_id INT Foreign
product_updates Varchar 100
views Varchar 100
place_order
Attribute Data type Size Key
p_order_id INT Primary
order_id INT Foreign
address_id INT Foreign
user_id INT Foreign
payment_id INT Foreign
dateoforder DATE
Payment
Attribute Data type Size Key
payment_id INT Primary
user_id INT Foreign
card_number INT
cvv INT
Shipment
Attribute Data type Size Key
Shipment_id INT Primary
p_order_id INT Foreign
grand_total Float
expected delivery date DATE
actual delivery date DATE
product quantity INT
shipment_date DATE