-- ══ Create & Use Database ══
CREATE DATABASE ECommerce_Ultra_DB;
USE ECommerce_Ultra_DB;
-- ══ Core Lookup Tables ══
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryName NVARCHAR(50)
);
CREATE TABLE Suppliers (
SupplierID INT PRIMARY KEY,
SupplierName NVARCHAR(50),
City NVARCHAR(50)
);
-- ══ Customers Table ══
CREATE TABLE Customers (
ID INT PRIMARY KEY,
Name NVARCHAR(100),
Email NVARCHAR(100),
Phone NVARCHAR(20),
City NVARCHAR(50),
Gender NVARCHAR(10),
Age INT,
RegistrationDate DATE DEFAULT GETDATE()
);
-- ══ Products Table ══
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
SKU NVARCHAR(50) UNIQUE,
ProductName NVARCHAR(100),
CategoryID INT FOREIGN KEY REFERENCES Categories(CategoryID),
SupplierID INT FOREIGN KEY REFERENCES Suppliers(SupplierID),
Price DECIMAL(10,2),
StockQuantity INT
);
-- ══ Orders & OrderItems ══
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT FOREIGN KEY REFERENCES Customers(ID),
OrderDate DATE,
TotalAmount DECIMAL(10,2),
Status NVARCHAR(20)
);
CREATE TABLE OrderItems (
OrderItemID INT IDENTITY(1,1) PRIMARY KEY,
OrderID INT FOREIGN KEY REFERENCES Orders(OrderID),
ProductID INT FOREIGN KEY REFERENCES Products(ProductID),
Quantity INT,
LineTotal DECIMAL(10,2)
);
🗄️
Total Tables
10
Fully normalized schema
🔑
Primary Keys
10
One per table
🔗
Foreign Keys
9
Referential integrity enforced
💡 Design Pattern
Star-schema inspired: Orders as fact table, surrounded by Customers, Products, Payments, Shipping dimension tables.