Demonstrate an advanced knowledge of stored procedures

Assignment Help PL-SQL Programming

Reference no: EM132385486

Assignment 2

AIMS AND OBJECTIVES:
• to perform queries on a relational database system using SQL;
• to demonstrate an advanced knowledge of stored procedures, stored functions and triggers.
Implement the following tasks using ORACLE SQL*Plus.

Task 1

Download the file NATURALTHERAPY.sql from the LMS site and run it on ORACLE SQL*Plus. This file contains all the CREATE and INSERT statements you will need for this assignment.

NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.

The list of tables available for this assignment is the following:

MetabolicCategory (categoryNo, name)
FamilyDoc (GPNo, name, contactNo )
MedicalPractitioner (PractionerNo, name, contactNo, speciality)
Naturopath (NaturopathNo, name, contactNo, officeStartTime, officeEndTime, emailAddress)
RelevantCondition(conditionId, name) MedicalCondition(conditionId, name) Habits(conditionId, name)
Patient(patientId, name, gender, DoB, address, state,postcode, homePhone, businessPhone, maritalStatus, occupation, duration,unit, race, registrationDate , GPNo, NaturopathNo)
MetabolicFunction (functionNo, name, categoryNo)
Visit (visitNo, visitDate, time, amountPaid, receiptNo, service, medication, chiefComplaints, patientId)
PatientMetabolic (functionNo, patientId, score) PatientPractitioner (patientId, practionerNo) PatientRelCon (patientId, conditionId, notes)
PatientHabit (patientId, conditionId, year, average, unit, notes)
PatientMedCon (patientId, conditionId, notes)
RelevantConDetail (patientid, conditionId, specify)

NOTE: PK is printed bold and underlined, FK is printed italic, PK that is also FK is printed bold, italic, and underlined.
Using the tables provided above, provide SQL statements for the following queries.

(a) Display the number of patients referred to by each medical practitioner in the database.

(b) Find out which occupation has the greatest number of patients with high blood pressure.

(d) Display the details of the patient (i.e. Name, Gender, Address, Postcode, DOB) who smokes and has the highest (most severe) total of metabolic functions.

(e) Display the details of patients (i.e. Name, Gender, Address, Postcode, DOB) who haven't visited the naturopath in the last 6 months and who are suffering from a severe heart problem (the total score > 9 for Heart in metabolic screening questionnaire).

(f) Display the details of patients (i.e. Name, Gender, Address, Postcode, DOB) who smoke, have diabetes and high blood pressure. List these patients in an increasing order of their age.

(g) For each patient who has at least an allergy, display the patient's name together with his/her metabolic functions that have the total score >3.

(h) Display the details of all patients in the database (i.e. Name, Gender, Address, Postcode, DOB) together with the referring family doctor of that patient (if any).

(i) Display the average number of patients registered into the database each month.

(j) Display the most popular naturopath that has been visited by the patients.

Task 2

Provide the implementation of the following stored procedures and function. For submission, please include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality.

(a) Write a stored procedure that receives a date as input and displays as the output the list of patients who visited the naturopath on that day.

(b) Write a stored procedure that accepts as input a particular medical problem (from the past medical history), and as the output displays the number of patients suffering from that problem, grouped by the 3 age groups (children 0 - 12 years, teenagers over 12 - 20 years, and adult >20 years).

(c) Write a stored function that uses a patient's id as input and calculates the total amount paid to date by that patient. You also need to show an SQL statement to display the total amount paid by all of the patients in the database.

Task 3

Provide the implementation of the following triggers. For submission, please include both the PL/SQL code and an insert statement to demonstrate the trigger functionality.

(a) A trigger that is activated if the combination of a patient's name, DOB and living address (including address, state, postcode) already exists in the database.
(b) A trigger that is activated whenever a new medication given to a patient matches any one of the patient's allergy list.

Reference no: EM132385486

Questions Cloud

COIT12207 - Internet Applications Assignment Problem : COIT12207 - Internet Applications Assignment Help and Solution, Assessment Help - CQ University, Australia - Creating an online Temperature convertor

Quantitative Research Methods Assignment Problem : Quantitative Research Methods Assignment - Describe the complete sample as well as each experimental condition separately

GHL6017 People Resourcing and Development Assignment : GHL6017 People Resourcing and Development Assignment help and solution Gulf College, Assessment help, Case Study: Changes in HRD at Hansen Group

Summarize and interpret each composite distribution : Summarize and interpret each composite distribution by presenting a box plot (also known as a 5-point summary) for the variable

Demonstrate an advanced knowledge of stored procedures : Demonstrate an advanced knowledge of stored procedures, stored functions and triggers, Display the number of patients referred to by each medical practitioner

Write a report to management identifying any issues : Write a brief description on the Company, identifying the industry they were in, their turnover, how long they had been in business and history of organisation

ACCT5391C Implement an insolvency program Assignment : FNSACC611 - ACCT5391C Implement an insolvency program Assignment help and solution, RMIT University, Assessment help - Who obtained the Supreme Court order

HRM09103 Organisational Change Management Assignment : HRM09103 Organisational Change Management Assignment help and solution, Edinburgh Napier University, Assessment help - evaluate the use of motivation theories.

MGT602 Business Decision Analytics Assignment : MGT602 Business Decision Analytics Assignment help and solution, Decision Style Analysis Reflective report, Assessment help, Torrens University.

User Account

All Pages