DkhK8AH3

1-- MegaPizza DATABASE SCRIPT
2
3-- Deletes Old DataBase
4DROP TABLE IF EXISTS Customer;
5DROP TABLE IF EXISTS CustomerOrder;
6DROP TABLE IF EXISTS PreviouslyOrdered;
7DROP DATABASE MegaPizza;
8
9-- Creates DataBase
10CREATE DATABASE MegaPizza;
11
12-- Customer TABLE
13CREATE TABLE Customer(
14	[CustomerID]		int NOT NULL PRIMARY KEY,											 	-- Customer ID
15	[FName]				VARCHAR(255),																-- Customer First Name
16	[LName]				VARCHAR(255),																-- Customer Last Name
17	[Address]			VARCHAR(255),																-- Customer Address
18	[Phone]				int,																		-- Customer Phone Number
19	[Status]			VARCHAR(255)																-- Customer Status (Hoax or not)
20);
21
22-- Order Table
23CREATE TABLE CustomerOrder(
24	[OrderID]				int NOT NULL PRIMARY KEY,													-- Order ID
25	[Date]					VARCHAR(255),
26	[Time]					VARCHAR(255),
27	[Type]					VARCHAR(255),
28	[Description]			VARCHAR(255),
29	[TotalAmountDue]		VARCHAR(255),
30	[PaymentMethod]			VARCHAR(255),
31	[OrderStatus]			VARCHAR(255),
32	[MenuItemsOrderedID]	int,
33	[EmployeeID]			int,
34	[CustomerID]			int FOREIGN KEY REFERENCES Customer(CustomerID)	
35	ON UPDATE CASCADE 
36	ON DELETE CASCADE										-- Order ID Foreign Key	
37);
38
39-- PreviouslyOrdered Table (Used for customer's previously ordered Orders)
40CREATE TABLE PreviouslyOrdered(
41	[ID]						int NOT NULL PRIMARY KEY,												-- PreviouslyOrdered ID
42	[OrderID]					int FOREIGN KEY REFERENCES CustomerOrder([OrderID])						-- Order ID Foreign Key	
43);
44ALTER TABLE	Customer ADD CONSTRAINT  PreviouslyOrderedID FOREIGN KEY (PreviouslyOrderedID) REFERENCES PreviouslyOrdered(ID)		-- Customer Previously Ordered List Foreign Key
45
46
47INSERT INTO Customer (CustomerID,FName,LName, Address, Phone, Status)
48VALUES ('1','Ethan','Moriarty','29 asdatms st','0466166194','Hoax');
49
50SELECT * FROM Customer;