File size: 2,594 Bytes
71c4d3c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | -- Create Database if not exists
CREATE DATABASE IF NOT EXISTS supply_chain_db;
USE supply_chain_db;
-- Drop tables in reverse order of foreign keys for safe recreation
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS customers;
-- 1. Departments Table
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
-- 2. Categories Table
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL
);
-- 3. Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_fname VARCHAR(100),
customer_lname VARCHAR(100),
customer_segment VARCHAR(50),
customer_street VARCHAR(255),
customer_city VARCHAR(100),
customer_state VARCHAR(50),
customer_country VARCHAR(100),
customer_zipcode VARCHAR(50)
);
-- 4. Products Table
CREATE TABLE products (
product_card_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
product_category_id INT,
product_price DECIMAL(10, 2),
product_status INT,
FOREIGN KEY (product_category_id) REFERENCES categories(category_id)
);
-- 5. Orders Table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_customer_id INT,
order_date DATETIME,
shipping_date DATETIME,
shipping_mode VARCHAR(50),
days_for_shipping_real INT,
days_for_shipping_scheduled INT,
delivery_status VARCHAR(50),
late_delivery_risk INT,
order_status VARCHAR(50),
type VARCHAR(50),
market VARCHAR(50),
order_region VARCHAR(100),
order_country VARCHAR(100),
order_state VARCHAR(100),
order_city VARCHAR(100),
order_zipcode VARCHAR(50),
latitude DECIMAL(10, 8),
longitude DECIMAL(11, 8),
FOREIGN KEY (order_customer_id) REFERENCES customers(customer_id)
);
-- 6. Order Items Table
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT,
order_item_cardprod_id INT,
order_item_quantity INT,
order_item_product_price DECIMAL(10, 2),
order_item_discount DECIMAL(10, 2),
order_item_discount_rate DECIMAL(5, 4),
order_item_total DECIMAL(10, 2), -- Net sales (after discount)
sales DECIMAL(10, 2), -- Gross sales (before discount)
benefit_per_order DECIMAL(10, 2),-- Net profit
order_item_profit_ratio DECIMAL(5, 4),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (order_item_cardprod_id) REFERENCES products(product_card_id)
);
|