SFWR ENG 3DB3代写、代做SQL、代写SQL语言程序、代做createTables

日期：2018-11-07 09:50

McMaster University

SFWR ENG 3DB3 Fall 2018

Assignment 2

Due: November 5, 2018 at 10:00am

October 23, 2018

For this assignment, you will continue to work with the Ministry of Health (MoH), and perform

data analytics over patient data. On Avenue, under Assignments, you will find:

a) the DDL for CREATE TABLE statements, createTables.ddl (to create the necessary

tables), and

b) the INSERT statements loadData.ddl (to load data into the tables).

These files correspond to a simplified schema shown in the E-R diagram asg2ER.pdf. Please

run these two scripts on your database (i.e., remember to update the CONNECT TO statement with

your database name). You will use this schema for the questions below.

I. SQL (80 marks)

Write and provide SQL statements for the following queries. Execute each of your SQL queries

against your MoH database, and give the result of each query.

q1) [3 marks] Identify all hospitals (name, city) with an annual budget greater than $3 million.

Order the results in descending order of annual budget.

q2) [4 marks] Identify all patients (their first name, last name, gender, date of birth) who are 40

years of age or less, are from the city of Toronto, and have been diagnosed with some type

of Cancer. Do not include duplicates. (Hint: the DATE and LIKE function may be used, if

needed.)

q3) a) [4 marks] Find the average salary for all physicians in each medical specialty.

b) [4 marks] [4 marks] Revise your query in part (a) to report the average salary for only

those physi- cians working in Toronto or Hamilton. Report the average salary only for the

medical specialties with at least 5 physicians in the group.

c) [4 marks] Report the average salary for nurses according to their years of service. Display

your results such that the most experienced nurses are listed first.

1

q4) [5 marks] How many patients were admitted to each hospital during August 5 to August 10,

2017 Show your results per hospital.

q5) (a) [4 marks] Which departments (return the department name) exist in all hospitals? (For

example, we might expect that every hospital has a Cardiology department.)

(b) [4 marks] Which department and hospital has the largest number of staff (physicians and

nurses) working in that department?

c) [4 marks] Which department(s) are unique among all hospitals? (For example, the paediatric

oncology department exists only in hospital A. Note that a department is unique if its

count is = 1)

q6) a) [4 marks] Identify all nurses (their first name, last name) who cared for at most 3 patients

(i.e., 0, 1 or 2 patients). Show your results in alphabetical order by last name.

b) [4 marks]Identify all patients with a poor prognosis and was cared for by a nurse in part

(a).

q7) [5 marks] On which date did the Hamilton General Hospital experience the largest number of

patient admissions?

q8) [6 marks] Each time a drug is prescribed in a prescription, and the patient fills the prescription,

a sale is generated for the drug according to its cost price. Find the drug (drugcode, name) with

the largest sales revenue. Also report the total sales amount for this drug. (Note: simply adding

the drug costs, ignoring the dosage, is sufficient).

q9) [6 marks] Find all patients (ID, first name, last name, gender) that have been diagnosed with

Diabetes but have not yet taken a Red Blood Cell nor a Lymphocytes medical test.

q10) (a) [4 marks][4 marks] For each physician in the Intensive Care Unit (department) at McMaster

Uni- versity Medical Centre, return the disease and prognosis of each of their patients.

Do not return duplicates.

(b) [4 marks] [4 marks] For each of the patients in part (a), report the patient ID and the

total cost the patient has spent in medical tests (regardless of the physician treating them).

Display your results in decreasing order of total cost.

(c) [4 marks] [4 marks] For each of the patients in part (a), report the patient ID and the

total cost the patient has spent on medical drugs via prescriptions. Display your results in

decreasing order of total cost.

q11) [7 marks] Identify those patients (ID, first name, last name) that have been admitted to exactly

two hospitals with an urgent or standard admissions category.

II. Relational Algebra (6X6=36 marks)

For the SQL queries 1 to 4 and 10, 11 in Part I, give the corresponding relational algebra expressions.

2

III. Indexes (14 marks)

For the workload given in Part I, several queries have been showing poor performance (i.e., increasing

response times). Your task is to improve the performance of this workload as much as possible by

recommending four indexes that should be defined on the tables. What four indexes would you

recommend? For each index, state:

The attribute(s) the index is defined on.

Properties of the index (e.g., type of index, clustered/unclustered, etc.)

Which queries (q1 - q11) you think this index will help, and why.

Grading

This assignment is worth 12% towards your final grade.

Submission

All files are to be submitted using the Avenue system. Please ensure you submit all files with the

correct names and your student ID prefixed to each file name in the drop box created for this assignment

and your tutorial. For example, if your student ID is 1234567, the file name for Part III will

be 1234567-index.pdf. Upload four files with the indicated file extensions (no compression based

.tar, .zip, .rar files).

For Part I: Submit your SQL statements and the result for each query in two files. Submit your

SQL statements in a script file called queries.sql, and the corresponding query results in a

file called queries.results. Ensure your SQL statements are syntactically correct and that

they are executable on the DB2 servers. Non-executable queries will not be marked. Clearly

label, with comments, which query the result tuples correspond to in queries.results.

For Part II: Submit your relational algebra expressions in a file named ra.pdf.

For Part III: Submit your index recommendations in a file named index.pdf