please help , the TA's arent
Deliverables To complete this assignment, submit the following three files to Webcourses: 1. An SQL file (i.e. save the file with file extension.sql) containing the SQL written to perform the tasks. 2. An exported SQL file using MySQL Workbench Data Export option. The file name should be the following format: FirstnameLastnameAssignment#.sql. Example: Karin WhitingAssignment2.sql 3. An ER Diagram generated by MySQL Workbench. The file name should be the following format: FirstnameLastnameAssignment#ERDiagram.mwb. Example: Karin WhitingAssignment2ERDiagram.mwb Assignment Scope 1. Create database frozenfood. 2. Create tables in the database. 3. Insert data into tables. 4. Generate an ER diagram. References 1. 8_SQL Create Database.pptx 2. 10_SQL Select Database.pptx 3. 11_SQL Create Table.pptx 4. 13_SQL Insert Query.pptx To access the DBMS 1. Launch the MySQL Command Line Client executable or MySQL Workbench 2. Login in using the password set during installation "cgs2545" or your chosen password. 1. Tasks Query Description 1. Create a new database named frozenfood 2. Change to use the database frozenfood 3. Create a table named employee with the following attributes, data types, and constraints: a. ID, integer, 7 characters, not null, auto increment b. lastName, variable character, 50 characters, not null c. firstName, variable character, 50 characters, not null d. address, variable character, 90 characters, not null e city, variable character, 90 characters, not null f. state, character, 2 characters, not null g. zipCode, character, 5 characters, not null h. phone, character10 characters, not null email, variable character, 90 characters, not null, unique primary key is the ID field 4. Create a table named customer with the following attributes, data types, and constraints: a. ID, integer, 7 characters, not null, auto increment b. lastName, variable character, 50 characters, not null c. firstName, variable character, 50 characters, not null d. address, variable character, 90 characters, not null e city, variable character. 90 characters, not null f. state, character, 2 characters, not null g. zipCode, character, 5 characters, not null h. phone, character. 10 characters, not null i email, variable character, 90 characters, not null, unique primary key is the ID field 5. Create a table named supplier with the following attributes, data types, and constraints a. ID, integer, 7 characters, not null, auto increment b. supplier Name, variable character, 50 characters, not null, unique c. address, variable character. 90 characters, not null d. city, variable character, 90 characters, not null state, character, 2 characters, not null f. zipCode, character, 5 characters, not null 2. phone, character, 10 characters, not null h. email, variable character. 90 characters, not null, unique i. primary key is the ID field 6. Insert data in file customer.sql into table customer 7. Insert data in file employee.sql into table employee 8. Insert data in file supplier.sql into table supplier f. zipCode, character, 5 characters, not null g. phone, character, 10 characters, not null h. email, variable character. 90 characters, not null, unique i primary key is the ID field 6. Insert data in file customer.sol into table customer 7. Insert data in file employee.sql into table employee 8. Insert data in file supplier.sql into table supplier 9. Generate an ER Diagram using MySQL Workbench, save as a .mwb file 10. Export database frozenfood using MySQL Workbench, save as a.sql file 11. Provide written source code in a sgl file 3 Test Cases Test Case 1 Test Case 2 Test Case 3 Test Case 4 Table employee should look like Figure 1 Table customer should look like Figure 2 Table supplier should look like Figure 3 ER Diagram should look like Figure 4 wywab select reply 19 17 es old TE DE O 137 walls 24 Alva waya HT Nella ters Clara 771 Bar Court Gabi CA 1 2013 100 ta UM SON Y HT Test Case 3 Test Case 4 Table supplier should look like Figure 3 ER Diagram should look like Figure 4 mysql> select from employee; 10 firstName Lustune address 1 city I state 1 zipcode Iphone erald 1 Yvonne | Prince 12 Creekside st Huntington Beach CA 2 Dante Rennie 1 137 wall st 1 Remphis 1 TN Alysha waters 1411 Shirley st Abingdon IVA 4 Avaya I Clarke 7771 Border Court San Marcos I CA 5 Haniya | i ser 16 South Salphar Springs Street Jefferson TIA 61 Nathalie Newman 194> Saddle Drive Covington 1 IA 7 Date | Guthrie i 9 Wrangler Ave 1 Ontario R | Reverley Roberts 1 7919 S. Greenview Drive I cuyahoga Fa11s I OH 9 Paris i Solis 1 763 West Mulberry St Battle creek 1 MT 10 mala I Heath 545 Ohio Ave Scottsdale AZ 10 TONS in sel (0.00 sec) 192547 38117 24210 192078 170171 170411 1 01764 44223 | 49616 | 85260 2025530109 Typrince frozenfoods.com 12025550112 drenni frozenfoods.com 2025550115 awaters@frozenfoods.com 2025550119 aclarke frozenfoods.com 1 2025550121 kaisen rozenfoods.com 19035550177 na frozenfoods.com 1 2005558194 | deuthrinefrozenfoods.com | 7025550175 robertsefrozenfoods.com 1 2025550132 polis@frozenfoods.com | 2025550135 wheatherfrozenfoods.com Figure 1 Table employee mysql> select from customer; ID firsthame lastame address | city I state 1 zipcode phone 1 mail 1 Yvonne 1 Goodman 12 Creekside st Nashville TN 1 37205 1 2025556109 I voodmanlisp.com 2 Dante i Mackenzie | 137 wall st Nashville TN 137205 | 2025550112 dmucken luisp.com 3 | Alysha 1 Rollins | 411 Shirley st Nashville | TN 1 37205 2025550115 1 rollinseisp.com 4 Avaya I Gonzalez | 7771 Border Court 1 Nashville TN 1 37205 S025550119 gonzalezisp.com 5 Haniya 1 Kelly 1 6 South Sulphur Springs Street Nashville i IN 137205 1 2025550121 kelly@isp.com 6 Nathalie I Chambers 1942 New Saddle Drive Clover INC 1 29710 12025558122 chambrers isp.com 7 Dante 1 Hollins 9 Wrangler Ave I clover I 1 29/10 | 2025550124 | drollinsisp.com * Beverlwy Naku | 2319 S. Grevi Drive I Clover I SC 29/10 1 2025550125 backelisp.com 9 1 Paris Lindsey 1 151 West Mulberry St Clover 150 29/10 1 2825550112 I plindsay iyp.com 10 wala I Melle 1 545 Ohio Ave | Clever I SE 120/10 120358501 15 lumellaisp.com 11 Nice Prince 1 20 Middle River Street I willeughty 6.4944 202995017 mprincip.com Rennie 12 JAVAT 1112 North country st I willoughby OH 44094 1 20255501191 frennisp.com 13 All Maters I 1 Rockland ane I Willoughby I ON 4404 12025550146 water disp.com 14 John Paul 1 clarke I 1 Riverside Lane Willoughby 10 4401 2029950151 jpcelarkisp.com Kaiser i willoughby 15 Dane 1494 Wall Road DI 44091 202510155 kedai sergisp.com 16 Mad Towasan 19970 State Court Duluth GA 30006 2025550157 hnemanisp.com Guthrie 17 Maha Duluth 54 woods an Drive GA 30095 2025550161 guthrieisp.com 18 1 Tulisa | Roberts 7174 Studebaker Street Duluth IGA 130096 1202555016) trobertsisp.com 19 Robin Solis |8474 Wentworth Street Duluth I GA 1 30095 I 2025590164 solis sp.com 20 Kavita Heath 796 Hartford st Duluth IGA 10006 1 2025550165 kheathaisp.com 21 Meera white | 484 Bridgest Piscataway NO 00854 12025550166 whitegisp.com 22 Bradlee Esparza 7031 Gainsway st 1 Piscatoay 100854 I 2025590173 besparza isp.com 23 Lelloni Leonard Piscataway 137 Monroe Street 103834 1 2925550179 lleonardisp.com 24 stefania Brook 7020 West Surrey st piscatway NO 00854 25 Grover I 2025550179 1 sbrook isp.com squires 7618 Madison Court Piscataway NO 09034 2025530100 26 Jonathan Kumar i 25 Annadale Court Esquires lsp.com Jupiter I FL 133450 2025530182 27 I MES ville 9841 Seith Drive jkumarasp.com Jupiter I FL 133450 | 2925590187 aneville lsp.com 23 Uzair Sparrow 7999 Hall Street Jupiter I FL 1 33458 I 2025550188 usarrolsp.com 29 A I Currie 1 8411 Pleasant st | Jupiter I FL 33450 1 2925556194 ecurrip.com 30 Inaami Wallace 33 Pierced Jupiter FL 33458 1 2025550195 iwallacelisp.com 31 Efe House 97 High Point Street Luna I MO 2970 2025550236 whousuisp.com 32 ALLIEN ALKING 66 Harrison or I Lantiam I MO 120706 2005SS017 atkinsons.com Michelle Hamiru 32 Hill Hold av Land HD I 20/06 1202555016 14 May HAS 240 Devon Lane rrip.com Lankan PD 120/ 20255501 Tordana lek rhauri. 1911 LA LA Lanham 29/04 12035550 jheckup.com Figure 2 Table customer al select from supplier suppliertiane zipcode phone CA 3665736 69116 7607848320 CA 1 te Direction Foods 16121 Gothard St. Suite - 21 Morgan Foods 53eak Court Drive Suite 100 Dutt and Wagner of Virginia, Inc 4 | Culinary Specialties, Inc 1231 Linda vista Drive 5 1 3ohn E. Korner Company 2830 jefferson Hy & Divers Led Foods and Seasoning LLC 1400 Greengate Drive Suite 300 71 fincnote Foods North America 4200 Concours Street Suite se 8 Ascot Valley Foods 205 Ascot Ply kellege Company + G H - 18 Appetizer USA 15210 N St Stresate A Hetington Heach Memphis Abingdom San Marcos Jefferson Covington Ontario Cwana Falls Battle Creek Scottsdale Infowirection foods.com an-foods.com vireina.com alties.com cany.com ersified foods.COM Stefoods.com costoods.com 9207 7012 7433 *1764 44223 6 LA 8609142352 577470 333769411 09621433 686722601 etterswa.com 10 ous in set (e. Rec Figure 3 Table supplier supplier ID INT supplier Name VAROHAR (50) address VARCHAR(90) city VARCHAR(90) state CHAR(2) zipCode CHAR(5) phone CHAR(10) email VARCHAR(90) Indexes customer ID INT firstName VARCHAR(50) lastName VARCHAR(50) address VARCHAR(90) Ooty VARCHAR(90) 0 state CHAR(2) zipCode CHAR(5) phone OHAR(10) email VARCHAR(90) Indes employee ID INT firstName VARCHAR(50) lastName VARCHAR(50) address VARCHAR(90) city VARCHAR(90) state CHAR(2) zip Code CHAR(5) phone OHAR(10) email VARCHAR(90) Indexes Figure 4 ER Diagram
