Achieving Operational Excellence: Building a Relational Database for Operation.

This database exercise assumes that before taking this course, you have learned how to develop a database using Microsoft Access, and how to create tables, 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 started only 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 some data as shown in the following tables and requests that you build Client, Pet, Walker, and WalkTime tables in an Access database, define the primary key for each of these tables, and create the relationships among the tables. For grading purpose, the Client table should contain your name, otherwise you will get no point for this database problem. 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 (once again for grading 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 WalkTimeCode 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 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 tells Caedee when her walkers are scheduled to walk the pets. The Walker Schedule report should contain fields of walker name, walk time, client name, and pet name. 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. 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. The Client List report should contain fields of client name, address, city, emergency phone, pet name. 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, your completed database should contain four tables, two forms, and two reports.

Please show all steps