Homework # 1

Happy Valley Kennels

Due: 1/23/2001

70-455

Information Resources Management

1/16/2001

50 points

 

 

The attached case describes the operations of a typical small business. You are to read the case and prepare a recommendation for the owners. Your recommendation only has to address the data management portion of the system. Specifically, you should recommend what should be used to manage the data and do not have to address programming. This could be a recommendation to use a database management system that may require separate programming, like Oracle or MS SQL Server or a database management system that may not require programming beyond what is provided by the DMBS, like Access. You could also recommend that the data be managed without using any database, like Excel or Visual Basic with "flat files".

What are the advantages of using a "real" DBMS for this application? The disadvantages? Information on Oracle 8i Personal Edition is available at www.oracle.com, and information on SQL Server 2000 is available at www.microsoft.com.

What are the advantages of using Access for this application? The disadvantages?

What are the advantages of using Excel for this application? The disadvantages?

Based on your analysis of the above points, what is your recommendation for Happy Valley Kennels? Why? Be convincing. Document any additional assumptions you make in arriving at your recommendation. What are the circumstances under which your recommendation would change? Suppose Happy Valley Kennels wishes to provide a web interface to their new system. How (if at all) would this change your recommendation? Even though this is a database course, a well-reasoned answer as to why something other than a database is clearly the obvious choice will earn just as many points as a well-reasoned answer that recommends a database management system.

Scoring:

"real" database

advantages - 5 points

disadvantages - 5 points

Access

advantages - 5 points

disadvantages - 5 points

Excel

advantages - 5 points

disadvantages - 5 points

Recommendation - 15 points

Web interface changes - 5 points

 

Turn In: Your typed analysis and recommendation that addresses all of the items mentioned above.

Happy Valley Kennels Inc.

 

Background

The Happy Valley Kennel (HVK) is a boarding kennel for dogs, or a doggy hotel. When dog owners go on vacation, they make bookings at the HVK to bring their pet in and have the kennel staff look after them. Jim and Sally Read own the kennel. All of their information processing is currently done in manual form, but they have recently purchased a PC for their business and are interested in converting some of their manual procedures to computer-based systems. In particular, they are looking for some automated support for the "Reservation and Care System."

You have been hired by the Reads to help them build an information system to help manage this process. The feasibility of the project has already been established and requirements determination has begun. In order to proceed, you have scheduled an interview with Sally Read, who manages the day-to-day kennel operation. The following paragraphs document your interview experience.

The Interview

It is 9 am on a Friday morning. Sally has just finished making the morning rounds of the dogs and you each have a cup of coffee and are ready to proceed. You begin by asking Sally to describe how reservations are made.

Sally: Well, usually reservations are phoned in. Sometimes people come in off the street, but I’d say 75% or more of our reservations are by phone.

You: OK, great. Now, can you describe what happens when someone calls in?

Sally: Sure. When a customer is planning a vacation, they call to make a booking. Jim or I or one of our part time staff will take the call. We pull the kennel card for the customer from the file, and make any updates…you know, address change, change in the dog’s food, that kind of thing. Then we assign the dog to a run.

You: A run?

Sally: Yeah…haven’t you ever seen the inside of a kennel? Here, let me show you.

Sally takes you on a quick tour of the kennel. She shows you the dog runs, a 4 ft by 20 ft indoor/outdoor cage with a metal door on a pulley that can be opened and closed to let the dog in and out. She explains that in the summer these doors are left open all the time so dogs are free to go in or out as they please. In the winter, when the inside of the kennel is heated, the doors are shut all the time. Several times a day someone puts all the dogs out for 15 minutes or so (longer if it’s a nice day). Then they come back inside. You also see a sheet of paper tacked to the kennel door that has the names of all the dogs in residence at the time along with their food preferences. Sally tells you that is the Kennel Log, and offers to make you a copy (Exhibit 1). Once you return to the office, you continue your questions:

You: OK. So the dog is assigned to the run. Now I noticed that some of the runs had big doors and some had little doors. I also thought I saw chain link on the tops of some of the runs, but not all. Does that factor into the run assignment?

Sally: It sure does. The assignment of a run is based on many factors, such as the availability of the run for the duration of the stay, whether the dog is a climber or not (climbers need to be put in covered runs), and whether it is a small, medium or large dog (since some of the runs have larger doors which are easier for bigger dogs to go in and out of). We also find it is better to have some dogs facing the back of the kennel property. Dogs facing the front or roadside of the kennel tend to bark more, so really yappy dogs get put facing the back.

You: OK. What happens next?

Sally: Well, once the run is assigned, we note the dog’s name, run, and food preference in the Kennel log for the days he or she will be visiting. I think that’s pretty much it.

You: Now, you said that you update the kennel card, right? What is the kennel card?

Sally: Actually it’s just a recipe card that we keep for each of the dogs. I keep them in this old recipe box here (she shows you the box). On it, we write the owners’ names, the address and the phone number, the dog’s name and sex, whether it has been spayed or neutered, and what food the dog prefers. We also note any special dietary needs and any special medications that the dog takes. Oh, yeah, we need to record the family veterinarian too, just in case the dog gets sick while staying with us.

You: So you check with the customer each time they call to make sure the information is still current?

Sally: Well, almost every time. If it’s really busy, I sometimes forget.

You: And what about for new customers? Do you fill this out over the phone the first time someone calls?

Sally: Yep. Oh, did I say we record the breed and age of the dog too?

You: OK. So, I think that tells me how it works when someone makes a booking. Now what happens on the day the dog is brought in?

Sally: Well, first I pull out the kennel card and transfer the owner’s information to a contract. You know, the owner’s name, the dog’s name, all that stuff. Oh, do you want a sample contract? (Exhibit 2) Anyway, then we have to figure out the price – I’ll tell you how we do that later, OK? We enter the daily rate onto the contract, here, and then check with the owner to see if they brought any toys for the dog or if they have a leash to leave or a blanket. We note these in the contract too, just so we don’t forget when they go home. You know, if a blanket gets really wet we’ll pull it out of the run to dry. Then sometimes we forget to send them home. This way we remember to check. I also note the time of arrival. We used to not charge dogs if they came in after noon on their check in day. We don’t do that now, but I still note the time – force of habit.

You: It’s amazing how hard it is to break some of those habits isn’t it?

Sally: It sure is. So, where was I? Oh yes. Once all of this is figured out and the contract is complete, I get the owner to sign it and I give a copy to them before they go. The remaining copies get stored in the office file cabinet until check out time. We get the owner to bring the dog into the kennel and we put the dog into its assigned run. Once they’ve said their goodbyes, that’s pretty much it.

At this point, a car drives into the lot.

Sally: Oh, that will be the Willis’ – you remember the really friendly Golden Retriever, Chelsea? Well, these are her parents. Will you excuse my while I check them out?

You: No problem. Actually is it OK if I watch what you are doing?

Sally: Sure.

The Willis’ come into the office. They chat briefly with Sally about their trip to the Bahamas and Sally catches them up on the local news, while she roots around in the filing cabinet. She finds Chelsea’s contract and tells the Willis’ that everything was just fine while Chelsea was there, except that she didn’t eat on the first night. You notice that there is a notes area on the contract where this information is recorded. The Willis’ comment that she almost never does and Sally agrees.

Then Sally notes the check out time (10:15 by now) and counts the number of days that Chelsea has been there. Sally multiplies this by the daily rate from the contract, and gives the Willis’ their total. While they make out a check, Sally completes the contract and signs it off. The Willis’ get another copy and then Sally puts the remaining copy into a stack of other papers.

Once the Willis’ are gone, you ask Sally a couple of questions about what you just saw.

You: How did you figure out how many days to charge them for?

Sally: Well, they came in on Tuesday of this week, so they get charged for Tuesday, Wednesday and Thursday. Since it’s before noon, I don’t charge them for Friday. So, it works out to 3 days.

You: The notes you have about the dogs (e.g., that Chelsea didn’t eat the first night), when do you make those?

Sally: Whenever something needs to be noted. Usually after feeding time (5 pm) or at the end of the night, just before I turn in I go through the kennel log and try to remember if I need to write anything down.

You: Great, this is really helpful Sally. Now, the contract copy that you kept, the pink copy, what happens to those.

Sally: Well, as you can see there’s a pretty big stack of them. Those are all of the "closed" contracts. Eventually they get passed to the accountant who keeps our books, but that pile is basically the filing system we use. Awful, isn’t it?

You: Oh, one more thing. You mentioned something earlier about getting the vet’s phone number in case you have to take a dog in. How does that work?

Sally: Well, it’s only ever happened a half a dozen times that I can remember in 5 years. So, it’s not really a big issue. If a dog gets sick (and I mean really sick – if it’s just a bit under the weather we wait until the owner comes back), we take it to the owner’s vet if it’s not too far away or to a local vet. We call the owner at the emergency contact number they leave us and make sure it’s OK and then they deal with everything from there on in. So, really we’re just a taxi service or something.

At this point Jim Read comes into the office with a box of canned dog food. You exchange pleasantries with Jim while he puts the food on a shelf and then continue the interview.

You: So, I think I have a pretty good handle on how the booking and check in and check out work. What else is there? I guess, just the daily routine while they are here. Sally, you told me about letting them in and out during the winter and about taking notes and all that. Is there anything else that happens during the day – specifically things that will affect the system we’re building?

Jim: Well, have you talked about feeding at all?

You and Sally: No, we hadn’t gotten to there.

Jim: Each day at about 5pm, we feed the canine guests. Every dog has its own favorite food and we try to accommodate that. Sally has probably already showed you where we write that down in the Kennel Log. So, at around 5 o’clock, we go out to the kitchen, look all the foods and amounts up and prepare the food. We give it to the dogs, and then come back in about 30 minutes to make sure it is all gone.

We get the feed for the dogs from a variety of suppliers. I guess in all, we stock about 10 of the most common dog food brands. When a customer makes a booking, the person taking the booking checks to see if the dog’s preferred food (from the Kennel Card) is one that the kennel stocks. If not, we give the customers the option to bring their own food or to use a substitute. When we are running low on food, Sally lets me know, and I send a purchase order to the appropriate supplier (mostly that’s just a phone call or a fax). Food is usually delivered the next day. We get a shipping notice when the food arrives and then have 30 days to pay our outstanding balance. We get invoiced monthly from the suppliers for food costs, and I usually pay by check when we get those invoices.

You: OK. Is there anything else we need to think about?

Sally: Oh, well I haven’t told you yet about the daily rate. I promised I would do that earlier, didn’t I?

You: Uh huh. I had forgotten, I’m glad you remembered.

Sally: Small dogs (e.g., Poodles, Chihuahuas) are charged a daily rate of $10.00. Medium sized dogs (e.g., Shetland Sheepdogs, Siberian Huskies, Golden Retrievers) are charged $11.00 per day and large breeds (e.g., St. Bernards, Rottweilers, Alaskan Malamutes) are charged $12.00 per day. That’s the basic rate.

Some dogs require daily medication for various physical ailments. Well, let’s see – Rhabytt here (points to today’s Kennel Log) takes Eltroxin 2 times per day every day for a thyroid condition. Our policy is that we will administer these medications while caring for a dog, but we charge (currently) an extra $1.00 per day. We’ve talked about increasing this, but so far haven’t bothered.

As Jim said, most owners use the food we provide, but if we don’t stock the preferred food, then the owner has the option to bring their own. If they do this, then the daily rate is discounted (currently the discount rate is 10%), but only for large dogs.

You: So, let’s see if I have this right. Rhabytt is a Siberian Husky, so she would get charged the rate for medium sized dogs. That’s $11.00, right? Plus, since you have to give her medication, she would be charged an additional $1.00 per day. Now, your note here looks like it says she is eating her own food, rather than one of the kennel brands.

Sally. Right…

You. So, she would get a 10% discount on the daily rate because the owner’s brought their own food. Oh, no. Wait. You said that only applies to large dogs. So, is that everything on the daily rate?

Sally: Yes, oh, unless the same owners bring in more than 2 dogs at a time or if they have two dogs sharing the same run. Then they get a bit of a discount.

You: How much?

Sally. Well, if two dogs share the same run…now, we don’t normally like to do this because the dogs usually need their own space, but sometimes it works really well – a couple of poodles or something)… Anyway, if they share, they get 10% off the daily rate. And then, if an owner brings in 3 dogs or more, they get a 7% discount.

You: Great. Well, I know I have enough to get started with. I’m going to take all these notes away and try to organize my thoughts about how the system works. It will take me about two weeks, working with my partner to get everything done. In the meantime, I may have to call you or email you with a few additional questions. Then, I’ll come back and show you what I’ve done and we’ll make sure that we have everything nailed down.

Jim/Sally: OK. We’ll talk to you soon. Bye.

Exhibit 1 – Kennel Log

1

‘Spot" Allan OWN FOOD

40

2

39

3

38

4

37

5

36

6

35

7

34

‘Rhabytt’ Compeau OWN FOOD

pills – Extroxin 2xdaily

8

33

‘Thumper’ French SHURGAIN

9

32

10

31

11

30

12

29

13

‘Chelsea’ Willis IAMS (out today)

28

14

27

15

‘King’ Davies DOG CHOW

26

‘Toby’ Mann DOG CHOW

16

25

17

24

18

23

19

22

20

21

NOTES:

    1. Shaded runs (27-34) are covered
    2. Underlined runs (13-28) have large doors to accommodate larger dogs
    3. Runs 21-40 face the back of the kennel property

Exhibit 2 – Sample Contract

Happy Valley Kennels Inc.

Boarding Contract

Date

 

Customer Name

Address:

Emergency Contact

 

Pet Name:

Size: S M L

Start Date:

Special Needs:

End Date:

Daily Rate:

Discounts/Extras:

Items Left:

Notes:

I understand and agree to the terms and conditions of this contract (see reverse side) and agree that Happy Valley Kennels is not liable for loss or illness of my pet.

   

Signature of Owner

Date

INVOICE

                 

Daily Rate

X

Days

=

Subtotal

+

GST

=

TOTAL

 

 

   

Happy Valley Kennels

Date