Untitled

avatar
unknown
plain_text
2 years ago
7.4 kB
17
Indexable
DROP DATABASE IF EXISTS bgo11s4603 ;

CREATE DATABASE bgo11s4603;

USE bgo11s4603;
SHOW TABLES ;
-- table locality
CREATE TABLE locality (
    id INT(11) NOT NULL,
    locality_name VARCHAR(50) NOT NULL,
    province_name VARCHAR(50) NOT NULL,
    country_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;


-- table carries
CREATE TABLE carriers(
    id int(11) NOT NULL AUTO_INCREMENT,
    cid int(11) NOT NULL,
    company_name varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
    telephone varchar(15) NOT NULL,
    locality_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_carriers_cid (cid),
    CONSTRAINT fk_carries_locality_id FOREIGN KEY (locality_id) REFERENCES locality (id)
) ENGINE = InnoDB default CHARSET = utf8;

-- table sellers
CREATE TABLE sellers (
    id int(11) NOT NULL AUTO_INCREMENT,
    cid int(11) NOT NULL,
    company_name varchar(255) NOT NULL,
    address varchar(255) NOT NULL,
    telephone varchar(15) NOT NULL,
    locality_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_sellers_cid (cid),
    CONSTRAINT fk_sellers_locality_id FOREIGN KEY (locality_id) REFERENCES locality (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table warehouses
CREATE TABLE warehouses (
    id int(11) NOT NULL AUTO_INCREMENT,
    warehouse_code varchar(25) NOT NULL,
    address varchar(255) NOT NULL,
    telephone varchar(15) NOT NULL,
    minimum_capacity int NOT NULL,
    minimum_temperature float NOT NULL,
    locality_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_warehouses_warehouse_code (warehouse_code),
    CONSTRAINT fk_warehouses_locality_id FOREIGN KEY (locality_id) REFERENCES locality (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table sections
CREATE TABLE sections (
    id int(11) NOT NULL AUTO_INCREMENT,
    section_number int(11) NOT NULL,
    current_temperature float NOT NULL,
    minimum_temperature float NOT NULL,
    current_capacity int NOT NULL,
    minimum_capacity int NOT NULL,
    maximum_capacity int NOT NULL,
    warehouse_id int(11) NOT NULL,
    product_type_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_sections_section_number (section_number),
    CONSTRAINT fk_sections_warehouse_id FOREIGN KEY (warehouse_id) REFERENCES warehouses (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table buyers
CREATE TABLE buyers (
    id int(11) NOT NULL AUTO_INCREMENT,
    card_number_id int(11) NOT NULL,
    first_name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_buyers_card_number_id (card_number_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table products
CREATE TABLE products (
    id int(11) NOT NULL AUTO_INCREMENT,
    product_code varchar(25) NOT NULL,
    description text NOT NULL,
    height float NOT NULL,
    length float NOT NULL,
    width float NOT NULL,
    weight float NOT NULL,
    expiration_rate float NOT NULL,
    freezing_rate float NOT NULL,
    recom_freez_temp float NOT NULL,
    seller_id int(11) NOT NULL,
    product_type_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_products_product_code (product_code),
    CONSTRAINT fk_products_seller_id FOREIGN KEY (seller_id) REFERENCES sellers (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table product_records
CREATE TABLE product_records (
    id int(11) NOT NULL AUTO_INCREMENT,
    last_update datetime NOT NULL,
    purchase_price float NOT NULL,
    sale_price float NOT NULL,
    product_id int(11) NOT NULL,
    PRIMARY KEY (id),   
    KEY idx_product_records_product_id (product_id),
    CONSTRAINT fk_product_records_product_id FOREIGN KEY (product_id) REFERENCES products (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;


-- table product_orders
CREATE TABLE  purchase_orders (
    id int(11) NOT NULL AUTO_INCREMENT,
    order_number varchar(25) NOT NULL,
    order_date date NOT NULL,
    tracking_code varchar(25) NOT NULL,
    buyer_id int(11) NOT NULL,
    product_record_id int(11) NOT NULL,
    order_status_id int(11) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_product_orders_buyer_id (buyer_id),
    KEY idx_product_orders_product_record_id (product_record_id),
    CONSTRAINT fk_product_orders_buyer_id FOREIGN KEY (buyer_id) REFERENCES buyers (id),
    CONSTRAINT fk_product_orders_product_record_id FOREIGN KEY (product_record_id) REFERENCES product_records (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table sections
CREATE TABLE sections (
    id int(11) NOT NULL AUTO_INCREMENT,
    section_number int(11) NOT NULL,
    current_temperature float NOT NULL,
    minimum_temperature float NOT NULL,
    current_capacity int NOT NULL,
    minimum_capacity int NOT NULL,
    maximum_capacity int NOT NULL,
    warehouse_id int(11) NOT NULL,
    product_type_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_sections_section_number (section_number),
    CONSTRAINT fk_sections_warehouse_id FOREIGN KEY (warehouse_id) REFERENCES warehouses (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table product_batches
CREATE TABLE product_batches(
    id int(11) NOT NULL AUTO_INCREMENT,
    batch_number int(11) NOT NULL,
    due_date DATE NOT NULL,
    minimum_temperature float NOT NULL,
    current_temperature float NOT NULL,
    initial_quantity int NOT NULL,
    current_quantity int NOT NULL,
    manufacturing_date date NOT NULL,
    manufacturing_hour INT NOT NULL,
    section_id int(11) NOT NULL,
    product_id int(11) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_product_batches_section_id (section_id),
    KEY idx_product_batches_product_id (product_id),
    CONSTRAINT fk_product_batches_section_id FOREIGN KEY (section_id) REFERENCES sections (id) ON DELETE CASCADE,
    CONSTRAINT fk_product_batches_product_id FOREIGN KEY (product_id) REFERENCES products (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table employees
CREATE TABLE employees (
    id int(11) NOT NULL AUTO_INCREMENT,
    card_number_id int(11) NOT NULL,
    first_name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    warehouse_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_employees_card_number_id (card_number_id),
    CONSTRAINT fk_employees_warehouse_id FOREIGN KEY (warehouse_id) REFERENCES warehouses (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- table inbound_orders
CREATE TABLE inbound_orders(
    id int(11) NOT NULL AUTO_INCREMENT,
    order_number varchar(50) NOT NULL,
    order_date DATE NOT NULL,
    warehouse_id int(11) NOT NULL,
    employee_id int(11) NOT NULL,
    product_batch_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_inbound_orders_order_number (order_number),
    KEY Idx_inboud_orders_employee_id (employee_id),
    CONSTRAINT fk_inbound_orders_warehouse_id FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_inbound_orders_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id),
    CONSTRAINT fk_inbound_orders_product_batch_id FOREIGN KEY (product_batch_id) REFERENCES product_batches (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- Allow cascade delete on product_batch_id
ALTER TABLE inbound_orders
DROP FOREIGN KEY fk_inbound_orders_product_batch_id,
ADD FOREIGN KEY (product_batch_id) REFERENCES product_batches(id) ON DELETE CASCADE;

SELECT * FROM locality;

USE dbmelifood SHOW TABLES;
Editor is loading...
Leave a Comment