car_nikolaeva_sofia/init.sql

CREATE TABLE employee (
    employee_id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    position VARCHAR(100) NOT NULL
);

CREATE TABLE client (
    client_id SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address VARCHAR(200),
    passport VARCHAR(20) NOT NULL
);

CREATE TABLE car (
    car_id SERIAL PRIMARY KEY,
    reg_number VARCHAR(10) UNIQUE NOT NULL,
    brand VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year INT,
    price NUMERIC(10,2) NOT NULL,
    status VARCHAR(50) DEFAULT 'в наличии'
);

CREATE TABLE warehouse (
    warehouse_id SERIAL PRIMARY KEY,
    address VARCHAR(200) NOT NULL
);

CREATE TABLE supplier (
    supplier_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    address VARCHAR(200),
    phone VARCHAR(20)
);

CREATE TABLE contract (
    contract_id SERIAL PRIMARY KEY,
    car_id INT NOT NULL REFERENCES car(car_id),
    client_id INT NOT NULL REFERENCES client(client_id),
    employee_id INT NOT NULL REFERENCES employee(employee_id),
    contract_date DATE NOT NULL DEFAULT CURRENT_DATE,
    sale_price NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES client(client_id),
    car_id INT NOT NULL REFERENCES car(car_id),
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(50) DEFAULT 'новый'
);

-- Поставки 
CREATE TABLE supplier_car (
    supplier_car_id SERIAL PRIMARY KEY,
    supplier_id INT NOT NULL REFERENCES supplier(supplier_id),
    car_id INT NOT NULL REFERENCES car(car_id),
    supply_date DATE NOT NULL DEFAULT CURRENT_DATE,
    supply_price NUMERIC(10,2) NOT NULL
);

-- Хранение на складах
CREATE TABLE car_warehouse (
    car_warehouse_id SERIAL PRIMARY KEY,
    car_id INT NOT NULL REFERENCES car(car_id),
    warehouse_id INT NOT NULL REFERENCES warehouse(warehouse_id),
    arrival_date DATE NOT NULL DEFAULT CURRENT_DATE,
    quantity INT DEFAULT 1
);