MySQL Help

Using MySQL, write the statements to develop a physical model of your database by creating the tables and constraints based on your Task 1 logical model. Be sure to use your data dictionary and create all constraints appropriately.

Once your tables are created, you will populate them with data using MySQL statements to insert records into the database. You should insert a minimum of five records into each table and more where necessary to ensure that the data in the database is sufficient to allow for each of your reports to be tested.

The result will be a script file that includes statements to

DROP all tables;

CREATE all tables with constraints;

INSERT data into all tables; and

SELECT data from all tables to show the data in each table.

Name this file yourname_task2.txt.

Also create a Word document that shows the structure of each table and the data in each of your tables (copy and paste from MySQL).

Name this file yourname_tables.docx.

My Data Dictionary:

Table Name	Column Name	Description of Data	Data Type	Lgth	NOT NULL	PK	FK
SHOP INFORMATION
	S_id	Shop ID number to identify which store it is.	INTEGER	10		Y
	S_name	Shop's name.	CHAR	20	Y
	Location	Shop's location.	VARCHAR	20	Y
	Hours	Hours the shop is open.	INTEGER	20	Y
CUSTOMER
	C_id	Customer's ID number.	INTEGER	10		Y
	C_fname	Customer's first name.	CHAR	10	Y
	C_lname	Customer's last name.	CHAR	10	Y
	Health_choice	Customer' preferred health choice.	VARCHAR	50	Y
	Flavor_choice	Customer's preferred flavor choice.	VARCGAR	50	Y
REFUND
	Refund_id	Refund ID number.	INTEGER	10		Y
	C_id	Customer's ID number.	INTEGER	10			Y
	Refund_reason	Reasoning for wanting the refund.	VARCHAR	50	Y
	Refund_amount	The amount that will be returned in the refund.	DECIMAL	10	Y
	Receipt_number	The number on the receipt.	INTEGER	10
MENU
	Menu_item_id	Menu's ID number.	INTEGER	10		Y
	Menu_item_name	Menu item's and the name they're called to be ordered.	VARCHAR	50	Y
STAFF
	Staff_id	Worker's ID number.	INTEGER	10		Y
	Staff_name	Worker's name.	CHAR	10	Y
	Staff_occupation	Worker's current position.	CHAR	10	Y
	Staff_area_of_expertise	Worker's field they excel at to help the customer.	VARCHAR	50	Y
ORDER
	Bill_id	The bill's ID number.	INTEGER	10		Y
	C_id	Customer's ID number.	INTEGER	10		Y
	Order_id	Order ID number.	INTEGER	10			Y
	Menu_item_id	Menu's ID number.	INTEGER	10			Y
DELIVERY CHECKOUT
	Total	Total of the delivery.	DECIMAL	10	Y
	Delivery_location	The location of where the delivery is going.	VARCHAR	50		Y
DELIVERY
	Delivery_id	Delivery ID number.	INTEGER	10		Y
	Transport_id	Transport Company ID number.	INTEGER	10			Y
	Order_id	Order ID number	INTEGER	10			Y
	Payment_type	Payment type: Cash, Card, Paypal, Check.	CHAR	10		Y
COURIER
	Transport_id	Transport company ID number.	INTEGER	10		Y
	Transport_name	Transport company name.	CHAR	10	Y
PAYMENT
	Payment_type	Payment type: Cash, Card, Paypal, Check.	CHAR	10		Y