Database Project
Building a Retail Management System
SQL Live Coding Business Logic
Phase 1: Identifying Entities (Noun Analysis Method)
The first step is not drawing, but "reading". We use the Noun Analysis method: read the system
description and highlight key nouns.
The System Story:
"We are a retail company. A Customer comes to place an Order . This order contains multiple
Products from our inventory. We must record the specific Details of each item sold and its price
at that moment."
Why did we choose these entities?
1 Customer: A real person/entity. We need to store their 3 Order: A distinct event/transaction with a unique ID
name, phone, and address. and Date.
2 Product: A physical item with attributes like Price, 4 Order Details: Because the relationship between
Name, and Stock. Orders and Products is M:N, we need this bridge table.
Tip for your project: Ask yourself: "Do I need to store multiple pieces of information about this noun?" If Yes, it's an Entity
(Table). If No, it's just an Attribute.
1. Business Scenario Overview
We are acting as System Analysts for a large e-store. The goal is to build a database to manage sales, inventory, and financial
reporting.
Customers Products Orders Details
Who is buying? What do we sell? Invoice Header Invoice Body
2. Business Rules & Requirements
Student Note: In your project, you must list these rules clearly before drawing. This is the "Contract" between you and
the client.
A. Structural Rules (Relationships)
These define how data connects (Translates to ERD):
Customers & Orders: A single customer can place "Many" orders. An order belongs to only one customer. (1:M)
Orders & Products: An invoice contains "Many" products, and a product can appear in "Many" invoices. (M:N)
B. Operational Rules (Constraints)
These define limits and logic (Translates to SQL Constraints):
Inventory Check: A sale cannot occur if the requested quantity exceeds the available stock. Check Constraint
Price Freezing: The product price must be recorded in the `Order_Details` table at the moment of sale to prevent
historical invoices from changing if the product price changes later. Business Logic
3. Diagrams & Design
Based on the rules above, here is our roadmap (The Blueprint).
Figure 1: Entity Relationship Diagram (ERD)
Figure 2: Relational Schema (Table Design)
Phase 1: Master Data
We start by creating the static tables that don't change often: Customers and Products.
Pro Tip: Notice the use of `CHECK (Stock_Qty >= 0)` to prevent negative inventory.
-- 1. Customers Table (CRM Data)
CREATE TABLE Customers (
Cust_ID INT PRIMARY KEY,
Full_Name VARCHAR(100) NOT NULL,
City VARCHAR(50),
Credit_Limit DECIMAL(10, 2) DEFAULT 0
);
-- 2. Products Table (Inventory Data)
CREATE TABLE Products (
Prod_ID INT PRIMARY KEY,
Prod_Name VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
Stock_Qty INT CHECK (Stock_Qty >= 0) -- Constraint: No negative stock
);
2 Phase 2: Transaction Data
Now we create the Invoices (Header & Details).
Important Interview Question: Why do we duplicate `Unit_Price` in the Details table?
Answer: To freeze the historical price. The price in the Products table is current, but the price in Order_Details is history.
-- 3. Orders Table (The Invoice Header - Who & When)
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Order_Date DATE DEFAULT GETDATE(),
Customer_ID INT,
FOREIGN KEY (Customer_ID) REFERENCES Customers(Cust_ID)
);
-- 4. Order Details (The Invoice Body - What & How Much)
-- This is the table that resolves the M:N relationship
CREATE TABLE Order_Details (
Detail_ID INT PRIMARY KEY,
Order_ID INT,
Product_ID INT,
Quantity INT,
Unit_Price DECIMAL(10, 2), -- The Frozen Price!
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
FOREIGN KEY (Product_ID) REFERENCES Products(Prod_ID)
);
3 Phase 3: Data Entry Simulation
Let's simulate a sale: A customer (Sherif) buys a Laptop and a Mouse.
-- Insert Master Data
INSERT INTO Customers VALUES (101, 'Sherif Magdy', 'Cairo', 5000.00);
INSERT INTO Products VALUES (1, 'Laptop Dell', 15000.00, 10);
INSERT INTO Products VALUES (2, 'Wireless Mouse', 200.00, 50);
-- 1. Create the Invoice Header (Order #500)
INSERT INTO Orders (Order_ID, Customer_ID) VALUES (500, 101);
-- 2. Add Items to the Invoice (1 Laptop & 2 Mice)
INSERT INTO Order_Details VALUES (1, 500, 1, 1, 15000.00);
INSERT INTO Order_Details VALUES (2, 500, 2, 2, 200.00);
Business Intelligence (BI)
Sales Report Generation
Requirement: Calculate the total invoice value (Quantity × Price) for Order #500.
SELECT
Orders.Order_ID,
Customers.Full_Name,
-- Calculation Logic: Sum of (Qty * Price) for all rows
SUM(Order_Details.Quantity * Order_Details.Unit_Price) AS Total_Invoice_Value
FROM Orders
JOIN Order_Details ON Orders.Order_ID = Order_Details.Order_ID
JOIN Customers ON Orders.Customer_ID = Customers.Cust_ID
WHERE Orders.Order_ID = 500
GROUP BY Orders.Order_ID, Customers.Full_Name;