Simple 2 table database to design DB Design Requirements 1. You will need two tables o employees . positions 2. Design the ERD (Entity Relationship Diagram) using www sql designer o Tutorial on www sql designer is in our notes and will be covered in class o include a screen shot image of the design with your final zip file 3. You need to make sure Each table has an appropriately named PK field One table will need a FK You will need to change the name of some of the fields You need to split one of the fields into two fields o Use the appropriate field types o Use the default charset/collate that we have been using so far 4. Create the two tables Have the create statements inside a file.sql o Then have the alter to put in the FK Inserts 1. Create the inserts using the data below 2. Some of the data is in the wrong format (the dates) 3. You may want to do the "positions" table inserts first, this will let you put the FK's for position_id into the employees table without having to do a bunch of udpates Name city state zip date hired position Joe Shmoe Burbank CA 91501 | 07/04/11 | Manager John Jacob Mesa AZ Worker 85202 ||04/05/09 Drone Sally Struthers Mesa AZ 85203 | 09/15/10 CEO Michelle Smith Pasadena CA 91103 | 10/05/10 Manager John Smith Mesa AZ 85202 | 03/14/15 Worker Drone Paul Lynn Mesa AZ Worker 85280 || 02/02/18 Drone Natasha Burbank ICA Romanof 91502||09/22/11 | Specialist Worker Sam Hill || Mesa AZ 8520101/15/12 Drone Stan Lee Burbank CA 95103 || 01/05/14 Manager Bruce Banner Mesa AZ 85212||04/29/20 Worker Drone Natasha Romanof Burbank ICA 9150209/22/11 Specialist Sam Hill || Mesa AZ 85201 ||01/15/12 Worker Drone Stan Lee Burbank CA 95103 | 01/05/14Manager Bruce Worker Mesa AZ 85212||04/29/20 Banner Drone Queries NOTE: Remember that the FROM portion of your query is the tough part. You will need to set up one JOIN for the two tables. 1. Query that Selects ALL FIELDS except the PKs from the two tables (should have 10 results) 2. Queries that select ALL FIELDS except the PKs but: o only selects those with a Position of Manager only selects "Worker Drone" from "AZ" o only selects "Manager" from "Pasadena" NOTE: if you use "Mesa" instead of Pasadena you get 0 results Query that lists every worker from "Mesa" sorted by last name Query that counts the number of "Manager"s by state. (GROUP BY state). This one can NOT use all fields. Simple 2 table database to design DB Design Requirements 1. You will need two tables o employees . positions 2. Design the ERD (Entity Relationship Diagram) using www sql designer o Tutorial on www sql designer is in our notes and will be covered in class o include a screen shot image of the design with your final zip file 3. You need to make sure Each table has an appropriately named PK field One table will need a FK You will need to change the name of some of the fields You need to split one of the fields into two fields o Use the appropriate field types o Use the default charset/collate that we have been using so far 4. Create the two tables Have the create statements inside a file.sql o Then have the alter to put in the FK Inserts 1. Create the inserts using the data below 2. Some of the data is in the wrong format (the dates) 3. You may want to do the "positions" table inserts first, this will let you put the FK's for position_id into the employees table without having to do a bunch of udpates Name city state zip date hired position Joe Shmoe Burbank CA 91501 | 07/04/11 | Manager John Jacob Mesa AZ Worker 85202 ||04/05/09 Drone Sally Struthers Mesa AZ 85203 | 09/15/10 CEO Michelle Smith Pasadena CA 91103 | 10/05/10 Manager John Smith Mesa AZ 85202 | 03/14/15 Worker Drone Paul Lynn Mesa AZ Worker 85280 || 02/02/18 Drone Natasha Burbank ICA Romanof 91502||09/22/11 | Specialist Worker Sam Hill || Mesa AZ 8520101/15/12 Drone Stan Lee Burbank CA 95103 || 01/05/14 Manager Bruce Banner Mesa AZ 85212||04/29/20 Worker Drone Natasha Romanof Burbank ICA 9150209/22/11 Specialist Sam Hill || Mesa AZ 85201 ||01/15/12 Worker Drone Stan Lee Burbank CA 95103 | 01/05/14Manager Bruce Worker Mesa AZ 85212||04/29/20 Banner Drone Queries NOTE: Remember that the FROM portion of your query is the tough part. You will need to set up one JOIN for the two tables. 1. Query that Selects ALL FIELDS except the PKs from the two tables (should have 10 results) 2. Queries that select ALL FIELDS except the PKs but: o only selects those with a Position of Manager only selects "Worker Drone" from "AZ" o only selects "Manager" from "Pasadena" NOTE: if you use "Mesa" instead of Pasadena you get 0 results Query that lists every worker from "Mesa" sorted by last name Query that counts the number of "Manager"s by state. (GROUP BY state). This one can NOT use all fields
