Answered step by step
Verified Expert Solution
Link Copied!

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

image text in transcribed

image text in transcribed

image text in transcribed

image text in transcribed

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 sheet

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

SQL Instant Reference

Authors: Gruber, Martin Gruber

2nd Edition

0782125395, 9780782125399

More Books

Students also viewed these Databases questions

Question

Explain the causes of indiscipline.

Answered: 1 week ago