Paws+and++Claws+Case+Study

=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.



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.



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.



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.



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.



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.



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.



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.