Question
I need help debugging my code and erd per the following guidelines Customers can order from many restaurants. Restaurants may have orders from many customers.
I need help debugging my code and erd per the following guidelines
Customers can order from many restaurants.
Restaurants may have orders from many customers.
A driver is assigned to the unique order between a single customer and restaurant at a given date and time.
Customers should be tracked by their number, name, address, phone number and payment ID.
Restaurants should be tracked by their number, name, payment ID, address, phone number and cuisine.
Orders will be assigned a unique number and timestamped.
Drivers want to track the status (placed, out, cancelled and fulfilled) of each order.
Drivers have a unique ID number assigned by corporate. We will also need to track their name, phone number, license plate number, drivers license number, payment ID and status (active/inactive).
GrubHub has a proprietary accounting module that will handle all monetary transactions using the assigned payment IDs.
sql code
create table Customer (custNo integer NOT NULL, custFirstName varchar(12), custLastName varchar(12), custAddress varchar(20), custCity varchar(15), custState varchar(02), custZip varchar(05), custPhoneNo varchar(15), custPaymentID varchar(15), CONSTRAINT Customer_PK PRIMARY KEY (custNo));
create table Restaurant (restNo integer NOT NULL, restName varchar(12), restAddress varchar(20), restCity varchar(15), restState varchar(02), restZip varchar(05), restPhoneNo varchar(15), restCuisine varchar(12), restPaymentID varchar(05), CONSTRAINT Restaurant_PK PRIMARY KEY (restNo));
create table Driver (drivNo integer NOT NULL, drivFirstName varchar(12), drivLastName varchar(12), drivLicPlaNo varchar(07), drivLicNo varchar(10), drivPhoneNo varchar(15), drivPaymentID varchar(06), drivStatus varchar(08), CONSTRAINT Driver_PK PRIMARY KEY (drivNo));
create table Orders (ordeNo integer NOT NULL, ordeDate varchar(12), ordeTime varchar(10), ordeStatus varchar(20), custNo integer NOT NULL, restNo integer NOT NULL, drivNo integer NOT NULL, CONSTRAINT Orders_PK PRIMARY KEY (ordeNo)); CONSTRAINT custNo_FK FOREIGN KEY(custNo) REFERENCES Customer(custNo), CONSTRAINT restNoOrders_FK FOREIGN KEY (restNo) REFERENCES Restaurant(restNo)); CONSTRAINT drivNoOrders_FK FOREIGN KEY (drivNo) REFERENCES Driver(drivNo));
insert into Customer (custNo, custFirstName, custLastName, custAddress, custCity, custState, custZip, custPhoneNo, custPaymentID) values (1, 'April', 'Ludgate', '123 S 55 Ave', 'Omaha', 'NE', '68132', '402-553-4397', '23456'), (2, 'April', 'Ludgate', '123 S 55 Ave', 'Omaha', 'NE', '68132', '402-553-4397', '23456'), (3, 'Leslie', 'Knope', '4387 Waffles Drive', 'Pawnee', 'IN', '46011', '234-432-5437', '98754'), (4, 'Ron', 'Swanson', '987 Bacon Avenue', 'Pawnee', 'IN', '46011', '456-987-3265', '234789'), (5, 'Andy', 'Dwyer', '2468 The Pit', 'Pawnee', 'IN', '46011', '', '12390');
insert into Restaurant (restNo, restName, restAddress, restCity, restState, restZip, restPhoneNo, restCuisine, restPaymentID) value (1, 'Paunch Burger', '6872 Lard Lane', 'Pawnee', 'IN', '46011', '678-893-1568', 'Fast food', '4826') (2, 'Paunch Burger', '6872 Lard Lane', 'Pawnee', 'IN', '46011', '678-893-1568', 'Fast food', '4826') (3, 'JJs Diner', '23428 Main St.', 'Pawnee', 'IN', '46011', '456-987-3185', 'Breakfast', '78623') (4, 'Mulligans', '6876 Classy Rd.', 'Indianapolis', 'IN', '46077', '786-235-4862', 'Steakhouse', '48623') (5, 'JJs Diner', '23428 Main St.', 'Pawnee', 'IN', '46011', '456-987-3185', 'Breakfast', '78623')
insert into Driver (drivNo, drivFirstName, drivLastName, drivPhoneNo, drivLicPlaNo, drivLicNo, drivPaymentID, drivStatus) values (1, 'Henry', 'Roth', '(286) 937-2250', 'IUC989', 'C04790049', '444862', 'active'), (2, 'Charity', 'Osborne', '(815) 605-0336', 'REW222', 'D89973937', '959227', 'active'), (3, 'Fritz', 'Macias', '(943) 893-6193', 'XUA177', 'U06119817', '718371', 'active'), (4, 'Brenden', 'Hill', '(513) 284-9064', 'IOL455', 'X22173227', '334452', 'active'), (5, 'Henry', 'Roth', '(286) 937-2250', 'IUC989', 'C04790049', '444862', 'active'), (6, 'Leah', 'Peters', '(909) 477-8843', 'AJA879', 'V44276914', '603268', 'inactive');
insert into Orders (ordeNo, ordeDate, ordeTime, ordeStat, custNo, restNo, drivNo) values (1, '9/30/2020', '6:44', 'out', 1, 1, 1), (2, '9/30/2020', '10:44', 'placed', 2, 2, 2), (3, '9/30/2020', '10:54', 'fulfilled', 3, 3, 3), (4, '9/30/2020', '10:46', 'placed', 4, 4, 4), (5, '9/30/2020', '11:44', 'cancelled', 5, 5, 5);
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started