ERD Diagram of the database
i) Identify the Entities:
First, figure out what your database’s main units are (its tables). Depending on what your
project is about, these could be:
Customers
Products
Orders
Suppliers
OrderDetails
ii) Attributes for Each Entity
Customers: CustomerID, Name, Email, Address
Products: ProductID, Name, Description, Price, Stock
Orders: OrderID, CustomerID, OrderDate, TotalAmount
Suppliers: SupplierID, Name, ContactInfo
OrderDetails: OrderDetailID, OrderID, ProductID, Quantity
iii) Relationships Between Entities
One-to-many (1)
Many-to-many (N)
All the Business Rules
1. Management of Product
There must be a unique number for each object.
Products need to be put into groups.
Track how much of each product you have in stock.
2. Management of Order
Orders must be linked to people who have signed up.
There must be a unique order ID for each order.
At each step, the state of the order should be changed (ordered, shipped, delivered).
3. Management of Customer
A customer ID is needed for each person who buys something.
Keep customer contact information safe.
Record what each customer has bought in the past.
4. Management of Supplier
A different source ID is needed for each one.
Keep thorough contact information for your suppliers.
Keep track of the goods that each seller sends you.
5. Reporting
Every day, every week, and every month, make sales reports.
Watch how well your products are doing and what your customers are buying.
Give inventory records to keep track of stock amounts.
5 Key Objectives of the Database
1. Product management that works well
Ensure that over 5,000 goods are correctly tracked and put into the right
categories.
2. Order processing was sped up.
Manage and automate about 1000 items every day, from creation to delivery.
3. Better management of customer relationships
Keep thorough records of the purchases that more than 50,000 registered users
have made.
4. Coordinating with Suppliers
Make it easy to do business and connect with about 200 providers.
5. Reporting everything
Give full, real-time reports on sales, supplies, and customer behavior to help
people make decisions.
SQL queries for Database creation
Database creation
CREATE DATABASE EcoCommerce;
Table creation
CREATE TABLE Customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
address TEXT,
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Suppliers (
supplier_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
contact_name VARCHAR(100),
contact_email VARCHAR(100),
contact_phone VARCHAR(15),
address TEXT
);
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
supplier_id INT,
name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT DEFAULT 0,
FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) DEFAULT ‘Pending’,
total_amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
CREATE TABLE OrderItems (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
Data insertion
INSERT INTO Customers (name, email, phone, address) VALUES
(‘John Doe’, *********************** ‘555-1234’, ‘123 Elm Street’),
(‘Jane Smith’, ************************* ‘555-5678’, ‘456 Oak Street’);
INSERT INTO Suppliers (name, contact_name, contact_email, contact_phone, address)
VALUES
(‘EcoSupplies’, ‘Alice Green’, ****************************** ‘555-8765’, ‘789 Pine Street’),
(‘GreenGoods’, ‘Bob Brown’, *************************** ‘555-4321’, ‘321 Birch Street’);
INSERT INTO Products (supplier_id, name, category, price, stock_quantity) VALUES
(1, ‘Eco-Friendly Notebook’, ‘Stationery’, 5.99, 100),
(2, ‘Reusable Water Bottle’, ‘Accessories’, 9.99, 50);
INSERT INTO Orders (customer_id, total_amount) VALUES
(1, 15.98),
(2, 9.99);
INSERT INTO OrderItems (order_id, product_id, quantity, price) VALUES
(1, 1, 2, 5.99),
(2, 2, 1, 9.99);
Screenshots of the queries and respective responses
