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 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 (see attached layout of
the spreadsheet structure).
Required:
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 attached “Database Normalization.PPT” file, to guide you in
creating your final model of NSJI database tables and relationships.
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
