Description

FIT2094 Databases
Assignment 2B – SQL – County General Hospital
Learning Outcomes: 2, 4, 5, 7 (see Unit Preview)
Assignment weighting 15%
Marked out of 100 returned as a grade out of 15

If a procedure is carried out by a technician the hospital does not record the details of the technician who completed the procedure.
County General Hospital only records the details of a procedure carried out on an admission after the procedure has been completed.
Not all admissions require a procedure to be carried out.
A data model has been developed for the County General Hospital, the logical model is shown below:

The tables for the County General Hospital have been created in the Monash Oracle server and are available from the user CGH who has given you select rights on the tables. You will use these tables to answer the SQL Queries listed below.
Your answers for these tasks must be placed in the supplied SQL Script cgh_queries.sql
******************************************************************************************************
In answering these queries you are ONLY permitted to use the SQL structures and syntax which have been covered within this unit ie. the material covered in SQL Basic (week 7 workshops & week 8 tutorial), SQL Intermediate (week 9 workshops & week 10 tutorial), SQL Advanced (week 10 workshops & week 11 tutorial) and the Oracle Common Functions document.
SQL syntax and commands outside of the covered work, as detailed above, will not be accepted/marked.
******************************************************************************************************
ANSI joins must be used where two or more tables are to be joined, under no circumstances can “implicit join notation” be used – see the week 7 workshop slides and the week 8 tutorial
When required to show output which involves the patient or doctor name as a full name, the name must not have any leading spaces (ie. not start with a space).
Where a full name is requested in any output it should be in the form first name last name eg. Abra Baltzar.

Queries to complete [100 marks]
1. List the doctor title, first name, last name and contact phone number for all doctors who specialise in the area of “ORTHOPEDIC SURGERY” (this is the specialisation description). Order the list by the doctors’ last name and within this, if two doctors have the same last name, order them by their respective first names.
[6 marks]
2. List the item code, item description, item stock and the cost centre title which provides these items for all items which have a stock greater than 50 items and include the word ‘disposable’ in their item description. Order the output by the item code.
[6 marks]
[9 marks]
4. List the procedure code, name, description, and standard cost where the procedure is less expensive than the average procedure standard cost. The output must show the most expensive procedure first. The procedure standard cost must be displayed with two decimal points and a leading $ symbol, for example as $120.54
[9 marks]
[9 marks]
6. List the admission number, patient id, first name, last name and the length of their stay in the hospital for all patients who have been discharged and who were in the hospital longer than the average stay for all discharged patients. The length of stay must be shown in the form 10 days 2.0 hrs where hours are rounded to one decimal digit. The output must be ordered by admission number.
[12 marks]
[15 marks]
8. For every procedure, list the items which have been used and the maximum number of those items used when the procedure was carried out on an admission. Your list must show the procedure code, procedure name, item code and item description and the maximum quantity of this item used for the given procedure.
43556 Vascular surgery AN002 Std Anaesthetic Pack 1
43556 Vascular surgery BI500 Bupivacaine Inj .5% 10ml Steriamp 2
If the procedure has not been carried out on any admission or has not used any items then the item code, item description and maximum quantity columns must show “—“. The output must be in procedure name order and within a procedure in item code order.
[16 marks]
9a – FIT2094 only.
For each procedure carried out in admissions, find the person/s who charged the maximum cost for that procedure (the person who charged the patient was the person who performed the procedure). Note that some procedures were carried out by the technicians.
As an example, say we have the following “dummy” data (note these are just representative numbers, they do not reflect the data you have access to):
proc_code adprc_patcost perform_dr_id
5000 550 1021
5000 524.50 1021
5000 575 1001
5000 530 —-
In this dummy data, the procedure 5000 has been carried out four times. The maximum cost of procedure 5000 was $575 and it was carried out by doctor 1001.
Your report must show the procedure code, the procedure name, performing doctor’s id, performing doctor’s full name (including title) as a single column called ‘Doctor Name’ and the maximum cost for the procedure. The maximum procedure cost must be displayed with two decimal points and a leading $ symbol, for example as $575.00. If the procedure was carried out by a technician, then the perform doctor id column must show “—-” and the ‘Doctor Name’ column must show “Technician”.
The output should be in procedure code order, and within a procedure in doctor id order.
[18 marks]
— End of Queries —-
SUBMISSION REQUIREMENTS
For this assignment there is only one file you are required to submit:
● cgh_queries.sql
Do not zip this file into a zip archive, submit the SQL script as it is. The SQL script must also have been pushed to the FIT GitLab server with an appropriate history as you developed your solutions (a minimum of four pushes). Please ensure your commit comments are meaningful.
Late submission will incur penalties at the rate of -5 mark for every 12 hours the submission is late.
Please note we cannot mark any work on the GitLab Server, you need to ensure that you submit correctly via Moodle since it is only in this process that you complete the required student declaration without which work cannot be assessed.
It is your responsibility to ENSURE that the file you submit is the correct file – we strongly recommend after uploading a submission, and prior to actually submitting, that you download the submission and double-check its contents.
Your assignment MUST show a status of “Submitted for grading” before it will be marked.

Please carefully read the documentation under the “Assignment Submission” on the Moodle Assessments page which covers things such as extensions and resubmission.
CRITERIA FOR MARKING
Submissions will be graded on:
● the correct application of SQL statements and constructs to: ○ retrieve the required data in the required format, and
○ where a layout or column heading has been specified, appropriately reflect these requirements.
Submissions will be grade penalised if they:
● contain SET ECHO … or SPOOL commands,
● the SQL code is not formatted consistently
● do not have a semicolon (;) closing the query for every query submitted,
● do not make use of column aliases when you use arithmetic calculation, concatenation, functions, or other output manipulation unless specified otherwise in the above,
● do not use to_char/to_date where appropriate in handling dates,
● use subqueries and SQL conditions unnecessarily (although you are not required to consider efficiency of your solution you should try an ensure that you use the minimum number of subqueries and SQL conditions when arriving at your answer),
● do not have an appropriate development history on the FIT GitLab server for the source file (at least four pushes required),
● use PL/SQL or Views,
● do not restrict the SQL structures and syntax used to those which have been covered within this unit ie. in SQL Basic (week 7 workshops & week 8 tutorial), SQL Intermediate (week 9 workshops & week 10 tutorial) and SQL Advanced (week 10 workshops & week 11 tutorial). Such material will NOT be marked.