DBMS (Computer Information Systems)

INSY 3304 - Project 1

Due 3/12

Res Num

Check In

Check Out

Cust ID

Cust Name

Cust Phone

Cust Type

Cust Desc

Loyalty ID

Agent ID

Agent Name

Agent Type

Agent Desc

Guest Count

Room Num

Room Type

Room Desc

Rate Type

Rate Desc

Rate Amt

1001

9/5/2020

9/7/2020

85

Wesley Tanner

(817)555-1193

C

Corporate

323

20

Megan Smith

FD

Front Desk

2

224

K

King Bed

C

Corporate

$120

225

D

2 Double Beds

C

Corporate

$125

1002

9/1/2020

9/10/2020

100

Breanna Rhodes

(214)555-9191

I

Individual

129

5

Janice May

T

Telephone

4

305

D

2 Double Beds

S

Standard

$149

1003

9/9/2020

9/11/2020

15

Jeff Miner

I

Individual

14

John King

RC

Online Res Center

3

409

D

2 Double Beds

W

Weekend

$99

1004

9/12/2020

9/14/2020

77

Kim Jackson

(817)555-4911

C

Corporate

210

28

Ray Schultz

T

Telephone

4

320

D

2 Double Beds

C

Corporate

$110

409

D

2 Double Beds

C

Corporate

$105

321

K

King Bed

C

Corporate

$112

1005

9/15/2020

9/18/2020

119

Mary Vaughn

(817)555-2334

I

Individual

118

20

Megan Smith

FD

Front Desk

1

302

K

King Bed

S

Standard

$139

1006

9/24/2020

9/26/2020

97

Chris Mancha

(469)555-3440

I

Individual

153

14

John King

RC

Online Res Center

2

501

KS

King Suite

W

Weekend

$119

502

KS

King Suite

W

Weekend

$119

1007

9/20/2020

9/25/2020

100

Breanna Rhodes

(214)555-9191

I

Individual

129

20

Megan Smith

FD

Front Desk

2

302

K

King Bed

S

Standard

$139

1008

10/1/2020

10/3/2020

85

Wesley Tanner

(817)555-1193

C

Corporate

323

5

Janice May

T

Telephone

3

320

D

2 Double Beds

W

Weekend

$89

321

K

King Bed

W

Weekend

$99

1009

10/1/2020

10/4/2020

28

Renee Walker

(214)555-9285

I

Individual

135

14

John King

RC

Online Res Center

2

502

KS

King Suite

W

Weekend

$129

409

D

2 Double Beds

W

Weekend

$129

1010

10/1/2020

10/9/2020

23

Shelby Day

I

Individual

28

Ray Schultz

T

Telephone

4

225

D

2 Double Beds

W

Weekend

$109

Business Rules:

Each reservation belongs to a specific customer; customers may or may not have any reservations.

Each customer is a specific type; some customer types may not have any customers.

Each reservation is booked by a specific agent; each agent may or may not have booked any reservations.

Each agent works for a specific department; each department has at least one agent (employee).

Each reservation is for one or more rooms, all with the same rate type within the same reservation, but rates types may vary from reservation-to-reservation; some rooms may not have any reservations.

Based on the information provided above, complete the following:

1. FIRST NORMAL FORM (1NF) – 20 points:

a. Decompose the composite attributes into simple attributes.

b. Convert the table above to 1NF (eliminate repeating groups of data and select an appropriate PK).

c. Show the table structure format (table name with PK and all dependent attributes in parentheses).

d. Create a dependency diagram for the table above.

2. SECOND NORMAL FORM (2NF) – 20 points:

a. Show the table structure format for each table in 2NF.

b. Create the dependency diagrams for the resulting tables.

3. THIRD NORMAL FORM (3NF) - 25 points:

a. Convert to 3NF and show the table structure format for each table.

b. Create the dependency diagrams for the resulting tables.

4. ENTITY-RELATIONSHIP MODEL - 35 points:

a. Create a relational schema showing all 3NF tables (entities), their attributes, and the connectivity of the relationships. Each entity should be depicted as a rectangle with the name and the top and the attributes listed under the name. This may be done in Access.

b. Using Chen notation, create an ERD showing all of the 3NF tables above. You must show the entities, relationships, connectivity, participation, and cardinality (it is not necessary to show the attributes on the ERD).

This assignment is due by 11:59 PM on the due date. Microsoft Visio or other diagramming software is recommended to create the required diagrams. Please upload your assignment as a single document. This may be done by putting all diagrams into a single Word, Excel, or PDF file.