Reference no: EM131070387

Snoopy Dog Walker Inc. - Project

Snoopy Dog Walker Inc. provides dog-walking services to caring and busy pet owners. The service proved very popular with pet lovers who recognize the value of providing their pets with daily exercise but cannot find their own time to do it. The service started six months ago, and it currently provides pet walking services for 50 pets. It continues to register about five pets per week. Paperwork is increasing, and Mr. Fred Falcon, the service's owner, needs a better record-keeping system.

During a meeting with Mr. Falcon, he 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 the 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 the 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. However, Mr. Falcon wants the available walk times, as well as walker, client, and pet information kept in the database. Mr. Falcon's record-keeping needs are simple. He requires a database that tracks of the clients, their pets, available walk times, and the pet walkers. Mr. Falcon gives you some information on database designs. You decide to complete the database in two stages. The first stage constitutes your Project 1.

You realize that the database will need four tables with one each for the clients, pets, walkers, and walk times. In the 1st project, you decide to have two tables: CLIENT and PET. The other tables will be added in the 2nd project along with various queries and reports needed in the system.

Requirement 1: Create a new database and name it yourlastnamefirstnameProject1. For example, the database created by John Doe would be called DoeJohnProject1. Failure to follow this will result in zero credit for your project.

Requirement 2: The Client table stores contact information for each client and the client identification number serves as the primary key. Figure 1 shows the structure for the Client table. Create a table structure using the information given in Figure 1.

Field Name	Data Type	Field Description	Field Size	Required Field	Meaningful Caption*
ClientNo	Auto Number	Unique, identifying number for each client. Primary key.	Long Integer	Yes	Yes
CLastName	Text	Customer's last name.	15	Yes	Yes
CFirstName	Text	Customer's first name.	15	Yes	Yes
CAddress	Text	Customer's street address.	20	Yes	Yes
CCity	Text	Customer's city.	15	Yes	Yes
CState	Text	Customer's state abbreviation. The default is OH.	2	Yes	Yes
CZip	Text	Customer's zip code+4. Use an input mask.	9	Yes	Yes
C Phone	Text	Customer's home phone number. Use an input mask.	10	Yes	Yes
EPhone	Text	Customer's emergency phone number. Use an input mask.	10	Yes	Yes

Figure 1: CLIENT Table Structure

*Meaningful caption for a field means, you should add a caption in the table design that shows the full name for the table. For example, a field called CLName for customer's last name should have the caption as "Customer Last Name."

Requirement 3: The Pet table stores data about each pet: pet number, pet name, client identification number, walker identification number, quoted price, preferred walk time, enrollment date, and any comments. Figure 2 shows the structure for the Pet table. Please note that the WalkerNo and WalkTimeCode fields are part of the Pet table but data for those fields will be added later in 2nd project after creating the tables for walkers and walk time codes.

Field Name	Data Type	Field Description	Field Size	Required Field	Meaningful Caption
PetNo	Text	Unique identification number assigned to each pet. Primary key.	3	Yes	Yes
PetName	Text	Pet's name.	10	Yes	Yes
ClientNo	Number	Client identification number. Will match a client number from the Client table.	Long Integer	Yes	Yes
WalkerNo	Text	Walker identification no. NOT required for the time being. Will be added in Project 2.	4	No	Yes
QuotedPrice	Currency	The daily walk fee.		Yes	Yes
WalkTimeCode	Text	Code designating the preferred walking time.  NOT required for the time being.	2	No	Yes
EnrollmentDate	Date/Time	Indicates when the client enrolled the pet. Use short date format.  Use Input mask.		Yes	Yes
Comments	Long Text	Contains any additional information that may be necessary.		No	Yes

Figure 2: PET Table Structure

Requirement 4: After carefully studying the system, you realize that a client may have more than one pet and a pet belongs to only one owner. You decide to create a one-to-many relationship between CLIENT table and PET table. Since each record in PET table contains a ClientNo field, you use the ClientNo field as the common filed to create the one-to-many relationship between the two tables. You also enforce referential integrity, so that no owner record can be deleted when there is a pet record for the owner.

Requirement 5: Now, create a form for inputting client data. Use the layout shown in Figure 3. Reorder fields as shown in Figure 3.

Figure 3: Client Data Entry Form

Requirement 6: Using the data given in Figure 4 and the form you have created (Figure 3), enter the client data.

ClientNo. (Auto number)	CLastName	CfirstName	CAddress	CCity	CState	CZip	Cphone (Area Code 419)	Ephone (Area Code 419)
	Kalvert	Traci	7066 College Blvd.	Overland Park	OH	43211- 1234	555-7154	555-7159
	Lang	Brad	5253 Duck Creek	Iowa City	OH	43240- 2345	555-8777	555-8779
	Larson	Kristen	3966 Woodland	Des Moines	OH	44326-3456	555-8908	555-8909
	Martin	Jerry	52345 College Blvd.	Overland Park	OH	43210- 4567	555-8505	555-8509
	Hopper	Jeff	4435 Main Street	Kansas City	OH	43111- 5678	555-8774	555-8779
	Hubert	Holly	2345 Grand	Kansas City	OH	43108- 6789	555-6004	555-6009
	Bouchart	Bob	5200 Main Street	Kansas City	OH	43105- 7890	555-3081	555-3089
	Margolis	Karen	52345 College Blvd.	Overland Park	OH	43210- 8901	555-8505	555-8509
	Braven	Mary	600 Industrial Parkway	Columbus	OH	43031- 9012	555-7002	555-7009
	Mackintosh	Helen	2584 Meyer Blvd.	Kansas City	OH	43132- 0123	555-9414	555-9419

Figure 4: CLIENT Data

Requirement 7: Create a form using the layout shown in Figure 5 to enter the PET data. The order of fields should be as shown in the figure. The ClientNo field in the form must use a Combo Box to look up the Client table. You should be able to select a client using the client information (last name and first name) and have the ClientNo inserted in the Pet table.

Figure 5: Pet Data Entry Form

Requirement 8: Using the data given in Figure 6 and the form you have just created (Figure 5), enter the pet data

PetNo.	PetName	CLastName	CFirstName	WalkerNo.	QuotedPrice	WalkTimeCode	EnrollmentDate	Comments
1	Ice	Kalvert	Traci		$8.00		06/10/2014
2	Babu	Lang	Brad		$9.00		06/15/2014
3	Mani	Larson	Kristen		$10.00		06/20/2014
4	Ralph	Martin	Jerry		$8.00		06/25/2014
5	Jimmy	Hopper	Jeff		$8.00		06/30/2014
6	Slinky	Hubert	Holly		$7.00		07/05/2014
7	Slimy	Hubert	Holly		$7.00		08/05/2014
8	Balu	Bouchart	Bob		$9.00		07/10/2014
9	Louis	Bouchart	Bob		$8.00		08/10/2014
10	Luci	Margolis	Karen		$11.00		07/15/2014
11	Baghee	Margolis	Karen		$11.00		09/15/2014
12	Tommy	Braven	Mary		$10.00		06/20/2014
13	Sheri	Braven	Mary		$8.00		07/20/2014
14	Raven	Braven	Mary		$9.00		08/20/2014
15	Snow	Mackintosh	Helen		$7.00		06/25/2014
16	Thunder	Mackintosh	Helen		$11.00		08/25/2014
17	Windy	Mackintosh	Helen		$9.00		09/25/2014

Figure 6: PET Data

With the creation of CLIENT and PET tables, creation of Client Data Entry Form and Pet Data Entry Form, and the entering of the data in the respective table, the database is ready for some query processing and reporting.

Requirement 9: Prepare a report of all clients as of today (report creation date). Name the report Client List. Use Figure 7 to decide the report layout and the needed fields. For client names, you can print the last name and first name separately but adjacent to each other. Produce the report in the ascending order of client's last name. Test the correctness of your report by verifying the shown report contents against the expected report contents.

Snoopy Dog Walker Inc.
Client List
Customer Last Name	Customer First Name	Address	City	Phone

Figure 7: List of Clients

Requirement 10: Produce the following query:

a. Find the clients, who enrolled a dog before 08/12/2014. Include the client's Client No., Clastname, Cfirstname, and Caddress. Save the query as CLIENTS ENROLLED BEFORE 08/12/2014.

b. Produce a report from the above query. Use Figure 8 to decide the report layout and the needed fields. Produce the report in the ascending order of client's last name. Test the correctness of your report by verifying the shown report contents against the expected report contents.

Snoopy Dog Walker Inc.
Clients Enrolled Before 08/12/2014
Client No.	Customer Last Name	Customer First Name	Address

Figure 8: List of Clients Enrolled Before 08/12/2014

Requirement 11: Produce the following query:

a. Find the clients living in Kansas City. Include the client's Client No., Clast name, Cfirst name, Caddress, the name(s) of their pet(s), and their respective Quoted Prices. Save the query as CLIENTS in KANSAS CITY.

b. Produce a report from the above query as shown in Figure 9. The report should be grouped by ClientNo, ClastName, and Cfirstname. Find the sum of Quoted Price for each client. Use Block layout.

	Snoopy Dog Walker Inc.
	Clients in Kansas City
Client No.	Customer Last Name	Customer First Name	Address	Pet name	Quoted Price

Figure 9: Clients in Kansas City