INSY 3304 The University of Texas at Arlington Database Management System SQL Project Lab Report INSY 3304 (Database Management System) Project- 2 This pro

INSY 3304 The University of Texas at Arlington Database Management System SQL Project Lab Report INSY 3304 (Database Management System) 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. 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
App
t ID
101
Appt
Date
6/25/20
101
6/25/20
102
102
103
104
105
105
106
107
108
109
109
6/25/20
6/25/20
6/25/20
6/25/20
6/25/20
6/25/20
6/25/20
Appt
Time
9:00
AM
9:00
AM
9:00
AM
9:00
AM
9:30
AM
9:30
AM
9:45
AM
9:45
AM
10:00
AM
6/25/20
10:30
AM
6/25/20
10:30
AM
6/25/20
11:00
AM
6/25/20
11:00
AM
Bloc
k
Code
L1
L2
L1
L1
L1
L3
L1
L2
L4
Bloc
k
Cod
e
Desc
Leve
l1
Leve
l2
Leve
l1
Leve
l1
Leve
l1
Leve
l3
Leve
l1
Leve
l2
Leve
l4
L3
Leve
l3
L2
Leve
l2
L1
Leve
l1
L2
Leve
l2
Block
Time
10
15
10
10
10
20
10
15
30
20
15
10
15
App
t
Rea
son
Cod
e
NP
GBP
PSF
SR
PSF
PT
NP
AI
PT
PT
GBP
PSF
SR
Patient L
Name
Appt
Reason
Desc
New
Patient
Genera
l Back
Pain
PostSurger
y
Follow
Up
Suture
Remov
al
PostSurger
y
Follow
Up
Physica
l
Therap
y
New
Patient
Auto
Injury
Physica
l
Therap
y
Physica
l
Therap
y
Genera
l Back
Pain
PostSurger
y
Follow
Up
Suture
Remov
al
Patien
t ID
Patient F
Name
Dr L
Name
Billing
Type
Patient Phone
Tanner
101
Wesley
(817)555-1193
I
Billing
Type
Desc
Insuranc
e
Ins
Co
ID
323
Ins Co
Name
Huma
na
Dr
ID
2
Dr F
Name
Michae
l
Tanner
101
Wesley
817)555-1193
Brenda
I
Insuran
ce
323
Huma
na
2
I
Insuranc
e
129
Blue
Cross
5
Janice
(214)555-9191
Jeff
I
Insuranc
e
129
Blue
Cross
5
Janice
(469)555-2301
Kim
SP
(817)555-4911
Mary
WC
(817)555-2334
I
Vaughn
119
Mary
Self Pay
2
Michae
l
(817)555-2334
I
Worker’
s Comp
Insuranc
e
Insuranc
e
210
129
129
State
Farm
Blue
Cross
Blue
Cross
3
2
2
Ray
Michae
l
Michae
l
Mancha
97
Chris
(469)555-3440
Renee
SP
Self Pay
3
I
Insuranc
e
I
Insuranc
e
129
Blue
Cross
323
Huma
na
3
Ray
2
Michae
l
(214)555-1084
James
James
Complete
CM
Complete
CM
Complete
CM
Complete
CN
Confirme
d
Smith
NC
Confirme
d
Not
Confirme
d
CN
Confirme
d
CN
Confirme
d
CN
May
(214)555-9285
I
Insuranc
e
I
Insuranc
e
135
TriCar
e
135
TriCar
e
5
Janice
Clayton
84
CM
Smith
Clayton
84
Complete
Schultz
(214)555-9285
Johnny
CM
Smith
Ray
Redmond
105
Complete
Schultz
Walker
28
CM
Schultz
Vaughn
119
Complete
Smith
Jackson
77
CM
May
Miner
15
Complete
May
(214)555-9191
Brenda
CM
Smith
Micha
el
Rhodes
100
Appt
Status
Desc
Smith
Rhodes
100
Ap
pt
Sta
tus
Co
de
May
(214)555-9285
5
Janice
110
111
6/25/20
6/25/20
12:00
AM
1:00
PM
L4
L4
Leve
l4
Leve
l4
30
30
PT
PT
Physica
l
Therap
y
Physica
l
Therap
y
Clayton
84
James
Schultz
(214)555-9285
I
Insuranc
e
135
TriCar
e
3
Ray
Day
23
Shelby
CN
Confirme
d
NC
Not
Confirme
d
Schultz
(817)555-1198
WC
Worker’
s Comp
323
Huma
na
3
Ray
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.
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.
INSY 3304 – Project 1- Due 7/8
App
t ID
101
Appt
Date
6/25/20
101
6/25/20
102
102
103
104
105
105
106
107
108
109
109
6/25/20
6/25/20
6/25/20
6/25/20
6/25/20
6/25/20
6/25/20
Appt
Time
9:00
AM
9:00
AM
9:00
AM
9:00
AM
9:30
AM
9:30
AM
9:45
AM
9:45
AM
10:00
AM
6/25/20
10:30
AM
6/25/20
10:30
AM
6/25/20
11:00
AM
6/25/20
11:00
AM
Bloc
k
Code
L1
L2
L1
L1
L1
L3
L1
L2
L4
Bloc
k
Cod
e
Desc
Leve
l1
Leve
l2
Leve
l1
Leve
l1
Leve
l1
Leve
l3
Leve
l1
Leve
l2
Leve
l4
L3
Leve
l3
L2
Leve
l2
L1
Leve
l1
L2
Leve
l2
Block
Time
10
15
10
10
10
20
10
15
30
20
15
10
15
App
t
Rea
son
Cod
e
NP
GBP
PSF
SR
PSF
PT
NP
AI
PT
PT
GBP
PSF
SR
Patient L
Name
Appt
Reason
Desc
New
Patient
Genera
l Back
Pain
PostSurger
y
Follow
Up
Suture
Remov
al
PostSurger
y
Follow
Up
Physica
l
Therap
y
New
Patient
Auto
Injury
Physica
l
Therap
y
Physica
l
Therap
y
Genera
l Back
Pain
PostSurger
y
Follow
Up
Suture
Remov
al
Patien
t ID
Patient F
Name
Dr L
Name
Billing
Type
Patient Phone
Tanner
101
Wesley
(817)555-1193
I
Billing
Type
Desc
Insuranc
e
Ins
Co
ID
323
Ins Co
Name
Huma
na
Dr
ID
2
Dr F
Name
Michae
l
Tanner
101
Wesley
817)555-1193
Brenda
I
Insuran
ce
323
Huma
na
2
I
Insuranc
e
129
Blue
Cross
5
Janice
(214)555-9191
Jeff
I
Insuranc
e
129
Blue
Cross
5
Janice
(469)555-2301
Kim
SP
(817)555-4911
Mary
WC
(817)555-2334
I
Vaughn
119
Mary
Self Pay
2
Michae
l
(817)555-2334
I
Worker’
s Comp
Insuranc
e
Insuranc
e
210
129
129
State
Farm
Blue
Cross
Blue
Cross
3
2
2
Ray
Michae
l
Michae
l
Mancha
97
Chris
(469)555-3440
Renee
SP
Self Pay
3
I
Insuranc
e
I
Insuranc
e
129
Blue
Cross
323
Huma
na
3
Ray
2
Michae
l
(214)555-1084
James
James
Complete
CM
Complete
CM
Complete
CM
Complete
CN
Confirme
d
Smith
NC
Confirme
d
Not
Confirme
d
CN
Confirme
d
CN
Confirme
d
CN
May
(214)555-9285
I
Insuranc
e
I
Insuranc
e
135
TriCar
e
135
TriCar
e
5
Janice
Clayton
84
CM
Smith
Clayton
84
Complete
Schultz
(214)555-9285
Johnny
CM
Smith
Ray
Redmond
105
Complete
Schultz
Walker
28
CM
Schultz
Vaughn
119
Complete
Smith
Jackson
77
CM
May
Miner
15
Complete
May
(214)555-9191
Brenda
CM
Smith
Micha
el
Rhodes
100
Appt
Status
Desc
Smith
Rhodes
100
Ap
pt
Sta
tus
Co
de
May
(214)555-9285
5
Janice
110
111
6/25/20
6/25/20
12:00
AM
1:00
PM
L4
L4
Leve
l4
Leve
l4
30
30
PT
PT
Physica
l
Therap
y
Physica
l
Therap
y
Clayton
84
James
Schultz
(214)555-9285
I
Insuranc
e
135
TriCar
e
3
Ray
Day
23
Shelby
CN
Confirme
d
NC
Not
Confirme
d
Schultz
(817)555-1198
WC
Worker’
s Comp
323
Huma
na
3
Ray
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

Don't use plagiarized sources. Get Your Custom Essay on
INSY 3304 The University of Texas at Arlington Database Management System SQL Project Lab Report INSY 3304 (Database Management System) Project- 2 This pro
Get an essay WRITTEN FOR YOU, Plagiarism free, and by an EXPERT!
Order Essay
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Top quality papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional academic writers

We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.

Free revisions

If you feel that we missed something, send the order for a free revision. You will have 10 days to send the order for revision after you receive the final paper. You can either do it on your own after signing in to your personal account or by contacting our support.

On-time delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & confidential

We use several checkers to make sure that all papers you receive are plagiarism-free. Our editors carefully go through all in-text citations. We also promise full confidentiality in all our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

You are welcome to choose your academic level and the type of your paper. Our academic experts will gladly help you with essays, case studies, research papers and other assignments.

Admissions

Admission help & business writing

You can be positive that we will be here 24/7 to help you get accepted to the Master’s program at the TOP-universities or help you get a well-paid position.

Reviews

Editing your paper

Our academic writers and editors will help you submit a well-structured and organized paper just on time. We will ensure that your final paper is of the highest quality and absolutely free of mistakes.

Reviews

Revising your paper

Our academic writers and editors will help you with unlimited number of revisions in case you need any customization of your academic papers