Building a Coronavirus Pandemic Database for the Central Hospital, England

1. Construct properly designed databases to support a range of applications in an efficient manner
2. Evaluate a range of techniques used to safeguard data and maintain highly available, secure systems
3. Analyse how large datasets can be stored and manipulated

In this Task you will build a database to support the needs of The Central Hospital, England who are working with local doctors’ surgeries to keep accurate data about the Coronavirus pandemic.  The National Health Service (NHS) want to improve the digitization of the data files and be able to run queries easily and efficiently and report statistics to the UK government.

A doctor will see a patient, the consultation is recorded [for the purpose of this assessment scenario any other illness unrelated to the virus infections are excluded from the scope of the task].  Patients are tested for the virus in the case of a positive result they will be either sent to hospital, advised to quarantine and/or asked to repeat the test in 7 days time.  

The doctor will note symptoms, indicate higher risk patients, the diagnosis, the medication, personal patient details, testing details.  Typically, symptoms could include: a new continuous cough, a high temperature, a loss of or change to sense of smell or taste.  You may assume other symptoms.  You may assume medication types, and could be commonly used medicine: paracetamol, cough linctus, high dose asprin, naproxen, etoricoxib, diclofenac, indomethacin.

The hospital admits patients referred by family doctor surgery.  The hospital doctors pick up the data from the surgeries, starting with referring surgery details, diagnosis, above patient information, PPE guidance for staff.  Hospital staff will give an evaluation of the PPE, evaluation is a number from 1 to 5.  The Hospital record the evaluations from all staff.

The research and development (R&D) department at the hospital will use the computer database to monitor the infections, collate statistics and analyse the data.  They may also run reports for the government.

Produce a design for this system. This should include an ER diagram and a set of tables with suitably defined columns and constraints to support this scenario. State any assumptions made.

20 marks

1. Create a database using Oracle SQL.  Populate the tables with appropriate test data, bearing in mind the following:

1. There may be some patients that have yet to be tested for Coronavirus.
2. There may be some patients who have yet to see a doctor.
3. All patients are uniquely identified by a primary key column
4. Records of patients seeing a doctor (known as a surgery consultation) are identified by a combination of foreign keys (not by a single column surrogate key)
5. There may be some patients that are yet to be given medication.

2. Develop a set of queries as follows:

1. Find the surgery that referred the most number of patients, include their location;
2. Write and test a query to list the patient name, gender, ethnicity and age of each patient who is in quarantine. Add a column that shows the average age of the patient;  
3. Find the location of the surgery that have an fewer high risk patients than the average number of high risk patients referred by all surgeries.

Devise 3 further queries that test your design. Think about what sort of queries would be generated by doctors and R&D staff for the above scenario.

Credit will be given to queries that are not trivial, for example, “SELECT * FROM Tablename” is unlikely to gain more than one mark.

3. The live system will quickly contain a large amount of data. From your design, pick one table that you think will be queried regularly to generate statistical data (e.g., counts or averages) and would contain a large amount of data in a real system.

1. Create a Materialised View that demonstrates such as query. Comment on your choice of options to create the view.
2. Devise some tests to compare the performance of the view, compared against running a SQL query that produces the same results. Include all queries carried out and comment on the results.

7 Marks

4. Create triggers that enforce the following business rules:

a. Ensure that quarantine of less than seven days duration cannot be allowed

b. If a hospital staff gives a poor evaluation of 2 or less , the details of their job role (department name, staff name, job title and date of the evaluation, supervisor or manager name and evaluation) must be placed in an audit table.