ERD Diagram of the database i) Identify the Entities: First, figure out what you

WRITE MY ESSAY

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

WRITE MY ESSAY