Database Design Problem
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 timepositions 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 (see attached layout of the spreadsheet structure).
1) From a reviewer’s perspective, explain what is wrong with NSJI’s approach to manage its data.Be specific, please! Do you think these problems are serious from an auditor’s perspective?From the company perspective?
2) To reduce the possible impact of these potential problems, what advice would you give to the company?
3) Use the spreadsheet structure provided, to create a conceptual model (entity relationship diagram – ERD) for NSJI.
4) Using the ERD and business rules described below, design a database model, transformed to 3NF (third normal form). As a guide, use the Espresso Coffee illustration in your textbook chapter 2.Also, use your Kay textbook (tech exercise 2.27 on page 62), to guide you in creating your final model
of NSJI database tables and relationships.
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