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 reviewers perspective, explain what is wrong with NSJIs 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
Address Tour Guide Faradi 0930 Rodrigo Employer ID Employer Name 10122 Beantown Tours 10122 Beantown Tours 10126 Bayside Inn & Country Club 10126 Bayside Inn & Country Club 10133 The Inn at Plum Hil 10133 The Inn at Plum Hil 10134 Capt'n John's Seafood 10135 The Adele Bannister House 10135 The Adele Bannister House 10138 Blue Hill Inn & Country Club 10145 The Clipper Ship Inn 10145 The Clipper Ship Inn 10145 The Clipper Ship Inn 10145 The Clipper Ship Inn 10146 Newport Mansion Guided Tours 10146 Newport Mansion Guided Tours 10151 Granite State Resort 10151 Granite State Resort 10151 Granite State Resort 10151 Granite State Resort 10152 Alpine Touring Center 10152 Alpine Touring Center 10154 Al Seasons Resort 10154 Al Seasons Resort 10154 Al Seasons Resort 10155 The Bramble Restaurant 10155 The Bramble Restaurant 10156 Seaport Scenic Tours 10156 Seaport Scenic Tours 10159 Summit Hotel & Conference Center 10162 Darby Inn & Restaurant 10163 Belleview Resort 10163 Belleview Resort 10167 Ski & Stay 10167 Ski & Stay 10170 Whittier Resort & Spa 10170 Whittier Resort & Spa 10174 Pier Restaurant 10175 Lion's Mouth Inn 10176 NH Fall Foliage Tours 10180 George's Restaurant & Galley 10185 Moondance Inn & Ski Resort 10185 Moondance Inn & Ski Resort 10190 The Briar Rose Inn 10191 Windsor Alpine Tours 10191 Windsor Alpine Tours 10194 Pear Tree Inn & Restaurant 10195 Gables & Golf Country Club 10195 Gables & Golf Country Club 10197 Aidan's of Mystic 10198 Trudel Spa & Resort 10201 Auberge Si-Gemaine 10210 Stonehurst Inn 10210 Stonehurst inn 10217 Harbourview Resort 10218 Canfield Golf & Country Club 10218 Canfield Golf & Country Club 10220 Hotel du Nord 10220 Hotel du Nord 10222 Whitney's Resort & Spa 10223 The Berkshire House 10223 The Berkshire House 10225 Harbor Whale Watch Tours 10226 Lighthouse Tour 105 State Street 105 State Street 354 Oceanside Drive 354 Oceanside Drive 354 Union Street 354 Union Street 22 Old Colony Way 151 Thames Street 151 Thames Street 38 Blue Hill Road 5 Cliffside Drive 5 Cliffside Drive 5 Cliffside Drive 5 Cliffside Drive 75 Ocean Drive 75 Ocean Drive 41 Hayward Avenue 41 Hayward Avenue 41 Hayward Avenue 41 Hayward Avenue 57 Main Street 57 Main Street 45 Oceanside Way 45 Oceanside Way 45 Oceanside Way 52 Wingate Drive 52 Wingate Drive 87 Cottrell Street 87 Cottrell Street 19 White Mountain Highway 52 Chestnut Street 29 Eden Street 29 Eden Street 587 Mountain Road 587 Mountain Road 15 Surmer Street 15 Summer Street 210 Memorial Boulevard 38 Langley Road 7 Alcott Road 90 Main Street 75 Thom Hill Road 75 Thom Hill Road 105 Queen Street 14 Longmeadow Road 14 Longmeadow Road 30 Richmond Road 38 Graham's Lane 38 Graham's Lane 390 West Main Road 40 Rue Revard 50 Rue Belevue 3 Market Street 3 Market Street 97 Fenwick Road 12 Broadway 12 Broadway 149 Bonsecours Street 149 Bonsecours Street 80 Carter Notch Road 299 Great Road 299 Great Road 17 Commercial Street 23 Com Neck Road City Boston Boston Brewster Brewster Vineyard Haven Vineyard Haven Orleans Newport Newport Chatham Rockport Rockport Rockport Rockport Newport Newport North Conway North Conway North Conway North Conway Bethel Bethel Falmouth Famouth Falmouth Hyannis Hyannis Mystic Mystic Franconia Woodstock Bar Harbor Bar Harbor Stowe Stowe Stockbridge Stockbridge Westerly Stowe North Conway Bar Harbor Lincoln Lincoln Charlottetown Laconia Laconia Lenox Cavendish Cavendish Mystic North Hatley St-Donat Haifax Haifax Haifas East Hartford East Hartford Montreal Montreal Twin Mountain Lenox Lenok Boothbay Harbor Block Island SUPrv Zip Code Country NAICS Code NAICS Description MA 02109 USA 56152 Tour Operators MA 02109 USA 56152 Tour Operators MA 02631 USA 72111 Hotels and Motels MA 02631 USA 72111 Hotels and Motels MA 02568 USA 721191 Bed-and-Breakfast inns MA 02568 USA 721191 Bed-and-Breakfast Inns MA 02653 USA 722211 Limited Service Restaurants RI 02840 USA 721191 Bed and Breakfast Inns RI 02840 USA 721191 Bed and Breakfast inns MA 02633 USA 72111 Hotels and Motels MA 01966 USA 721191 Bed and Breakfast inns MA 01966 USA 721191 Bed-and-Breakfast Inne MA 01966 USA 721191 Bed-and-Breakfast Inns MA 01966 USA 721191 Bed-and-Breakfast Inns RI 02840 USA 56 152 Tour Operators RI 02840 USA 56152 Tour Operators NH 03860 USA 72111 Hotels and Motels NH03860 USA 72111 Hotels and Motels NH 03860 USA 72111 Hotels and Motels NH 03860 USA 72111 Hotels and Motels ME 04217 USA 71392 Skiing Facilities ME 04217 USA 71392 Skiing Facilities MA 02540 USA 72111 Hotels and Motels MA 02540 USA 72111 Hotels and Motels MA 02540 USA 72111 Hotels and Motel MA 02601 USA 722211 Limited-Service Restaurants MA 02601 USA 722211 Limited Service Restaurants CT 06355 USA 71393 Marinas CT 06355 USA 71393 Marinas NH 03480 USA 72111 Hotels and Motels VT 05091 USA 721191 Bed and Breakfast inns ME 04609 USA 72111 Hotels and Motels ME 04609 USA 72111 Hotels and Motels VT 05672 USA 71392 Sking Facilities VT 05672 USA 71392 Skiing Facilities MA 01262 USA 72111 Hotels and Motels MA 01262 USA 72111 Hotels and Motels RI 02808 USA 72211 Ful-Service Restaurants VT 05672 USA 721191 Bed and Breakfast inns NH 03860 USA 56152 Tour Operators ME 04609 USA 722211 Limited Service Restaurants NH 03251 USA 721191 Bed-and-Breakfast Inns NH 03251 USA 721191 Bed-and-Breakfast inne PE C1A 8R4 Canada 721191 Bed-and-Breakfast Inns NH 03246 USA 71392 Skiing Facilities NH 03246 USA 71392 Sking Facilities MA 01240 USA 721191 Bed and Breakfast Inns PE COA 1NO Canada 71391 Golf Courses and Country Clubs PE COA 1NO Canada 71391 Golf Courses and Country Clubs CT 06355 USA 72232 Caterers ac JOB 200 Canada 72111 Hotels and Motels JOT 200 Canada 721191 Bed-and-Breakfast inn NS B3J 3N9 Canada 721191 Bed-and-Breakfast inns NS 83J 3N9 Canada 721191 Bed and Breakfast inns NS B3J 3N9 Canada 72111 Hotels and Motels CT 06108 USA 71391 Golf Courses and Country Club CT 06108 USA 71391 Golf Courses and Country Clubs ac H2Y 2X6 Canada 72111 Hotels and Motels ac H2Y 2X6 Canada 72111 Hotels and Motels NH 03596 USA 72111 Hotels and Motels MA 01240 USA 721191 Bed and Breakfast Inns MA 01240 USA 721191 Bed and Breakfast inns ME 04538 USA 56 152 Tour Operators RI 02807 USA 56152 Tour Operators NSJL Fate Contact First Name Contact Last Name Position Sarah Tasker Office Manager Sarah Tasker Office Manager Jeffrey Hersha General Manager Jeffrey Hersha General Manager Michele Yasenak General Manager Michele Yasenak General Manager John Fairbrother Manager Cheryl Coppolino General Manager Cheryl Coppolino General Manager Hwan Tang Personnel Director Oren Ben Joseph Personnel Manager Oren Ben Joseph Personnel Manager Oren Ben Joseph Personnel Manager Oren Ben Joseph Personnel Manager Katherine Foley Personnel Director Katherine Foley Personnel Director Christine Faraci HR Manager Christine Faraci HR Manager Christine HR Manager Christine Faradi HR Manager Mary Grant Manager Mary Grant Manager Chelsea Petraitis Director Chelsea Petralis Director Chelsea Petraitis Director Rodrigo Valencia Manager Valencia Manager Greg Robitaille Director Greg Robitaille Director Nancy Shea Personnel Manager Jahnavi Sonthi Manager Akash Shah Personnel Director Akash Shah Personnel Director Nathan Weiss Personnel Manager Nathan Weiss Personnel Manager Rebecca Giannopoulous Manager Rebecca Giannopoulous Manager Wen-Yi Huang Manager Bryce Kervin HR Manager Sam Bethel Director Victoria Gonzalez Manager Sonya Krikorian Personnel Director Sonya Krikorian Personnel Director Helen MacDonald Manager Michael Engber Manager Michael Engber Manager Rachel Camara Manager Spencer Mewherter General Manager Spencer Mewherter General Manager Aidan Gaughan Manager Francoise Boucher Personnel Manager Jacques Belanger Director Gavin McDonough Manager Gavin McDonough Manager Louise Tremblay Personnel Manager Celia Johnson Personnel Manager Celia Johnson Personnel Manager Martine Nommand General Manager Martine Nommand General Manager Matthew Lim Director Lubin Jian Manager Lubin Jian Manager Alexander Benton Director Andrew Adgate Director Phone PositionID Position Title Wage Hrs/W. Experience 6174511970 2045 Tour Guide 17.00 24 no 6174511970 2082 Reservationist 14 50 40 no 5082835775 2040 Waited Waitress 10 50 32 yes 5082835775 2073 Pro Shop Clerk 15.50 24 no 5086932320 2041 Housekeeping 12.00 40 no 5086932320 2068 Housekeeping 12.75 30 no 5082558721 2081 Kitchen Help 12.00 40 no 4018493093 2010 Kitchen Help 13.00 40 no 4018493093 2105 Host/Hostess 16.50 25 no 5088930808 2033 Lifeguard 20.50 35 yes 9785460193 2025 Kitchen Help 12.50 32 no 9785460193 2027 Waited Waitress 10.00 32 no 9785460193 2096 Housekeeping 13.50 30 no 9785460193 2097 Waited Waitress 12.00 32 no 4018496544 2007 Tour Guide 18.75 20 yes 4018496544 2017 15.00 20 no 6034688866 2036 Reservationist 14.75 32 ng 6034688866 2102 Reservationist 15.00 30 6034688866 2122 Kitchen Help 13.00 35 ng 6034638866 2130 Housekeeping 12.50 30 ng 2078249976 2058 Main Office Clerk 14.26 32 2078249976 2094 SM Patrol 19.00 25 ye 5083890777 2085 Waited Waitress 10.00 30 no 5083890777 2115 Gift Shop Clerk 13.00 25 no 5083890777 2120 Lleguard 19.00 32 yes 5082770387 2021 Waited Waitress 9.50 30 no 5082770387 2114 Cook 20.00 40 yes 8605723989 2056 Reservationist 15.00 24 no 8605723989 2074 Tour Guide 15.75 30 yes 6038239787 2037 Gift Shop Clerk 13.50 36 8029874603 2034 Waited Waitress 10.25 no 2072881961 2020 Host/Hostess 18.50 32 yes 2072881961 2124 Kitchen Help 14.50 40 yes 8022530809 2070 Housekeeping 13.00 24 no 8022530809 2107 Ski Patrol 18.75 25 yes 4132980811 2048 Front Desk Clerk 16.50 32 no 4132980811 2123 Main Office Clerk 14.50 32 no 4015960383 2086 Waited Waitress 10.75 32 no 8022539775 2076 Cook 20.00 32 yes 5034687093 2065 Tour Guide 15.00 20 20728-87750 2064 Waited Waitress 10.00 24 no 6036359208 2009 Day Care 15.00 32 yes 6036359208 2127 Waited Waitres 10.50 40 no 9026261595 2053 Host/Hostess 15.75 24 no 6032669233 2078 Ski Patrol 18.50 30 yes 6032669233 2079 Day Care 15.75 35 yes 4136374745 2028 Cook 25.00 40 yes 9029631555 2055 Greenskeeper 18.00 30 yes 9029631555 2110 Pro Shop Clerk 16.00 30 yes 8605721276 2004 Host/ Hostess 17.00 24 no 8198427783 2066 Lifeguard 18.00 32 yes 8194249003 2060 Concierge 19.00 35 yes 902484835 2090 Host/Hostess 17.00 40 yes 9024848354 2091 Housekeeping 12.75 40 no 9024840850 2093 Front Desk Clerk 15.00 32 no 8605697580 2049 Pro Shop Clerk 17.00 40 yes 8605697580 2118 Greenskaper 17.00 32 no 5148424623 2099 Concierge 19.75 40 yes 5148424623 2117 Housekeeping 13.50 30 6038465956 2077 Front Desk Clerk 15.25 40 no 4136379670 2061 Waited Waitress 9.50 30 no 4136379670 2111 Kitchen Help 13.00 32 no 2076331290 2101 Tour Guide 16.00 24 yes 4014663080 2087 Tour Guide 14.00 25 no Start Date End Date Openings 05/31 10701 1 06/01 10701 1. 1 05/01 10701 2 05/01 11/01 1 05/15 10/15 3 05/01 10/15 3 07/01 09/01 2 06/01 10/01 1 05/01 09/01 1 06/30 09/15 1. 07/01 10701 2 06/30 10/01 2 06/15 10/01 2 06/01 10/15 05/15 10/31 2 09/21 11/01 1 10/01 03/31 1 09/01 11/01 1. 09/01 03/31 3 06/01 10/15 2 12/01 04/01 1 12/01 04/15 1 05/31 10/15 2 05/01 1 06/15 09/30 2 06/30 09/15 1. 1 06/01 09/30 1 05/31 10/15 1 06/01 10/15 1 09/01 03/01 1 05/31 11/01 3 06/15 10701 1 06/01 10/15 2 12/01 04/01 2 12/15 03/31 2 07/01 11/01 1 07/01 11/15 1 1 06/15 11/01 1 11/01 04/01 1 09/01 12/01 1 05/31 09/30 2 12/15 03/31 1 12/01 05/01 1 07/01 09/01 2 12/15 04/01 2 2 12/15 04/01 1 1 08/01 12/15 1 06/01 10101 1. 07/01 10/31 1 07/01 09/30 1 06/01 09/01 1 11/01 05/01 1 05/01 11/01 1 05/01 11/01 2 06/30 09/30 1 05/01 10/15 1 05/01 11/01 1 11/15 03/31 1. 06/30 09/30 3 09/01 03/01 1 06/30 11/01 2 07/01 10/31 1. 07/01 09/30 1 10/31 1 11 Cancel Print
