MYSQL

Assignment Scope

1. Use database frozenfood.
2. Create tables in the database.
3. Insert data into tables.
4. Generate an ER diagram.

Query Description

1. Change to use the database frozenfood

2. Create a table named product with the following attributes, data types, and constraints:
   1. ID, integer, 7 characters, not null, auto increment
   2. productName, variable character, 90 characters, not null
   3. productType, variable character, 90 characters, not null
   4. description, variable character, 90 characters, not null
   5. size, variable character, 50 characters, not null
   6. quantity, integer, 7 characters, not null
   7. price, decimal, 7 characters, 2 to the right of the decimal point, not null
   8. primary key is the ID field

3. Create a table named employeeSupplier with the following attributes, data types, and constraints:
   1. employeeId, integer, 7 characters, not null
   2. supplierId, integer, 7 characters, not null
   3. primary key is the employeeId and supplierId fields
   4. foreign key on column employeeId references table employee column ID
   5. foreign key on column supplierId references table supplier column ID

4. Create a table named productSupplier with the following attributes, data types, and constraints:
   1. productId, integer, 7 characters, not null
   2. supplierId, integer, 7 characters, not null
   3. primary key is the productId and supplierId fields
   4. foreign key on column productId references table product column ID
   5. foreign key on column supplierId references table supplier column ID

5. Insert data in file product.sql into table product

6. Insert data in file employeeSupplier.sql into table employeeSupplier

7. Insert data in file productSupplier.sql into table productSupplier

8. Generate an ER Diagram using MySQL Workbench, save as a .mwb file

9. Export database frozenfood using MySQL Workbench, save as a .sql file

10. 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 (, ) */

(, ) (, ) (, ) (, ) (, ) (, ) (, ) (, ) (, ) (, )