Create table for Staff

CREATE TABLE Staff ( staff_number INT, name VARCHAR(255),d address VARCHAR(255), telephone_number INT, date_of_birth DATE,l gender VARCHAR(255), insurance_number INT, position VARCHAR(255), current_salary INT, salary_scale INT, PRIMARY KEY (staff_number) );

-- Create table for Qualifications

CREATE TABLE Qualifications ( staff_number INT, date_of_qualification DATE, type VARCHAR(255), name_of_institution VARCHAR(255), FOREIGN KEY (staff_number) REFERENCES Staff(staff_number) );

-- Create table for Work Experience

CREATE TABLE Work_Experience ( staff_number INT, name_of_organisation VARCHAR(255), position VARCHAR(255), start_date DATE, finish_date DATE, FOREIGN KEY (staff_number) REFERENCES Staff(staff_number) );

-- Create table for Employment Contracts

CREATE TABLE Employment_Contracts ( staff_number INT, hours_worked_per_week INT, permanent_or_temporary VARCHAR(255), type_of_salary_payment VARCHAR(255), FOREIGN KEY (staff_number) REFERENCES Staff(staff_number) );

-- Create table for Patients

CREATE TABLE Patients ( patient_number INT, name VARCHAR(255), address VARCHAR(255), telephone_number INT, date_of_birth DATE, gender VARCHAR(255), marital_status VARCHAR(255), date_registered DATE, PRIMARY KEY (patient_number) );

-- Create table for Next of Kin

CREATE TABLE Next_of_Kin ( patient_number INT, name VARCHAR(255), relationship VARCHAR(255), address VARCHAR(255), telephone_number INT, FOREIGN KEY (patient_number) REFERENCES Patients(patient_number) );

-- Create table for Local Doctors

CREATE TABLE Local_Doctors ( clinic_number INT, name VARCHAR(255), address VARCHAR(255), telephone_number INT, PRIMARY KEY (clinic_number) );

-- Create table for Patient Appointments

CREATE TABLE Patient_Appointments ( appointment_number INT, patient_number INT, staff_number INT, date_of_appointment DATE, time_of_appointment TIME, examination_room VARCHAR(255), FOREIGN KEY (patient_number) REFERENCES Patients(patient_number), FOREIGN KEY (staff_number) REFERENCES Staff(staff_number) );

-- Create table for Outpatients

CREATE TABLE Outpatients ( patient_number INT, name VARCHAR(255), address VARCHAR(255), telephone_number INT, date_of_birth DATE, gender VARCHAR(255), date_of_appointment DATE, time_of_appointment TIME, FOREIGN KEY (patient_number) REFERENCES Patients(patient_number) );

From these SQL codes

4. In Azure portal, we need to connect the database and write/run SQL scripts to create tables with appropriate data types 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.

5. In our report we need to provide the list of indexes and justification for creating them.

6. we need to write and run queries to insert at least 5 rows of data (random data) into each table. Insert our personal details in the staff table.

7. we 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.

8. we produce a report (view) listing the details of patients referred to the outpatient clinic

9. we produce a report (view) listing the details of patients currently located in ward 11.

10. we need to 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.

11. we need to 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)

12. we need to 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).