CREATE TABLE customers (
customer_id INT NOT NULL PRIMARY KEY,
cust_first_name VARCHAR(20),
cust_last_name VARCHAR(20),
cust_address VARCHAR(20),
phone_numbers int NOT NULL,
nls_language VARCHAR(3),
nls_territory VARCHAR(30),
credit_limit INT(10) ,
account_mgr_id int NOT NULL,
cust_geo_location GEOMETRY,
date_of_birth DATE,
matrital_status VARCHAR(20),
gender VARCHAR(1),
income_level VARCHAR(20)
);
CREATE TABLE Orders (
order_id INT(12) NOT NULL,
order_date TIMESTAMP(6),
order_mode VARCHAR(2),
customer_id INT(6) NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id),
order_status INT(6),
order_total INT(12),
sales_rep_id INT(6),
promotion_id INT(12),
PRIMARY KEY (order_id)
);
CREATE TABLE product_iformation (
product_id INT(6) NOT NULL,
product_name VARCHAR(50),
product_description VARCHAR(2000),
category_id INT(2),
supplier_id INT(1),
weight_class INT(6),
warranty_period TIMESTAMP,
product_status VARCHAR(20),
list_price INT(8),
min_price INT(8),
catalog_url VARCHAR(5),
PRIMARY KEY (product_id)
);
CREATE TABLE ORDER_ITEM (
order_id INT(12) NOT NULL,
FOREIGN KEY (order_id)
REFERENCES Orders(order_id),
line_item_id INT(3) NOT NULL ,
product_id INT(6) NOT NULL,
FOREIGN KEY (product_id)
REFERENCES product_iformation(product_id),
unit_price INT(8),
quantity INT(12)
);
CREATE TABLE product_description (
product_id INT(8) NOT NULL,
FOREIGN KEY (product_id)
REFERENCES product_iformation(product_id),
language_id VARCHAR(3) NOT NULL,
translated_name NVARCHAR(50) NOT NULL,
translated_descripton NVARCHAR(2000) NOT NULL
);
CREATE TABLE warehouse (
warehouse_id INT(3) NOT NULL PRIMARY KEY,
warehouse_spec LONGTEXT,
warehouse_name VARCHAR(35),
location_id INT(4),
wh_geo_location GEOMETRY
);
CREATE TABLE inventroies (
product_id INT(6) NOT NULL,
FOREIGN KEY (product_id)
REFERENCES product_iformation(product_id),
warehouse_id INT(3) NOT NULL,
FOREIGN KEY (warehouse_id)
REFERENCES warehouse(warehouse_id),
quantity_on_hand INT(8) NOT NULL
);
CREATE TABLE online_media (
product_id INT NOT NULL,
FOREIGN KEY (product_id)
REFERENCES product_iformation(product_id),
product_photo LONGBLOB,
product_photo_signature LONGBLOB,
photo_thumbnail LONGBLOB,
product_video LONGBLOB,
product_audio LONGBLOB,
product_text LONGBLOB,
product_testimonials LONGBLOB
);
CREATE TABLE tbs_extra (
tbs_id INT NOT NULL,
extra_info LONGTEXT
);
CREATE TABLESPACE tbs_extra
ADD DATAFILE 'tbs_extra.ibd'
INITIAL_SIZE = 10m
ENGINE = InnoDB;