restaurantdb.txt

drop database if exists restaurantDB;
create database restaurantDB;
use restaurantDB; 

create table restaurant (
    restaurantName varchar(50) primary key not null,
    street varchar(50) not null,
    city varchar(50) not null, 
    pc char(6) not null,
    websiteUrl varchar(100)
); 

create table foodItems (
    itemName varchar(100) primary key not null, 
    price decimal(5,2) not null
);

create table employee (
    id char(9) primary key not null, 
    firstName varchar(50), 
    lastName varchar(50),
    email varchar(100),
    restaurantName varchar(50) not null, 
    foreign key (restaurantName) references restaurant(restaurantName) on delete cascade
);

create table chef (
    id char(9) primary key not null,  
    foreign key (id) references employee(id) on delete cascade
);

create table chefCred (
    chefID char(9) not null, 
    cred varchar(100) not null,
    primary key(chefID, cred), 
    foreign key(chefID) references chef(id) on delete cascade
);

create table serverEmployee (
    id char(9) primary key not null,  
    foreign key (id) references employee(id) on delete cascade
);

create table delivery (
    id char(9) primary key not null,  
    foreign key (id) references employee(id) on delete cascade
);

create table managementEmployee (
    id char(9) primary key not null,  
    foreign key (id) references employee(id) on delete cascade
);


create table shift (
    endTime time, 
    startTime time, 
    shiftDay varchar(15) not null,
    employeeID char(9) not null, 
    primary key(employeeID, shiftDay),
    foreign key (employeeID) references employee(id) on delete cascade
);

create table customer (
    email varchar(100) primary key not null,
    phoneNum char(10), 
    street varchar(50) not null,
    city varchar(50) not null,
    pc char(6) not null,
    firstName varchar(50), 
    lastName varchar(50)
);


create table payment(
    paymentDate date not null, 
    paymentAmount decimal(5,2),
    customerEmail varchar(100) not null,
    creditAmnt decimal(5,2),
    primary key(paymentDate, customerEmail),
    foreign key (customerEmail) references customer(email) on delete cascade
);

create table customerOrder (
    orderId char(5) primary key not null,
    customerEmail varchar(100) not null, 
    totalPrice decimal(7,2) not null,
    tip decimal(5,2),
    restaurantName varchar(50) not null,
    deliveryId char(9) not null, 
    orderTime time,
    timeDelivered time,
    orderDate date, 
    foreign key (customerEmail) references customer(email) on delete cascade,
    foreign key (deliveryId) references delivery(id) on delete cascade,
    foreign key (restaurantName) references restaurant(restaurantName) on delete cascade
);


create table orderContains(
    orderId char(5) not null, 
    itemName varchar(50) not null, 
    primary key(orderId, itemName), 
    foreign key (orderId) references customerOrder(orderId) on delete cascade,
    foreign key (itemName) references foodItems(itemName) on delete cascade
);


create table foodOffered (
    restaurantName varchar(50) not null,
    itemName varchar(50) not null,
    primary key(restaurantName, itemName),
    foreign key (restaurantName) references restaurant(restaurantName) on delete cascade,
    foreign key (itemName) references foodItems(itemName) on delete cascade
);

create table isRelated (
    customerEmail varchar(100) not null,
    employeeID char(9) not null,
    relationshipType varchar(50),
    primary key(customerEmail, employeeID),
    foreign key (employeeID) references employee(id) on delete cascade,
    foreign key (customerEmail) references customer(email) on delete cascade
);


insert into restaurant values
('Miam', '40 Johnson St', 'Kingston', 'K7P8V8', 'https://miam.ca'),
('Kitchen Chicken','200 Princess St','Kingston','K7Z1A7','https://kitchenchicken.ca'),
('Bon Appetit','100 Earl St','Kingston','K7L1B2','https://bon-appetit.ca'),
('Sushi Sushi','440 Gore St','Kingston','K8L1K0','https://sushi-sushi.ca');

insert into foodItems values 
('Tacos', '10.45'),
('Pasta Dish', '15.45'),
('Eggs Benedict', '18.45'),
('Chicken Burger', '9.45'),
('Fish and Chips', '19.00'),
('Sushi Platter', '20.45');

insert into employee values
('000000001','John','August','johnaugust@company.ca', 'Miam'),
('000000002','Olivia','Kelsey','oliviakelsey@company.ca', 'Miam'),
('000000003','Antoine','Williams','antoinewilliams@company.ca', 'Miam'),
('000000004','Georgia','Smith','georgiasmith@company.ca', 'Miam'),

('000000005','Shirley','Acosta','shirleyacosta@company.ca', 'Kitchen Chicken'),
('000000006','Ted','Logan','tedlogan@company.ca', 'Miam'),
('000000007','Marie','Williems','mariewillems@company.ca', 'Bon Appetit'),
('000000008','Neil','Lee','neillee@company.ca', 'Sushi Sushi'),

('000000009','Matthew','Curry','matthewcurry@company.ca', 'Kitchen Chicken'),
('000000010','Ryan','Landry','ryanlandry@company.ca', 'Miam'),
('000000011','Cassie','Stevens','cassiestevens@company.ca', 'Bon Appetit'),
('000000012','Elle','Cortez','ellecortez@company.ca', 'Sushi Sushi'),

('000000013','Lilian','Morse','lilianmorse@company.ca', 'Kitchen Chicken'),
('000000014','Julie','Chase','juliechase@company.ca', 'Kitchen Chicken'),
('000000015','Keon','Hardy','keonhardy@company.ca', 'Kitchen Chicken'),
('000000016','Nicolas','Robin','nicolasrobin@company.ca', 'Kitchen Chicken'),


('000000017','Leila','Petit','leilapetit@company.ca', 'Bon Appetit'),
('000000018','Terrence','Chopin','terrencechopin@company.ca', 'Bon Appetit'),
('000000019','Keanu','Oliver','keanuoliver@company.ca', 'Bon Appetit'),
('000000020','Katie','Douglas','katiedouglas@company.ca', 'Sushi Sushi'),


('000000021','Freddy','Knox','freddyknox@company.ca', 'Sushi Sushi'),
('000000022','Serena','Zamora','serenazamora@company.ca', 'Sushi Sushi'),
('000000023','Aaron','Ramirez','aaronramirez@company.ca', 'Sushi Sushi'),
('000000024','Natalie','Roth','natalieroth@company.ca', 'Bon Appetit');


insert into chef values 
('000000001'),
('000000005'),
('000000009'),
('000000013'),
('000000017'),
('000000021');

insert into chefCred values
('000000001', 'Certified Foodservice Professional'),
('000000005', 'Certified Master Chef'),
('000000009', 'Certified Working Pastry Chef'),
('000000013', 'Master Certified Food Executive'),
('000000017', 'Master Certified Food Executive'),
('000000017', 'Certified Culinary Educator'),
('000000021', 'Certified Chef de Cuisine');

insert into serverEmployee values
('000000002'),
('000000006'),
('000000010'),
('000000014'),
('000000018'),
('000000022');

insert into managementEmployee values
('000000002'),
('000000007'),
('000000011'),
('000000015'),
('000000019'),
('000000023');

insert into delivery values
('000000004'),
('000000008'),
('000000012'),
('000000016'),
('000000020'),
('000000024');

insert into shift values 
('20:00', '12:00', 'Tuesday','000000001'),
('20:00', '12:00', 'Friday','000000001'),
('20:00', '12:00', 'Wednesday','000000002'),
('20:00', '12:00', 'Thursday','000000002'),
('20:00', '12:00', 'Thursday','000000003'),
('20:00', '12:00', 'Monday','000000004'),

('15:30', '9:00', 'Friday','000000005'),
('15:30', '9:00', 'Friday','000000006'),
('15:30', '9:00', 'Friday','000000007'),
('15:30', '9:00', 'Friday','000000008'),

('16:00', '10:00', 'Tuesday','000000009'),
('16:00', '10:00', 'Saturday','000000010'),
('16:00', '10:00', 'Saturday','000000011'),
('16:00', '10:00', 'Saturday','000000012'),

('16:00', '9:00', 'Tuesday','000000013'),
('16:00', '9:00', 'Tuesday','000000014'),
('16:00', '9:00', 'Tuesday','000000015'),
('16:00', '9:00', 'Tuesday','000000016'),

('16:00', '11:00', 'Wednesday','000000017'),
('16:00', '11:00', 'Wednesday','000000018'),
('16:00', '11:00', 'Wednesday','000000019'),
('16:00', '11:00', 'Wednesday','000000020'),

('20:00', '12:30', 'Thursday','000000021'),
('20:00', '12:30', 'Thursday','000000022'),
('20:00', '12:30', 'Thursday','000000023'),

('20:00', '12:30', 'Thursday','000000024'),
('20:00', '12:30', 'Monday','000000024'),
('20:00', '12:30', 'Tuesday','000000024'),
('20:00', '12:30', 'Wednesday','000000024');


insert into customer values
('sarahroth@gmail.com', '6137779999', '100 Bagot St', 'Kingston', 'K9L2F0',  'Sarah','Roth'),
('kylewilliams@gmail.com', '6134448888', '65 Princess St', 'Kingston', 'K7L2F6',  'Kyle','Williams'),
('alexx9@gmail.com', '6133338888', '80 Earl St', 'Kingston', 'K7L2G6',  'Alex','Oliver'),
('ethanh@gmail.com', '6477779999', '300 Princess St', 'Kingston', 'K9L7J6', 'Ethan','Hardy'),
('mariostevens@gmail.com', '6138088088', '650 Earl St', 'Kingston', 'K8L9P7',  'Mario','Stevens'),
('oliveolivia2@gmail.com', '6472225565', '600 Johnson St', 'Kingston', 'K9L9O1',  'Olivia','Curry');

insert into payment values
('2023-01-31', '13.58', 'sarahroth@gmail.com', '00.00'),
('2023-01-31', '20.08', 'kylewilliams@gmail.com', '13.58'),
('2023-01-31', '23.98', 'alexx9@gmail.com', '10.00'),
('2023-01-31', '10.68', 'ethanh@gmail.com', '00.00'),
('2023-01-31', '24.69', 'mariostevens@gmail.com', '3.58'),
('2023-01-31', '26.57', 'oliveolivia2@gmail.com', '1.58'),
('2023-02-01', '26.57', 'oliveolivia2@gmail.com', '1.58'),
('2023-02-02', '26.57', 'oliveolivia2@gmail.com', '1.58'),
('2023-02-02', '24.69', 'ethanh@gmail.com', '1.58');

insert into customerOrder values 
('00001', 'sarahroth@gmail.com', '13.58', '2.03','Miam','000000004','14:30', '15:00', '2023-01-30'),
('00002', 'kylewilliams@gmail.com', '20.08', '3.01','Bon Appetit','000000008','12:20','12:58', '2023-01-30'),
('00003', 'alexx9@gmail.com', '23.98', '3.59' ,'Miam','000000012','11:08','11:50', '2023-01-31'),
('00004', 'ethanh@gmail.com', '10.68', '1.60','Kitchen Chicken','000000016','10:30','11:02', '2023-01-31'),
('00005', 'mariostevens@gmail.com', '24.69', '3.70','Bon Appetit','000000020','12:45','13:30', '2023-01-31'),
('00006', 'oliveolivia2@gmail.com', '26.57', '3.98','Sushi Sushi','000000024','17:30','18:06',  '2023-01-30'),
('00007', 'oliveolivia2@gmail.com', '26.57', '3.98','Sushi Sushi','000000024','17:30','18:06',  '2023-02-01'),
('00008', 'oliveolivia2@gmail.com', '24.69', '3.98','Bon Appetit','000000024','17:30','18:06',  '2023-02-02'),
('00009', 'ethanh@gmail.com', '20.90', '9.98','Bon Appetit','000000004','18:30','19:06',  '2023-02-02');


insert into orderContains values
('00001','Tacos'),
('00002','Pasta Dish'),
('00003','Eggs Benedict'),
('00004','Chicken Burger'),
('00005','Fish and Chips'),
('00006','Sushi Platter'),
('00007','Sushi Platter'),
('00008','Fish and Chips'),
('00009', 'Pasta Dish');

insert into foodOffered values
('Miam','Tacos'),
('Bon Appetit','Pasta Dish'),
('Miam','Eggs Benedict'),
('Kitchen Chicken','Chicken Burger'),
('Bon Appetit','Fish and Chips'),
('Sushi Sushi','Sushi Platter');

insert into isRelated values
('sarahroth@gmail.com', '000000024', 'Child/Parent'),
('alexx9@gmail.com', '000000019', 'Siblings'),
('kylewilliams@gmail.com', '000000003', 'Siblings'),
('ethanh@gmail.com', '000000015', 'Child/Parent'),
('mariostevens@gmail.com', '000000011', 'Cousins'),
('oliveolivia2@gmail.com', '000000009', 'Cousins');