Case Study Scenario

The purpose of the Veterinary Hospital Registration system is to handle and record all the information relating to the clients, veterinarians, pets, treatments administered, insurance coverage and appointments (open, closed, denied, cancelled and missed). The clients are owners of domestic animals such as cats, dogs, caged birds and so on. Some of the veterinarians at the hospital are specialists in particular types of animals but the majority can treat most types of animal.

All first-time clients of the hospital must register using the hospital's online registration system over the internet. For registration, the client must provide the following information: name and address, national ID number, date of birth and an email address. Once registered, a client will be given a username and password to be used to log into the system to make appointments in the future. The username and password will always be emailed to the client at the email address provided. The client will also be assigned a unique account number which will identify the client for the life of the system.

A record is kept of the pets belonging to the clients who have registered with the hospital. Details of all pets are recorded online during the client's registration. These details include the pet's name, animal type (for example: dog, cat), pet's date of birth, date registered with the hospital and the number of the insurance plan covering the pet. The pet will also be assigned a unique pet ID number which will identify the animal for the life of the system. All clients must register their pets in advance of them ever needing to see a vet at the hospital for treatment.

The hospital, in association with an insurance company, offers insurance coverage for pets belonging to clients. All clients must have an insurance plan for their pets to get treatment at the hospital. During the registration process, the system will access the insurance company's Computer System and allow the client to select a plan from a list of available insurance coverage plans. The selected plan can be for one or more pets and the plan can cover all or part of the cost of treatment. Details are kept of the plans taken up by clients including the insurance plan number, the date it was taken out, whether the plan covers one or more pet, the level of coverage and the treatments which the plan will cover.

Registered clients must make appointments for their pets in advance over the internet or by calling in to the hospital in an emergency. If a client is desirous of bringing two or more animals to the surgery each is treated as a separate appointment. For appointments to be made, clients will be prompted to provide their username and password to get access to the appointments module. Once the username and password is authenticated, clients will be prompted to provide their national ID number, the pet's ID number and the date requested for the appointment. The system will access the Vets database and the Appointments database and allow the client to choose from a vet from list of available vets and a time slot from the list of available time slots for each vet on the particular day requested. Once a vet and a time slot is selected, a record is created in the Appointments database. The record will contain client ID, pet ID, vet ID, the date, time slot, the status of the appointment (open) and the plan number of the insurance plan covering the pet. The client will then be allowed to print an appointment ticket with an appointment number and the details of the confirmed appointment. This printed appointment ticket is to be presented when clients present themselves at the hospital with their pets.

On the day of the appointment, the client must present the attendant at the hospital with the printed appointment ticket which the attendant will use to query the Appointments database to verify that the appointment is legitimate. The client will then provide ID documents to support all the information given over the Internet (name, address, national ID number). The system will automatically interface with the National Registration System to authenticate the client's national identification number. The system will also automatically interface with the insurance company's Computer System to query whether the insurance premiums for the insurance plan covering the particular pet are up-to-date. If the client's identification is not authenticated or the insurance premiums are not up-to-date, the appointment is denied. The appointment ticket is returned to the client stamped as "denied". The status field on the appointment record is updated to "denied".

After attending each appointment, the appointment ticket is stamped as "closed" by the attendant and returned to the client. The hospital attendant will also update the database with information concerning the treatment administered to the particular pet. A client sometimes makes an appointment and then either cancels it or does not turn up for the appointment. The hospital attendant will update the status field of the particular appointment record as "cancelled', or "missed" accordingly.

The management of the hospital must be able to identify clients who regularly make appointments that they do not keep and to provide reports and make ad hoc queries from all the data stored in the database of the hospital registration system.

c) Project Part C - E-R Diagram and Database Design

Using the scenario above and the context and high level diagram shown below complete the f following question. N.B. Students must use the example of the context diagram and high level diagram provided and must also create database tables together with the Entity Relationship File in word document. You must also provide the cardinalities for the Entity relationship document.

1) Assume you are required to design an Entity-Relationship diagram for a database in which all information pertaining to the system is to be stored. Given the information in the case, state all the E-R entities and use them to sketch an E-R Diagram showing both maximum and minimum cardinalities for each relationship in the diagram. State ALL assumptions made to arrive at the cardinalities.

2) For all E-R entities, provide the primary key attribute and four (4) or more other attributes (make up these where the information from the case proves insufficient).

3) For all relationships in your E-R diagram above, show the database tables for a relational database design. Provide at least two attribute along with the primary key attribute for any new table derived.