University of Sussex Informatics
Databases
Assignment 2 (Deadline 1.12.16, 4pm)
Autumn 2016
This coursework must be submitted electronically via E-submission. You have to answer all 12 questions. Your answers must be uploaded in one file using the template file published on the StudyDirect page. This tem- plate contains essential comments to steer the automatic testing rig. Please follow the instructions carefully and do not remove any line from that file. Don’t write your name anywhere in the file but include your candidate num- berinthefirstlineasacomment(e.g.– 30455).
YOU MUST WORK ON THE ASSIGNMENT ON YOUR OWN! The standard rules for collusion and plagiarism apply and any cases discovered will be reported and investigated.
Detailed Instructions (follow carefully)
This assignment refers to an implementation of the hospital database as de- signed in the the first assignment (see StudyDirect). To be able to answer the questions of this second assignment you must first run the SQL script hospitalSetup.sql that defines the tables that your code needs to rely on1. It is available from our StudyDirect site. For the completion of this assignment it may be necessary to inspect the code in this script and under- stand how it implements the requirements outlined in the first assignment. Do not modify the structure of the tables in the given script when you write your answers. Note that only few data records have been inserted into the tables. It is recommended that you test your code with additional sample data you insert into the tables yourself. However, do not include any of the test data or the corresponding insert statements in your submission. Also, you must not include the code of hospitalSetup.sql in your answer.
In the following, SQL always refers to the MySQL 5.1 dialect and all your code must run on our ITS server where it will be tested for marking pur- poses. Use comments where appropriate which can be included e.g. like this:
-- this is a one-line comment.
1Note that the implementation is a translation of the E/R model given in the model answers of Assignment 1, with a few minor deviations regarding the sub-entity types.
1
You will automatically receive 0 marks for any answer that contains a syn- tax error2, so please test your code carefully before you submit. If you are unable to answer a question write a comment or just skip the question. For- mat your code so that it is readable (this means in particular avoid putting long queries on one line).
Important: For questions 1-11 terminate your SQL statement for each ques- tion with a semicolon. For question 12 terminate your function declaration with the provided termination symbol $$.
1. Write a single SQL statement to set up a table according to the fol- lowing (relation) schema:
Hospital MedicalRecord(recNo, patient, doctor, enteredOn, diagnosis, treatment) PRIMARY KEY(recNo,patient)
FOREIGN KEY patient REFERENCES Hospital Patient(NINumber) FOREIGN KEY doctor REFERENCES Hospital Doctor(NINumber)
diagnosis NOT NULL enteredOn NOT NULL
Your code must execute without error assuming that all other tables have been set up by running script hospitalSetup.sql. The data types (domains) you choose for the columns should be most appropri- ate for the data they will contain. You must also accommodate the following requirements:
- (a) For table and column names you must pick the names used in the schema above (otherwise you will lose marks as tests will fail).
- (b) There are never more than 65,535 medical records for a single patient. The numbering of those records only uses positive inte- gers.
- (c) The enteredOn column records date and time of when the med- ical record has been entered. It should have as default value the current date and time!
- (d) The diagnosis column can contain some long text but never more than 224 bytes.
- (e) The treatment column contains text but is never more than one thousand characters long.
2This will be different in exams where you do not have an SQL tool to test your queries. 2
- (f) Equip the FOREIGN KEY constraints, and only those, with con- straint names FK patient and FK doctor, respectively.
- (g) When a patient is deleted from the database, all their medical records shall be automatically deleted too.
- (h) On the other hand, it should be possible to delete doctors who provided medical records without automatically deleting their medical records.
- (i) Changing the NI number of a patient or doctor should not be permitted if they have or have produced, respectively, a medical record.
[16 marks]
Instructions for Queries
For Questions 2–11 specified below, write one single SQL query, respectively, that solves the task. You can use nested queries (also known as subselects or inner queries including subqueries) wherever you like. Note, however, that unnecessarily contrived answers might attract (mild) penalties, even if they are correct.
Important: For automated testing to work it is essential that you choose column headings according to the exact instructions of the question and that you list columns in the exact order mentioned in the question. Where no explicit headers are mentioned, use the orig- inal column name. Non-compliance might result in a 0 mark for the question.
Note that your queries must work correctly with any data (according to the schema) in the tables, not just the few records provided. All references to time, when not explicit, are relative and ‘today’ always refers to the time of running the query.
- Add to the table Hospital MedicalRecord, defined in Question 1, a column duration that stores the duration of the consultation that led to the record being taken. The duration is expressed in hours, minutes and (possibly) seconds. Choose an appropriate type for this column. You must not create a new table here. [5 marks]
- Decrease the salaries of all doctors with any expertise in ear related matters by 10 percent. This means that the new salary must be 9 of
the old salary.
10
3
[6 marks]
- List all the patients who live in a city which contains the string right (all lowercase!). List their first name, last name (with original column headings) and the (four digit) year of their birth with column heading born. Sort the result table alphabetically by last name. Rows with equal last name should be ordered alphabetically by first name.
[7 marks]
- For all patients who have not had their 30th birthday yet list their na-
tional insurance number, first name, last name (all with original col-
umn headings) and their body mass index rounded up to three digits
after the decimal point with heading BMI. The body mass index of
a person equals weightInKg . Please note that on our database the heightInMetres 2
height is recorded in centimetres and the weight in kilograms.
[8 marks]
- Compute how many doctors the hospital has. The heading of the sin- gle column in the result table must be number. [4 marks]
- For each doctor list how many operations they have carried out this calendar year. The result table should contain three columns, the na- tional insurance number of the doctor, the last name (both with origi- nal headings) and the number of operations with heading operations. Sort the result by the number of operations with the highest number appearing on top. [8 marks]
- List all doctors who are not mentored by anybody, but are mentoring someone themselves. The result table should have three columns: the doctors’ national insurance number (with original column heading), the (uppercase!) initial of their first name (one letter) with column heading init, and their last name (with original column heading).
[8 marks]
- In the table Hospital Operations the primary key guarantees that no two operations in the same theatre start at the same time. However, this does not automatically guarantee that two operation do not overlap. Therefore, list all pairs of operations that overlap. The resulting table must have three columns: theatre number and start date&time for the first operation with headings theatre and start- Time1, respectively, and the start time (only time, no date!) for the second (overlapping) operation in the same theatre with heading start-
4
Time2. Note that startTime1 must be before startTime2 to avoid du- plicate listing of the same overlap pair. So, for instance, if your result table contains a row:
2 2016-12-02 9:00 11:11
then the result table must not include the (symmetric) row:
2 2016-12-02 11:11 9:00.
Hint: You may want to check out the single row functions for date and time in the MySQL manual. [8 marks]
- For each operating theatre used for at least one operation, find out which day(s) had the most operations in it. The result table should have five columns: the theatre number (with original column head- ing), the day of the month (as number) with heading day, the (En- glish) name of the month (as string with first character capitalized) with heading month, the 4-digit year with heading year and the num- ber of operations in the theatre on that day with heading numOps. So the result table may contain a row:
34 3 October 2016 6
if operating theatre 34 had 6 operations on the 3rd of October 2016 and never more than 6 on any other day. Note that a theatre can appear several times in this result table if there were several days that have been equally maximally busy. Sort the result table by theatre number (smallest first) and for equal theatre numbers chronologically by date (earliest first). [9 marks]
- List those operating theatres that have seen more operations this May (meaning the month of May of this year) compared to last May (mean- ing the month of May of the previous year). The result table should have four columns: the operating theatre’s number with original col- umn name, its number of operations last year with heading lastMay, the number of operations this year with heading thisMay, and the cor- responding increase with heading increase. The result table may, for instance, contain rows that look like this:
4 40 43 3
if operating theatre 4 had 40 operations last May, 43 operations this May and thus an increase of 3. Sort the result table by the increase in operations with the highest increase on top. [9 marks]
5
Last question on next page!
Instructions for Stored Procedures
For the following creation of stored procedures/functions the delim- iter has to change. This has been done for you already in the template, so use delimiter $$ specified there. Don’t forget to test your function. Even if the declaration is successful the function might still throw an error during execution. Any function that throws an SQL runtime error may receive 0 marks. Do not include any test code in the sub- mission though, just the function declaration.
12. Write a stored function usage theatre that, given a theatre num- ber and a year (specified by a positive four digit integer number), com- putes the total time the given theatre has been occupied for (due to operations) in the specified year. The total time computed should be returned as a string. You must accordingly choose an appropriate re- sult type for your procedure. The result string must look like this: 125days 9hrs 32mins where the concrete numbers of course depend on the data.
There are a number of ‘unwanted’ cases that you need to deal with. If the given year is in the future the result string must be: The year is in the future(noperiodattheend).Ifitisnotinthefuture but the operating theatre, say 42, does not exist the result string must be: There is no operating theatre 42. If, however, the year is not in the future and the operating theatre exists, but there are no operations in the year specified, then the result string must be: Operating theatre 42 had no operations in 1066 (in case the theatre was 42 and the year was 1066). Please make sure you use the exact spelling and spacing of the result strings as given in
the examples above.
[12 marks]
6