Create Table Query ); Copy the above statements and paste them into the space th
ID: 3726749 • Letter: C
Question
Create Table Query
);
Copy the above statements and paste them into the space that opened up for you in Visual Studio. After you run the above statement, you should see that the Customer table is created in your new database file. At this point, copy the CREATE TABLE statement and paste it into a new script (text) file. Name this script file yourNetid.txt, where yourNetid is your UIC Netid.
Since there are 20 tables in the relational schema, you need to repeat the CREATE TABLE statement 20 times. Use the GO command at the end to run the SQL statement and create all your tables at once. You should have 20 CREATE TABLE statements in yourNetid.txt file.
PK OrderiD PK MeaID PK.FK1 PK MenulD FK1 MenuTypelD FK1 TableNo FK2 CustomerlD FK3 StaffNo Table MenuT PK IableNo PK RecipelD PK MenuTyRelD PK BilINo FromTime ToTime FK1 Meal ID | PK | Staff FK1 OrderlD Recipe PK FK1 PK,FK2 PK | Receipt FK1 TableNo FK2 JobTitle PaymentType TotalAmount FK1 BillNo FK2 CC No | PK.FK1 |ltern PK CustomerlD PK FK1 StaffNo FK1 CustomeriD SupplyTypeExplanation / Answer
CREATE TABLE `Table`(
TableNo int NOT NULL PRIMARY KEY,
Size int,
IsReserved TINYINT(1)
);
CREATE TABLE Customer(
CustomerID int NOT NULL PRIMARY KEY,
`Name` VARCHAR(255),
Phone BIGINT,
Email VARCHAR(255)
);
CREATE TABLE Staff(
StaffNo int NOT NULL PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Street VARCHAR(100),
City VARCHAR(50),
`State` VARCHAR(50),
Zip CHAR(6),
Phone BIGINT,
Gender ENUM('M', 'F'),
BirthDate DATE,
JobTitle VARCHAR(255),
Salary DOUBLE,
JoinedDate DATE
);
CREATE TABLE OrderHeader(
OrderID int PRIMARY KEY,
`Date` DATE,
`Time` TIME,
TableNo int,
CustomerID int,
StaffNo int,
FOREGIN KEY(TableNo) REFERENCES Table(TableNo),
FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY(StaffNo) REFERENCES Staff(StaffNo)
);
CREATE TABLE ReservationNo(
ReservationNo int NOT NULL PRIMARY KEY,
`Date` DATE,
`Time` TIME,
partyOf VARCHAR(50),
TableNo int,
CustomerID int,
FOREIGN KEY(TableNo) REFERENCES `Table`(TableNo),
FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
);
CREATE TABLE Bill(
BillNo int NOT NULL PRIMARY KEY,
`Date` DATE,
`Time` TIME,
BillAmount DOUBLE,
OrderID int,
FOREIGN KEY(OrderID) REFERENCES OrderHeader(OrderID)
);
CREATE TABLE Meal(
MealID int NOT NULL PRIMARY KEY,
`Name` VARCHAR(255),
`Description` TEXT,
UnitPrice DOUBLE,
Size int
);
CREATE TABLE OrderLine(
OrderID int,
MealID int,
Quantity int,
UnitPrice DOUBLE,
PRIMARY KEY(OrderID, MealID),
FOREIGN KEY(OrderID) REFERENCES OrderHeader(OrderID),
FOREIGN KEY(MealID) REFERENCES Meal(MealID)
);
CREATE TABLE CreditCard(
CC_No int NOT NULL PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
ExpiryDate DATE,
VerificationCode VARCHAR(20),
CustomerID int,
FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID)
);
CREATE TABLE CashReceipt(
ReceiptNo int NOT NULL PRIMARY KEY,
`Date` DATE,
`Time` TIME,
PaymentType VARCHAR(20),
TotalAmount DOUBLE,
BillNo int,
CC_No int,
FOREIGN KEY(BillNo) REFERENCES Bill(BillNo),
FOREIGN KEY(CC_No) REFERENCES CreditCard(CC_No)
);
CREATE TABLE NextOfKin(
StaffNo int PRIMARY KEY,
FullName VARCHAR(255),
Relationship VARCHAR(30),
Steet VARCHAR(100),
City VARCHAR(50),
State VARCHAR(50),
Zip CHAR(6),
Phone BIGINT
FOREIGN KEY(StaffNo) REFERENCES Staff(StaffNo)
);
CREATE TABLE MenuType(
MenuTypeID int NOT NULL PRIMARY KEY,
FromTime TIME,
ToTime TIME
);
CREATE TABLE Menu(
MenuID INT NOT NULL PRIMARY KEY,
Season VARCHAR(50),
MenuTypeID int,
FOREIGN KEY(MenuTypeID) REFERENCES MenuType(MenuTypeID)
);
CREATE TABLE MealMenu(
MealID int,
MenuID int,
PRIMARY KEY(MealID, MenuID),
FOREIGN KEY(MealID) REFERENCES Meal(MealID),
FOREIGN KEY(MenuID) REFERENCES Menu(MenuID)
);
CREATE TABLE RecipeDirection(
RecipeID int NOT NULL PRIMARY KEY,
ServingSize int,
Directions VARCHAR(255),
MealID int,
FOREIGN KEY(MealID) REFERENCES Meal(MealID)
);
CREATE TABLE StaffMeal(
MealID int,
StaffNo int,
PRIMARY KEY(MealID, StaffNo),
FOREIGN KEY(MealID) REFERENCES Meal(MealID),
FOREIGN KEY(StaffNo) REFERENCES Staff(StaffNo)
);
CREATE TABLE SupplyItem(
ItemNo int NOT NULL PRIMARY KEY,
`Name` VARCHAR(255),
`Description` TEXT,
QtyOnhand VARCHAR(100),
SupplyType VARCHAR(100)
);
CREATE TABLE Recipe(
RecipeID int,
ItemNo int,
PRIMARY KEY(RecipeID, ItemNo),
FOREIGN KEY(RecipeID) REFERENCES RecipeDirection(RecipeID),
FOREIGN KEY(ItemNo) REFERENCES SupplyItem(ItemNo)
);
CREATE TABLE TABLE Ingredient(
ItemNo int NOT NULL PRIMARY KEY,
UnitOfMeasure DOUBLE,
CaloriesPerUnit DOUBLE,
FOREIGN KEY(ItemNo) REFERENCES SupplyItem(ItemNo)
);
CREATE TABLE Manager(
StaffNo int NOT NULL PRIMARY KEY,
Performace VARCHAR(100),
PositionDate DATE,
CarAllowance DOUBLE,
MonthlyBonus DOUBLE,
FOREIGN KEY(StaffNo) REFERENCES Manager(StaffNo)
);
GO;