Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit


 

 

Autumn 2022 

Informatics

Databases

Assignment 2

 

This assessed coursework should be submitted online as Canvas E-submission. The due date of this coursework is 4pm on Thursday, 16th of December   2021.

This coursework has two parts, and your submission should consist of two separate files, one for your answers to the questions in Part 1 and one for your answers to the questions in Part 2.

1. Your solutions to the questions in Part 1 should be inserted into the file a2.sql which you can download from the submission point in Canvas. You should insert your code for each question after the cor- responding question comment.

2. Your solutions to the questions in Part 2 should be submitted as the pdf file a2.pdf. You must submit a pdf file.

All questions in both Parts 1 and 2 should be answered. There is a total of 100 marks available with 75 marks in Part 1 and 25 in Part 2.

 

*** IMPORTANT ***

This document contains the questions for Part 1 only.

The questions for Part 2 will be distributed at the end of week 10.

You must work on this assignment on your own.  The standard Informatics rules for collusion, plagiarism and lateness apply. Any cases of potential misconduct discovered will be reported and investigated.


 

Part 1

Answer all questions in Part 1.

Detailed Instructions (follow carefully)

This assignment refers to an implementation of the hospital database as de- signed in the the first assignment (see Canvas).

To be able to answer the questions of this second assignment you must first run the SQL script hospital setup.sql that defines the tables that your code needs to rely on1 . It is available from our Canvas site.

For the completion of this assignment it may be necessary to inspect the code in this script and understand how it implements the requirements out- lined in the first assignment. Do not modify the structure of the tables in the given script when you write your answers.

 

Note that only a small number of data records have been inserted into the tables.  In order to adequately test your code you will need to insert ad- ditional sample data.  However, do not include any of the test data or the corresponding insert statements in your submission.

Do not include the code of hospital setup.sql in your submission.

Format your code so that it is readable (this means in particular avoid putting long queries on one line).

Important: For questions 1-10 terminate your SQL statementfor each ques- tion with a semicolon. For question 11 terminate yourfunction declaration with the provided termination symbol ss.

 

1. Write a single SQL statement to set up a table according to the fol- lowing (relation) schema:

medical record(rec no, patient, doctor, entered on, diagnosis, treatment) PRIMARY KEY(rec no,patient)

FOREIGN KEY patient REFERENCES patient(ni number)

FOREIGN KEY doctor REFERENCES doctor(ni number)

diagnosis NOT NULL

entered on NOT NULL

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.


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 the most ap- propriate for the data they will contain. You must also accommodate the following requirements:

(a) There are never more than 65,535 medical records for a patient. The numbering of those records only uses positive integers.

(b) The entered on 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!

(c) The diagnosis column can contain some long text, but never more than 224 bytes.

(d) The treatment column contains text, but is never more than one thousand characters long.

(e) Equip the FOREIGN KEY constraints, and only those, with con- straint names FK patient and FK doctor, respectively.

(f) When a patient is deleted from the database, all their medical records shall be automatically deleted too.

(g) On the other hand, it should be possible to delete doctors who provided medical records without automatically deleting their medical records.

(h) Changing the NI number of a patient or doctor should not be permitted if they have or have produced, respectively, a medical record.

[10 marks]

Instructions for Queries

For Questions 2–10 specified below, write one single SQL query that solves the task. You can use nested queries (also known as subselects or inner queries including subqueries) wherever you like. Note, how- ever, that unnecessarily contrived answers might attract (mild) penal- ties, even if they are correct.

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.

 

2. Add a column duration to the table medical record that stores the du- ration of the consultation that led to the record being taken. The du- ration is expressed in hours, minutes and (possibly) seconds. Choose an appropriate type for this column. Do not create a new table.

[4 marks]

3. Decrease the salaries of all doctors with any expertise in ear related matters by 10 percent. This means that the new salary must be  of the old salary.                                                                    [5 marks]

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

[6 marks]

5. 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  . Please note that on our database the height is recorded in centimetres and the weight in kilograms.

[7 marks]

6. Compute how many doctors the hospital has. The heading of the sin- gle column in the result table must be number.                    [3 marks]

7. For each doctor list how many operations they have carried out this calendar year.   The result table should contain three columns, the doctor’s national insurance number, 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.                                                               [7 marks]

8. 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).

[7 marks]

 

9. In the table 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 time 1, respectively, and the start time (only time, no date!) for the second (overlapping) operation in the same theatre with heading start time 2.  Note that start time 1 must be before start time 2 to avoid duplicate 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 rowfunctionsfor date and time in the MySQL manual.                                                     [8 marks]

10. 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 dom, 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 num ops. 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).

Hint:  You may need to include each of the different elements of the date appearing in the columns of the results table (i.e. day, month and year) in the relevant GROUP BY clause.                                [8 marks]

Instructions for Stored Procedures

For the following creation of stored procedures/functions the delimiter 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. Do not include any test code in the submission, just the function declaration.

11. 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 (no period at the end). If it is not in the future 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.

[10 marks]

 

Part 2

The questions for part 2 will be made available at the end of week 10.