CN o CLastNa me CFirstNa me CAddre ss CCity CStat

fantasicgilamonsterData Management

Nov 20, 2013 (3 years and 8 months ago)

1,052 views

1.

(10 points) What types of relationships are possible in a relational database? Describe and
give an example of each. Identify and describe the three basic operations used to extract
useful sets of data from a relational database?

2.

(10 points) List and describe three main capabilities or tools of a DBMS. List at least two
ways that a business's data can become redundant or inconsistent.

3.

(10 points) What are the differences between data mining and OLAP? What makes data
mining an impo
rtant business tool? What types of information does data mining produce?
In what type of circumstance would you advise a company to use data mining? When
would you advise a company to use OLAP?

4.

(15 points) Achieving Operational Excellence: Building a Rela
tional 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
ow
ners. 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 a
verage, 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 pet(s), chooses a preferred walk time for his 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 var
ies 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 t
hat 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, create se
veral relationships, design Client and Pet forms, design
Walker Schedule and Client List report, and construct several queries.

Table 1: KoKo’s Canine Pet Club Client Data

CN
o

CLastNa
me

CFirstNa
me

CAddre
ss

CCity

CStat
e

CZi
p

CPho
ne

CEmergencyPh
one


000
1

Lee

Susan

101
Sunnyvil
le Lane

Edmond

OK

7300
3

606
-
8975

606
-
5039

000
Stone

David

1408
Yukon

OK

7306
899
-
606
-
3402

2

Peter
Pan
Drive

9

8182

000
3

Yu

William

7120
Lakeridg
e

Midwest
City

OK

7309
9

899
-
4979

899
-
2222

000
4

Monac

Levitica

303
Northrid
ge

Edmond

OK

7309
9

899
-
2395

909
-
8679

000
5

Ruaz

Monica

1701
Memoria
l Road

Oklaho
ma City

OK

7300
1

905
-
8440

606
-
4102

000
6

Barker

Gayle

1983
Sliding
Glass

Edmond

OK

7303
4

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

PetN
o

PetNam
e

CN
o

WalkerN
o

QuotedPric
e

TimeCod
e

EnrollmentDat
e

Comments

0001

Mickey

000
1

W001

$7.00

1

6/1/03

Is a very
friendly,
well
-
behaved
poodle

0002

Fiedo

000
1

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

000
5

W002

$10.00

2

7/12/03

Is a
sensitive
Pomeranian
. Needs lots
of attention

0004

Sir
Lancelot

000
5

W002

$7.00

2

7/12/03

Is a playful
German
Shepard,
but is
hesitant
around
strangers

0005

Bruno

000
2

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

000
3

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

000
4

W004

$7.00

3

8/22/03

Is a mixed
-
breed. Very
playful

0008

Bear

000
W004

$8.00

3

8/22/03

Is a Black
4

Labrador

0009

Gretchen

000
4

W004

$8.00

3

8/22/03

Is a Black
Labrador

0010

Prancy

000
6

W004

$8.50

5

8/13/03

Prancy can
be
aggressive
towards
other
animals

0011

Lacy

000
6

W004

$8.50

5

8/13/03

Is very
affectionate
. Lacy will
run, if she
gets off of
her leash

0012

Dazzle

000
6

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 and that the client identification number serves as the primary key. The
Walker table stores basic information about each walker and the Walk Times tabl
e 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 e
ach 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 Pe
t 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 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 t
he
contact information about the client. The form header includes the service's name, the
form's name, and graphics. 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 requests Walker Schedule and Client List reports. The Walker Schedule r
eport 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 build a select
query, and then base the report on the select query. The Walker Schedule
report header
contains the service's name, a report title, the current date, and graphics. 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 service's c
urrent 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 service's name, report name, current
data, and graphics. To maintain a consistent appe
arance with the Walker Schedule report,
you use a report style similar to the Walker Schedule report. While you are free to work
with the design of the forms and reports, each form and report should have a consistent,
professional appearance.

Based on the

requirements in this assignment, you build a database, which contains four
tables, two forms, and two reports.

Upload your work (one Word file and one Access file) by the midnight of the due date.