61 lines
1.5 KiB
Text
61 lines
1.5 KiB
Text
DROP TABLE IF EXISTS OrderSpec;
|
|
DROP TABLE IF EXISTS Recipe;
|
|
DROP TABLE IF EXISTS Ingredient;
|
|
DROP TABLE IF EXISTS Pallet;
|
|
DROP TABLE IF EXISTS Cookie;
|
|
DROP TABLE IF EXISTS Orders;
|
|
DROP TABLE IF EXISTS Customer;
|
|
|
|
CREATE TABLE Customer (
|
|
name VARCHAR(255) PRIMARY KEY,
|
|
address VARCHAR(255)
|
|
);
|
|
|
|
CREATE TABLE Orders (
|
|
order_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
customer_name VARCHAR(255),
|
|
delivery_date DATE,
|
|
order_date DATE,
|
|
FOREIGN KEY (customer_name) REFERENCES Customer(name)
|
|
);
|
|
|
|
CREATE TABLE Cookie (
|
|
name VARCHAR(255) PRIMARY KEY
|
|
);
|
|
|
|
CREATE TABLE Pallet (
|
|
pallet_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
order_id INT,
|
|
manufacture_date DATETIME,
|
|
delivery_date DATE,
|
|
blocked VARCHAR(255) DEFAULT 'no',
|
|
cookie_name VARCHAR(255),
|
|
FOREIGN KEY(cookie_name) REFERENCES Cookie(name),
|
|
FOREIGN KEY (order_id) REFERENCES Orders(order_id)
|
|
);
|
|
|
|
CREATE TABLE OrderSpec (
|
|
order_id INT,
|
|
cookie_name VARCHAR(255),
|
|
quantity INT,
|
|
PRIMARY KEY (order_id, cookie_name),
|
|
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
|
|
FOREIGN KEY (cookie_name) REFERENCES Cookie(name)
|
|
);
|
|
|
|
CREATE TABLE Ingredient (
|
|
ingredient_name VARCHAR(255) PRIMARY KEY,
|
|
stock INT,
|
|
unit VARCHAR(50),
|
|
delivery_date DATE,
|
|
delivery_amount INT
|
|
);
|
|
|
|
CREATE TABLE Recipe (
|
|
ingredient_name VARCHAR(255),
|
|
cookie_name VARCHAR(255),
|
|
amount FLOAT,
|
|
PRIMARY KEY (ingredient_name, cookie_name),
|
|
FOREIGN KEY (ingredient_name) REFERENCES Ingredient(ingredient_name),
|
|
FOREIGN KEY (cookie_name) REFERENCES Cookie(name)
|
|
);
|