Databases Assignment 2 – Sussex Informatics University UK.

Unit Title : Databases
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 a 2. sql which you can download from the submission point in Canvas. You should insert your code for each question after the corresponding 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.
Databases Assignment 2 – Sussex Informatics University UK.

Databases Assignment 2

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.

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 designed 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 set up.sql that defines the tables that your code needs to rely on 1. 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 additional 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 statement for each question with a semicolon. For question 11 terminate your function declaration with the provided termination symbol $$.

Databases Assignment 2 – Sussex Informatics University UK.

Databases Assignment 2

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

Your code must execute without error assuming that all other tables have been set up by running script hospital Set up.sql. The data types (domains) you choose for the columns should be the most appropriate 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 medical 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 constraint 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.

Databases Assignment 2 – Sussex Informatics University UK.

Databases Assignment 2


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

ORDER This Databases Assignment 2 NOW And Get Instant Discount

Order Your Assignment