Only the SQL QURIES Context In this assessment you will create an Azure SQL database and write SQL queries to generate t

[answerhappygod]

Only the SQL QURIES
Context
In this assessment you will create an Azure SQL database and
write SQL queries to generate the information required or perform
data manipulation. Finally, you will write a short description of
data security and ethics considerations.
This case study describes a hypothetical small hospital. The
Hospital specialises in the provision of health care for elderly
people. Listed in these sections is a description of the data
recorded, maintained, and accessed by the hospital staff to support
the management and day- to-day operations of the Hospital.
Wards
The Hope Hospital has 17 wards with a total of 240 beds
available for short- and long-term patients, and an outpatient
clinic. Each ward is uniquely identified by a number (for example,
ward 11) and also a ward name (for example,
1
Orthopedic), location (for example, E Block), total number of
beds, and telephone extension number (for example, Extn. 7711).
Staff
The Hospital has a Medical Director, who has overall
responsibility for the management of the hospital. The Medical
Director maintains control over the use of the hospital resources
(including staff, beds, and supplies) in the provision of
cost-effective treatment for all patients.
The Hospital has a Personnel Officer, who is responsible for
ensuring that the appropriate number and type of staff are
allocated to each ward and the outpatient1 clinic. The
information stored for each member of staff includes a staff
number, name (first and last), full address, telephone number, date
of birth, gender, insurance number, position held, current salary,
and salary scale. It also includes each member’s qualifications
(which includes date of qualification, type, and name of
institution), and work experience details (which includes the name
of the organization, position, and start and finish dates).
Figure B.1 Hospital staff form.
The type of employment contract for each member of staff is also
recorded, including the number of hours worked per week, whether
the member of staff is on permanent basis or temporary contract,
and the type of salary payment (weekly/ monthly). An example of a
hospital form used to record the details of a member of staff
called Moira Samuel working in ward 11 is shown in Figure B.1.
Each ward and the outpatient clinic has a member of staff with
the position of Charge Nurse. The Charge Nurse is responsible for
overseeing the day-to-day operation of the ward/clinic. The Charge
Nurse is allocated a budget to run the ward and must ensure that
all resources (staff, beds, and supplies) are used effectively in
the care of patients. The Medical Director works closely with the
Charge Nurses to ensure the efficient running of the hospital.
A Charge Nurse is responsible for setting up a weekly staff
rotation and must ensure that the ward/clinic has the correct
number and type of staff on duty at any time during the day or
night. In a given week, each member of staff is assigned to work an
early, late, or night shift.
As well as the Charge Nurse, each ward is allocated senior and
junior nurses, doctors and auxiliaries. Specialist staff
1 An inpatient service is any service you have once you’ve
been formally admitted to a hospital. As either a day or overnight
patient. So, outpatient is anything where you’re not formally
admitted to hospital. The most common outpatient procedures are
going to hospital for tests, attending an outpatient clinic, or
having consultation in hospital with a specialist.
2
(for example, consultants and physiotherapists) are allocated to
several wards or the clinic. An example of a hospital report
listing the details of the staff allocated to ward 11 is shown in
Figure B.2.
Figure B.2 The first page of the Hospital report listing ward
staff.
Patients
When a patient is first referred to the hospital, he or she is
allocated a unique patient number. At this time, additional details
of the patient are also recorded, including name (first and last),
address, telephone number, date of birth, gender, marital status,
date registered with the hospital, and the details of the patient’s
next-of-kin.
Patient’s next-of-kin
The details of a patient’s next-of-kin are recorded, which
includes the next-of-kin’s full name, relationship to the patient,
address, and telephone number.
Local doctors
Patients are normally referred to the hospital by their local
doctor. The details of local doctors are held, including their full
name, clinic number, address, and telephone number. The clinic
number is unique throughout Australia. An example of a Hospital
patient registration form used to record the details of a patient
called Anne Phelps is shown in Figure B.3.
3
Figure B.3 Hospital patient registration form.
Patient appointments
When a patient is referred by his or her doctor to attend the
Hospital, the patient is given an appointment for an examination by
a hospital consultant.
Each appointment is given a unique appointment number. The
details of each patient’s appointment are recorded and include the
name and staff number of the consultant performing the examination,
the date and time of the appointment, and the examination room (for
example, Room E252).
As a result of the examination, the patient is either
recommended to attend the outpatient clinic or is placed on a
waiting list until a bed can be found in an appropriate ward.
Outpatients
The details of outpatients are stored and include the patient
number, name (first and last), address, telephone number, date of
birth, gender, and the date and time of the appointment at the
outpatient clinic.
C. Write SQL queries
In Azure portal, connect to the database and write/run SQL
scripts to create tables with appropriate data type and
constraints, indexes and relationships based on conceptual design.
The following Constraints should be applied:
Gender should be limited to ‘Male’, ‘Female’, and ‘Other.
All Date of Birth should be after 1/1/1900
Other date fields (columns) should be after 1/1/2020
Bed number, ward number, room number should be within the range
specified in the case study.
In your report provide the list of indexes and justification for
creating them.
Write and run queries to insert at least 5 rows of data (random
data) into each table. Insert your personal details
in the staff table.
Produce a report (view) listing the details of ‘permanent’ staff
aged between 30 and 40 years who work in any
ward except ward number 11.
Produce a report (view) listing the details of patients referred
to the outpatient clinic
Produce a report (view) listing the details of patients
currently located in ward 11.
Write an update query to increase the ‘current salary’ of staff
who live in VIC or NSW and work more than 30
hours per week by 5%. Perform transaction management (using
Commit/Rollback commands) to ensure data
integrity during and after update execution.
Create Stored Function to identify the total number of staff
assigned to each ward (the input to the function is
ward number, the output of function is the total number of staff
assigned to that ward)
Create Stored function to calculate the average salary of staff
(input: ward number, if input parameter is 0, the
function should return the average salary of all staff at
hospital