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

| November 24, 2016

uestion
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 tables 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
doesnt 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.

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/1

Get a 30 % discount on an order above $ 50
Use the following coupon code:
COCONUT
Order your essay today and save 30% with the discount code: COCONUTOrder Now
Positive SSL