assignment作业 | lab代写 | sql | database | 代做oracle – CSE 4 DBF 201 9 Semester 2

CSE 4 DBF 201 9 Semester 2

assignment作业 | lab代写 | sql | database | 代做oracle – 这是一个关于sql的题目, 主要考察了关于sql的内容,是一个比较经典的题目, 是有一定代表意义的sql/database/oracle等代写方向, 该题目是值得借鉴的lab代写的题目

lab代写 代写lab

assignment 2 ( 20 %)

Due date: 10 am Wednesday, October 23 rd 2019


to perform queries on a relational database system using SQL; to demonstrate an advanced knowledge of stored procedures, stored functions and triggers.

This is an individual Assignment. You are not permitted to work as a group when writing this assignment.

Copying, Plagiarism: Plagiarism is the submission of somebody elses work in a manner that gives the impression that the work is your own. The Department of Computer Science and Information Technology treats plagiarism very seriously. When it is detected, penalties are strictly imposed. Students are referred to the Department of Computer Science and Information Technologys Handbook and policy documents with regard to plagiarism and assignment return, and also to the section of Academic Integrity on the subject learning guide.

No extensions will be given: Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances that prevent the assignment being submitted on time, an application for special consideration may be made. See Student Handbook for details. Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime.


Task 1 should be saved to a file named task1.txt using the SPOOL command. Task 2 should be saved to a file named task2.txt using the SPOOL command. Task 3 should be saved to a file named task3.txt using the SPOOL command.

Note an example of using the SPOOL command is given in the lab book. In the SPOOL file, you need to provide the query/procedure/function/trigger execution and the sample output. For the trigger, you need to show a sample test that demonstrates the successful execution of the trigger.

All the tasks above are to be submitted in soft-copy format using the submission link provided on LMS by 10.00am Wednesday, October 23rd, 2019.


The relevant sql queries for the Natural Therapy Centre Database System; The required stored procedures, stored function, and triggers.

Implement the following tasks using oracle SQL*Plus.

Task 1 [50 marks]

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.


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,


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.

(c) Display the area postcodes that have at least two patients under the age of 84 years old

suffering from asthma.

(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 havent

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

[5 marks each  50%]

Task 2 [30 marks]

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 >

(c) Write a stored function that uses a patients 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.
[10 marks each  30%]

Task 3 [20 marks]

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 patients 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 patients allergy list.
[10 marks each  20%]