Project Delivery.sql

CREATE DATABASE delivery_system;
USE delivery_system;


CREATE TABLE state (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    abbreviation CHAR(2) NOT NULL,
    population INT
);


CREATE TABLE city (
    id INT AUTO_INCREMENT PRIMARY KEY,
    state_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    population INT,
    
    CONSTRAINT fk_city_state
        FOREIGN KEY (state_id)
        REFERENCES state(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);


CREATE TABLE restaurant (
    id INT AUTO_INCREMENT PRIMARY KEY,
    city_id INT NOT NULL,
    corporate_reason VARCHAR(150) NOT NULL,
    fantasy_name VARCHAR(150),
    address VARCHAR(200),
    telephone VARCHAR(20),

    CONSTRAINT fk_restaurant_city
        FOREIGN KEY (city_id)
        REFERENCES city(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);


CREATE TABLE customer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    city_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    date_birth DATE,
    gender VARCHAR(20),
    telephone VARCHAR(20),
    road VARCHAR(150),
    zip_code VARCHAR(15),
    neighborhood VARCHAR(100),
    complement VARCHAR(100),
    property_number VARCHAR(20),
    reference_point VARCHAR(150),

    CONSTRAINT fk_customer_city
        FOREIGN KEY (city_id)
        REFERENCES city(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);


CREATE TABLE deliveryman (
    id INT AUTO_INCREMENT PRIMARY KEY,
    city_id INT NOT NULL,
    cnh VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    address VARCHAR(200),
    telephone VARCHAR(20),

    CONSTRAINT fk_deliveryman_city
        FOREIGN KEY (city_id)
        REFERENCES city(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);


CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    restaurant_id INT NOT NULL,
    city_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150),
    password VARCHAR(255),
    address VARCHAR(200),
    telephone VARCHAR(20),
    admission_date DATE,
    date_birth DATE,

    CONSTRAINT fk_employee_restaurant
        FOREIGN KEY (restaurant_id)
        REFERENCES restaurant(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_employee_city
        FOREIGN KEY (city_id)
        REFERENCES city(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);


CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(200) NOT NULL,
    unit_value DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    type VARCHAR(50)
);


CREATE TABLE `order` (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    restaurant_id INT NOT NULL,
    deliveryman_id INT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_order DECIMAL(10,2),

    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_order_restaurant
        FOREIGN KEY (restaurant_id)
        REFERENCES restaurant(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_order_deliveryman
        FOREIGN KEY (deliveryman_id)
        REFERENCES deliveryman(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);


CREATE TABLE order_product (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_quantity INT NOT NULL,
    discount DECIMAL(10,2) DEFAULT 0,
    unit_value DECIMAL(10,2) NOT NULL,

    PRIMARY KEY (order_id, product_id),

    CONSTRAINT fk_op_order
        FOREIGN KEY (order_id)
        REFERENCES `order`(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT fk_op_product
        FOREIGN KEY (product_id)
        REFERENCES product(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);