Pmt_id driver phone license plate drivers license drv.

INFO 1620: Hands-on Midterm
Introduction to Database Design
Now that you know how to model a database, write create statements and add records to a database, let’s look at an example end to end!
Task 1: Create an ERD (15 pts)
1.Using the business rules below, create an ERD using LucidChart.
○Make sure to include all needed entities, attributes, relationships and keys needed.
○Double check that you have the right data types assigned for each attribute.
○Look for M:N to relationships and repeating groups. Adjust your model as needed.
Business Rules for the GrubHub Database
You have been asked by the GrubHub drivers of Omaha to help them track customer orders. In a recent meeting, you were able to develop these business rules with their help:
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.
2.Export your ERD as pdf file and save that file named with your last name. This will be one of the files you submit for this assignment.
Task 2: Make the CREATE sсrіpt (15 pts)
3.Using the ERD you created in Task 1, develop a create.sql file that will create a database named grubhub.
4.Using the grubhub database, include statements in your file to create all the entities, attributes and primary and foreign key constraints detailed in your ERD.
○Make sure to end each statement with a semicolon (;)
○The creation sсrіpts we have used in class are a great guide!
5.Test running your create.sql file in our development environment. Adjust your sсrіpt as needed.
Task 3: Add Sample Data (15 pts)
6.Add insert statements to your create.sql file to add the data pictured below. This data is present in tables at the end of this document.
7.Test running your create.sql file in our development environment. Adjust your sсrіpt as needed.
Task 4: Submission (5 pts)
1.2.3.4.5.6.7.8.Upload all of the files needed to complete the steps above to the dropbox in our online class. Please do not zip together these files. Your files should include:
An ERD PDF document.
A Create.sql containing SQL to create your tables and insert data.
9.Then Submit!
record Name Address Phone pmt_id restaurant name address phone
1 April Ludgate 123 S 55 Ave, Omaha, NE 68132 402-553-4397 23456 Paunch Burger 6872 Lard Lane, Pawnee, IN 46011 678-893-1568
2 April Ludgate 123 S 55 Ave, Omaha, NE 68132 402-553-4397 23456 Paunch Burger 6872 Lard Lane, Pawnee, IN 46011 678-893-1568
3 Leslie Knope 4387 Waffles Drive, Pawnee, IN, 46011 234-432-5437 98754 JJ′s Diner 23428 Main St. Pawnee, IN 46011 456-987-3185
4 Ron Swanson 987 Bacon Avenue, Pawnee, IN 46011 456-987-3265 234789 Mulligan′s 6876 Classy Rd., Indianapolis, IN 46077 786-235-4862
5 Andy Dwyer 2468 The Pit, Pawnee, IN 46011 12390 JJ′s Diner 23428 Main St. Pawnee, IN 46011 456-987-3185
6
record cuisine rest. Pmt_id driver phone license plate drivers license drv. Pmt_id drv. Status order date order status
1 Fast food 4826 Henry Roth (286) 937-2250 IUC989 C04790049 444862 active 9/30/2020 6:44 out
2 Fast food 4826 Charity Osborne (815) 605-0336 REW222 D89973937 959227 active 9/30/2020 10:44 placed
3 Breakfast 78623 Fritz Macias (943) 893-6193 XUA177 U06119817 718371 active 9/30/2020 10:54 fulfilled
4 Steakhouse 48623 Brenden Hill (513) 284-9064 IOL455 X22173227 334452 active 9/30/2020 10:46 placed
5 Breakfast 78623 Henry Roth (286) 937-2250 IUC989 C04790049 444862 active 9/30/2020 11:44 cancelled
6 Leah Peters (909) 477-8843 AJA879 V44276914 603268 inactive