02_create_tables.sql

USE business_performance;
CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    state VARCHAR(50),
    region VARCHAR(20),
    store_type VARCHAR(30),
    opening_date DATE
);
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    job_role VARCHAR(50),
    store_id INT,
    hire_date DATE,
    salary DECIMAL(10,2),

    FOREIGN KEY (store_id)
        REFERENCES stores(store_id)
);
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    category VARCHAR(50),
    cost_price DECIMAL(10,2),
    selling_price DECIMAL(10,2)
);
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    gender VARCHAR(10),
    age INT,
    city VARCHAR(50),
    membership_type VARCHAR(30)
);
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    store_id INT,
    employee_id INT,
    customer_id INT,
    product_id INT,
    quantity INT,
    discount DECIMAL(5,2),

    FOREIGN KEY (store_id)
        REFERENCES stores(store_id),

    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id),

    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),

    FOREIGN KEY (product_id)
        REFERENCES products(product_id)
);
CREATE TABLE targets (
    target_id INT PRIMARY KEY,
    store_id INT,
    target_month DATE,
    sales_target DECIMAL(12,2),

    FOREIGN KEY (store_id)
        REFERENCES stores(store_id)
);
SHOW TABLES;
DESCRIBE stores;
DESCRIBE sales;