BEGIN TRANSACTION
-- USE BMGT402_DB_Instructor_01
-- USE BMGT402_DB_Student_XXX
DROP TABLE OrderDetails;
DROP TABLE DeliveryPerson;
DROP TABLE UMDStudent;
DROP TABLE MenuItem;
CREATE TABLE UMDStudent (
StudentID INT NOT NULL,
StudentName VARCHAR(25) NOT NULL,
Dorm VARCHAR(25) NOT NULL,
CONSTRAINT pk_UMDstudent PRIMARY KEY (StudentID));
CREATE TABLE MenuItem (
itemNo INT NOT NULL,
category VARCHAR(25) NOT NULL,
itemName VARCHAR(25) NOT NULL,
price INT NOT NULL,
priceRange VARCHAR(25) NOT NULL,
itemDescription VARCHAR(30) NOT NULL,
CONSTRAINT pk_MenuItem PRIMARY KEY (itemNo));
CREATE TABLE DeliveryPerson (
deliveryNo INT NOT NULL,
PersonName VARCHAR(25) NOT NULL,
DeliveryPersonID INT NOT NULL,
CONSTRAINT pk_DeliveryPerson PRIMARY KEY (deliveryNo));
CREATE TABLE OrderDetails (
OrderNo INT IDENTITY (50,1) NOT NULL,
StudentID INT NOT NULL,
itemNo INT NOT NULL,
quantity INT NOT NULL,
tipAmount NUMERIC(3,2) NOT NULL,
deliveryNo INT NOT NULL,
orderDate DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT pk_OrderDetails PRIMARY KEY (OrderNo),
CONSTRAINT fk1_OrderDetails FOREIGN KEY (studentID) REFERENCES
UMDStudent(StudentId),
CONSTRAINT fk2_OrderDetails FOREIGN KEY (itemNo) REFERENCES MenuItem(itemNo),
CONSTRAINT fk3_OrderDetails FOREIGN KEY (deliveryNo) REFERENCES
DeliveryPerson(deliveryNo));
--------------------------------------------------------------------
-- Inserting data
INSERT INTO UMDStudent VALUES
(123456,'Bob','Hagerstown'),
(234561,'Jerry','Oakland'),
(345612,'Barbie','Ellicott'),
(456123,'Kendall','Prince Frederick'),
(561234,'Taylor','Johnson-Whittle'),
-- additional values
(678901,'Alex','Prince Frederick'),
(789012,'Emily','Hagerstown'),
(890123,'Max','Ellicott'),
(901234,'Sophia','Oakland'),
(123890,'Luke','Johnson-Whittle'),
(234901,'Emma','Hagerstown'),
-- additional values
(345678,'Avery','Prince Frederick'),
(456789,'Cameron','Hagerstown'),
(567890,'Dylan','Ellicott'),
(078901,'Emily','Oakland'),
(089012,'Jordan','Johnson-Whittle')
--------------------------------------------------------------------
INSERT INTO MenuItem VALUES
(1,'Appetizer','Hummus Platter',23,'$$$','Hummus with oil and herbs'),
(2,'Entree','Greek Salad',8,'$','Romaine with greek dressing'),
(3,'Entree','Falafel Sandwich',13,'$$','Fried falafel in pita bread'),
(4,'Entree','Lamb gyros',11,'$$','Roasted lamb in pita bread'),
(5,'Entree','Greek beef stew',9,'$','Red wine beef stew in broth'),
(6,'Dessert','Lemon & Honey Baklava',15,'$$','Layers of phyllo dough')
-- do not need to add additional values to this
--------------------------------------------------------------------
INSERT INTO DeliveryPerson VALUES
(101,'Josh', 987654),
(102, 'Savannah', 876543),
(103,'Ava', 765432),
(104,'Carlos', 654321),
(105,'Theo', 543210),
(106,'Jerry', 543210),
(107, 'Long', 654321),
(108,'Ron', 765432),
(109,'Wesley', 876543),
(110,'Simon', 987654),
-- additional values
(111,'Adam', 112233),
(112,'Bella', 223344),
(113,'Charlie', 334455),
(114,'Diana', 445566),
(115,'Ethan', 556677),
(116,'Fiona', 667788),
-- additional values
(117, 'Olivia', 778899),
(118, 'Parker', 889900),
(119, 'Quinn', 990011),
(120, 'Riley', 001122),
(121, 'Sawyer', 112233);
--------------------------------------------------------------------
INSERT INTO OrderDetails VALUES
(123456, 1, 3, 7.10, 101, '2023-05-01'),
(234561, 2, 2, 3.45, 102, '2023-05-01'),
(345612, 6, 1, 4.50, 103, '2023-05-02'),
(456123, 5, 3, 6.75, 104, '2023-05-02'),
(561234, 4, 5, 5.65, 105, '2023-05-02'),
(123456, 4, 3, 1.10, 106, '2023-05-03'),
(234561, 5, 2, 1.12, 107, '2023-05-04'),
(345612, 3, 1, 1.14, 108, '2023-05-04'),
(456123, 2, 3, 1.16, 109, '2023-05-04'),
(561234, 1, 5, 1.18, 110, '2023-05-04'),
-- additional values
(678901, 6, 2, 4.10, 111, '2023-05-06'),
(789012, 5, 5, 2.45, 112, '2023-05-07'),
(890123, 4, 1, 3.50, 113, '2023-05-07'),
(901234, 4, 3, 5.75, 114, '2023-05-08'),
(123890, 4, 4, 4.65, 115, '2023-05-08'),
(234901, 3, 6, 1.10, 112, '2023-05-10'),
(078901, 2, 5, 2.12, 111, '2023-05-11'),
(789012, 1, 3, 3.14, 112, '2023-05-11'),
(890123, 1, 1, 1.16, 112, '2023-05-11'),
(901234, 2, 6, 5.18, 114, '2023-05-12'),
(123890, 3, 2, 4.20, 115, '2023-05-12'),
(234901, 4, 4, 1.22, 116, '2023-05-12'),
-- additional values
(901234, 6, 4, 3.75, 113, '2023-05-13'),
(123890, 2, 5, 2.20, 112, '2023-05-14'),
(234901, 1, 1, 1.50, 115, '2023-05-15'),
(345678, 3, 3, 2.25, 114, '2023-05-15'),
(456789, 4, 2, 1.80, 115, '2023-05-16')
commit;
--------------------------------------------------------------------
-- Displaying all results from the tables
SELECT * FROM UMDStudent;
SELECT * FROM MenuItem;
SELECT * FROM DeliveryPerson;
SELECT * FROM OrderDetails;
--------------------------------------------------------------------
