Appt ID

Appt Date

Appt Time

Block Code

Block Code Desc

Block Time

Appt Reason Code

Appt Reason Desc

Patient ID

Patient Name

Patient Phone

Billing Type

Billing Type Desc

Ins Co ID

Ins Co Name

Dr ID

Dr Name

Appt Status Code

Appt Status Desc

101

9/1/2018

9:00 AM

L1

Level 1

10 Minutes

NP

New Patient

101

Wesley Tanner

(817)555-1193

I

Insurance

323

Humana

2

Michael Smith

CM

Complete

L2

Level 2

15 Minutes

GBP

General Back Pain

102

9/1/2018

9:00 AM

L1

Level 1

10 Minutes

PSF

Post-Surgery Follow Up

100

Brenda Rhodes

(214)555-9191

I

Insurance

129

Blue Cross

5

Janice May

CM

Complete

L1

Level 1

10 Minutes

SR

Suture Removal

103

9/1/2018

9:30 AM

L1

Level 1

10 Minutes

PSF

Post-Surgery Follow Up

15

Jeff Miner

(469)555-2301

SP

Self Pay

2

Michael Smith

CM

Complete

104

9/1/2018

9:30 AM

L3

Level 3

20 Minutes

PT

Physical Therapy

77

Kim Jackson

(817)555-4911

WC

Worker's Comp

210

State Farm

3

Ray Schultz

CM

Complete

105

9/1/2018

9:45 AM

L1

Level 1

10 Minutes

NP

New Patient

119

Mary Vaughn

(817)555-2334

I

Insurance

129

Blue Cross

2

Michael Smith

CM

Complete

L2

Level 2

15 Minutes

AI

Auto Injury

106

9/1/2018

10:00 AM

L4

Level 4

30 Minutes

PT

Physical Therapy

97

Chris Mancha

(469)555-3440

SP

Self Pay

3

Ray Schultz

CN

Confirmed

107

9/1/2018

10:30 AM

L3

Level 3

20 Minutes

PT

Physical Therapy

28

Renee Walker

(214)555-9285

I

Insurance

129

Blue Cross

3

Ray Schultz

CN

Confirmed

108

9/1/2018

10:30 AM

L2

Level 2

15 Minutes

GBP

General Back Pain

105

Johnny Redmond

(214)555-1084

I

Insurance

323

Humana

2

Michael Smith

NC

Not Confirmed

109

9/1/2018

11:00 AM

L1

Level 1

10 Minutes

PSF

Post-Surgery Follow Up

84

James Clayton

(214)555-9285

I

Insurance

135

TriCare

5

Janice May

CN

Confirmed

110

9/1/2018

11:00 AM

L4

Level 4

30 Minutes

PT

Physical Therapy

84

James Clayton

(214)555-9285

I

Insurance

135

TriCare

3

Ray Schultz

CN

Confirmed

111

9/1/2018

1:00 PM

L4

Level 4

30 Minutes

PT

Physical Therapy

23

Shelby Day

(817)555-1198

I

Insurance

323

Humana

3

Ray Schultz

NC

Not Confirmed

Based on the information provided above, complete the following:

1. FIRST NORMAL FORM (1NF) :

Decompose the composite attributes into simple attributes.

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

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

Create a dependency diagram for the table above.

2. SECOND NORMAL FORM (2NF) :

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

Create the dependency diagrams for the resulting tables.

3. THIRD NORMAL FORM (3NF) :

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

Create the dependency diagrams for the resulting tables.

4. ENTITY-RELATIONSHIP MODEL :

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).

Based on the ERD created in step 4a, create a relational schema showing the entities, their attributes, and the connectivity of the relationships. This relational schema should show each entity with its attributes listed inside a rectangle, similar to Crows Foot notation (i.e., do not show the attributes inside ovals stemming from the entities). This may be done in Access.