--Warehouse Information Table
CREATE TABLE IM_Warehouses (
WarehouseID INT PRIMARY KEY, -- Warehouse ID
WarehouseName NVARCHAR(100), -- Warehouse name
Location NVARCHAR(100) -- Warehouse location
);
--Product List
CREATE TABLE IM_Products (
ProductID INT PRIMARY KEY, -- Product ID
ProductName NVARCHAR(100), -- Product Name
Category NVARCHAR(50), -- Product Category
UnitCost DECIMAL(10, 2), -- Product unit price
ReorderLevel INT -- Reordering level
);
--Product inventory list
CREATE TABLE IM_Inventory (
InventoryID INT PRIMARY KEY, -- Inventory ID
WarehouseID INT, -- Warehouse ID
ProductID INT, -- Product ID
Quantity INT, -- Inventory quantity
LastUpdated DATETIME, -- Last updated time
FOREIGN KEY (WarehouseID) REFERENCES IM_Warehouses(WarehouseID),
FOREIGN KEY (ProductID) REFERENCES IM_Products(ProductID)
);
--Order table
CREATE TABLE IM_Orders (
OrderID INT PRIMARY KEY, -- Order ID
OrderDate DATETIME, -- Order date
CustomerID INT, -- Customer ID, refer to IM_Customers table
WarehouseID INT, -- Warehouse ID, refer to IM_Warehouses table
FOREIGN KEY (WarehouseID) REFERENCES IM_Warehouses(WarehouseID)
);
--Order Details
CREATE TABLE IM_OrderDetails (
OrderDetailID INT PRIMARY KEY, -- Order Details ID
OrderID INT, -- Order ID, refer to the IM_Orders table
ProductID INT, -- Product ID, reference IM_Products table
Quantity INT, -- Order quantity
UnitPrice DECIMAL(10, 2), -- Product unit price
FOREIGN KEY (OrderID) REFERENCES IM_Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES IM_Products(ProductID)
);
--Customer List
CREATE TABLE IM_Customers (
CustomerID INT PRIMARY KEY, -- Customer ID
CustomerName NVARCHAR(100), -- Customer Name
CustomerEmail NVARCHAR(100) -- Customer Email
);
--Simulated data
INSERT INTO IM_Warehouses (WarehouseID, WarehouseName, Location) VALUES
(1, 'JD Intelligent Warehousing-Beijing Library', 'Beijing'),
(2, 'Ali Intelligent Warehousing-Hangzhou Library', 'Hangzhou'),
(3, 'Huawei Intelligent Warehousing-Shenzhen Library', 'Shenzhen'),
(4, 'ZTE Intelligent Warehousing-Chengdu Library', 'Chengdu'),
(5, 'SF Express Intelligent Warehousing-Guangzhou Library', 'Guangzhou');
INSERT INTO IM_Products (ProductID, ProductName, Category, UnitCost, ReorderLevel) VALUES
(1, 'Laptop', 'Electronics', 1200.00, 50),
(2, 'Smartphone', 'Electronics', 800.00, 100),
(3, 'Office Chair', 'furniture', 250.00, 30),
(4, 'earphone', 'Accessories', 150.00, 70),
(5, 'monitor', 'Electronics', 300.00, 20),
(6, 'keyboard', 'Accessories', 80.00, 40),
(7, 'mouse', 'Accessories', 40.00, 50),
(8, 'printer', 'Electronics', 150.00, 10),
(9, 'desk lamp', 'furniture', 90.00, 25),
(10, 'USB flash', 'Accessories', 20.00, 150);
INSERT INTO IM_Customers (CustomerID, CustomerName, CustomerEmail) VALUES
(1, 'Customer A', 'customerA@'),
(2, 'Customer B', 'customerB@'),
(3, 'Customer C', 'customerC@'),
(4, 'Customer D', 'customerD@'),
(5, 'Customer E', 'customerE@'),
(6, 'Customer F', 'customerF@'),
(7, 'Customer G', 'customerG@'),
(8, 'Customer H', 'customerH@'),
(9, 'Customer I', 'customerI@'),
(10, 'Customer J', 'customerJ@');
INSERT INTO IM_Inventory (InventoryID, WarehouseID, ProductID, Quantity, LastUpdated) VALUES
(1, 1, 1, 60, '2024-08-01'),
(2, 1, 2, 120, '2024-08-01'),
(3, 1, 6, 200, '2024-08-01'),
(4, 1, 9, 30, '2024-08-01'),
(5, 2, 1, 80, '2024-08-01'),
(6, 2, 3, 20, '2024-08-01'),
(7, 2, 7, 90, '2024-08-01'),
(8, 2, 10, 150, '2024-08-01'),
(9, 3, 4, 50, '2024-08-01'),
(10, 3, 5, 10, '2024-08-01'),
(11, 3, 8, 5, '2024-08-01'),
(12, 4, 2, 60, '2024-08-01'),
(13, 4, 5, 40, '2024-08-01'),
(14, 4, 6, 120, '2024-08-01'),
(15, 5, 3, 80, '2024-08-01'),
(16, 5, 7, 30, '2024-08-01'),
(17, 5, 8, 20, '2024-08-01');
INSERT INTO IM_Orders (OrderID, OrderDate, CustomerID, WarehouseID) VALUES
(1, '2024-08-02', 1, 1),
(2, '2024-08-05', 2, 2),
(3, '2024-08-07', 3, 3),
(4, '2024-08-10', 4, 1),
(5, '2024-08-12', 5, 2),
(6, '2024-08-14', 6, 3),
(7, '2024-08-16', 7, 4),
(8, '2024-08-18', 8, 5),
(9, '2024-08-20', 9, 1),
(10, '2024-08-22', 10, 2);
INSERT INTO IM_OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, UnitPrice) VALUES
(1, 1, 1, 10, 1200.00),
(2, 1, 2, 20, 800.00),
(3, 2, 3, 15, 250.00),
(4, 2, 4, 5, 150.00),
(5, 3, 5, 2, 300.00),
(6, 3, 6, 8, 80.00),
(7, 4, 7, 5, 40.00),
(8, 4, 8, 1, 150.00),
(9, 5, 9, 3, 90.00),
(10, 5, 10, 10, 20.00),
(11, 6, 1, 2, 1200.00),
(12, 6, 3, 5, 250.00),
(13, 7, 4, 10, 150.00),
(14, 7, 6, 3, 80.00),
(15, 8, 5, 7, 300.00),
(16, 8, 9, 4, 90.00),
(17, 9, 2, 6, 800.00),
(18, 9, 10, 8, 20.00),
(19, 10, 7, 5, 40.00),
(20, 10, 8, 2, 150.00);