Database Design & Development - Coursework

  100% Pass and No Plagiarism Guaranteed

Database Design & Development - Coursework

CI5310 Database & UML Modelling – Coursework 1 2016/17

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.

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.

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 full discussion 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 marks)