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
Income inequality in the us economy
: Given the change in the standard deviation of Per Capita Income over these two years and the percentiles of per capita income for Per Capita Income 1, what do y
|
What amount of interest expense for the bonds did ABC report
: What amount of interest expense for the bonds did ABC report on its income statement in 2019 for each bond separately and in total
|
How much income after taxes should the company report
: The company's effective tax rate is 30%. How much income after taxes should the company report for the month of April, based on accrual accounting
|
Complete necessary journal entry for the interest payment
: Complete the necessary journal entry for the interest payment date of June 30, 2019 by selecting the account names from the drop-down menus
|
CN5000 Database Systems Assignment
: CN5000 Database Systems Assignment Help and Solution, University of East London - Assessment Writing Service - Database Design and Implementation
|
What would be the ending balance on the statement of changes
: She used the following totals to help in her calculations: Net income = $30,000. What would be the ending balance on the Statement of Changes of Equity
|
What lessons from women struggles for equality
: What lessons from women's struggles for equality in the past can help inform current and future women's rights issues?
|
What are the generic strategies available to firms
: What are the generic strategies available to firms? List and describe how firms compete using these strategies, along with an example of a firm that is using ea
|
Explain gross earnings for a month
: Tam sells mutual funds on a graduated commission structure. He receives 3.3% on the first $50,000 of sales in a month , 4.4% on the next $50,000 , and 5.5% on a
|