Janies Pet Minding

Janie is a local supplier of pet walking and sitting services for business people. The plan is to eventually grow the business to also provide online accessories to pet owners. A few years after starting, the business has experienced growing demand for the services offered. Subsequently, Janie needs an accounting system to record transactions and manage the details of customers and their pets. Due to the growth of the business, Janie wants to hire a part-time worker to develop and manage a system for processing orders. Having successfully applied for the job, you begin researching with Janie about how the company works.

YOU: Tell me about how you take orders.

JANIE: When a potential customer requests a job, I first take down all of the customer information, such as the customers: first and last name, street address, city, state, and postcode. I also request their phone number (work and mobile) along with their email address in case I have urgent matters that need to be attended.

YOU: Sorry to interrupt, but can some customers have the same name?

JANIE: Yes. As I was saying, then I record all of the information about their pets, such as the pets name, date of birth, gender, type of animal, a photo, along with details of accessories used by the pet.

YOU: What other information would you like to record in the database?

JANIE: Well, I then show the customers a list of the services that I offer. Here, I detail the services including day stays, walking, overnight stays and pet taxi services. Each has a different cost, which needs to be recorded in the database. When the customer is ready to order a service, I complete a form with information about the customer, pet, pickup date, and payment method (Cash, credit card, cheque, account) recorded on the top of the form. However, I often make copying mistakes and would appreciate it if I did not have to enter repetitive information each time. I then put the date at the top and usually include an order number that I have created. After that heading, I list the service(s) the customer wants, including quantity.

YOU: What other information would you like to get out of the database? JANIE: I would like to be able to:

take orders quickly, because certain times are very busy for me. show how many services are due to be performed each week. change prices easily, because the cost of providing services is rising fast.

YOU: I can create a form for you to quickly enter details about the services offered, and queries will help you with the other two tasks. I can also help you manage your business with specific queries that might help you improve the services offered to clients. Can you think of some analysis that would be helpful?

JANIE:

To improve my advertising campaigns and overall management of the business, I would love to see:

my best-selling services

who my best customers have been

a report of what accounts remains unpaid as well as a break down of how customers have paid (for bank reconciliation and cash management purposes). Before running the report, I would also like an easy way to update the payment records in my database.

Required:

(1) Create an E-R Diagram for Janies business using a Diagram Editor or Microsoft Access relationship manager.

(2) Create tables using the following guidelines.

(a) Enter records for at least ten customers. Use a hypothetical name, address, phone number, and e-mail address details to create customer records. Assume that the customers are located in Australian cities and states.

(b) Enter at least four records for the services offered including: day stays, walking, overnight stays and pet taxi services.

(c) Each customer should have at least one order; a few customers should place two orders. Each order should contain at least one service and some must include multiple services.

(d) Appropriately limit the size of the text fields; for example, a telephone number does not need the default length of 255 characters.