Develop an entity relationship model

Assignment Help Other Subject
Reference no: EM13900003

The Case Study - Healthy Pets Clinics

Introduction to the case study

A practice called Healthy Pets provides private health care for domestic pets throughout USA. This service is provided through various clinics located in several main cities across the country. The Director of Healthy Pets is concerned that there is a lack of communication within the practice and particularly in 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 enable more effective and efficient running of the practice. The director has provided the following description of the current system.

Data Requirements

Veterinary Clinics

Healthy PetsCompanyhas many veterinary clinics located in several cities across USA (New York, Chicago, Dallas, and Boston). The details of each clinic include the clinic number, clinic address (street, city, and zip code), telephone number and e-mail address. Each clinic has a Manager and a number of staff (for example, vets, nurses, secretaries, and cleaners). Clinic number is unique throughout the practice.

Staff

The details stored about each member of staff include staff number, name (first and last), address (street, city, and zip code), telephone number, date of birth, sex, social security number (SSN), position, and current annual salary. The staff number is unique throughout the practice.

Pet Owners

When a pet owner first contacts a clinic of Healthy Pets the details of the pet owner are recorded, which include an owner number, owner name (first name and last name), address (street, city and zip code), and telephone number. The owner number is unique to a particular clinic.

Pets

The details of the pet requiring treatment are noted, which include a pet number, pet name, type of pet, description, age of the pet (if unknown, an approximate age is recorded), date registered at clinic, current status (alive/deceased), and the details of the pet owner. The pet number is unique to a particular clinic.

Examinations

When a sick pet is brought to a clinic, the vet on duty examines the pet. The details of each examination are recorded and include an examination number, the date and time of the examination, the name of the vet, the pet number, pet name, and type of pet, and a full description of the examination results. The examination number is unique to a particular clinic. As a result of the examination, the vet may propose treatment(s) for the pet.

Treatments

Healthy Pets provides various treatments for all types of pets. These treatments are provided at a standard rate across all clinics. The details of each treatment include a treatment number, a full description of the treatment, and the cost to the pet owner. For example, treatments include:

T123   Penicillin antibiotic course                           $50.00
T155   Feline hysterectomy                                   $200.00
T112   Vaccination course against feline flu             $70.00

A standard rate of $20.00 is charged for each examination, which is recorded as a type of treatment. The treatment number uniquely identifies each type of treatment and is used by all Healthy Pets clinics.

Pet Treatments

Based on the results of the examination of a sick pet, the vet may propose one or more types of treatment. For each type of treatment, the information recorded includes the examination number and date, the pet number, name and type, treatment number, description, quantity of each type of treatment, and date the treatment is to begin and end. Any additional comments on the provision of each type of treatment are also recorded.

Invoices

The pet owner is responsible for the cost of the treatment given to a pet. The owner is invoiced for the treatment arising from each examination, and the details recorded on the invoice include the invoice number, invoice date, owner number, owner name and full address, pet number, pet name, and the details of the treatment given. The invoice provides the cost for each type of treatment and the total cost of all treatments given to the pet. 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, check, cash, and visa). The invoice number is unique throughout the practice.

Clinic Pharmaceutical Supplies (Stock)

Each clinic maintains a stock of pharmaceutical supplies (for example, antibiotics, painkillers). The details of pharmaceutical supplies include a drug number and name, description, dosage, method of administration, quantity in stock, reorder level, reorder quantity, and cost. The drug number uniquely identifies each type of pharmaceutical supply. The drug number is unique for each pharmaceutical supply and used throughout the practice.

Appointments

If the pet requires to be seen by the vet at a later date, the owner and pet are given an appointment. The details of an appointment are recorded and include an appointment number, owner number, owner name (first name and last name), home telephone number, the pet number, pet name, type of pet, and the appointment date and time. The appointment number is unique to a particular clinic.
Note: The Director of Healthy Pets has decided that although it is desirable to design appointments into the system, it should not be implemented immediately. You are therefore required to cater for appointments in your design documents (E-R model and relational schema) but NOT to implement this feature at the moment.

Requirements for the case study (deliverables)

a) Develop an Entity Relationship (E-R) model to support the above scenario. Your model should comprise:

(i) An E-R diagram clearly showing the multiplicities - cardinality and membership constraints - for each pair of related entities

(ii) A relational schema derived from the E-R diagram including appropriate attributes associated with each entity, and the primary and foreign keys of each relation

b) Create tables, including well-designed test data, to implement the application, and identify appropriate integrity constraints to help ensure that data is entered with correct values

c) Set up and test the following queries and reports using Oracle with SQLPLUS and/or PL/SQL:

(I) List the names and addresses of all pet owners registered at the Boston clinic.

(II) Produce a report listing the manager's name, clinic address, and telephone number for each clinic, ordered by clinic number.

(III) Produce a report showing all pet owners (Pet_owner_name, city, number of pets) who have registered more than one pet for treatment.

(IV) Show the essential details of an unpaid invoice for a given pet owner.

(V) Show which clinic has treated the highest number of Cats so far and how many.

(VI) Determine the maximum, minimum and average cost of all treatments administered in the clinics so far.

(VII) List the total number of pets in each pet type, ordered by pet type.

(VIII) Produce a list of the names and staff numbers for all vets and nurses who will retire 5 years from now (if the retirement age is 60) ordered by staff name.

(IX) List the pet number, name, and description of pets owned by a particular owner.

(X) For a particular clinic, produce a list showing drug number, unit cost and total cost of all the pharmaceutical items they stock at the moment.

Reference no: EM13900003

Questions Cloud

Hampton company reports the following information : 1.Hampton Company reports the following information for its recent calendar year
Calculation of average pressure and upsetting force : Calculation of average pressure and upsetting force in forging and how to minimize barrelling effect - barrelling caused by friction can be minimized by applying an effective lubricant or ultrasonic vibration
Identify a successful three-way handshake? : Identify a successful three-way handshake?
Discuss whether ross is behaving in a professional manner : complete the preparation of the current month's financial statements by a 5 o'clock deadline.  Discuss whether Ross is behaving in a professional manner.
Develop an entity relationship model : Develop an Entity Relationship (E-R) model to support the above scenario. Your model should comprise: An E-R diagram clearly showing the multiplicities - cardinality and membership constraints - for each pair of related entities
List some factors that increase the demand for carpet : List some factors that increase the demand for carpet. Do you think Mohawk should view itself as a carpet or floor-covering manufacturer?
Evaluate the ethical issues in complex project environments : Evaluate the ethical issues in complex project environments
How a relational data solution can be applied : how a relational data solution can be applied to a current business problem
Compute the percentage of ebitda to total revenues : Compute the percentage of EBITDA to total revenues for 2004. Round to one decimal place after converting to a percentage. Compute the ratio of EBITDA to interest expense for 2004. Round to one decimal place.

Reviews

Write a Review

 

Other Subject Questions & Answers

  Structure of the hospitality industry

Structure of the Hospitality Industry - What is the role of a manager in the segment of the hospitality industry that you have chosen? Discuss global factors of importance that a manager must be prepared to deal with

  Nozick believes that just society must not coerce individual

Nozick believes that a just society must not coerce individuals into contributing to practices to which they do not personally agree-even if they benefit from the practice. Discuss his view, his reasons for holding it, and evaluate what he has to say..

  Aqueous solutions are good buffer systems

Which of the following aqueous solutions are good buffer systems ?

  Determineand interpret the p-value for the test

For a sample of 12 items from a normallydistributed population for which the standard deviation is σ =17.0, the sample mean is 230.8. At the 0.05 level of significance,test Hο : μ ≤ 220versus H 1 : µ > 220. Determineand interpret the p-value for the ..

  Define term virtue ethics and identify three most virtues

Define the term virtue ethics and identify the three most important virtues that you live by. Why is it important for you and others to live by these virtues? Give an example of someone you know who lives by these same virtues and explain why you con..

  How is binge eating disorder different from obesity

How is binge eating disorder different from obesity? What are the significant diagnostic features of both disorders? How are they treated?

  Main idea of the poem sex without love

I think the main idea of the poem "Sex Without Love" by Sharon Olds is that we are not living life to it's full potential if we do not love--we just go through the motions of life.

  Public and private healthcare systems

Write a one page essay comparing and contrasting the public and private healthcare systems in the United States.

  Can you find evidence on rba website to support your view

What are the differences between a bank-accepted-bill (BAB) and an Aust. Gov. Treasury note? Which would you expect to have the higher yield and why? Can you find evidence on the RBA website to support your view?

  Levels of core stability and dynamic flexibility

Are there sports that require high levels of core stability and low levels of dynamic flexibility? What about low core stability and high dynamic balance? Are these two variables naturally intertwined or can they be achieved independently?

  Sociological concept of deviance

Explain the sociological concept of deviance and how the funtionalist, interactionist, and conflist perspective explain deviance.

  Ethical issues in reproductive technology

Critically examine the ethical issues related to "New Reproductive Technology - ethical issues", using the textbook (Legal Aspects of healthcare administration), web resources, and professional journal articles (which might also be found online).

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd