yzkBk2BE

1DROP DATABASE IF EXISTS Sandwich
2GO
3
4CREATE DATABASE Sandwich
5GO
6
7DROP TABLE IF EXISTS Bread
8DROP TABLE IF EXISTS Cheese
9DROP TABLE IF EXISTS Filling
10DROP TABLE IF EXISTS Topping
11DROP TABLE IF EXISTS Extras
12DROP TABLE IF EXISTS Category
13GO
14
15--Stores categories for ingredient types
16CREATE TABLE Category(
17	CategoryID INT IDENTITY NOT NULL PRIMARY KEY,
18	CategoryName VARCHAR(50) NULL
19)
20
21CREATE TABLE Bread(
22	BreadID INT IDENTITY NOT NULL PRIMARY KEY,
23	BreadName VARCHAR(50) NULL,
24	Price DECIMAL(6,2) NULL,
25	CategoryID INT NULL,
26	FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
27)
28
29CREATE TABLE Cheese(
30	CheeseID INT IDENTITY NOT NULL PRIMARY KEY,
31	CheeseName VARCHAR(50) NULL,
32	Price DECIMAL(6,2) NOT NULL,
33	CategoryID INT NULL,
34	FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
35)
36
37CREATE TABLE Filling(
38	FillingID INT IDENTITY NOT NULL PRIMARY KEY,
39	FillingName VARCHAR(50) NULL,
40	Price DECIMAL(6,2) NOT NULL,
41	CategoryID INT NULL,
42	FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
43)
44
45CREATE TABLE Topping(
46	ToppingID INT IDENTITY NOT NULL PRIMARY KEY,
47	ToppingName VARCHAR(50) NULL,
48	Price DECIMAL(6,2) NOT NULL,
49	CategoryID INT NULL,
50	FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
51)
52
53CREATE TABLE Extras(
54	ExtrasID INT IDENTITY NOT NULL PRIMARY KEY,
55	ExtrasName VARCHAR(50) NULL,
56	Price DECIMAL(6,2) NOT NULL,
57	CategoryID INT NULL,
58	FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
59)
60
61INSERT INTO Category VALUES ('Bread'), ('Cheese'), ('Filling'), ('Toppings'), ('Extras')
62
63INSERT INTO Bread VALUES ('White', 0.10, 1), ('Brown', 0.12, 1)
64
65INSERT INTO Cheese VALUES ('Cheddar', 0.15, 2), ('Swiss', 0.13, 2), ('American', 0.10, 2)
66
67INSERT INTO Filling VALUES ('Ham', 1.00, 3), ('Chicken', 0.70, 3), ('Steak', 1.50, 3), ('Tuna', 0.75, 3), ('Turkey', 0.85, 3)
68
69INSERT INTO Topping VALUES ('Lettuce', 0.20, 4), ('Sweetcorn', 0.20, 4), ('Onions', 0.15, 4), ('Cucumbers', 0.15, 4), ('Tomatoes', 0.25, 4)
70
71INSERT INTO Extras VALUES ('Mayo', 0.20, 5), ('Garlic Mayo', 0.25, 5), ('Ketchup', 0.20, 5), ('Chilli', 0.30, 5), ('Bbq', 0.30, 5)
72
73SELECT * FROM Category
74SELECT * FROM Bread
75SELECT * FROM Cheese
76SELECT * FROM Filling
77SELECT * FROM Topping
78SELECT * FROM Extras