4hBLQ380

1CREATE TABLE IF NOT EXISTS contact_info
2(
3    contact_info_id VARCHAR(100) PRIMARY KEY,
4    name VARCHAR (100) NOT NULL,
5    address VARCHAR (150) NOT NULL,
6    email VARCHAR(150) UNIQUE NOT NULL,
7    phone VARCHAR(30) NOT NULL
8);
9CREATE TABLE IF NOT EXISTS users(
10    user_id VARCHAR(100),
11    username VARCHAR(50),
12    password VARCHAR(255),
13    PRIMARY KEY(user_id,username),
14    FOREIGN KEY (user_id) references contact_info(contact_info_id) ON DELETE CASCADE
15);
16CREATE TABLE IF NOT EXISTS food_category
17(
18    food_category_id INTEGER(12) NOT NULL,
19    food_category_name VARCHAR(100),
20    PRIMARY KEY(food_category_id,food_category_name)
21);
22
23CREATE TABLE IF NOT EXISTS food_item
24(
25    food_item_name VARCHAR(100) PRIMARY KEY,
26    food_item_price INTEGER(10),
27    food_category_id INTEGER(10),
28    FOREIGN KEY(food_category_id) REFERENCES food_category(food_category_id) ON DELETE CASCADE
29);
30
31CREATE TABLE IF NOT EXISTS menu
32(
33    menu_name VARCHAR(100),
34    menu_start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE,
35    menu_end_date TIMESTAMP,
36    is_menu_active BOOLEAN DEFAULT 1,
37    PRIMARY KEY(menu_name,menu_start_date)
38);
39
40CREATE TABLE IF NOT EXISTS menu_content
41(
42    menu_name VARCHAR(100),
43    food_item_name VARCHAR(100),
44    is_food_available BOOLEAN DEFAULT 1,
45    FOREIGN KEY(food_item_name) REFERENCES food_item(food_item_name) ON DELETE CASCADE,
46    FOREIGN KEY (menu_name) REFERENCES menu(menu_name) ON DELETE CASCADE
47);
48CREATE TABLE IF NOT EXISTS customer(
49    customer_id VARCHAR(100) PRIMARY KEY,
50    FOREIGN KEY (customer_id) REFERENCES users(user_id)
51);
52
53CREATE TABLE IF NOT EXISTS staff_category
54(
55    staff_category VARCHAR (100) PRIMARY KEY,
56    salary double(10,2)
57);
58CREATE TABLE IF NOT EXISTS staff(
59    staff_id VARCHAR(100) PRIMARY KEY,
60    staff_category VARCHAR(100),
61    last_paid_date TIMESTAMP,
62    joined_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE,
63    FOREIGN KEY (staff_id) REFERENCES users(user_id) ON DELETE CASCADE,
64    FOREIGN KEY (staff_category) REFERENCES staff_category(staff_category) ON DELETE CASCADE);
65
66CREATE TABLE IF NOT EXISTS import_company
67(
68    import_company_id VARCHAR(100) PRIMARY KEY,
69    total_transactions double(10,2) DEFAULT 0.0,
70    remain_transactions double(10,2) DEFAULT 0.0,
71    FOREIGN KEY(import_company_id) REFERENCES contact_info(contact_info_id)
72);
73
74CREATE TABLE IF NOT EXISTS restaurant
75(
76    restaurant_id VARCHAR(100) PRIMARY KEY,
77    total_staff INTEGER(10),
78    capacity INTEGER(10),
79    total_tables INTEGER(10),
80    FOREIGN KEY(restaurant_id) REFERENCES contact_info(contact_info_id)  ON DELETE CASCADE
81);
82
83
84CREATE TABLE IF NOT EXISTS restaurant_table(
85    table_no INTEGER(10) PRIMARY KEY,
86    is_empty BOOLEAN DEFAULT 1
87);
88
89CREATE TABLE IF NOT EXISTS import(
90    import_company_id VARCHAR(100),
91    bill_no INTEGER(10),
92    total_price double(10,2),
93    import_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE,
94    PRIMARY KEY(bill_no,import_date),
95    FOREIGN KEY(import_company_id) REFERENCES import_company(import_company_id)
96);
97
98CREATE TABLE IF NOT EXISTS import_detail(
99    import_good VARCHAR(200),
100    import_type VARCHAR(128),
101    bill_no INTEGER(10),
102    quantity double(10,2),
103    price double(10,2),
104    FOREIGN KEY (bill_no) REFERENCES import(bill_no) ON DELETE CASCADE
105);
106
107CREATE TABLE IF NOT EXISTS stock(
108    stock_name VARCHAR(200) PRIMARY KEY,
109	type_of_stock VARCHAR(120),
110    last_import_date TIMESTAMP,
111    quantity double(10,2),
112    FOREIGN KEY (last_import_date) REFERENCES import(import_date)
113);
114CREATE TABLE IF NOT EXISTS reservation
115(
116    customer_id VARCHAR(100),
117    table_no INTEGER(10),
118    number_of_person INTEGER(2),
119    reservation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE,
120    reservation_fulfilled_status BOOLEAN DEFAULT false,
121    reserved_for_date Date,
122    reserved_for_time TIME,
123    PRIMARY KEY (reservation_date),
124    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
125    FOREIGN KEY (table_no) REFERENCES restaurant_table(table_no)
126);
127CREATE TABLE IF NOT EXISTS bill(
128	bill_no INTEGER(10) PRIMARY KEY,
129	order_id INTEGER(10),
130	total_price double(10,2),
131    issue_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE
132);
133-- FOREIGN key refer gareko error cha below. Unchanged as per instruction
134CREATE TABLE IF NOT EXISTS home_delivery(
135    customer_id INT(11),
136    delivery_staff_id INT(11),
137    bill_no INT(11),
138    order_id INT(11),
139    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE,
140    is_delivered BOOLEAN DEFAULT 0,
141    PRIMARY KEY (order_date),
142    FOREIGN KEY (customer_id) REFERENCES customer(user_id),
143    FOREIGN KEY (delivery_staff_id) REFERENCES staff(user_id),
144    FOREIGN KEY (bill_no) REFERENCES bill(bill_no)
145);
146
147
148CREATE TABLE IF NOT EXISTS food_order
149(
150    order_id varchar(50),
151    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE,
152    PRIMARY KEY (order_id)
153);
154
155CREATE TABLE IF NOT EXISTS order_item(
156    order_id varchar(50),
157    food_item_name varchar(100),
158    quantity int(3),
159    FOREIGN KEY (order_id) REFERENCES food_order(order_id),
160    FOREIGN KEY (food_item_name) REFERENCES food_item(food_item_name)
161);
162
163CREATE TABLE IF NOT EXISTS order_relates_staff(
164    order_id varchar(50),
165    staff_id int (11),
166    FOREIGN KEY (order_id) REFERENCES food_order(order_id),
167   FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
168);
169
170CREATE TABLE IF NOT EXISTS order_relates_table(
171    order_id varchar(50),
172    table_no int (11),
173    FOREIGN KEY (order_id) REFERENCES food_order(order_id),
174    FOREIGN KEY(table_no) REFERENCES restaurant_table(table_no)
175);
176    
177CREATE TABLE IF NOT EXISTS order_relates_home_delivery(
178    order_id varchar(50),
179    customer_id int(11),
180    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP UNIQUE,
181    FOREIGN KEY (order_id) REFERENCES food_order(order_id),
182    FOREIGN KEY (customer_id,order_date) REFERENCES home_delivery(customer_id,order_date)
183);