Database Project
Digigtalcobra96
3304_Proj1_fl2021.docxINSY 3304 - Project 1
|
Appt ID |
Appt Date |
Appt Time |
Block Code |
Block Desc |
Block Minutes
|
Reason Code |
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 |
Pmt Status |
Pmt Desc |
|
101 |
9/25/2021 |
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 |
PF |
Paid in Full |
|
|
|
|
L2 |
Level 2 |
15 Minutes |
GBP |
General Back Pain |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
L2 |
Level 2 |
15 Minutes |
XR |
X-Ray |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
102 |
9/25/2021 |
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 |
PP |
Partial Pmt |
|
|
|
|
L1 |
Level 1 |
10 Minutes |
SR |
Suture Removal |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
103 |
9/25/2021 |
10:00 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 |
PF |
Paid in Full |
|
|
|
|
L2 |
Level 2 |
15 Minutes |
SR |
Suture Removal |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
104 |
9/25/2021 |
10:30 AM |
L3 |
Level 3 |
20 Minutes |
PT |
Physical Therapy |
77 |
Kim Jackson |
(817)555-4911 |
WC |
Worker's Comp |
210 |
State Farm |
1 |
Kay Jones |
CM |
Complete |
PF |
Paid in Full |
|
105 |
9/25/2021 |
10:30 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 |
PP |
Partial Pmt |
|
|
|
|
L2 |
Level 2 |
15 Minutes |
AI |
Auto Injury |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
106 |
9/25/2021 |
10:30 AM |
L4 |
Level 4 |
30 Minutes |
PT |
Physical Therapy |
97 |
Chris Mancha |
(469)555-3440 |
SP |
Self-Pay |
|
|
3 |
Ray Schultz |
CM |
Complete |
NP |
Not Paid |
|
107 |
9/25/2021 |
11: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 |
PP |
Partial Pmt |
|
108 |
9/25/2021 |
11:30 AM |
L2 |
Level 2 |
15 Minutes |
GBP |
General Back Pain |
105 |
Johnny Redmond |
(214)555-1084 |
I |
Insurance |
323 |
Humana |
2 |
Michael Smith |
CN |
Confirmed |
NP |
Not Paid |
|
109 |
9/25/2021 |
2:00 PM |
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 |
NP |
Not Paid |
|
|
|
|
L2 |
Level 2 |
15 Minutes |
SR |
Suture Removal |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
110 |
9/26/2021 |
8:30 AM |
L4 |
Level 4 |
30 Minutes |
PT |
Physical Therapy |
84 |
James Clayton |
(214)555-9285 |
I |
Insurance |
135 |
TriCare |
3 |
Ray Schultz |
NC |
Not Confirmed |
NP |
Not Paid |
|
111 |
9/26/2021 |
8:30 AM |
L1 |
Level 2 |
10 Minutes |
NP |
New Patient |
23 |
Shelby Davis |
(817)555-1198 |
WC |
Worker’s Comp |
323 |
Humana |
1 |
Janice May |
CN |
Confirmed |
NP |
Not Paid |
|
|
|
|
L2 |
Level 2 |
15 Minutes |
HP |
Hip Pain |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
L2 |
Level 2 |
15 Minutes |
XR |
X-Ray |
|
|
|
|
|
|
|
|
|
|
|
|
|
Additional Information:
Each patient has at least one appointment. Each appointment is for a specific patient.
Each appointment is handled by a specific doctor. Each doctor may handle one or more appointments (or may have no appointments).
Each appointment has one or more block codes and one or more reason codes.
Each appointment has one billing type. Billing type may be assigned to one or more appointments (or to no appointments).
Each appointment has a specific status code applied. There may be status codes which have not been applied to any appointments.
Based on the information provided above, complete the following:
1. FIRST NORMAL FORM (1NF):
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):
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):
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:
a. Create the Relational Schema for all the 3NF tables.
b. Using Chen notation, create an ERD showing all the 3NF tables. You must show the entities, relationships, connectivity, participation, and cardinality (it is not necessary to show the attributes on the ERD).
IMPORTANT:
· Hand-written assignments will not be accepted. You may use Visio, draw.io, or similar software to create the diagrams.
· Your submission should be uploaded as a single PDF document.
· Submissions are due by 11:59 PM on the due date. Late work is accepted but with a late penalty of 20% per day.
