SQL Database Schema and Queries for Restaurant Management

Posted on Jan 5, 2025 in Mathematics

Group 23 Project SQL DDL and DML

SQL CREATE TABLE Statements

Employee Table

CREATE TABLE Employee (
    employeeID INT IDENTITY(1000,1) NOT NULL,
    employeeName VARCHAR(40) NOT NULL,
    employeeDOB DATE NOT NULL,
    employeeSalary NUMERIC(7,2) NOT NULL,
    employeeTitle VARCHAR (20),
    CONSTRAINT pk_employeeID PRIMARY KEY (employeeID)
);

Utility Company Table

CREATE TABLE UtilityComp (
    ucompID INT IDENTITY(2000,1) NOT NULL,
    ucompName VARCHAR(50) NOT NULL,
    ucWebsite VARCHAR(40) NOT NULL,
    CONSTRAINT pk_ucompID PRIMARY KEY (ucompID)
);

Order Table

CREATE TABLE Order_ (
    orderID INT IDENTITY(10000,1) NOT NULL,
    orderDate DATE NOT NULL,
    CONSTRAINT pk_orderID PRIMARY KEY (orderID)
);

Dish Table

CREATE TABLE Dish (
    dishID INT IDENTITY(1,1),
    dishName VARCHAR(40) NOT NULL,
    dishPrice NUMERIC(4,2) NOT NULL,
    CONSTRAINT pk_dishID PRIMARY KEY (dishID)
);

Material Table

CREATE TABLE Material (
    materialID INT IDENTITY(1,1),
    materialName VARCHAR(50) NOT NULL,
    materialCost NUMERIC(7,2) NOT NULL,
    materialInventory NUMERIC(7,2) NOT NULL,
    materialUnit VARCHAR(10),
    CONSTRAINT pk_materialID PRIMARY KEY (materialID)
);

Vendor Table

CREATE TABLE Vendor (
    vendorID INT IDENTITY(3000,1),
    vendorName VARCHAR(50) NOT NULL,
    vendorPhone CHAR(10) NOT NULL,
    CONSTRAINT pk_vendorID PRIMARY KEY (vendorID)
);

Bills Table

CREATE TABLE Bills (
    ID INT IDENTITY(1,1),
    billID AS RIGHT('0000000'+CAST(ID AS VARCHAR(5)),5) PERSISTED,
    billDate DATE NOT NULL,
    transType VARCHAR(10) NOT NULL,
    bDescription VARCHAR(100) NOT NULL,
    Amount NUMERIC(10,2) NOT NULL,
    employeeID INT,
    ucompID INT,
    vendorID INT,
    CONSTRAINT pk_Bills_ID PRIMARY KEY (ID),
    CONSTRAINT fk_Bills_employeeID FOREIGN KEY (employeeID) REFERENCES Employee(employeeID) ON DELETE NO ACTION ON UPDATE CASCADE,
    CONSTRAINT fk_Bills_ucompID FOREIGN KEY (ucompID) REFERENCES UtilityComp(UcompID) ON DELETE NO ACTION ON UPDATE CASCADE,
    CONSTRAINT fk_Bills_vendorID FOREIGN KEY (vendorID) REFERENCES Vendor(vendorID) ON DELETE NO ACTION ON UPDATE CASCADE
);

OrderHas Table

CREATE TABLE OrderHas (
    OrderAmount INT NOT NULL,
    dishID INT NOT NULL,
    orderID INT NOT NULL,
    CONSTRAINT pk_orderhas_orderID_dishID PRIMARY KEY (orderID,dishID),
    CONSTRAINT fk_orderhas_dishID FOREIGN KEY (dishID) REFERENCES Dish(dishID) ON DELETE NO ACTION ON UPDATE CASCADE,
    CONSTRAINT fk_orderhas_orderID FOREIGN KEY (orderID) REFERENCES order_(orderID) ON DELETE NO ACTION ON UPDATE CASCADE
);

UsedIn Table

CREATE TABLE UsedIn (
    materialID INT,
    dishID INT,
    CONSTRAINT pk_usedin_mID_dID PRIMARY KEY (materialID,dishID),
    CONSTRAINT fk_usedin_dishID FOREIGN KEY (dishID) REFERENCES Dish(dishID) ON DELETE NO ACTION ON UPDATE CASCADE,
    CONSTRAINT fk_usedin_materialID FOREIGN KEY (materialID) REFERENCES Material(materialID) ON DELETE NO ACTION ON UPDATE CASCADE
);

SuppliedBy Table

CREATE TABLE SuppliedBy (
    materialID INT,
    vendorID INT,
    CONSTRAINT pk_SuppliedBy_mID_dID PRIMARY KEY (materialID,vendorID),
    CONSTRAINT fk_SuppliedBy_vendorID FOREIGN KEY (vendorID) REFERENCES Vendor(vendorID) ON DELETE NO ACTION ON UPDATE CASCADE,
    CONSTRAINT fk_SuppliedBy_materialID FOREIGN KEY (materialID) REFERENCES Material(materialID) ON DELETE NO ACTION ON UPDATE CASCADE
);

SQL INSERT Statements

Dish Data

INSERT INTO Dish VALUES
('Dough Drop Soup',10.95),
('Half Roasted Beijing Duck', 28.00),
('Five Color Northern Clear Noodle Salad', 10.95),
('Combo Salad',7.99),
('Pork Belly with Sour Napa', 14.95),
('Braised Pork Belly with Mixed Veg', 14.95),
('Fish Fillet in Chili Bean Paste', 14.95),
('Ma Po Tofu', 9.95),
('Stir-fried Hand Torn Cabbage',9.95),
('Garlic Chives Pie', 5.95);

Order Data

INSERT INTO Order_ VALUES
('2015-03-05'),
('2015-06-06'),
('2015-06-12'),
('2015-04-15'),
('2015-05-07'),
('2015-06-05'),
('2015-08-15'),
('2015-06-16'),
('2015-05-23'),
('2015-07-07');

OrderHas Data

INSERT INTO OrderHas VALUES
(1,2,10000),
(1,1,10000),
(2,10,10001),
(1,5,10001),
(1,4,10002),
(1,7,10002),
(1,3,10003),
(1,4,10003),
(1,6,10003),
(1,8,10003),
(1,2,10004),
(1,3,10005),
(1,2,10005),
(1,9,10006),
(1,5,10006),
(1,5,10007),
(1,1,10008),
(1,4,10008),
(2,10,10009),
(1,2,10009);

Employee Data

INSERT INTO Employee VALUES
('Changsheng Zhang','1980-04-05',3400,'Chief'),
('Tiequan Hao','1975-07-15',3200,'Chief'),
('Chunbiao Wu','1983-06-16',4000,'Manager'),
('Mengyin Wu','1988-07-18',3000,'Waiter'),
('Henry Chen','1995-11-12',2500,'Waiter'),
('Xu Zhao','1977-08-20',2800,'Chief');

Material Data

INSERT INTO Material VALUES
('Duck',10,10,'ea'),
('Flour',5.79,200,'OZ'),
('Egg',2.5,50,'Lb'),
('Green Onion',4.53,32,'Lb'),
('Tomato',1.2,40,'ea'),
('Potato',0.9,20,'Lb'),
('Pancake Dough',3.5,20,'dz'),
('Cucumber',0.35,25,'ea'),
('Lettuce',0.54,20,'ct'),
('Sweet Brown Sauce',10,2,'ea'),
('Chinese Bean Sauce',11,2,'ea'),
('Chinese Spicy Bean Sauce',13,2,'ea'),
('Pork',2.5,30,'Lb'),
('Caraway',0.13,10,'OZ'),
('Black Fungus',10,30,'OZ'),
('Beef',3.96,30,'Lb'),
('Tofu',5,40,'Lb'),
('Napa',1.55,50,'Lb'),
('Eggplant',1.5,20,'ct'),
('Tilapia',3.5,10,'Lb'),
('Cabbage',0.54,20,'ct'),
('Garlic',3,10,'Lb');

Vendor Data

INSERT INTO Vendor VALUES
('Big Sea Trading','2532993333'),
('Sun Food Market','2066828823'),
('Great Wall Supermarket','4252511600'),
('Cash & Carry','4256415956'),
('Costco','4258271693');

UtilityComp Data

INSERT INTO UtilityComp VALUES
('PSE','www.pse.com'),
('Bellevue Plaza','www.bellevueplaza.com'),
('Bellevue Public Service','www.ci.bellevue.wa.us'),
('Justin C LO CPA','www.justincpa.com');

Bills Data

INSERT INTO Bills VALUES
('2015-07-29','CHECK','CHECK NUMBER 247',258.31,NULL,NULL,3001),
('2015-07-29','CHECK','CHECK NUMBER 234',332.67,NULL,NULL,3000),
('2015-07-28','CHECK','CHECK NUMBER 237',482,NULL,NULL,3002),
('2015-07-24','CHECK','CHECK NUMBER 235',578.37,NULL,NULL,3001),
('2015-07-23','CHECK','CHECK NUMBER 228',141.74,NULL,2002,NULL),
('2015-07-22','CHECK','CHECK NUMBER 240',678.88,1001,NULL,NULL),
('2015-07-22','CHECK','CHECK NUMBER 224',624.87,NULL,NULL,3000),
('2015-07-22','CHECK','CHECK NUMBER 239',1155.47,1003,NULL,NULL),
('2015-07-22','CHECK','CHECK NUMBER 225',1638.37,NULL,NULL,3001),
('2015-07-21','CHECK','CHECK NUMBER 232 Allied Waste Svc ARC ID: 0480000934',41.8,NULL,2002,NULL),
('2015-07-30','DEBIT','Online Payment 4785419581 To AFTS CONTRACT SERVICING 07/31',3568.65,NULL,2001,NULL),
('2015-07-30','DEBIT','ASIAN FOOD CENT BELLEVUE WA',16.61,NULL,NULL,3002),
('2015-07-31','DEBIT','Online Payment 4785419535 To Justin C LO CPA PS',200,NULL,2003,NULL),
('2015-07-27','DEBIT','C&C SMART FOODS BELLEVUE WA',214.39,NULL,NULL,3003),
('2015-07-24','DEBIT','C&C SMART FOODS BELLEVUE WA',101.53,NULL,NULL,3003),
('2015-07-20','CHECK','CHECK NUMBER 221',1665.83,1004,NULL,NULL),
('2015-07-19','CHECK','CHECK NUMBER 220',1250,1005,NULL,NULL),
('2015-07-19','CHECK','CHECK NUMBER 219',1000.51,1006,NULL,NULL),
('2015-07-19','CHECK','CHECK NUMBER 218',1332.55,1002,NULL,NULL),
('2015-07-16','DEBIT','COSTCO WHSE #00 KIRKLAND WA',83.69,NULL,NULL,3004),
('2015-07-15','DEBIT','COSTCO WHSE #00 KIRKLAND WA',43.25,NULL,NULL,3004);

SuppliedBy Data

INSERT INTO SuppliedBy VALUES
(1,3000),
(10,3000),
(11,3000),
(12,3000),
(13,3000),
(15,3000),
(18,3000),
(10,3001),
(11,3001),
(12,3001),
(13,3001),
(15,3001),
(18,3001),
(19,3001),
(20,3001),
(22,3001),
(1,3002),
(2,3002),
(7,3002),
(10,3002),
(15,3002),
(16,3002),
(17,3002),
(11,3002),
(2,3003),
(3,3003),
(4,3003),
(5,3003),
(6,3003),
(8,3003),
(9,3003),
(14,3003),
(19,3003),
(20,3003),
(21,3003),
(3,3004),
(13,3004),
(16,3004);

UsedIn Data

INSERT INTO UsedIn VALUES
(2,1),
(3,1),
(4,1),
(5,1),
(14,1),
(1,2),
(7,2),
(8,2),
(9,2),
(10,2),
(14,2),
(2,3),
(3,3),
(8,3),
(9,3),
(11,3),
(13,3),
(14,3),
(15,3),
(1,4),
(4,4),
(8,4),
(13,4),
(14,4),
(15,4),
(16,4),
(17,4),
(18,4),
(19,4),
(22,4),
(13,5),
(14,5),
(22,5),
(5,6),
(6,6),
(13,6),
(19,6),
(12,7),
(20,7),
(21,7),
(12,8),
(13,8),
(17,8),
(21,9),
(22,9),
(2,10),
(4,10),
(22,10);

SQL SELECT COUNT(*) Statements

SELECT COUNT(*) AS 'Count: Total Bills'
FROM [BUDT758_DB_Student_113].[dbo].[Bills];

SELECT COUNT(*) AS 'Count: Total Dishes'
FROM [BUDT758_DB_Student_113].[dbo].[Dish];

SELECT COUNT(*) AS 'Count: Total Employees'
FROM [BUDT758_DB_Student_113].[dbo].[Employee];

SELECT COUNT(*) AS 'Count: Total Materials'
FROM [BUDT758_DB_Student_113].[dbo].[Material];

SELECT COUNT(*) AS 'Count: Total Orders'
FROM [BUDT758_DB_Student_113].[dbo].[Order_];

SELECT COUNT(*) AS 'Count: Order/Dish Combinations'
FROM [BUDT758_DB_Student_113].[dbo].[OrderHas];

SELECT COUNT(*) AS 'Count: Vendor/Material Combinations'
FROM [BUDT758_DB_Student_113].[dbo].[SuppliedBy];

SELECT COUNT(*) AS 'Count: Material/Recipe Combinations'
FROM [BUDT758_DB_Student_113].[dbo].[UsedIn];

SELECT COUNT(*) AS 'Count: Count: Utility Companies'
FROM [BUDT758_DB_Student_113].[dbo].[UtilityComp];

SELECT COUNT(*) AS 'Count: Vendors'
FROM [BUDT758_DB_Student_113].[dbo].[Vendor];

SQL SELECT Statements

Total Amount of Bills to Each Vendor in 2015

SELECT v.[vendorName], SUM(Amount) AS 'Total Paid'
FROM [BUDT758_DB_Student_113].[dbo].[Bills] b, [BUDT758_DB_Student_113].[dbo].[Vendor] v
WHERE year(b.billDate) = 2015 AND b.vendorID = v.vendorID
GROUP BY v.vendorName
ORDER BY v.vendorName ASC;

Total Sales in 2015

SELECT SUM(total) FROM
(SELECT SUM(o.orderAmount*d.dishPrice) as total FROM orderhas o, dish d, order_
WHERE o.dishID = d.dishID AND order_.orderID = o.orderID AND year(order_.orderDate) = 2015) a;

Salary Bill History for Each Employee

SELECT e.employeeName, b.billID, b.billDate, b.Amount, b.bDescription
FROM bills b, Employee e
WHERE e.employeeID = b.employeeID;

Oldest Employees

SELECT e.[employeeName], e.[employeeDOB]
FROM [BUDT758_DB_Student_113].[dbo].[Employee] e
ORDER BY e.employeeDOB ASC;

Number of Ingredients in Each Dish

SELECT [dishID], count(materialID) AS 'Number of Materials'
FROM [BUDT758_DB_Student_113].[dbo].[UsedIn]
GROUP BY dishID;

Ingredients Needed for Dishes

SELECT d.dishName, m.materialName
FROM material m, UsedIn u, dish d
WHERE u.materialID = m.materialID and d.dishID = u.dishID
GROUP BY d.dishName, m.materialName;

Most Popular Dish and Number of Times Ordered

SELECT o.[dishID], SUM(OrderAmount) AS 'Number of Orders'
FROM [BUDT758_DB_Student_113].[dbo].[OrderHas] o
GROUP BY o.dishID
ORDER BY [Number of Orders] DESC;