You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
105 lines
3.0 KiB
105 lines
3.0 KiB
CREATE TABLE IF NOT EXISTS products (
|
|
product_id int PRIMARY KEY AUTO_INCREMENT,
|
|
product_name VARCHAR(20) NOT NULL,
|
|
price DOUBLE NOT NULL,
|
|
stock_quantity INT DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
customer_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
customer_name VARCHAR(20) NOT NULL,
|
|
phone VARCHAR(11)
|
|
);
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS kpi (
|
|
kid INT PRIMARY KEY AUTO_INCREMENT,
|
|
grade INT CHECK(grade >= 0),
|
|
sid INT,
|
|
FOREIGN KEY (sid) REFERENCES sales_people(salesperson_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sales_people (
|
|
salesperson_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
saleperson_name VARCHAR(20) NOT NULL,
|
|
phone VARCHAR(11)
|
|
);
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS suppliers (
|
|
supplier_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
company_name VARCHAR(20) NOT NULL,
|
|
phone VARCHAR(11)
|
|
);
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS warehouses (
|
|
warehouse_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
location VARCHAR(20) NOT NULL,
|
|
capacity INT DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sales_orders (
|
|
order_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
order_date DATE NOT NULL,
|
|
customer_id INT,
|
|
salesperson_id INT,
|
|
total_amount int NOT NULL,
|
|
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
|
|
FOREIGN KEY (salesperson_id) REFERENCES sales_people(salesperson_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS order_details (
|
|
order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
order_id INT,
|
|
product_id INT,
|
|
quantity INT NOT NULL,
|
|
unit_price DOUBLE NOT NULL,
|
|
FOREIGN KEY (order_id) REFERENCES sales_orders(order_id),
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS returns (
|
|
return_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
return_date DATE NOT NULL,
|
|
order_id INT,
|
|
reason varchar(30),
|
|
FOREIGN KEY (order_id) REFERENCES sales_orders(order_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS product_suppliers (
|
|
product_supplier_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT,
|
|
supplier_id INT,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id),
|
|
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS product_warehouses (
|
|
product_warehouse_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
product_id INT,
|
|
warehouse_id INT,
|
|
quantity_in_warehouse INT DEFAULT 0,
|
|
FOREIGN KEY (product_id) REFERENCES products(product_id),
|
|
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sales_activities (
|
|
activity_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
activity_name VARCHAR(20) NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
total_sales_amount double DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS shipping_methods (
|
|
shipping_method_id INT PRIMARY KEY AUTO_INCREMENT,
|
|
methodname VARCHAR(20) NOT NULL,
|
|
cost_per_unit double NOT NULL CHECK (cost_per_unit >= 0),
|
|
estimated_delivery_time INT NOT NULL CHECK (estimated_delivery_time > 0),
|
|
order_id INT,
|
|
FOREIGN KEY (order_id) REFERENCES sales_orders(order_id)
|
|
);
|
|
|
|
|