A REA Business Process odeling and RelationalDatabase Development Case: The Paws and Claws Veterinary

Robert M. Ballenger

CASE SUMMARY
Blue Ridge is the county seat of Culver County and is located in southeastern West Virginia. Culver County is the largest county
in West Virginia in terms of geographical size, but it has the smallest population. Blue Ridge has a population of approximately
17,500 people and the county's total population is about 51,000 people. With the exception of Blue Ridge, the county's population is
highly geographically dispersed. The main industries in Culver County are agriculture, lumber, coal, and paper products.
For the past decade, like many other rural areas in the United States, Culver County has been experiencing a shortage of
veterinarians. Several years ago the county received a grant from the Federal Government to fund a new, but modest, veterinary clinic
and the veterinary medical education of two veterinarians. In return for their paid veterinary medical education, the two new vets
agreed to practice for a minimum of five years at the new veterinary medical facility, the Paws & Claws Veterinary Clinic, located in
Blue Ridge.
The vets, Judith Bishop and Mark Haley, arrived in Blue Ridge a little over a month ago and have started to fulfill their
obligation. Their respective West Virginia Veterinarian License numbers are 456Y2006 and 503Y2006. The county has assigned
them the following employee numbers, 2295 and 2310, respectively. The initial budget for the veterinary clinic included the purchase
of a small client/server-based veterinary information system to assist in billing and record keeping. However, the County Manager
wanted to wait until the veterinarians arrived before purchasing the computer. She was unsure of the requirements in terms of
hardware and software and wanted the veterinarians' input into the decision.
Last month Richard Diverio graduated from college, and a week ago started his first job as an Accounting Information Systems
(AIS) Consultant for a regional consulting firm. Culver County is a client of the firm. His first assignment is to work with the Culver
County Assistant County Manager to install the accounting system for the new clinic. She briefed him on the situation at the Paws &
Claws Veterinary Clinic (PCVC) and has requested that he find a suitable "off-the-shelf" software package that will satisfy the
processing and reporting requirements of the vets, the county, and the state's Agricultural Department.

FUNCTIONAL REQUIREMENTS

Richard knew that the first thing he had to do was to determine the functional requirements of the new billing system. He
conducted multiple interviews with the vets, personnel from the county, and the state's Agricultural Department. After the interviews
were complete and he felt he understood the requirements of the various parties, the AIS consultant prepared a functional requirements
report, which included a sample of the data required for the new system. The report was submitted to all the parties interviewed for
approval. A few minor changes were suggested and incorporated into the report. A final version of the PCVC Functional
Requirements Report was distributed to all concerned parties.
Below are the specifications and the sample data contained in the Information Requirements section of the PCVC Functional
Requirements Report.

Owner Information

The system needs to track information on the owners whose pets are registered with Paws & Claws Veterinary Clinic. The system
must track the owner's name, address, telephone number, account balance, year-to-date-charges, year-to-date-payments, and previous
month's account balance. The doctors would like to assign each owner a unique alphanumeric identifier so that they may identify a
specific owner that is registered with the clinic. The identifier will consist of the first five letters of the owner's last name with a
unique two-digit number added at the end. For example, if there were three owners registered with the last name Johnson, the three
owner numbers would be JOHNS01, JOHNS02, and JOHNS03. A sample of the owners’ data is contained Table 1.

moz-screenshot.jpgmoz-screenshot-1.jpgmoz-screenshot-2.jpgmoz-screenshot-3.jpg
PawsandClaws_table1.JPG

Pet Information

The new system must contain basic information on all the pets that are registered with the Paws & Claws Veterinary Clinic, see
Table 2 for sample data. Pets will not be registered as patients until their first appointment at the clinic. All of the animals treated by
PCVC belong to one and only one owner. The vets strongly wish to use the pet’s name and the owner’s identifier to uniquely identity
each pet. Each owner will have one and only one pet with the same name. If over time, an individual owner acquires a new pet and
names it the same name as a previous pet, the appropriate Roman numeral will be suffixed to the new pet’s name when it is added to
the system, for example, Fluffy II or Fluffy III.

PawsandClaws_table2.JPG


Dogs and cats in Culver County require a county pet license. The county pet license number is seven characters long with the first
two positions being alphabetic characters followed by five digits. The county issues the license. If a license is required and the owner
has not purchased one, the license may be purchased at the clinic. The sale of a license is considered a service performed by the
clinic.

Veterinary Services

The new system must contain information on all of the veterinary and licensing services available from the Paws and Claws
Veterinary Clinic. This information consists of a veterinary services code, a description of the service performed, and the standard fee
charged by the clinic for this specific service. See Table 3 for a sample fee schedule of the veterinary services available at the Paws &
Claws Veterinary Clinic.

PawsandClaws_table3.JPG

Pet Classification Information

Each type of animal and pet treated by the Paws & Claws Veterinary Clinic is assigned a standard animal and pet classification
code. The classification code consists of two components separated by a period. The first component indicates the animal type and is
three digits in length. The second component, a two-digit pet classification code, represents a specific breed of pet within the specified
animal type. A sample of these codes may be found in Table 4.

PawsandClaws_table4.JPG

The state mandates that certain types of animals receive a variety of different inoculations every 24 months. A schedule of these
requirements may be found in Table 5. Whenever a new type of animal is added to the PCVC database, the relevant inoculation
requirements must also be added. The required inoculations listed in Table 5 are also listed as veterinary services in Table 3.
The state requires the clinic to develop a system to track and report all inoculations given. By law the clinic is required to update
their records whenever an inoculation is part of the professional services performed by the veterinarian. Because they just opened the
clinic, there is little historical inoculation information available, see Table 6 for a sample.

PawsandClaws_table5.JPG


Service History

The clinic needs to maintain a complete history of all services performed on each pet, including the date, time, amount billed for
the service, and the veterinarian that performed the service. The clinic requires the capability to over-ride the standard fee schedule.
Some fees are subject to frequent change, especially those associated with injections and lab work. Consequently, the clinic needs to
know the fee charged at the time a service was originally provided. A sample set of representative transactions may be found in Table
6.

PawsandClaws_table6.JPG


1Services performed by Judith Bishop
2Services performed by Mark Haley

Cash Receipts

The clinic also needs to keep track of all payments received for services performed; sample data is presented in Table 7. Each
payment must be assigned a unique remittance number by the system. Payments may be in the form of cash, check, or credit card.
The clinic accepts Master Card, Visa, and American Express. Some owners pay for the services rendered the day they were
performed, while others send in their payments through the mail. Each day's cash receipts are deposited into one of the two checking
accounts maintained by the clinic: 1st Bank of Blue Ridge - Acct # 5878962, or Culver County Bank and Trust - Acct # 6892578. The
county has opened these accounts for the clinic. The county will not allow the clinic to have multiple accounts at the same bank, but
the county may choose to use different or additional banks in the future. Because Blue Ridge is a small town, there is no chance that
the clinic will have the same account number at different banks.

PawsandClaws_table7.JPG


The receptionist, John Bolton, will enter the payments into the billing application and will prepare the bank deposits at the end of
each day. The county requires that the application record who entered each payment into the system. The county has assigned him
the following employee number, 2345. It is the county’s policy that any individual that handles cash must be bondable. In addition,
the policy states that the county must secure a fidelity bond on that individual before they may process any cash receipts. Therefore,
the county has taken out a fidelity bond with Appalachian Mutual on John Bolton, policy number FB234765.
Like most veterinary medical practices, PCVC uses a balance forward accounts receivable system. This means that payments are
applied to the account balance and not applied to specific invoices or services rendered.

Form Requirements

The veterinarians and the administrative staff require the following initial set of forms:

Navigational Menu System

The application should have a navigational menu system designed for everyday users. The menu system is to be user-friendly and
capable of executing all the forms and reports included in the application. The menu system is to consist of a startup menu, which is
the main navigational menu presented to the user when the application is first executed, and sub-menus as appropriate. The forms and
reports are to be grouped logically together on the sub-menus. Each sub-menu is to contain a selection to return back to the startup
menu. The startup menu is to be displayed in the foreground and the database object selection window in the background.

Static Data Forms

Forms are to be developed for all the tables that contain static data (non-transaction data). These forms are to allow a user to add,
maintain, delete, and query rows in the table attached to the form. These forms are to be columnar type forms that display data or
allow for input of one record at a time. The forms are to be user friendly with all the field labels consisting of user-friendly descriptive
names.

Application Forms

1. Owner and Pets Information Form. This form will be used to add, maintain, and query information relating to owners and
their pets. The form is to display the Owner’s ID, Owner’s Last Name, Owner’s First Name, Owner’s Street Address1,
Owner’s Street Address2, City, State, Zip Code, and Telephone Number at the top of the page in columnar format. The static
data for each of the owner's pets are to be displayed in tabular format below the owner's information.
2. Services Performed Form. This form is to display a history of the services performed on a specific pet. The form is to
display the Owner’s ID, Owner’s First Name, Owner’s Last Name, and Pet’s Name at the top of the page in columnar format.
The Date of Service, Service Code, Description of Service, and the First and Last Name of the Veterinarian performing the
service are to be in tabular format below the owner and pet information. The tabular portion is to list all the services
performed on the pet in the columnar section. The services are to be displayed in chronological order.
3. Billing Information Data Entry Form. This will be the data entry form used by the veterinarians to record the services they
perform on a pet. Therefore, the form is to allow for the entry of new services performed and for the query of previous
services performed on a specific pet. The previous services are to appear in chronological order. The top portion of this form
is to include the Owner’s ID, Owner’s First Name, and Owner’s Last Name. The bottom portion of the form is to be in
tabular format and is to display and allow for the entry of following billing information: Pet’s Name, Date of Service, Time
of Service, Service Code, Service Fee, and Veterinarian’s Employee Number.
4. Cash Receipts Data Entry Form. The clinic will use this data entry form to record the cash receipts they receive from owners.
The form is to allow for the entry of new cash receipts and for the query of previously processed cash receipts for a specific
owner. The previous cash receipts are to appear in chronological order. The top portion of this form is to include the
Owner’s ID, Owner’s First Name, and Owner’s Last Name. The bottom portion of the form is to be in tabular format and is
to allow for the entry and display of the following cash receipt data: Date Received, Amount Received, Payment Type,
Deposit Bank ID, and Employee Number of the clinic’s employee that entered the cash receipt.

Query Requirements

The veterinarians and administrative staff will require a wide variety of queries to extract information from the database. They
have determined that the initial set of queries is to include the following:
1. Owner Contact Query. This query is to answer the question, what is an owner’s contact information? The query is to include
the Owner's ID, Owner's Last Name, Owner's First Name, and Telephone Number. Sort by Owner’s ID.
2. Owner Zip Code Query. This query will be used to generate owner address labels. The labels are used when the clinic needs
to send literature and announcements to the owners. The clinic will receive a postage discount if the mailings are presorted
by zip code. The query is to display the Owner's ID, Owner's First Name, Owner's Last Name, City, State, and Zip Code
sorted by Zip Code and Owner’s Last Name.
3. Owner’s Pets Query. The clinic needs a query to display all the owners and the pets that belong to them. The following is to
be included in the query: Owner's ID, Owner's Last Name, Owner's First Name, Pet’s Name, Pet Description, and Animal
Type. Sort by Owner's ID and Pet’s Name.
4. Pet License Query. The county requires a query to list pet license related information for those pets that visit the clinic. The
county requires the following data to be included in the query: Owner's ID, Owner's Last Name, Owner's First Name, Pet’s
Name, Animal Type and Pet Description displayed in a single column separated by a hyphen, and County Pet License
Number. Only those pets with licenses are to appear in the query. The results are to be sorted by Owner's ID and Pet’s Name.
5. Inoculations Given Query. The state requires a query to list the inoculations given by the clinic. The state stipulates that the
Date Given, Inoculation Type, the Veterinarian’s License Number, Owner's Last Name, Owner's First Name, and Pet's Name
be included in the query results. The query is to be sorted by Inoculation Type, Date Given, and Vet’s Name.
6. Owner Account Balance Query. In order to assist the clinic in managing their accounts receivables, they require a query to
display the current account balance of an owner. This query is to display the Owner’s ID, Owner’s Last Name, Owner’s First
Name, Telephone Number, and Current Account Balance sorted by Owner's ID. Only those owners that have an account
balance greater than zero are to be displayed.
AIS Educator Journal Volume 2, No. 1 2007 - 31 Paws and Claws

Report Requirements

There are several initial reports required by the veterinarians and the administrative staff. The individual report specifications
appear below. The data items provided in each report specification are to appear on the report, reading from left to right, in the same
order they are listed. All totals and sub-totals are to be highlighted and clearly labeled as to what the total represents. If totals are
specified, then a single solid line is to appear on the line immediately above the total.
1. Services Performed History by Owner Report. The report is to include the Owner’s ID, Owner’s Last Name, Owner’s First
Name, Month (Month Name, Year), Date of Service, Pet’s Name, Description of Service, and Service Fee. Sort the report by
Month of Service, then Owner, and then Date of Service. Calculate Service Fee totals by Month and Owner. The owner
related data is to appear on the same line, for example, Owner’s ID and Name.
2. Services Performed History by Veterinarian Report. Display the Veterinarian’s First and Last Name, Month (Month Name,
Year), Date of Service, Owner’s ID, Owner’s Last Name, Owner’s First Name, Pet’s Name, Description of Service, and
Service Fee. The report is to be sorted in the following order: Month of Service, Vet, Date of Service and Owner. Service
Fee totals are to be calculated by Month and Vet. The veterinarian’s related data is to appear on the same line, for example,
Veterinarian’s First and Last Name.
3. Daily Deposits Report. This report is to be used by the clinic to reconcile the daily cash receipts and bank deposits. The
report is to list by bank the cash receipts included in each daily bank deposit. The report is to consist of the Bank's Name,
Payment Date, Remittance Number, Owner’s ID, Owner’s Last Name, Owner’s First Name, and Amount Paid. Sort the
report in the following order: Bank, Payment Date, and Remittance Number. The Amount Paid is to be totaled by Bank and
Payment Date.
4. Daily Services Performed Report. This report will serve as the clinic’s Sales Journal. The report is to include the Date of
Service, Owner’s ID, Owner’s Last Name, Owner’s First Name, Pet’s Name, Service Code, Description of Service, and
Service Fee. The report is to be sorted by Date of Service, Owner and Pet. Calculate total service fees by Date of Service,
Owner, and Pet. The owner related data is to appear on the same line, for example, Owner’s ID and Name.
5. Account Balance Report. This report should show the results of Query #6.

CURRENT CHALLENGES FACING THE ORGANIZATION

Richard began to match up the functional requirements with the features available in various "off-the-shelf" software packages.
But to no avail, no standard software package was able to meet all of the critical requirements. There were several standard packages
that handled veterinary billing and medical recordkeeping. But, the real issue was that no standard package could handle the
integration of the state's agricultural requirements into a single comprehensive package.
Richard reviewed his findings with the Assistant County Manager. She understood the situation and asked him to make a
recommendation. He stated that the only real alternative was to internally develop the software for PCVC's veterinary billing system.
Her reply was that the county's Management Information System department was already overloaded and probably would take months
to get the software written. Furthermore, the Paws & Claws Veterinary Clinic needed the software yesterday! Richard stated the
project could easily be done utilizing a standard client/server database management system. She then asked if he had experience with
any of these systems. Richard replied, rather self-confidently, that yes he had developed software in college with a database
management system (DBMS). She immediately contacted his supervisor and he assigned Richard the task of developing the veterinary
billing system for PCVC.