Reference no: EM133036586

CN5000 Database Systems - University of East London

Assignment- design, develop and document a prototype Database system

‘NHS Health Service' Database Design and Implementation

In the year 2019 a Coronavirus disease 2019 (COVID-19) changed the world, also known as COVID and the coronavirus, it is a contagious disease caused by severe acute respiratory syndrome coronavirus 2 (SARS-CoV-2). The first known case was identified in Wuhan, China, in December 2019. The disease has since spread worldwide, leading to an ongoing pandemic. As a result, this had a great impact on the entire world and immediate measures had to be taken to address the situation where immunization became the key priority to control the pandemic. Many countries became part of the vaccination programme that led to the establishment of many health practices to administer the growing demand. In order to achieve this, they require a robust Database Management System to manage all the data relating to the immunization records. You are approached by ‘NHS Health Service' to develop a database system to manage their records.

‘NHS Health Service' is a private medical practice. During the Covid-19 pandemic, they are planning to open a Covid-19 vaccine service centre in the Beckton area. As a group of technical consultants, you are required to produce a database system to support the Covid-19 vaccine service. The practice will maintain a stock of the Covid-19 vaccines currently approved for use in the UK such as Oxford/AstraZeneca, Pfizer/BioNTech, Moderna and Janssen. The Covid-19 vaccines will be acquired from four suppliers. When vaccines are acquired in bulk (typically more than 500 dosages), they are eligible for a 15% discount. The database will store the Patients details such as name, date of birth, age, gender, address, phone, email, appointment date/time, vaccinated (yes/no), vaccine name, batch number, dose number (1st/2nd) etc. You are required to develop integrity and referential integrity constraints that will ensure the correctness of the data when it is entered. One for the fully vaccinated individuals the vaccine status will change to vaccinated else will remain unvaccinated who have neither taken a any dose nor booked a vaccine slot. The ones who have either made a booking or at least taken one dose will show the status as pending.

Integrity Constraints

1. The patient's gender should be recorded as ‘M' for males or ‘F' for females.
2. The patient's name should not contain any numeric characters.
3. The patient's email address should have @ symbol present to be valid.
4. When scheduling an appointment, it should not be possible to schedule it in the past.
5. When updating vaccine stock, it should not be possible to have negative stock to ensure that accurate stock levels are maintained.
6. Appointments cannot be made if the vaccine is out of stock.
7. The patient's ID should be generated automatically using a sequence and a trigger.

SQL Queries

1. Display all the patient's details and order them by patient name.
2. Filter and display the patient's names by gender.
3. Produce a list of total vaccines ordered and the price from each supplier.
4. Produce a list of all the upcoming appointments.
5. Find the patients details who have already received the first dose of the Covid-19 vaccine and those with pending status
6. Find the patients details who have received both doses of the Covid-19 vaccine in past 6 months
7. Find the patients who have and have not made Covid-19 vaccine appointments.

Trigger
Create a trigger that places the patients who have received two dosages of Covid-19 vaccines into a fully vaccinated patients table. This allows records to be maintained while improving query times for patients waiting for vaccines.

Attachment:- Database Systems.rar