CI5310 Database Design & Development Coursework 1, 2016/17, Report

This coursework report presents a comprehensive database design and implementation for a Travel Jabs Clinic, addressing the need for a centralized system to improve communication and efficiency. The report begins with the creation of an entity-relationship (ER) model using UML notation, specifying cardinality ratios and participation constraints, along with any necessary assumptions. A detailed data dictionary is then produced to complement the ER diagram. The relational tables are implemented using Oracle, including the necessary CREATE TABLE statements. The tables are populated with relevant data, and a discussion of the constraints used (entity, referential, and column) is provided, explaining their role in maintaining data integrity. Finally, the report includes three SQL queries designed to demonstrate the database's ability to meet the system's data requirements, along with screenshots of the query outputs. The price list for the vaccines is also provided in the assignment brief.

CI5310 Database & UML Modelling – Coursework 1 2016/17
Database Design & Development
(This coursework is worth 25% of the module)
Submission Date:
Please submit your report via the Digital
Dropbox on StudySpace.
Deadline: Friday, 16th December 2016 by 11:59pm.
You must use the report template which can be downloaded
from the assignments folder on StudySpace. If you do not
use the template, your report will not be marked.
1

Guidelines
The work consists of the design, implementation and population of a small database system
that will form the basis of the report to be handed in.
You will be expected to work on your own and should submit deliverables arrived at
independently using your own knowledge and effort.
Plagiarism is presenting somebody else’s work as your own. It includes: submitting joint
coursework as an individual effort; copying another student’s coursework; stealing
coursework from another student and submitting it as your own work. Suspected plagiarism
will be investigated and if found to have occurred will be dealt with according to the
procedures set down by the University. Please see your student handbook for further details
of what is/isn’t plagiarism.
You must meet all deadlines set. Failure to do so will result in a penalty - ALL work received
after this time will be recorded as LATE.
2

Database for Travel Jabs Clinic
Guidelines
The work consists of the design, implementation and population of a small database system
that will form the basis of the report to be handed in.
You will be expected to work on your own and should submit deliverables arrived at
independently using your own knowledge and effort. Help can be obtained from the
workshop supervisor or other students if you are stuck. Such assistance should be strictly
limited to general advice and should not address a specific solution to the problem. Any
duplication of table design and/or data – from other students’ work - will be treated as
plagiarism.
Travel Jabs Clinic Database
A company called Travel Jabs provides private travel immunisation throughout the UK. This
service is provided through various clinics located in the main cities of the UK. The Director
of Travel Jabs is concerned that there is a lack of communication within the company and
particularly in the sharing of information and resources across the various clinics. To resolve
this problem the Director has requested the creation of a centralised database system to
assist in the more effective and efficient running of the practice. The Director has provided
the following description of the current system.
System Overview:
Travel Clinics
Travel Jabs has many travel clinics located in the main cities of the UK. The details of each
clinic include the clinic number, clinic address (consisting of the street, city, county, and
postcode), email address, telephone and fax numbers. Each clinic has a Manager and a
number of staff (for example, nurses, secretaries, cleaners). Each member of staff reports to
only one manager.
Staff
The details stored on each member of staff include the staff number, name (first and last),
address (street, city, county, and postcode), home telephone number, date of birth, gender,
job description, national insurance number and current annual salary.
3

Patients
When a patient first contacts a clinic of Travel Jabs, the details of the patient are recorded,
which include a patient number, (first name and last name), address (street, city, county,
and postcode), date of birth, gender and home telephone number.
Vaccines
Travel Jabs provides various vaccines at a standard rate across all clinics. The details of each
vaccine include a vaccine number, a full description of the treatment and the cost. (The
price list is shown in Appendix A).
Invoices
Each patient is invoiced for the vaccine(s) and the details recorded on the invoice include
the invoice number, invoice date, patient number, patient name and full address. The
invoice provides the cost for each vaccine and the total cost. Additional data is also recorded
on the payment of the invoice, including the date the invoice was paid and the method of
payment (for example, cheque, cash, credit card). Invoices are issued on the same day as
the vaccines are administered.
The invoice does not state the member of staff who administers the vaccine(s) but this
information should be stored in the database.
Appointments
Every patient must make an appointment. The details of an appointment are recorded and
include appointment date and time. For each vaccine, an appointment time of 10 minutes is
booked. If a patient requires more than one vaccine, then the appointment time will be 10
minutes for each vaccine.
The database should be capable of supporting the following example query transactions:
(a) Present a report listing the Manager’s name, clinic address, and telephone number for
each clinic, ordered by clinic number.
(b) Present a report listing the names and details of patients.
(c) List the historic details of vaccinations for each patient.
(d) List the appointments for a given date and for a particular clinic.
(e) List the details of all staff that administered vaccines on a given day.
(f) List the details of an unpaid invoice for a patient.
(g) List the details of patients and vaccines required for those patients on a particular day.
(h) List income generated from vaccines for a given week.
4

Tasks and Grading
1. Create an entity-relationship model (using UML notation). Specify the
cardinality ratio and participation constraint of each relationship type. State any
assumptions you make when creating the ER model (if necessary).
(30 marks)
2. Produce a Data Dictionary for the ER diagram in task (1).
(25 marks)
3. Implement the set of relational tables created in task (2) using Oracle.
You must include your CREATE TABLE statements in the report.
(10 marks)
4. Populate the tables with relevant data.
A screen shot of the populated tables must be included in the report.
(10 marks)
5. A f u l l d i s c u s s i o n of the constraints used, covering the three types:
entity, referential and column. Explain why you used them and how they ensure
that the integrity and consistency of your data is maintained.
(10 marks)
6. Write 3 queries to demonstrate that your database meets the data requirements
of the system. You have to work out what you consider to be the best 3 queries
that demonstrate the database meets the data requirements of the system. In
your queries you are expected to use a range of SQL queries. ***
A screen shot of the query output must be included in the report.
*** If you produce 3 queries that are simply SELECT * FROM, then you will receive no
marks.
(15 m ar ks)
5

APPENDIX A
Price List
Vaccines
Hepatitis A per dose ( course 2 doses) £38
Hepatitis A Junior per dose ( course 2 doses) £33
Typhoid £21
Combined Hepatitis A & Typhoid £55
Hepatitis B per dose ( course 3 doses) £26
Hepatitis B paediatric per dose (3 doses) £22
Combined Hepatitis A & B per dose ( course 3 doses) £52
Combined Diptheria/Tetanus £15
Combined Diptheria/Tetanus/Polio £25
Yellow Fever (including certificate) single dose £50
Polio £21
Rabies per dose ( course 3 doses) £40
ACWY Vac £30
Japanese B Encephalitis per dose ( course 3 doses) £49
Tick Borne Encephalitis per dose (course 2 or 3 doses) £50
Cholera Vaccine (Dukoral)- 2 dose course £45
6

7