ZuoTi.Pro
Question

Database Design Problem You have been hired to review the accuracy of the books of Northeast...

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

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 Openings
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 yes 2073 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 need not be fluent in English. 2041 Housekeeping 12.00 40 no 05/15 10/15 3
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 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 no 2081 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 yes Employees must be fluent in English. 2010 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 Employees must be fluent in English. 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 no 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 no 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 yes Open year round. Employees need not be English speaking. Director’s last name is pronounced “puh TRAY tiss.” 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 yes Open year round. Employees need not be English speaking. Director’s last name is pronounced “puh TRAY 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 Open year round. Employees need not be English speaking. Director’s last name is 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 no 2034 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 yes Personnel Director’s first name is pronounced “AAH cash.” 2020 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 Personnel Director’s first name is pronounced “AAH cash.” 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 2064 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 yes Open year round; more positions 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 Open year round; more 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 USA 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 2049 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 preferred. 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 Open year round. Bilingual French/English speakers 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 no 2111 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 no ALL employees must pass a basic swimming test. 2101 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 yes 2087 Tour Guide 14.00 25 no 06/30 10/31 1
engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

1) Currently NSJI maintains all of its data in flat file spreadsheet, than can cause following problems:
   i) As the data is in flat file spreadsheet, then data duplication can be possible.
   ii) Data can also be scattered in different files, extracting data from different files is also a difficult task.
   iii) There is no normalization scheme when data is in flat file spreadsheet.

The problem is serious from an auditor perspective, due to following reasons:
   i) As there is not database to manage data, review process is also difficult
   ii) Audit reporst can not be easily obtained from this type of data management.

The problem is serious from an company perspective too, due to following reasons:
   i) Data Integrity and Data duplication problem, that leads to various anamolies with in the organization.
   ii) Access management of Data with in the company is also a concern.
   iii) Data recovery problem also arise.

Normalized ERD is given below:

ERD City EMP LOVER PER SON id to me NAICS hone ho

0 0
Add a comment
Know the answer?
Add Answer to:
Database Design Problem You have been hired to review the accuracy of the books of Northeast...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • please answer these queries. 1. List the artist name of the artists who do not have...

    please answer these queries. 1. List the artist name of the artists who do not have a webaddress and their leadsource is “Directmail” 2. List the names of members in the artist called 'Today'. 3. Report the total runtime in minutes FOR EACH album in the Titles table. 4.List the firstname, lastname of members who are represented by the salesperson “Lisa Williams” 5.List EACH salesperson’s firstname along with the number of Members that EACH SalesPerson represents. DROP TABLES IF EXISTS...

  • Oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DRO...

    oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DROP TABLE BOOKS; DROP TABLE PROMOTION; DROP TABLE AUTHOR; DROP TABLE CUSTOMERS; DROP TABLE PUBLISHER; CREATE TABLE Customers ( Customer# NUMBER(4), LastName VARCHAR2(10) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Email VARCHAR(40), Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4), Region CHAR(2), CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#) ); INSERT INTO CUSTOMERS VALUES (1001, 'MORALES', 'BONITA', '[email protected]', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'SE'); INSERT INTO CUSTOMERS VALUES...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT