Assignment Scope Use database frozenfood.
Create tables in the database.
Insert data into tables.
Generate an ER diagram.
Query Description
Change to use the database frozenfood
Create a table named product with the following attributes, data types, and constraints:
ID, integer, 7 characters, not null, auto increment
productName, variable character, 90 characters, not null
productType, variable character, 90 characters, not null
description, variable character, 90 characters, not null
size, variable character, 50 characters, not null
quantity, integer, 7 characters, not null
price, decimal, 7 characters, 2 to the right of the decimal point, not null
primary key is the ID field
Create a table named employeeSupplier with the following attributes, data types, and constraints:
employeeId, integer, 7 characters, not null
supplierId, integer, 7 characters, not null
primary key is the employeeId and supplierId fields
foreign key on column employeeId references table employee column ID
foreign key on column supplierId references table supplier column ID
Create a table named productSupplier with the following attributes, data types, and constraints:
productId, integer, 7 characters, not null
supplierId, integer, 7 characters, not null
primary key is the productId and supplierId fields
foreign key on column productId references table product column ID
foreign key on column supplierId references table supplier column ID
Insert data in file product. sql into table product
Insert data in file employeeSupplier. sql into table employeeSupplier
Insert data in file productSupplier. sql into table productSupplier
Generate an ER Diagram using MySQL Workbench, save as a .mwb file
Export database frozenfood using MySQL Workbench, save as a .sql file
Provide written source code in a .sql file
Test Cases
Test Case 1
Table product should look like Figure 1
Test Case 2
Verify table employeeSupplier using the SQL command in file testCase2TestCase3Code. sql, result set should look like Figure 2
Test Case 3
Verify table productSupplier using the SQL command in file testCase2TestCase3Code. sql, result set should look like Figure 3
Test Case 4
ER Diagram should look like Figure 4
/*
insert into table productSupplier the primary key for each product and supplier based on table. column product. id and supplier. id
data provided in format (, )
*/
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )

/*
Data order:
productName, productType, description, size, quantity, price
*/
lamb shank, meat, lamb shank in rosemary and mint sauce, 10 ounce, 20, 9.99
veal, meat, veal osso buco, 15.5 ounce, 15, 11.99
short rib, meat, braveheart angus beef boneless short rib, 12 ounce, 40, 7.99
flank steak, meat, braveheart angus beef flank steak, 2 pound, 30, 16.99
chicken, meat, boneless chicken breasts, 8 count, 56, 21.99
pork chops, meat, bacon-wrapped pork chops, 4 count, 42, 19.99
langoustines, seafood, medium wild langoustines, 7 count, 25, 20.99
octopus, seafood, cooked small octopus tentacles, 3 count, 15, 23.00
shrimp, seafood, red shrimp from argentina XL, 12 count, 50, 12.99
clams, seafood, coquina clams, 12 count, 45, 9.99
peas, vegetable, extra fine french peas, 2 pound, 60, 5.99
artichoke, vegetable, artichoke bottoms, 2 pound, 24, 17.69
bean, vegetable, very find yellow wax beans, 2 pound, 30, 6.29
broccoli, vegetable, broccoli florets, 2 pound, 55, 4.49
potatoes, vegetable, family size smashed red potatoes, 32 ounce, 55, 12.99
potatoes, vegetable, family size whipped sweet potatoes, 32 ounce, 55, 14.49
potatoes, vegetable, potatoes au gratin, 3 ounce, 55, 8.49
rice, grain and rice, jasmine rice, 2 pound, 45, 9.99
rice, grain and rice, brown, red and wild rice medley, 2 pound, 35, 9.99
quinoa, grain and rice, organic quinoa, 2 pound, 26, 12.99
lentils, grain and rice, black beluga lentils, 2 pound 18, 9.99
pastry, bakery, frangipane king cake, 19 ounce, 32, 19.99
pastry, bakery, chocolate croissant, 3 ounce, 90, 1.90
dessert, bakery, tiramisu classico, 3 oz, 60, 4.99
dessert, bakery, authentic french creme brulee, 5 ounce, 54, 5.99
pie, bakery, cappuccino pie, 3 pound, 10, 36.50
cake, bakery, red berry cheesecake, 3 pound, 12, 36.50
bread, bakery, brioche burger buns, 4 count, 44, 4.80
bread, bakery, ciabatta sandwich 6 inch, 3 count, 28, 3.29
dough, bakery, mini blinis, 16 count, 26, 4.46

/*
insert into table employeeSupplier the primary key for each employee and supplier based on table. column employee. id and supplier. id
data provided in format (, )
*/
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )
(, )