Answered step by step
Verified Expert Solution
Question
1 Approved Answer
could someone help me please those are the inly information You have been hired to review the accuracy of the books of Northeast Seasonal Jobs
could someone help me please
those are the inly information
You have been hired to review the accuracy of the books of Northeast Seasonal Jobs International (NSJI), a job broker. NSJI matches employers whose business is seasonal (like ski resorts) with people looking for part time positions at such places. Employers are located in the Northeast (New England), but recently expanded to include parts of Canada. NSJI maintains all of this information in a flat file spreadsheet. For the spreadsheet structure see the Excel file "Student_NSJI_Flat_File.xlsx" attached. Requirements 1) From a reviewer's perspective, explain what is wrong with NSJI's approach to manage its data. Do you think these problems are serious from the company perspective? Please be specific. 2) To reduce the possible impact of these potential problems, what advice would you give to the company? 3) Using the spreadsheet structure provided, transform the flat file to 3NF (third normal form). Business Rules You have ascertained from an interview with your client (NSJI): Contact position is fixed - does not change (i.e. it is a field of Employer) "Comments" is a field of Employer, not Position "NAICS" stands for "North American Industry Classification System" "Openings" is a field of Position, not Employer "Phone" is a field of Employer Position ID is unique for all employers First, you need to understand what this spreadsheet wants to tell you. Although there are many columns, the main takeaway from this spreadsheet is that "certain employers want certain numbers of certain positions to be filled". Once this is clear, you know you need a "main table" which only consists of three columns: Employer_ID, Position_ID, and Openings, while all the other columns in the big spreadsheet just serve as additional information for either Employer_ID or Position_ID. For example, Employer_Name explains the employer's name by the ID number, Address explains the employer's address, NAICS_Code explains the industry the employer lies in, and so on. Similarly, Position Title, Wage, Hrs/Wk, etc. are additional information for Position_ID. Therefore, you need to have two other tables with these pieces of additional information, like this: Employer Information Table Employer_ID, Employer_Name, Address, City, ... etc. Position Information Table Position_ID, Position Title, Wage, Hrs/Wk, ... etc. Now you have three tables: the main table, employer table, and position table. Now you have three tables: the main table, employer table, and position table. Pay attention, NAICS_Code and NAICS_Description are repeated information, i.e. they can explain each other, so they cannot be in the same table for the purpose of 3NF. Therefore, you can leave either one of them in the employer table, and start a new table which only consists of NAICS_Code and NAICS_Description. This table only explains each code with the description. In conclusion, you will have four tables for HW4. Other important issues: 1. There should not be repeated rows in a table. There are repeated employer rows and NAICS rows, so remember to simplify the table. 2. You need to submit the 4 tables with data. Some students submit four tables with just the title line, which is not complete. 3. You can answer the first two questions either in a separate Word file or in the original Excel file using a separate sheet. You have been hired to review the accuracy of the books of Northeast Seasonal Jobs International (NSJI), a job broker. NSJI matches employers whose business is seasonal (like ski resorts) with people looking for part time positions at such places. Employers are located in the Northeast (New England), but recently expanded to include parts of Canada. NSJI maintains all of this information in a flat file spreadsheet. For the spreadsheet structure see the Excel file "Student_NSJI_Flat_File.xlsx" attached. Requirements 1) From a reviewer's perspective, explain what is wrong with NSJI's approach to manage its data. Do you think these problems are serious from the company perspective? Please be specific. 2) To reduce the possible impact of these potential problems, what advice would you give to the company? 3) Using the spreadsheet structure provided, transform the flat file to 3NF (third normal form). Business Rules You have ascertained from an interview with your client (NSJI): Contact position is fixed - does not change (i.e. it is a field of Employer) "Comments" is a field of Employer, not Position "NAICS" stands for "North American Industry Classification System" "Openings" is a field of Position, not Employer "Phone" is a field of Employer Position ID is unique for all employers First, you need to understand what this spreadsheet wants to tell you. Although there are many columns, the main takeaway from this spreadsheet is that "certain employers want certain numbers of certain positions to be filled". Once this is clear, you know you need a "main table" which only consists of three columns: Employer_ID, Position_ID, and Openings, while all the other columns in the big spreadsheet just serve as additional information for either Employer_ID or Position_ID. For example, Employer_Name explains the employer's name by the ID number, Address explains the employer's address, NAICS_Code explains the industry the employer lies in, and so on. Similarly, Position Title, Wage, Hrs/Wk, etc. are additional information for Position_ID. Therefore, you need to have two other tables with these pieces of additional information, like this: Employer Information Table Employer_ID, Employer_Name, Address, City, ... etc. Position Information Table Position_ID, Position Title, Wage, Hrs/Wk, ... etc. Now you have three tables: the main table, employer table, and position table. Now you have three tables: the main table, employer table, and position table. Pay attention, NAICS_Code and NAICS_Description are repeated information, i.e. they can explain each other, so they cannot be in the same table for the purpose of 3NF. Therefore, you can leave either one of them in the employer table, and start a new table which only consists of NAICS_Code and NAICS_Description. This table only explains each code with the description. In conclusion, you will have four tables for HW4. Other important issues: 1. There should not be repeated rows in a table. There are repeated employer rows and NAICS rows, so remember to simplify the table. 2. You need to submit the 4 tables with data. Some students submit four tables with just the title line, which is not complete. 3. You can answer the first two questions either in a separate Word file or in the original Excel file using a separate sheetStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started