INSY 3304 University of Texas SQL File Data Base Management Project This project must be completed using an Oracle database with all statements manually ty

INSY 3304 University of Texas SQL File Data Base Management Project 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. 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. INSY 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. 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 PatientID as NUMBER(5) in the Patient 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 output file.
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 output file).
II. A) Insert the sample data from Project 1 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 output file.
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 output file.
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 output file.
Patient table
Change the phone number of Patient 101 to ‘2145551234’
Add Patient 120 (Amanda Green, no phone number)
ApptStatus table
Add a new status:
ApptStatus
ApptStatusDesc
X
Cancelled
Appt table
Change the appointment time for Appt 110 to 11:30.
Change the appointment status for Appt 108 to Cancelled
ApptDetail table
Add the following:
ApptID
ApptReasonCode
108
NP
BlockCode
L1
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 PatientID first, then by
ApptReasonCode). All SELECT statements and their resulting output (i.e., the table and its data)
must be included in your output file.
How to Create, Run, and Print Statements and Output for an SQL File
•
•
•
•
•
•
•
•
•
•
•
Download and install Oracle Express Edition 11g from the following link:
https://www.oracle.com/technetwork/database/database-technologies/expressedition/downloads/xe-prior-releases-5172097.html
Note: You will be required to create an account.
Open Notepad and type the following at the top of the file:
spool pathproject2_abc.txt’
— Include the full path. This will start logging to the specified file.
set echo on
— This will ensure that all input and output is logged to the file.
Type all statements into a Notepad file (it is recommended that you do this a few
statements at a time, testing as you go along). Please indicate the different sections of the
project using comment lines, which can be created using two hyphens at the beginning of
the comment text; e.g., “–Part I” or “–Part II” (without the quotations). Name the file
using your initials as a suffix with an sql extension (e.g., project2_abc.sql).
Type the following at the very end of the file:
set echo off
— This will turn off logging.
spool off
— This will close the file.
Start your database using the “Start Database” option in the Oracle Database program
group (from the Windows “Start button”).
Open the SQL command line from the “Run SQL Command Line” option in the Oracle
Database program group (from the Windows “Start button”). When the window opens,
you will see an SQL prompt. Type the following command to connect to your database:
connect system/password
where password is the password you entered during the installation process.
Once connected, you may run your SQL file by typing the start command at the SQL
prompt (include the full path). For example:
start C:UserssarraDesktopProject2_kls.sql
You will see all of the statements and output scroll by. When it is finished, the SQL
prompt will appear again.
To disconnect from the database and close your session, type the following:
exit
Ensure that all your statements and related output appear correctly in the spooled text file.
If you have mistakes, you can make the necessary corrections to the SQL statements in
your notepad file. Note: If your SQL file contains Create Table statements, you will
need to drop the previously-created tables before running the SQL file again. You can do
this at the SQL prompt, or you can add the Drop Table statements to the beginning of the
SQL file before you run it (the latter is the better option).
INSY 3304 – Project 1- Due 7/8
Appt
ID
Appt Date
101
6/25/20
102
6/25/20
103
6/25/20
104
6/25/20
105
6/25/20
Appt
Time
9:00
AM
9:00
AM
9:30
AM
9:30
AM
9:45
AM
Appt
Reas
on
Code
L1
Block
Code
Desc
Level
1
Block
Time
10
Minutes
L2
Level
2
15
Minutes
GBP
L1
Level
1
10
Minutes
PSF
L1
Level
1
10
Minutes
SR
L1
Level
1
10
Minutes
PSF
L2
Level
3
Level
1
Level
2
20
Minutes
10
Minutes
15
Minutes
L4
Level
4
30
Minutes
20
Minutes
PT
Block
Code
L3
L1
NP
Appt
Reason
Desc
New
Patient
General
Back
Pain
PostSurgery
Follow
Up
Suture
Removal
PostSurgery
Follow
Up
AI
Physical
Therapy
New
Patient
Auto
Injury
PT
Physical
Therapy
PT
NP
6/25/20
10:00
AM
107
6/25/20
10:30
AM
L3
Level
3
108
6/25/20
10:30
AM
L2
Level
2
15
Minutes
GBP
109
6/25/20
11:00
AM
L1
Level
1
10
Minutes
PSF
Physical
Therapy
General
Back
Pain
PostSurgery
Follow
Up
L2
Level
2
15
Minutes
SR
Suture
Removal
106
Patient
ID
Billing
Type Desc
Ins
Co
ID
I
Insurance
323
Ins Co
Name
Human
a
(214)555-9191
I
Insurance
129
Blue
Cross
(469)555-2301
SP
Self Pay
(817)555-4911
WC
Worker’s
Comp
210
(817)555-2334
I
Insurance
129
(469)555-3440
SP
Self Pay
101
Patient
Name
Wesley
Tanner
(817)555-1193
100
Brenda
Rhodes
Jeff Miner
15
Billing
Type
Patient Phone
119
Kim
Jackson
Mary
Vaughn
97
Chris
Mancha
28
Renee
Walker
(214)555-9285
I
Insurance
Johnny
Redmond
(214)555-1084
I
84
James
Clayton
(214)555-9285
77
105
State
Farm
Blue
Cross
Dr
ID
Appt
Status
Code
Appt Status
Desc
CM
Complete
2
Dr Name
Michael
Smith
5
Janice
May
CM
Complete
2
Michael
Smith
CM
Complete
CM
Complete
CM
Complete
2
Ray
Schultz
Michael
Smith
3
Ray
Schultz
CN
Confirmed
129
Blue
Cross
3
Ray
Schultz
CN
Confirmed
Insurance
323
Human
a
2
Michael
Smith
NC
Not
Confirmed
I
Insurance
135
TriCare
5
Janice
May
CN
Confirmed
3
110
6/25/20
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
6/25/20
1:00
PM
L4
Level
4
30
Minutes
PT
Physical
Therapy
23
Shelby Day
(817)555-1198
WC
Worker’s
Comp
323
Human
a
3
Ray
Schultz
NC
Not
Confirmed
Additional Information:
Each patient has at least one appointment. Each appointment is for a specific patient and a specific start time.
Each appointment has one or more reason codes and one or more block codes.
Each appointment has a specific billing type. There may be billing types for which no appointments exist yet.
Each appointment is handled by a specific doctor. Each doctor handles one or more 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:
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).
5. Submit Documents
Submit your documents from Steps 1 – 4 as a SINGLE pdf document.
5. Project Questionnaire:
Complete the project questionnaire which will be posted in Canvas and will consist of questions (T/F and Multiple Choice) pertaining to your
completed project.
Both the submitted documents and completed questionnaire are due by 11:59 PM on the due date. Video examples and additional documents will
be provided to help you complete this project.

Purchase answer to see full
attachment