customer_sales_relations_db.sql

-- SQL Code to safely create and populate tables, dropping them first if they already exist

-- Drop tables in reverse order of dependency to avoid foreign key constraints
DROP TABLE IF EXISTS Line;
DROP TABLE IF EXISTS Invoice;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Customer;
DROP TABLE IF EXISTS Vendor;

-- Create Vendor table
CREATE TABLE Vendor (
    V_CODE VARCHAR(10) NOT NULL PRIMARY KEY,
    V_NAME VARCHAR(50) NOT NULL,
    V_CONTACT VARCHAR(50) NOT NULL,
    V_AREACODE CHAR(5) NOT NULL,
    V_PHONE VARCHAR(15) NOT NULL,
    V_STATE CHAR(2) NOT NULL,
    V_ORDER DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Customer table
CREATE TABLE Customer (
    CUS_CODE VARCHAR(10) NOT NULL PRIMARY KEY,
    CUS_LNAME VARCHAR(50) NOT NULL,
    CUS_FNAME VARCHAR(50) NOT NULL,
    CUS_INITIAL CHAR(1) NOT NULL,
    CUS_AREACODE CHAR(5) NOT NULL,
    CUS_PHONE VARCHAR(15) NOT NULL,
    CUS_BALANCE DECIMAL(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Product table
CREATE TABLE Product (
    P_CODE VARCHAR(10) NOT NULL PRIMARY KEY,
    P_DESCRIPT VARCHAR(100) NOT NULL,
    P_INDATE DATE NOT NULL,
    P_QOH INT NOT NULL,
    P_MIN INT NOT NULL,
    P_PRICE DECIMAL(10,2) NOT NULL,
    P_DISCOUNT DECIMAL(5,2) NOT NULL,
    V_CODE VARCHAR(10) NOT NULL,
    FOREIGN KEY (V_CODE) REFERENCES Vendor(V_CODE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Invoice table
CREATE TABLE Invoice (
    INV_NUMBER VARCHAR(10) NOT NULL PRIMARY KEY,
    CUS_CODE VARCHAR(10) NOT NULL,
    INV_DATE DATE NOT NULL,
    FOREIGN KEY (CUS_CODE) REFERENCES Customer(CUS_CODE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create Line table
CREATE TABLE Line (
    INV_NUMBER VARCHAR(10) NOT NULL,
    LINE_NUMBER INT NOT NULL,
    P_CODE VARCHAR(10) NOT NULL,
    LINE_UNITS INT NOT NULL,
    LINE_PRICE DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (INV_NUMBER, LINE_NUMBER),
    FOREIGN KEY (INV_NUMBER) REFERENCES Invoice(INV_NUMBER),
    FOREIGN KEY (P_CODE) REFERENCES Product(P_CODE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Populate Vendor table
INSERT INTO Vendor (V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE, V_STATE, V_ORDER) VALUES
('V001', 'Tech Supplies', 'John Doe', '123', '555-0110', 'CA', 15000.00),
('V002', 'Office Lux', 'Jane Smith', '124', '555-0220', 'NY', 20000.00);

-- Populate Customer table
INSERT INTO Customer (CUS_CODE, CUS_LNAME, CUS_FNAME, CUS_INITIAL, CUS_AREACODE, CUS_PHONE, CUS_BALANCE) VALUES
('C001', 'Smith', 'John', 'D', '123', '555-1111', 120.50),
('C002', 'Johnson', 'Jane', 'M', '124', '555-2222', 585.75);

-- Populate Product table
INSERT INTO Product (P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE) VALUES
('P100', 'Laptop 15inch', '2023-01-15', 30, 10, 850.00, 0.10, 'V001'),
('P101', 'Office Chair', '2023-02-20', 20, 5, 225.00, 0.15, 'V002');

-- Populate Invoice table
INSERT INTO Invoice (INV_NUMBER, CUS_CODE, INV_DATE) VALUES
('INV001', 'C001', '2023-05-01'),
('INV002', 'C002', '2023-05-02');

-- Populate Line table
INSERT INTO Line (INV_NUMBER, LINE_NUMBER, P_CODE, LINE_UNITS, LINE_PRICE) VALUES
('INV001', 1, 'P100', 1, 850.00),
('INV002', 1, 'P101', 2, 450.00);  -- Note the price here is for two units, assuming the discount is applied per unit