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

Database Normalization Structure of tables must comply with several rules called normal forms to transform data tables that are not in normal form into tables that comply with the rules. Failure to normalize results in anomalies: errors that might occur when adding, changing, or deleting data stored in the database. The goal of normalization is to produce a database model that contains relations that are in third normal form. Functional Dependence An attribute (column in a table) is functionally dependent on a second attribute (or a collection of other attributes), if a value for the first attribute determines a single value for the second attribute at any time. When functional dependence exists, the first attribute determines the second attribute. Primary Keys Primary key: a value that uniquely identifies a specific row in a table. Typically stored in the tables first column. Composite primary key: two or more columns that provide a primary key for each row. A candidate attribute (a column or collection of columns) is that table's primary key if: - All attributes in the table are functionally dependent on the candidate attribute. - No collection of other columns in the table, taken together, has the first property. Unnormalized Relation First Normal Form Unnormalized table: contains repeating groups (repeating attributes [fields] within each row [record]). A table is in first normal form (1NF) if it doesn't contain repeating groups. Relation in First Normal Form Problems with First Normal Form Include functional dependencies called update anomalies. As a result: - Data may be inconsistent. - Updates may require changes to multiple rows. - Additions and deletions are problematic. Partial dependencies: attribute is dependent on a portion of the primary key. Second Normal Form (2NF) A table is in second normal form (2NF) if it is in 1NF and has no partial dependencies. It has no non-key attributes that are dependent on only a portion of the primary key. Non-key attributes: an attribute that is not part of the primary key. Two steps to get from 1NF to 2NF 1. Create a new table for each subset of the table that is partially dependent on a part of the composite primary key. 2. Place each of the non-key attributes that are dependent on a part of the composite primary key into the table that now has a primary key that is the field on which the non-key attribute is partially dependent. Relations in Second Normal Form Third Normal Form (3NF) A table is in third normal form (3NF) if it is in 2NF and has no transitive dependencies. A transitive dependency exists when a non-key attribute is functionally dependent on another non-key attribute. Relations in Third Normal Form NSJI Flat File Emp'r ID EmployerName Address City St/Prv Zip Code Country NAICSCode NAICSDesc ContactFirstName ContactLastName Position Phone Web Comments PositionID PositionTitle Wage Hrs/Wk Experience StartDate EndDate Opening 10122 Beantown Tours 105 State Street Boston MA 02109 USA 56152 Tour Operators Sarah Tasker Office Manager 6174511970 yes 2045 Tour Guide 17.00 24 no 05/31 10/01 1 10122 Beantown Tours 105 State Street Boston MA 02109 USA 56152 Tour Operators Sarah Tasker Office Manager 6174511970 yes 2082 Reservationist 14.50 40 no 06/01 10/01 1 10126 Bayside Inn & Country Club 354 Oceanside Drive Brewster MA 02631 USA 72111 Hotels and Motels Jeffrey Hersha General Manager 5082835775 yes 2040 Waiter/Waitress 10.50 32 yes 05/01 10/01 2 10126 Bayside Inn & Country Club 354 Oceanside Drive Brewster MA 02631 USA 72111 Hotels and Motels Jeffrey Hersha General Manager 5082835775 Pro Shop Clerk 15.50 24 no 05/01 11/01 1 10133 The Inn at Plum Hill 354 Union Street Vineyard Haven MA 02568 USA 721191 Bed-and-Breakfast Inns Michele Yasenak General Manager 5086932320 yes Open year round. Employees 2073 yes Open not be fluentEmployees 2041 need year round. in English. Housekeeping 12.00 40 no 05/15 10/15 10133 The Inn at Plum Hill 354 Union Street Vineyard Haven MA 02568 USA 721191 Bed-and-Breakfast Inns Michele Yasenak General Manager 5086932320 yes need not be fluent in English. 2068 Housekeeping 12.75 30 no 05/01 10/15 3 10134 Capt'n John's Seafood 22 Old Colony Way Orleans MA 02653 USA 722211 Limited-Service Restaurants John Fairbrother Manager 5082558721 Kitchen Help 12.00 40 no 07/01 09/01 2 10135 The Adele Bannister House 151 Thames Street Newport RI 02840 USA 721191 Bed-and-Breakfast Inns Cheryl Coppolino General Manager 4018493093 no Employees must be fluent in 2081 yes Employees must be fluent in 2010 English. Kitchen Help 13.00 40 no 06/01 10/01 1 10135 The Adele Bannister House 151 Thames Street Newport RI 02840 USA 721191 Bed-and-Breakfast Inns Cheryl Coppolino General Manager 4018493093 yes 2105 Host/Hostess 16.50 25 no 05/01 09/01 1 10138 Blue Hill Inn & Country Club 38 Blue Hill Road Chatham MA 02633 USA 72111 Hotels and Motels Hwan Tang Personnel Director 5088930808 yes 2033 Lifeguard 20.50 35 yes 06/30 09/15 1 10145 The Clipper Ship Inn 5 Cliffside Drive Rockport MA 01966 USA 721191 Bed-and-Breakfast Inns Oren Ben-Joseph Personnel Manager 9785460193 yes 2025 Kitchen Help 12.50 32 no 07/01 10/01 2 10145 The Clipper Ship Inn 5 Cliffside Drive Rockport MA 01966 USA 721191 Bed-and-Breakfast Inns Oren Ben-Joseph Personnel Manager 9785460193 yes 2027 Waiter/Waitress 10.00 32 no 06/30 10/01 2 10145 The Clipper Ship Inn 5 Cliffside Drive Rockport MA 01966 USA 721191 Bed-and-Breakfast Inns Oren Ben-Joseph Personnel Manager 9785460193 yes 2096 Housekeeping 13.50 30 no 06/15 10/01 2 10145 The Clipper Ship Inn 5 Cliffside Drive Rockport MA 01966 USA 721191 Bed-and-Breakfast Inns Oren Ben-Joseph Personnel Manager 9785460193 yes 2097 Waiter/Waitress 12.00 32 no 06/01 10/15 3 10146 Newport Mansion Guided Tours 75 Ocean Drive Newport RI 02840 USA 56152 Tour Operators Katherine Foley Personnel Director 4018496544 yes 2007 Tour Guide 18.75 20 yes 05/15 10/31 2 10146 Newport Mansion Guided Tours 75 Ocean Drive Newport RI 02840 USA 56152 Tour Operators Katherine Foley Personnel Director 4018496544 yes 2017 Tour Guide 15.00 20 no 09/21 11/01 1 10151 Granite State Resort 41 Hayward Avenue North Conway NH 03860 USA 72111 Hotels and Motels Christine Faraci HR Manager 6034688866 yes 2036 Reservationist 14.75 32 no 10/01 03/31 1 10151 Granite State Resort 41 Hayward Avenue North Conway NH 03860 USA 72111 Hotels and Motels Christine Faraci HR Manager 6034688866 yes 2102 Reservationist 15.00 30 no 09/01 11/01 1 10151 Granite State Resort 41 Hayward Avenue North Conway NH 03860 USA 72111 Hotels and Motels Christine Faraci HR Manager 6034688866 yes 2122 Kitchen Help 13.00 35 no 09/01 03/31 3 10151 Granite State Resort 41 Hayward Avenue North Conway NH 03860 USA 72111 Hotels and Motels Christine Faraci HR Manager 6034688866 yes 2130 Housekeeping 12.50 30 no 06/01 10/15 2 10152 Alpine Touring Center 57 Main Street Bethel ME 04217 USA 71392 Skiing Facilities Mary Grant Manager 2078249976 2058 Main Office Clerk 14.25 32 no 12/01 04/01 1 10152 Alpine Touring Center 57 Main Street Bethel ME 04217 USA 71392 Skiing Facilities Mary Grant Manager 2078249976 2094 Ski Patrol 19.00 25 yes 12/01 04/15 1 10154 All Seasons Resort 45 Oceanside Way Falmouth MA 02540 USA 72111 Hotels and Motels Chelsea Petraitis Director 5083890777 2085 Waiter/Waitress 10.00 30 no 05/31 10/15 2 10154 All Seasons Resort 45 Oceanside Way Falmouth MA 02540 USA 72111 Hotels and Motels Chelsea Petraitis Director 5083890777 no Open year round. Employees need not be English speaking. Employees no Open year round. name is Director's last need not be English speaking. Employees yes Open year round. TRAY is pronounced \"puh name tiss.\" Director's last need not be English speaking. yes pronounced \"puh TRAY is Director's last name tiss.\" 2115 Gift Shop Clerk 13.00 25 no 05/01 09/30 1 10154 All Seasons Resort 45 Oceanside Way Falmouth MA 02540 USA 72111 Hotels and Motels Chelsea Petraitis Director 5083890777 yes pronounced \"puh TRAY tiss.\" 2120 Lifeguard 19.00 32 yes 06/15 09/30 2 10155 The Bramble Restaurant 52 Wingate Drive Hyannis MA 02601 USA 722211 Limited-Service Restaurants Rodrigo Valencia Manager 5082770387 no 2021 Waiter/Waitress 9.50 30 no 06/30 09/15 1 10155 The Bramble Restaurant 52 Wingate Drive Hyannis MA 02601 USA 722211 Limited-Service Restaurants Rodrigo Valencia Manager 5082770387 no 2114 Cook 20.00 40 yes 06/01 09/30 1 10156 Seaport Scenic Tours 87 Cottrell Street Mystic CT 06355 USA 71393 Marinas Greg Robitaille Director 8605723989 yes 2056 Reservationist 15.00 24 no 05/31 10/15 1 10156 Seaport Scenic Tours 87 Cottrell Street Mystic CT 06355 USA 71393 Marinas Greg Robitaille Director 8605723989 yes 2074 Tour Guide 15.75 30 yes 06/01 10/15 1 10159 Summit Hotel & Conference Center 19 White Mountain Highway Franconia NH 03480 USA 72111 Hotels and Motels Nancy Shea Personnel Manager 6038239787 yes 2037 Gift Shop Clerk 13.50 35 no 09/01 03/01 1 10162 Darby Inn & Restaurant 52 Chestnut Street Woodstock VT 05091 USA 721191 Bed-and-Breakfast Inns Jahnavi Sonthi Manager 8029874603 Waiter/Waitress 10.25 30 no 05/31 11/01 3 10163 Belleview Resort 29 Eden Street Bar Harbor ME 04609 USA 72111 Hotels and Motels Akash Shah Personnel Director 2072881961 no Personnel Director's first name 2034 yes Personnel Director's first name 2020 is pronounced \"AAH cash.\" Host/Hostess 18.50 32 yes 06/15 10/01 1 10163 Belleview Resort 29 Eden Street Bar Harbor ME 04609 USA 72111 Hotels and Motels Akash Shah Personnel Director 2072881961 yes 2124 Kitchen Help 14.50 40 yes 06/01 10/15 2 10167 Ski & Stay 587 Mountain Road Stowe VT 05672 USA 71392 Skiing Facilities Nathan Weiss Personnel Manager 8022530809 no 2070 Housekeeping 13.00 24 no 12/01 04/01 2 10167 Ski & Stay 587 Mountain Road Stowe VT 05672 USA 71392 Skiing Facilities Nathan Weiss Personnel Manager 8022530809 no 2107 Ski Patrol 18.75 25 yes 12/15 03/31 2 10170 Whittier Resort & Spa 15 Summer Street Stockbridge MA 01262 USA 72111 Hotels and Motels Rebecca Giannopoulous Manager 4132980811 yes 2048 Front Desk Clerk 16.50 32 no 07/01 11/01 1 10170 Whittier Resort & Spa 15 Summer Street Stockbridge MA 01262 USA 72111 Hotels and Motels Rebecca Giannopoulous Manager 4132980811 yes 2123 Main Office Clerk 14.50 32 no 07/01 11/15 1 10174 Pier Restaurant 210 Memorial Boulevard Westerly RI 02808 USA 72211 Full-Service Restaurants Wen-Yi Huang Manager 4015960383 no 2086 Waiter/Waitress 10.75 32 no 06/15 11/01 1 10175 Lion's Mouth Inn 38 Langley Road Stowe VT 05672 USA 721191 Bed-and-Breakfast Inns Bryce Kervin HR Manager 8022539775 yes 2076 Cook 20.00 32 yes 11/01 04/01 1 10176 NH Fall Foliage Tours 7 Alcott Road North Conway NH 03860 USA 56152 Tour Operators Sam Bethel Director 6034687093 yes 2065 Tour Guide 15.00 20 no 09/01 12/01 1 10180 George's Restaurant & Galley 90 Main Street Bar Harbor ME 04609 USA 722211 Limited-Service Restaurants Victoria Gonzalez Manager 2072887750 no Waiter/Waitress 10.00 24 no 05/31 09/30 2 10185 Moondance Inn & Ski Resort 75 Thorn Hill Road Lincoln NH 03251 USA 721191 Bed-and-Breakfast Inns Sonya Krikorian Personnel Director 6036359208 2064 Open year round; more yes positionsyear round; more Open available in winter. 2089 Day Care 15.00 32 yes 12/15 03/31 1 10185 Moondance Inn & Ski Resort 75 Thorn Hill Road Lincoln NH 03251 USA 721191 Bed-and-Breakfast Inns Sonya Krikorian Personnel Director 6036359208 yes positions available in winter. 2127 Waiter/Waitress 10.50 40 no 12/01 05/01 1 10190 The Briar Rose Inn 105 Queen Street Charlottetown PE C1A 8R4 Canada 721191 Bed-and-Breakfast Inns Helen MacDonald Manager 9026261595 yes 2053 Host/Hostess 15.75 24 no 07/01 09/01 2 10191 Windsor Alpine Tours 14 Longmeadow Road Laconia NH 03246 USA 71392 Skiing Facilities Michael Engber Manager 6032669233 yes 2078 Ski Patrol 18.50 30 yes 12/15 04/01 2 10191 Windsor Alpine Tours 14 Longmeadow Road Laconia NH 03246 USA 71392 Skiing Facilities Michael Engber Manager 6032669233 yes 2079 Day Care 15.75 35 yes 12/15 04/01 1 10194 Pear Tree Inn & Restaurant 30 Richmond Road Lenox MA 01240 USA 721191 Bed-and-Breakfast Inns Rachel Camara Manager 4136374745 yes 2028 Cook 25.00 40 yes 08/01 12/15 1 10195 Gables & Golf Country Club 38 Graham's Lane Cavendish PE C0A 1N0 Canada 71391 Golf Courses and Country Clubs Spencer Mewherter General Manager 9029631555 yes 2055 Greenskeeper 18.00 30 yes 06/01 10/01 1 10195 Gables & Golf Country Club 38 Graham's Lane Cavendish PE C0A 1N0 Canada 71391 Golf Courses and Country Clubs Spencer Mewherter General Manager 9029631555 yes 2110 Pro Shop Clerk 16.00 30 yes 07/01 10/31 1 10197 Aidan's of Mystic 390 West Main Road Mystic CT 06355 72232 Caterers Aidan Gaughan Manager 8605721276 no 2004 Host/Hostess 17.00 24 no 07/01 09/30 1 10198 Trudel Spa & Resort 40 Rue Rivard North Hatley QC J0B 2C0 Canada 72111 Hotels and Motels Francoise Boucher Personnel Manager 8198427783 yes 2066 Lifeguard 18.00 32 yes 06/01 09/01 1 10201 Auberge St-Germaine 50 Rue Bellevue St-Donat QC J0T 2C0 Canada 721191 Bed-and-Breakfast Inns Jacques Belanger Director 8194249003 yes 2060 Concierge 19.00 35 yes 11/01 05/01 1 10210 Stonehurst Inn 3 Market Street Halifax NS B3J 3N9 Canada 721191 Bed-and-Breakfast Inns Gavin McDonough Manager 9024848354 no 2090 Host/Hostess 17.00 40 yes 05/01 11/01 1 10210 Stonehurst Inn 3 Market Street Halifax NS B3J 3N9 Canada 721191 Bed-and-Breakfast Inns Gavin McDonough Manager 9024848354 no 2091 Housekeeping 12.75 40 no 05/01 11/01 2 10217 Harbourview Resort 97 Fenwick Road Halifax NS B3J 3N9 Canada 72111 Hotels and Motels Louise Tremblay Personnel Manager 9024840850 yes 2093 Front Desk Clerk 15.00 32 no 06/30 09/30 1 10218 Canfield Golf & Country Club 12 Broadway East Hartford CT 06108 USA 71391 Golf Courses and Country Clubs Celia Johnson Personnel Manager 8605697580 yes Pro Shop Clerk 17.00 40 yes 05/01 10/15 1 10218 Canfield Golf & Country Club 12 Broadway East Hartford CT 06108 USA 71391 Golf Courses and Country Clubs Celia Johnson Personnel Manager 8605697580 yes 2118 Greenskeeper 17.00 32 no 05/01 11/01 1 10220 Hotel du Nord 149 Bonsecours Street Montreal QC H2Y 2X6 Canada 72111 Hotels and Motels Martine Normand General Manager 5148424623 yes Open year round. Bilingual French/English speakers Open year round. Bilingual preferred. French/English speakers 2049 2099 Concierge 19.75 40 yes 11/15 03/31 1 10220 Hotel du Nord 149 Bonsecours Street Montreal QC H2Y 2X6 Canada 72111 Hotels and Motels Martine Normand General Manager 5148424623 yes preferred. 2117 Housekeeping 13.50 30 no 06/30 09/30 3 10222 Whitney's Resort & Spa 80 Carter Notch Road Twin Mountain NH 03595 USA 72111 Hotels and Motels Matthew Lim Director 6038465956 yes 2077 Front Desk Clerk 15.25 40 no 09/01 03/01 1 10223 The Berkshire House 299 Great Road Lenox MA 01240 USA 721191 Bed-and-Breakfast Inns Lubin Jian Manager 4136379670 no 2061 Waiter/Waitress 9.50 30 no 06/30 11/01 2 10223 The Berkshire House 299 Great Road Lenox MA 01240 USA 721191 Bed-and-Breakfast Inns Lubin Jian Manager 4136379670 Kitchen Help 13.00 32 no 07/01 10/31 1 10225 Harbor Whale Watch Tours 17 Commercial Street Boothbay Harbor ME 04538 USA 56152 Tour Operators Alexander Benton Director 2076331290 Tour Guide 16.00 24 yes 07/01 09/30 1 10226 Lighthouse Tours 23 Corn Neck Road Block Island RI 02807 USA 56152 Tour Operators Andrew Adgate Director 4014663080 Tour Guide 14.00 25 no 06/30 10/31 1 USA English. is pronounced \"AAH cash.\" no ALL employees must pass a 2111 basic swimming test. no 2101 yes 2087 3 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