Here is a command that you can use in your MySQL Server to create a SS Logistics Company Database for Data Analytics Practice.

This is a database for a fictitious logistics management company. This is generated by Data Analytics Team of Slidescope – Training Institute

This Database covers ✅

Routes for shipment planning
Customers & Suppliers
Products catalog
Orders & order details
Shipments & drivers
Warehouses & inventory management

-- Create the database
CREATE DATABASE IF NOT EXISTS LogisticsDB;
USE ssLogisticsDB;

-- 1. Customers table
CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    CompanyName VARCHAR(100) NOT NULL,
    ContactName VARCHAR(100),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    Address TEXT,
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50),
    ZipCode VARCHAR(10)
);

-- 2. Suppliers table
CREATE TABLE Suppliers (
    SupplierID INT AUTO_INCREMENT PRIMARY KEY,
    CompanyName VARCHAR(100) NOT NULL,
    ContactName VARCHAR(100),
    Phone VARCHAR(20),
    Email VARCHAR(100),
    Address TEXT,
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50),
    ZipCode VARCHAR(10)
);

-- 3. Products table
CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    SupplierID INT,
    Category VARCHAR(50),
    UnitPrice DECIMAL(10,2),
    Weight DECIMAL(8,2),
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);

-- 4. Warehouses table
CREATE TABLE Warehouses (
    WarehouseID INT AUTO_INCREMENT PRIMARY KEY,
    WarehouseName VARCHAR(100),
    Address TEXT,
    City VARCHAR(50),
    State VARCHAR(50),
    Country VARCHAR(50),
    ZipCode VARCHAR(10),
    Capacity INT
);

-- 5. Drivers table
CREATE TABLE Drivers (
    DriverID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100),
    Phone VARCHAR(20),
    LicenseNumber VARCHAR(50),
    ExperienceYears INT
);

-- 6. Shipments table
CREATE TABLE Shipments (
    ShipmentID INT AUTO_INCREMENT PRIMARY KEY,
    ShipmentDate DATE,
    DriverID INT,
    OriginWarehouseID INT,
    DestinationWarehouseID INT,
    Status VARCHAR(50),
    FOREIGN KEY (DriverID) REFERENCES Drivers(DriverID),
    FOREIGN KEY (OriginWarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (DestinationWarehouseID) REFERENCES Warehouses(WarehouseID)
);

-- 7. Orders table
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    ShipDate DATE,
    ShipmentID INT,
    Status VARCHAR(50),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (ShipmentID) REFERENCES Shipments(ShipmentID)
);

-- 8. OrderItems table
CREATE TABLE OrderItems (
    OrderItemID INT AUTO_INCREMENT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    UnitPrice DECIMAL(10,2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 9. Inventory table
CREATE TABLE Inventory (
    InventoryID INT AUTO_INCREMENT PRIMARY KEY,
    WarehouseID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (WarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 10. Routes table
CREATE TABLE Routes (
    RouteID INT AUTO_INCREMENT PRIMARY KEY,
    OriginWarehouseID INT,
    DestinationWarehouseID INT,
    DistanceKM DECIMAL(8,2),
    EstimatedTimeHours DECIMAL(5,2),
    FOREIGN KEY (OriginWarehouseID) REFERENCES Warehouses(WarehouseID),
    FOREIGN KEY (DestinationWarehouseID) REFERENCES Warehouses(WarehouseID)
);

Here is the ER Diagram of this Database