DataBase Managemanet Systems
ams
3304_Proj2_fl2024.docxINSY 3304
Project 2
This project must be completed using an Oracle database with all statements manually typed into an SQL file and run as a batch in Oracle, and the output must be written to a text (.txt) file. Statements and results (feedback from Oracle) must be included in the output file for submission. Directions for downloading the software and for creating and running the SQL file will be posted in Canvas.
Project 2 Relational Schema
I. A) Based on the 3NF relational schema from Project 1 (shown above), analyze the tables, their relationships, and the sample data you were given in Project 1. Create the tables shown, using the best data types based on your analysis. Follow each table name with an underscore and your first, middle, and last initials (if you do not have a middle initial, use X). For example, if your name is Alice B Chandler, you would name the Customer table Customer_abc. Also, keep in mind that foreign key relationships require the same data types on both sides; e.g., if you declare CustID as NUMBER(5) in the Customer table, it must be declared as NUMBER(5) in the Rental table, too. All CREATE table statements and their resulting output (e.g., “Table created”) must be included in your printed output.
B) Execute a DESCRIBE statement for each of the tables. All DESCRIBE statements and their resulting output (i.e., the table structure) must be included in your printed output).
II. A) Insert the sample data from Project 1 (shown below) into each table. Execute a COMMIT statement to permanently save your changes. All INSERT statements and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your printed output.
|
Appt ID |
ApptDateTime |
Treatment Code |
Treatment Desc |
Treatment Rate |
Block Code |
Block Desc |
Block Minutes |
Patient ID |
Patient Name |
Patient Phone |
Billing Type |
Billing Type Desc |
Ins Co ID |
Ins Co Name |
Prov ID |
Prov Name |
Appt Status Code |
Appt Status Desc |
Pmt Status |
Pmt Desc |
|
101 |
2/19/24, 9:00 AM |
NP |
New Patient |
$45 |
L1 |
Level 1 |
15 |
101 |
Wesley Tanner |
(817)555-1193 |
I |
Insurance |
323 |
Humana |
2 |
Michael Smith |
CM |
Complete |
PD |
Paid in Full |
|
101 |
2/19/24, 9:00 AM |
GBP |
General Back Pain |
$60 |
L2 |
Level 2 |
20 |
101 |
Wesley Tanner |
(817)555-1193 |
I |
Insurance |
323 |
Humana |
2 |
Michael Smith |
CM |
Complete |
PD |
Paid in Full |
|
101 |
2/19/24, 9:00 AM |
XR |
X-Ray |
$250 |
L2 |
Level 2 |
20 |
101 |
Wesley Tanner |
(817)555-1193 |
I |
Insurance |
323 |
Humana |
2 |
Michael Smith |
CM |
Complete |
PD |
Paid in Full |
|
102 |
2/19/24, 9:00 AM |
PSF |
Post-Surgery Follow Up |
$30 |
L1 |
Level 1 |
15 |
100 |
Brenda Rhodes |
(214)555-9191 |
I |
Insurance |
129 |
Blue Cross |
5 |
Janice May |
CM |
Complete |
PP |
Partial Pmt |
|
102 |
2/19/24, 9:00 AM |
SR |
Suture Removal |
$50 |
L2 |
Level 2 |
20 |
100 |
Brenda Rhodes |
(214)555-9191 |
I |
Insurance |
129 |
Blue Cross |
5 |
Janice May |
CM |
Complete |
PP |
Partial Pmt |
|
103 |
2/19/24, 10:00 AM |
PSF |
Post-Surgery Follow Up |
$30 |
L1 |
Level 1 |
15 |
15 |
Jeff Miner |
(469)555-2301 |
SP |
Self-Pay |
|
|
2 |
Michael Smith |
CM |
Complete |
PD |
Paid in Full |
|
103 |
2/19/24, 10:00 AM |
SR |
Suture Removal |
$50 |
L2 |
Level 2 |
20 |
15 |
Jeff Miner |
(469)555-2301 |
SP |
Self-Pay |
|
|
2 |
Michael Smith |
CM |
Complete |
PD |
Paid in Full |
|
104 |
2/19/24, 10:30 AM |
PT30 |
Physical Therapy 30 |
$60 |
L3 |
Level 3 |
30 |
77 |
Kim Jackson |
(817)555-4911 |
WC |
Worker's Comp |
210 |
State Farm |
1 |
Kay Jones |
CM |
Complete |
PD |
Paid in Full |
|
105 |
2/19/24, 10:30 AM |
NP |
New Patient |
$45 |
L1 |
Level 1 |
15 |
119 |
Mary Vaughn |
(817)555-2334 |
I |
Insurance |
129 |
Blue Cross |
2 |
Michael Smith |
CM |
Complete |
PP |
Partial Pmt |
|
105 |
2/19/24, 10:30 AM |
BI |
Back Injury |
$60 |
L2 |
Level 2 |
20 |
119 |
Mary Vaughn |
(817)555-2334 |
I |
Insurance |
129 |
Blue Cross |
2 |
Michael Smith |
CM |
Complete |
PP |
Partial Pmt |
|
106 |
2/19/24, 10:30 AM |
PT60 |
Physical Therapy 60 |
$110 |
L4 |
Level 4 |
60 |
97 |
Chris Mancha |
(469)555-3440 |
SP |
Self-Pay |
|
|
3 |
Ray Schultz |
CM |
Complete |
NP |
Not Paid |
|
107 |
2/19/24, 11:30 AM |
PT30 |
Physical Therapy 30 |
$60 |
L3 |
Level 3 |
30 |
28 |
Renee Walker |
(214)555-9285 |
I |
Insurance |
129 |
Blue Cross |
3 |
Ray Schultz |
CN |
Confirmed |
PP |
Partial Pmt |
|
108 |
2/19/24, 11:30 AM |
GBP |
General Back Pain |
$60 |
L2 |
Level 2 |
20 |
105 |
Johnny Redmond |
(214)555-1084 |
I |
Insurance |
323 |
Humana |
2 |
Michael Smith |
CN |
Confirmed |
NP |
Not Paid |
|
109 |
2/19/24, 2:00 PM |
PSF |
Post-Surgery Follow Up |
$30 |
L1 |
Level 1 |
15 |
84 |
James Clayton |
(214)555-9285 |
I |
Insurance |
135 |
TriCare |
5 |
Janice May |
NC |
Not Confirmed |
NP |
Not Paid |
|
109 |
2/19/24, 2:00 PM |
SR |
Suture Removal |
$50 |
L2 |
Level 2 |
20 |
84 |
James Clayton |
(214)555-9285 |
I |
Insurance |
135 |
TriCare |
5 |
Janice May |
NC |
Not Confirmed |
NP |
Not Paid |
|
110 |
2/20/24, 8:30 AM |
PT60 |
Physical Therapy 60 |
$110 |
L4 |
Level 4 |
60 |
84 |
James Clayton |
(214)555-9285 |
I |
Insurance |
135 |
TriCare |
3 |
Ray Schultz |
NC |
Not Confirmed |
NP |
Not Paid |
|
111 |
2/20/24, 8:30 AM |
NP |
New Patient |
$45 |
L1 |
Level 1 |
15 |
23 |
Shelby Davis |
(817)555-1198 |
WC |
Worker's Comp |
323 |
Humana |
5 |
Janice May |
CN |
Confirmed |
NP |
Not Paid |
|
111 |
2/20/24, 8:30 AM |
HP |
Hip Pain |
$60 |
L2 |
Level 2 |
20 |
23 |
Shelby Davis |
(817)555-1198 |
WC |
Worker's Comp |
323 |
Humana |
5 |
Janice May |
CN |
Confirmed |
NP |
Not Paid |
|
111 |
2/20/24, 8:30 AM |
XR |
X-Ray |
$250 |
L2 |
Level 2 |
20 |
23 |
Shelby Davis |
(817)555-1198 |
WC |
Worker's Comp |
323 |
Humana |
5 |
Janice May |
CN |
Confirmed |
NP |
Not Paid |
B) Execute a SELECT statement on each table to list all contents (all columns and all rows). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your printed output.
III. Execute the transactions below to modify/add to the data entered in the previous step. Execute a COMMIT statement to permanently save your changes. All INSERT, UPDATE, and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your printed output.
PATIENT table
Change the phone number of Patient 100 to ‘2145551234’
Add Patient 120 (Amanda Green, no phone number)
TREATMENT table
Add a treatment:
TreatmentCode TreatmentDesc TreatmentRate BlockCode
CI1 Cortizone Injection 1 $50 L1
CI2 Cortizone Injection 1 $100 L1
APPOINTMENT table
Change the appointment date/time for Appt 108 to 2/21/2024
Change the billing type of Appt 107 to WC
Add an appointment:
ApptID ApptDateTime PatientID BillingType InsCoID ProvID ApptStatusCode PmtStatus
112 2/21/24 9AM 120 SP 2 NC NP
APPTDETAIL table
Add the following treatment codes for Appt 112:
NP
HP
CI2
Delete the following treatment code from Appt 105:
BI
Add the following treatment code for Appt 105:
GBP
IV. Execute a SELECT statement on each table to list all contents (all columns and all rows), sorted in ascending order by its primary key (in the APPTDETAIL table, sort by ApptID first, then by TreatementCode). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your printed output.
Submission:
Add comment lines at the top of your SQL file (after the spool and echo commands) that contain your name, section number, and project number. Run the file, and once you’ve worked through the errors and have completed all requirements, print the .txt file (not the .sql file). Add a cover sheet with your name, course/section, project number, and the date. Print and securely staple all pages together and submit at the beginning of class on the specified due date.
