Untitled
unknown
plain_text
a year ago
7.4 kB
6
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