Given the sample records in the EMPLOYEE table shown below, and using the Normalization Steps discussed in this Topic, normalize this table into 3NF. Be sure to document and provide each individual step in your assignment submission (see the in-class exercise example in the Resources folder for an example of the expected format). Do NOT just provide the resulting tables in 3NF.

example

EMP_NAME EMP_DEPT SPECIALITY SALARY_IN_K MANAGER_NAME MANAGER_BASE Darth Vader Imperial Waste Disposal The Force 62 Emperor Palpatine Death Star Luke Skywalker Sales The Force 39 Obi-Wan Kenobi Tatooine Pilot 5 General Jan Dodonna Yavin 4 Yoda Manager The Force 126 Jedi Council Jedi Temple Bobba Fett Imperial Waste Disposal Bounty Hunter 55 Emperor Palpatine Death Star Lando Calrissian Air Purification Pilot 49 Cloud City Council Cloud City Chewbacca Sales Pilot 9 General Jan Dodonna Yavin 4 Consider the following task report for Speedy Lawn Care Service. Normalize the report to 3NF following the process detailed in class. Technician Cost ($) Client Name Client Address Service Type Vehicle Ident. Name # Kim Jones Worthington Mowing 25 Carlos L2 John Smith Dublin Mowing 75 Fred L1 Aeration 120 Louie LT1 Barbara Worthington Mowing 50 Carlos L2 Wentz Charles Lee Hilliard Mowing 60 Louie LT1 Heinz Powell Aeration 255 Louie LT1 Schmidt Carrie Fisher Columbus Fertilizing Gina T25 1) State Assumptions i. Each technician is assigned a vehicle that they drive exclusively. ii. Technicians are able to provide different services (they are not specialized). iii. Cost is not based on service type, but some other factor (such as size of property, technician, etc.) iv. More than one technician could service the same address/client. v. Only one service type can be applied to any given address. 45 60 2) Remove Repeating Groups (multivalued attributes) Technician Vehicle Ident. Client Name Client Address Service Type Cost ($) Name # Kim Jones Worthington Mowing 25 Carlos L2 John Smith Dublin Mowing 75 Fred L1 John Smith Dublin Aeration 120 Louie LT1 Barbara Worthington Mowing 50 Carlos L2 Wentz Charles Lee Hilliard Mowing Louie LT1 Heinz Powell Aeration 255 Louie LT1 Schmidt Carrie Fisher Columbus Fertilizing 45 Gina T25 3) Determine Primary key from Candidate Keys i. Possible Candidate Key 1: Client Name, Service Type ii. Possible Candidate Key 2: Client Address, Service Type (won't work - Client Address is not full address, just city name, so not unique) iii. Primary Key: Client Name, Service Type 4) Write initial relation i. Services(Client Name Client Address, Service Type, Cost, Technician Name, Vehicle Ident. #) INF 5) Identify all Partial Functional Dependencies i. Client Name -> Client Address 6) Remove PFDs by splitting relation i. ClientAddresses(Client Name Client Address) ii. Services(Client Name Service Type, Cost, Technician Name, Vehicle Ident. #) 2NF 7) Repeat 4-5 for Transitive Dependencies i. Technician Name -> Vehicle Ident. # 8) Write final relations i. ClientAddresses(Client Name Client Address) 3NF ii. Services(Client Name Service Type, Cost, Technician Name) - 3NF iii. Technicians(Technician Name, Vehicle Ident. #) 3NF 9) Check EMP_NAME EMP_DEPT SPECIALITY SALARY_IN_K MANAGER_NAME MANAGER_BASE Darth Vader Imperial Waste Disposal The Force 62 Emperor Palpatine Death Star Luke Skywalker Sales The Force 39 Obi-Wan Kenobi Tatooine Pilot 5 General Jan Dodonna Yavin 4 Yoda Manager The Force 126 Jedi Council Jedi Temple Bobba Fett Imperial Waste Disposal Bounty Hunter 55 Emperor Palpatine Death Star Lando Calrissian Air Purification Pilot 49 Cloud City Council Cloud City Chewbacca Sales Pilot 9 General Jan Dodonna Yavin 4 Consider the following task report for Speedy Lawn Care Service. Normalize the report to 3NF following the process detailed in class. Technician Cost ($) Client Name Client Address Service Type Vehicle Ident. Name # Kim Jones Worthington Mowing 25 Carlos L2 John Smith Dublin Mowing 75 Fred L1 Aeration 120 Louie LT1 Barbara Worthington Mowing 50 Carlos L2 Wentz Charles Lee Hilliard Mowing 60 Louie LT1 Heinz Powell Aeration 255 Louie LT1 Schmidt Carrie Fisher Columbus Fertilizing Gina T25 1) State Assumptions i. Each technician is assigned a vehicle that they drive exclusively. ii. Technicians are able to provide different services (they are not specialized). iii. Cost is not based on service type, but some other factor (such as size of property, technician, etc.) iv. More than one technician could service the same address/client. v. Only one service type can be applied to any given address. 45 60 2) Remove Repeating Groups (multivalued attributes) Technician Vehicle Ident. Client Name Client Address Service Type Cost ($) Name # Kim Jones Worthington Mowing 25 Carlos L2 John Smith Dublin Mowing 75 Fred L1 John Smith Dublin Aeration 120 Louie LT1 Barbara Worthington Mowing 50 Carlos L2 Wentz Charles Lee Hilliard Mowing Louie LT1 Heinz Powell Aeration 255 Louie LT1 Schmidt Carrie Fisher Columbus Fertilizing 45 Gina T25 3) Determine Primary key from Candidate Keys i. Possible Candidate Key 1: Client Name, Service Type ii. Possible Candidate Key 2: Client Address, Service Type (won't work - Client Address is not full address, just city name, so not unique) iii. Primary Key: Client Name, Service Type 4) Write initial relation i. Services(Client Name Client Address, Service Type, Cost, Technician Name, Vehicle Ident. #) INF 5) Identify all Partial Functional Dependencies i. Client Name -> Client Address 6) Remove PFDs by splitting relation i. ClientAddresses(Client Name Client Address) ii. Services(Client Name Service Type, Cost, Technician Name, Vehicle Ident. #) 2NF 7) Repeat 4-5 for Transitive Dependencies i. Technician Name -> Vehicle Ident. # 8) Write final relations i. ClientAddresses(Client Name Client Address) 3NF ii. Services(Client Name Service Type, Cost, Technician Name) - 3NF iii. Technicians(Technician Name, Vehicle Ident. #) 3NF 9) Check