Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

DATABASE Administrator Make A Difference

Authors: Mohciine Elmourabit

1st Edition

B0CGM7XG75, 978-1722657802

More Books

Students also viewed these Databases questions

Question

4. Why does happiness resist easy change?

Answered: 1 week ago