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. Table 1: KoKos Canine Pet Club Client Data CNo CLastName CFirstName CAddress CCity CState CZip CPhone CEmergencyPhone 0001 Put Your Last Name Here Put Your First Name Here 8385 North First Street Edmond OK 73003 606-8975 606-5039 0002 Stone David 1408 Peter Pan Drive Yukon OK 73069 899-8182 606-3402 0003 Yu William 7120 Lakeridge Midwest City OK 73099 899-4979 899-2222 0004 Monac Levitica 303 Northridge Edmond OK 73099 899-2395 909-8679 0005 Ruaz Monica 1701 Memorial Road Oklahoma City OK 73001 905-8440 606-4102 0006 Barker Gayle 1983 Sliding Glass Edmond OK 73034 754-3666 359-1588 Table 2: Available Walker WalkerNo WFirstName WLastName W001 Bob Legier W002 Kyle Morgan W003 Burlon Jordan W004 Kelly Lamont Table 3: Available Walk Times TimeCode WalkTimeDescription 1 Early Morning 2 Late Morning 3 Early Afternoon 4 Late Afternoon 5 Early Evening Table 4: Pet Data PetNo PetName CNo WalkerNo QuotedPrice TimeCode EnrollmentDate Comments 0001 Mickey 0001 W001 $7.00 1 6/1/03 Is a very friendly, well-behaved poodle 0002 Fiedo 0001 W001 $10.00 1 6/1/03 Is a friendly miniature schnauzer. Keep on chain. He will run away, if he is off his chain 0003 Molly 0005 W002 $10.00 2 7/12/03 Is a sensitive Pomeranian. Needs lots of attention 0004 Sir Lancelot 0005 W002 $7.00 2 7/12/03 Is a playful German Shepard, but is hesitant around strangers 0005 Bruno 0002 W003 $8.00 3 8/15/03 Is a well-behaved Chow Chow. The owner wants Bruno walked by himself and not with other pets 0006 Sally 0003 W003 $8.00 3 8/22/03 Is a well-behaved mixed breed. She does frighten easily. Please keep her on a leash 0007 Peek-A-Poo 0004 W004 $7.00 3 8/22/03 Is a mixed-breed. Very playful 0008 Bear 0004 W004 $8.00 3 8/22/03 Is a Black Labrador 0009 Gretchen 0004 W004 $8.00 3 8/22/03 Is a Black Labrador 0010 Prancy 0006 W004 $8.50 5 8/13/03 Prancy can be aggressive towards other animals 0011 Lacy 0006 W004 $8.50 5 8/13/03 Is very affectionate. Lacy will run, if she gets off of her leash 0012 Dazzle 0006 W004 $10.00 5 8/13/03 Dazzle is afraid of strangers 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.