Employee Excel AccessDB assignment - 93446

Solution Posted by
jordanblink

jordanblink

Rating : (19)A+
Solution Detail
Price: $25.00
  • From: ,
  • Posted on: Sun 27 Sep, 2015
  • Request id: None
  • Purchased: 0 time(s)
  • Average Rating: No rating
Request Description
Task 1 – Eliminate redundancy • Remove any lookup fields and relationships on the Employees table. Get rid of the lookup field by right clicking it in datasheet view and select delete. Go into design view and remove the primary key. • Right click the table name in the left pane and select copy. Call the new table copy JobAssignment. • Clean up each of the following tables so that only the following fields exist: Employee: Social_Security_Number, First_Name, Middle_Initial, Last_Name, Address_Line_1, Address_Line_2, City, State, Zip_Code, Home_Phone_Number, Original_Hire_Date, Sex and Date_Of_Birth JobAssignment: Subsidiary_Number,Social_Security_Number, Employee_Number, FT_PT_Code,Status_Code, Termination_Date, Termination_Code, Hourly_Base_Pay, Job_Class_Code • There will be 4 employees who are working at more than 1 company, therefore there will be duplicate records for them in the Employees table. Confirm that there are more than one copy of each of the following Social_Security_Numbers and then delete the duplicate record of each one: 366-00-6857, 369-00-0677, 369-00-6020 and 371-00-7930. This can be done using a filter… • Now that the redundancy has been removed, e.g. each employee’s information only appears once in the Employee table, we can reset the primary keys. The primary key of the Employee table will be Social_Secuirty_Number and the primary key of the JobAssignment table will be a composite key of the Subsidiary_Number and Social_Security_Number. • Set up whatever relationships are necessary to ensure that a record cannot be put into the JobAssignment table unless a correlated Social_Security_Number can be found in the Employee table. Task 2 – Add some more lookup tables • Create a Subsidiary table that has a Subsidiary_Number (primary key) and a Subsidiary_Name column. The values going into the table should be: 001 – General Manufacturing 002 – Spastic Sprockets 003 – Pieces n Parts • Create a JobClassCode table that has Subsidiary_Number, Job_Class_Code and Job_Class_Name columns (primary key should be Subsidiary_Number and Job_Class_Code since neither alone is unique, but compositely they are): 001 104 Assembly 001 105 QA 001 115 Packaging 001 127 Shipping 001 203 Manager 001 207 Design 001 212 Repair 001 213 Support 001 214 Packaging 002 104 Design 002 105 Shipping 002 107 Repair 002 115 Manager 002 203 Support 002 207 Diagnostics 002 212 Assembly 002 213 Finance 002 214 QA 003 104 QA 003 105 Shipping 003 115 Packaging 003 123 Design 003 203 Repair 003 207 Diagnostics 003 212 Customer Service 003 213 Manager 003 214 Support 003 308 Assembly • Modify the database such that a JobAssignment record cannot be added without a valid Subsidiary_Number from the Subsidary table and a valid Job_Class_Code from the JobClassCode table. Remember JobClassCode must also have a valid Subsidiary_Number from the Subsidiary table. How would you set up the Referential Integrity on each of these relationships? Explain why… To set up referential integrity we have to open the Relationship view from under the Database Tools menu. Once we open that we can choose JobAssignment, Subsidary and JobClassCode tables. 1. To create a new relationship between tables we have to drop Subsidiary_Number column from JobAssignment table on to the same column from Job_Class_Code table. 2. Following pop-up would be opened, then select “Enforce Referential Integrity” and click on “Cascade Update Related Fields” and “Cascade Delete Related Records”. 3. Then click Create button. Task 3 – Show how to navigate the relationships between the following tables: Employee to JobAssignment, JobAssignment to Subsidiary and JobAssignment to JobClassCode. A screenshot is fine. Explain why some navigations show up while others do not. Yes, some navigation is showing in the above diagram for example, navigation between Employees and JobAssignment tables, JobAssignment and Subsidiary table and JobClassCode and Subsidairy table. However, we do not see the navigation from JobClassCode and JobAssignment tables via Job_Class_Code column in both the tables. Reason being, Job_Class_Code column is a part of the composite primary key of the JobClassCode table and hence we cannot identify a record uniquely using only Job_Class_Code, we need Subsidiary_Number as well. Hence a dependency relathiship is shown but the navigations are not visible.
Solution Description

Task 1 – Eliminate redundancy

  • Remove any lookup fields and relationships on the Employees table.  Get rid of the lookup field by right clicking it in datasheet view and select delete.  Go into design view and remove the primary key. 
  • Right click the table name in the left pane and select copy.  Call the new table copy JobAssignment.
  • Clean up each of the following tables so that only the following fields exist:

Employee: Social_Security_Number, First_Name, Middle_Initial, Last_Name, Address_Line_1, Address_Line_2, City, State, Zip_Code, Home_Phone_Number, Original_Hire_Date, Sex and Date_Of_Birth

JobAssignment: Subsidiary_Number,Social_Security_Number, Employee_Number, FT_PT_Code,Status_Code, Termination_Date, Termination_Code, Hourly_Base_Pay, Job_Class_Code

  • There will be 4 employees who are working at more than 1 company, therefore there will be duplicate records for them in the Employees table.  Confirm that there are more than one copy of each of the following Social_Security_Numbers and then delete the duplicate record of each one: 366-00-6857, 369-00-0677, 369-00-6020 and 371-00-7930.  This can be done using a filter…
  • Now that the redundancy has been removed, e.g. each employee’s information only appears once in the Employee table, we can reset the primary keys.  The primary key of the Employee table will be Social_Secuirty_Number and the primary key of the JobAssignment table will be a composite key of the Subsidiary_Number and Social_Security_Number.
  • Set up whatever relationships are necessary to ensure that a record cannot be put into the JobAssignment table unless a correlated Social_Security_Number can be found in the Employee table.

 

Task 2 – Add some more lookup tables

  • Create a Subsidiary table that has a Subsidiary_Number (primary key) and a Subsidiary_Name column.  The values going into the table should be:

001 – General Manufacturing

002 – Spastic Sprockets

003 – Pieces n Parts

 

  • Create a JobClassCode table that has Subsidiary_Number, Job_Class_Code and Job_Clas