Advanced Database Management Systems

Assignment

Spring 2021

Assignment Guidance

The learning outcomes assessed in this assignment are as follows:

•    It  is the student’s responsibility to familiarise themselves with the NCC Education Academic Misconduct policy.

•   Any assignment submitted after the submission deadline, without prior approval, will be given 0% and the student will be referred.

•    The late assignment submission may be marked as a referral attempt, but only a maximum mark of 40% can be awarded for that particular assessment.

•    If a student requests an extension to the official submission date this must be done in writing to the Course Director at least five full UK working days before the official submission date.

This request must be accompanied by supporting evidence.

•    This assignment is worth 40 % of the overall final mark for the module.

•   All references should be cited in text and included in a reference section at the end of the report using the Harvard Referencing Scheme.

•    The Statement and  Confirmation of Own Work must  be completed and submitted with the assignment.

•    This assignment must be submitted by the due date and time as given on the front of this assignment.

Case Study: Juster Norton Animal Care

Background

You have been asked to design and construct a database system for Juster Norton Animal Care, a veterinary practice based in London, United Kingdom.

How the company organises its work:

Juster Norton Animal Care is a vet practice based in London, United Kingdom. It is a medium sized veterinary surgeon currently managed by chief vet Roberta Lessing. Roberta wants a database system developed to handle the records of patients, prescriptions and referrals.

Although Juster Norton have traditionally dealt with dogs and cats they also have to treat more exotic species especially as these become more popular as pets; to this end they have employed Tony Nellis a vet from Australia who specialises in snakes and lizards.

Juster Norton’s requirements are for a system that keeps records of their patients (animals), type of animal, and the patient’s owners. For appointments they need to know with which vet the appointment is with as well as the time and date. The outcome of the appointment might be a prescription with one or more drugs on it. Repeat prescriptions are seen as being the outcome of a single appointment. An appointment might also lead to a referral or number of referrals for a particular treatment. Lessing referrals are for treatments carried out at only one particular treatment centre each. Excerpt from requirements interview with Roberta Lessing:

Things have been expanding in recent years. That’s why we’ve taken on Tony. So now we have myself, Tony, Adrian Cooper, Suleiman Faizi and Emma Goldman. The real substance of our work is the appointment where people bring in their pets. Owners who have more than one pet tend to use us for all of them. We charge for each appointment and for each prescription made out depending on the cost of the drug. Any treatment carried out by treatment centre is billed by them and has nothing to do with us. ”

Further details of the case study are shown in the documents below which give a representative example of data and can be taken as representative of a much larger data set.

Please note that the data as represented here is not necessarily in a normalised state and it is the job of you, the database developer, to organise the data in its most optimal state.

Document 1 Patient (aka Animal) record.

NOTE: Record keeping has not always been consistent, for example, owner records have  been kept in different formats on the patient record and the appointment cards. Note also   that there is inconsistent ways of recording the ‘Type of Animal’ field where ‘Dog’ has been used in one case and more detailed ‘Border Collie’ (a particular breed of dog) in another.

Refining the use of types could be part of the recommendations when implementing the new system.

Document 2 Appointment Record Card

Document 3 Hospitals and Treatments

Document 4 Prescription Sheet

Assignment

You are required to produce a report that addresses all of the following tasks:

Task 1                                                                                             (20 marks)

Create an ER diagram (using UML notation) of the Juster Norton Animal Care system. Please state any assumptions that you make.

Task 2                                                                                            (10 marks)

Critically assess the normalisation you have undertaken to produce a set of relations for the scenario. You should discuss the process of normalisation in general and also the specific way it has been applied in your work.

Task 3                                                                                            (5 marks)

Create a table listing for the Juster Norton Animal Care system. You should show all the attributes and identify primary and foreign keys.

Task 4                                                                                            (10 marks)

Create the relevant SQL CREATE TABLE statements for implementing the Juster Norton Animal Care system in a DBMS of your choice (not MS Access). You should provide screen dumps to show that the create statements have worked.

Task 5                                                                                             (4 marks)

Populate the database with the data shown in the case study.

Discuss the population of the database tables for the Juster Norton Animal Care system (order of population, issues and resolutions and SQL used) supported by appropriate screen dumps.

Task 6                                                                                            (16 marks)

Create the following queries. Note that you should use the AS, COUNT, GROUP BY and ORDER BY clauses where you think they are appropriate. You should provide a screen dump to show the query being run and the output of the query.

a.  Write a query that selects all the dogs that are currently patients.

b.  Write a query that selects the animals that belong to Mrs Jones.

c.  Write a query that selects the owner details for animals that have been referred to the Bourneville Animal Hospital.

d.  Write a query that shows all the drugs for the prescription for Rolph. e.  Write a query that counts the number of cats.

f.   Write a query that produces an output, to be used in a costing report for an appointment.

g.  Alter the database so that a record can now be kept of the specialisms of the staff members. Roberta Lessing should be cats and dogs; Tony Nellis will be reptiles;

Adrian Cooper should be dogs; Suleman Faizi should be cats; and Emma Goldman should be cats.

Task 7                                                                                             (5 marks)

Create a set of screen dumps showing all the data in each table - the data must be ordered by the primary key.

Task 8                                                                                            (15 marks)

Critically assess the issues that will need to be addressed with regard to  transaction management for the new database system. You should consider what support will be needed for transactions, concurrency control and recovery.

Task 9                                                                                            (10 marks)

Produce a reflective overview of how the database and queries you have created have met the requirements of the business. Some of the issues you could discuss include how the data model reflects the structure of data used by the business; the utility of the queries you have created; and how all the parts of the assignment constitute a usable system. You should also reflect on any future improvements that could help the business.

The remaining 5 Marks will be awarded for the presentation of the report.