Achieving Operational Excellence: Building a Relational Database for Operation. This exercise requires that you know how to develop a database using Microsoft Access, and know how to create forms, queries and reports. KoKo's Canine Per Club is a dog-walking service, catering to caring, yet busy, pet owners. The service proves very popular with pet lovers who recognize the value of providing their pets with daily exercise. Although the service was only started six months ago, it currently provides pet walking services for 50 pets and is registering, on average, 5 pets per week. Paperwork is increasing, and Caedee Hannah, the owner, needs a better record keeping system. During a meeting with Ms. Hannah, she explains to you that a new client must register with the service. During the registration process, the new client provides basic information about his/her pet(s), chooses a preferred walk time for his/her pet(s) and specifies a walker preference. During this time, a dog-walking fee is determined and recorded on the pet registration form. The dog-walking fee varies by pet and is based on the pet's size, temperament, and the number of pets the owner has. The pet owner can request that his pet be walked in the early morning, late morning, early afternoon, late afternoon, or early evening hours. Available walk times are currently kept on a clipboard by the phone. However, Caedee wants the available walk times, as well as walker, client, and pet information, kept in the database that you are building. Caedee's record keeping needs are simple. She requires a database that tracks her clients, their pets, available walk times, and the pet walkers. Caedee gives you a partially completed data (see the following tables) and requests that you build and populate Client, Pet, Walker, and WalkTime tables in a database, define the primary keys for these tables, and create the relationships between the tables. Then you create Client form and Pet form, and Walker Schedule report and Client List report After reviewing these data, you notice that the Client table stores contact information for each client (for assignment purpose, please place your name as the first client) and that the client identification number serves as the primary key. The Walker table stores basic information about each walker and the Walk Times table stores a set of walk time codes. When a client registers a pet, a walk time code is assigned to each pet. This walk time code indicates the pet owner's preference for the time of day when the pet should be walked. The Pet table stores information about each pet, including the pet number, pet name, client identification number, walker identification number, quoted price, preferred walk time, enrollment date, and any relevant comments. After studying these data, you decide three relationships are necessary. First, a relationship between the Pet and Client tables is needed. Since each table contains a ClientNo field, you use the ClientNo field to create the relationship. Second, a relationship between the Walker and Pet tables is necessary. The Walker and Pet tables have a WalkerNo field, and you use this field to create a relationship between the Walker and Pet tables. Third, both the Walk and Pet tables have a WalkTimeCode field. You use the EalkTimeCode field to create the relationship between the Walk and Pet tables. You decide each relationship should enforce referential integrity. After creating four data tables, you should create two forms: Client form and Pet form. When a new client enrolls his pet with the walking service, Caedee uses this form to input the client name and the contact information about the client. The form header includes the company name and the form's name. After a client registers, Caedee enrolls his pet using a Pet form. The Pet form captures basic information about the pet, such as the pet's name, walk time, and walker preference. You can use the Form Wizard to build initial Client form and Pet form. Once the forms are built, you can edit the forms in Design view. Caedee also requests Walker Schedule report and Client List report. The Walker Schedule report is generated on a weekly basis and tells Caedee when her walkers are scheduled to walk the pets. Since the Walker Schedule report uses data from four tables, you may build a select query, and then create the report based on the select query. The Walker Schedule report header contains the company name, a report title, and the current date. The information in the report body lists the walkers in ascending order based on the walker's last name. The Client List report provides a listing of the current clients (clients names and contact information). You may use the Report Wizard to speed initial report development, and then edit this report in Design view. The Client List report's header contains the company name, report title, and current data. While you are free to work with the design of the forms and reports, each form and report should have a consistent, professional appearance. According to above requirements, you build a database that contains four tables, two forms, and two reports.

Table 1: KoKo's Canine Pet Club Client Data CNo CLastName CFirstName CAddress CState CZip CPhone CEmergencyPhone 0001 Put Your Last Name Here Put Your First Name Here 8385 North First Street Edmond 0002 Stone oK 73003 606-8975 606-5039 an Drive Muson OK 73069 89,8182 606-3402 303 Northridge EdmondOK 73099 899-2395 909-8679 Edmond Ok 73034 754-3666 359-1588 1408 Peter Pan Drive Yukon OK 73069 899-8182 606-3402 7120 Lakeridge Midwest City OK 73099 899-4979|899-2222 0004 Monac 0005 Ruaz 0006 Barker Monica 1701 Memorial Road Oklahoma City OK 73001 905-8440 606-4102 Table 2: Available Walker WalkerNoWFirstName WLastName W001 W002 Kyle W003 W004Kelly Legier Morgan Burlon Table 3: Available Walk Times TimeCode WalkTimeDescription Early Morning Late Morning Early Afternoon Late Afternoon Early Evening Table 4: Pet Data PetNo PetName CNo WalkerNo QuotedPrice TimeCode EnrollmentDate Comments 000 Mickey 0002 Fiedo 0001 w001 $7.00 1/03 1/03 7/12/03 7/12/03 Is a very friendly, well-behaved poodle Is a friendly miniature schnauzer. Keep on chain. He will run away, if he is off his 001 W001 $10.00 chain 09 Moly 0o5 W002 $10.00 005 W002 Is a sensitive Pomeranian. Needs lots of attention $7.00 Is a playful German Shepard, but is hesitant around strangers Is a well-behaved Chow Chow. The owner wants Bruno walked by himself and not with other pets Is a well-behaved mixed breed. She does frighten easily. Please keep her on a leash Is a mixed-breed. Very playful Is a Black Labrador Is a Black Labrador Prancy can be aggressive towards other animals Is very affectionate. Lacy will run, if she gets off of her leash Dazzle is afraid of strangers Lancelot 0005 Bruno $8.00 15/03 006 Sally 0003 003 $8.00 /22/03 Peek-A- Poo 0007 0004W004 $7.00 008a04 58.00 009 Gretchen 004W004 8.0 004 W004 22/03 $8.00 $8.50 $8.50 r 10010 Prancy 0011 Lacy 0006 W004 0006 W004 13/03 13/03 8/13/03 0012 Dazzle 0006 W004 $10.00