Database Design and Implementation

Original Specification

The current state of Swine Flu in UK is alarming and the government urgently needs a database system to record all confirmed cases in England, Wales and Scotland. This database will be used by GPs, Councils and Government for a number of different purposes. Your task is to investigate and define requirements based on the following user requirements.

Additional Requirements

Additional Background Information

The first illness caused by a new Influenza A virus was confirmed in the UK on the 27 April 2009. Since then the virus has become much more common in this country and globally with the World Health Organisation declaring a pandemic on 11 June 2009. This is the first influenza pandemic since the 1968-1970 outbreak. A Swine Flu pandemic will have considerable impact across communities, organisations and service providers.

Swine flu vaccination has started, with the first doses being offered to people with the greatest risk of becoming seriously ill from the virus. New cases of swine flu in the week to October 22 were estimated at 53,000, up from 27,000 the week before.

The latest figures for England, released by the Chief Medical Officer, show that hospitalisation rates have also risen, with 506 people needing treatment. Of those, 99 have needed critical care, the greatest number so far.

The UK also saw a further 16 swine flu deaths, bringing the total to 122 since the virus first appeared; 93 in England, 15 in Scotland, eight in Northern Ireland and six in Wales.

The figures came as manufacturers delivered the first batches of swine flu vaccine, allowing the vaccination programme to begin.


Conceptual Design Stage

Entity Relationship Diagram

conceptual model

Attributes and Identifiers

Patient(PatientID, Forename, Surname, DOB, House_Number, Street, Town, County, Country, Post_Code, Ethinc_Identity, Date_of_Death )

GP(GP_Number, Forename, Surname, Practice_Number)

Vaccination(VaccinationID, Date )

Batch(BatchID, Manufacturer, Manufacturer_Batch_Number, Date_of_Production, Date_Recieved)

Case(CaseID, Date_Added )

Confirmed Case(CaseID, Date_Added, Date_Confirmed )

Overseas Destination(OverseasID, Country_Name, Date_Swine_Flu_Confirmed, Swine_Flu_Deaths)

Illness(IllnessID, Name)

Definition of Entity Types

Patient is a person who has made contact with their GP or the Swine Flu Hotline or has received a Swine Flu vaccine.

GP is a doctor who cares for patients, administers vaccines, and declares a case of swine flu confirmed or suspected.

Vaccination is given to a patient, administered by a GP and comes from a specific batch.

Batch is a collection of vaccinations developed by manufacturers around the world.

Case is a condition that is either confirmed by a GP or considered suspect and affects a patient.

Confirmed Case is confirmed by a GP

Overseas Destination one or more countries visited by the patient in the last 3 months

Illness is an underlying medical condition a patient is suffering from

Attribute Definitions


Attribute Name Data Type Required Example Data
PatientID Integer Yes 00000001
Forename Varchar Yes Simon
Surname Varchar Yes Reed
DOB Date Yes 01/01/2001
House_Number Varchar Yes The House
Street Varchar Yes Main Street
Town Varchar Yes Leeds
County Varchar Yes West Yorkshire
Country Varchar Yes England
Post_Code Varchar Yes LS1 1AA
Ethnic_Identity Varchar No Black African
Date_of_Death Date No 01/01/2009


Attribute Name Data Type Required Example Data
GP_Number Integer Yes 00001
Forename Varchar Yes Fred
Surname Varchar Yes Smith
Practice_Number Integer Yes 0001


Attribute Name Data Type Required Example Data
VaccinationID Integer Yes 0000001
Date Date Yes 01/09/2009


Attribute Name Data Type Required Example Data
BatchID Integer Yes 000001
Manufacturer Varchar Yes GSK
Manufacturer_Batch_Number Varchar Yes 46621AFN11
Date_of_Production Date Yes 01/01/2009
Date_Recieved Date Yes 01/02/2009


Attribute Name Data Type Required Example Data
CaseID Integer Yes 0000001
Date_Added Date Yes 01/01/2009

Confirmed Case

Attribute Name Data Type Required Example Data
CaseID Integer Yes 0000003
Date_Added Date Yes 01/01/2009
Date_Confirmed Date Yes 01/02/2009

Overseas Destination

Attribute Name Data Type Required Example Data
OverseasID Integer Yes 0001
Country_Name Varchar Yes USA
Date_Swine_Flu_Confirmed Date No 01/01/2009
Swine_Flu_Deaths Integer Yes 0


Attribute Name Data Type Required Example Data
IllnessID Integer Yes 0001
Name Varchar Yes Heart Disease