Tavana : ACC-1

Database Management System

ACCESS Applications

• Creating A Database

• Creating Tables

• Creating Relationships

• Creating Queries

• Practice Problem

• Database Problems

Tavana : ACC-2

1

2

3

Creating a Database

Data Types:

Text (Text and Numbers not used in calculations, up to 255 characters)Memo (Area that allows lengthy notes or comments)Number (Data for mathematical calculations, except money)Date/Time (Dates and times)Currency (Data for currency calculations to avoid rounding errors)

Auto Number (Sequential numbers automatically inserted, often used for primary key)Yes/No (Fields contain only one of two values)OLE Objects (Objects from other programs linked or embedded in a form or report)

Note: To Create a Primary Key Click on a ROW and then Click on the PRIMARY KEY Icon. For more than one key click on one row, hold CTRL key down and then click on the PRIMARY KEY icon.

Tavana : ACC-3

1

2

3

Creating Tables

Tavana : ACC-4

1

2

Creating Relationships

Tavana : ACC-5

1

2

Creating Queries

customer# customer name part# part description quantityC4 Carter P7 Pin 5C4 Carter P2 Nut 100C2 Carter P2 Nut 200C8 Brown P4 Nut 5

Tavana : ACC-6

ORDER

Customer Order Practice ProblemTables

Query

customer# customer nameC4 CarterC2 CarterC8 Brown

part# part descriptionP7 PinP2 NutP4 Nut

customer# part# quantityC4 P7 5C4 P2 100C2 P2 200C8 P4 5

PART

CUSTOMER-PART

CUSTOMER

CUSTOMER (customer# , customer name) PART (part# , part description)

CUSTOMER-PART (customer# , part# , quantity)

Tavana : ACC-7

EMPLOYEE employee# employee name qualification

E1 Smith B.S.

E1 Smith M.S.

E1 Smith Ph.D.

E2 Jones B.A.

E3 Jones B.A.

E3 Jones Ph.D.

Employee Qualification Practice ProblemTables

Query

employee# employee name

E1 Smith

E2 Jones

E3 Jones

employee# qualification

E1 B.S.

E1 M.S.

E1 Ph.D.

E2 B.A.

E3 B.A.

E3 Ph.D.

NAME QUALIFICATIONS

NAME (employee#, employee name)

QUALIFICATIONS (employee#, qualification)

Tony Gambino just recently opened a new casino in Happyville and has been making a

great deal of money from the casual gamblers and the day-trippers. However, he has

become concerned about several players who seem to spend a lot of time in his

casino. Tony is also concerned that some of the recently hired dealers might be

associated with these players and helping them cheat. He started a database to track

these players, which games they are playing, which dealers are working those games,

where in the casino the games are being played, and how much the player wins or

loses. As he began to enter this information into a single table given below, he realized

that he was wasting time with redundant data entry. Help Tony identify redundant

attributes and design an efficient database using ACCESS.

Tavana : ACC-8

Player# Player Name Game# Game Name Dealer# Dealer Name Yrs of Exp Date Win/Loss

1000 Lucky 10 Black Jack 300 Bob 10 4/1/2010 -300

2000 Bugsy 20 Craps 100 Jim 2 4/2/2010 500

3000 Bruno 10 Black Jack 400 Bill 10 4/3/2010 -1250

2000 Bugsy 30 Roulette 300 Bob 10 4/8/2010 -900

3000 Bruno 30 Roulette 100 Jim 2 4/6/2010 1000

1000 Lucky 20 Craps 200 Joe 20 4/3/2010 -250

4000 Mikey 40 Poker 200 Joe 20 4/1/2010 750

Happyville Casino

Explorer Healthcare needs to collect information concerning procedures performed by

each provider on a patient by patient basis. The table presented below shows sample

data needed by Explorer. Identify attribute types with redundancies and develop an

efficient DBMS with a query producing the following table using ACCESS.

Physician# Physician Patient# Patient Procedure# Procedure Insurance Co# Insurance Co Fee

10 Dr. Smith 111 Davis 1000 Office Visit BC Blue Cross 40.00

10 Dr. Smith 333 Stone 1000 Office Visit US US Healthcare 40.00

20 Dr. Fox 111 Davis 3000 X-Ray BC Blue Cross 80.00

30 Dr. Jones 222 Kelly 4000 Check-up KS Keystone 60.00

20 Dr. Fox 222 Kelly 3000 X-Ray BC Blue Cross 80.00

Tavana : ACC-9

Explorer Healthcare

Physician# Physician Patient# Patient Procedure# Procedure Insurance Co# Insurance Co Fee

10 Dr. Smith 111 Davis 1000 Office Visit BC Blue Cross 50.00

10 Dr. Smith 333 Stone 1000 Office Visit US US Healthcare 40.00

20 Dr. Fox 111 Davis 3000 X-Ray BC Blue Cross 80.00

30 Dr. Jones 222 Kelly 4000 Check-up KS Keystone 60.00

20 Dr. Fox 222 Kelly 3000 X-Ray BC Blue Cross 80.00

Tavana : ACC-9

If the fee in the first row is changed to 50.00, would the solution change?

Explorer Healthcare

Great Times needs a DBMS for their summer camp program to keep track of the trips,

persons picking up the campers, contact information, trip information, and date. The

table presented below shows sample data needed by Penny Creek. Identify attribute

types with redundancies and develop an efficient DBMS with a query producing the

following table using ACCESS.Camper# Camper Name Camper Address Pickup# Pickup Name Pickup Phone Location# Location Contact Trip Date

1 Kathy Kelly 10 Main St. 22 Mary Kelly 1111 111 Zoo Mr. Keeper 3/10/2010

3 Sam Jones 60 State Rd. 33 Jeff Jones 3333 222 Bowling Ms. Lane 3/20/2010

1 Kathy Kelly 10 Main St. 11 Jim Kelly 1111 222 Bowling Ms. Lane 3/20/2010

1 Kathy Kelly 10 Main St. 11 Jim Kelly 1111 333 Dorney Park Mr. Wetguy 3/5/2010

2 Randy Kelly 10 Main St. 22 Mary Kelly 1111 111 Zoo Mr. Keeper 3/10/2010

2 Randy Kelly 10 Main St. 11 Jim Kelly 1111 333 Dorney Park Mr. Slide 3/5/2010

5 Mira Davis 50 Row St. 33 Jeff Jones 3333 111 Zoo Mr. Keeper 3/15/2010

3 Sam Jones 60 State Rd. 33 Jeff Jones 3333 111 Zoo Mr. Keeper 3/15/2010

4 Joe Kane 30 Jungle Ave. 44 Cindy Kane 4444 222 Bowling Mr. Pin 3/20/2010

5 Mira Davis 50 Row St. 33 Jeff Jones 3333 111 Zoo Mr. Keeper 3/25/2010

Tavana : ACC-10

Great Times Summer Camp

Tavana : ACC-11

Jimmy “The Body Bag” Biggs was a fledgling, small time hit man. All his life his

goal was to move to the Big Apple and somehow get his foot in the Mob Door.

The problem was that Jimmy didn’t have any money. So for years his saved

money from his job at the local drive-in theater and computer repair shop,

hoping that one day he could fulfill his dream.

Then, one day, as Jimmy polished his guns, he heard on the news that a mob

hit man had “disappeared.” This was Jimmy’s chance. So the next day he

packed up his 1986 Camaro and drove to New York.

Once there Jimmy quickly became fast friends with Timmy “The Tuna”

Randazo. The Tuna had connections and told Jimmy that one of the reasons the

other hit man disappeared was that he had gotten sloppy. He didn’t keep good

records of his hits, which caused problems when The Boss needed to collect.

Jimmy told The Tuna that not only was he an excellent shot, but he was also

proficient in dealing with databases. He told The Tuna that if he could get him in

front of The Boss that he could help The Family keep better records. Then,

maybe he would be trusted and get his chance to be a hit man.

Jimmy “the Body Bag” Biggs

Tavana : ACC-12

Client# Client Client Address Weapon# Weapon Location# Location Victim# Victim Date of Hit Results

1 Rocco 123 Killerville Ave 11 Fish Wire 21 Ship Yard 100 Slim Sammy 2/15/2004 Hit

2 Tony the Tiger 33 Kelloggs Street 12 Ice Pick 22 Airport 110 The Bopper 3/12/2004 Miss

2 Tony the Tiger 33 Kelloggs Street 11 Fish Wire 23 Sports Complex 120 Fat Frank 6/10/2004 Miss

4 Little Al 71 Tin Roof 14 Knife 24 Ben Franklin Bridge 125 Bones McGee 8/12/2004 Hit

1 Rocco 123 Killerville Ave 13 Gun 22 Airport 115 Calm Clint 9/2/2004 Hit

3 Lovely Linda 44 Hotts Road 12 Ice Pick 21 Ship Yard 130 Big Daddy Don 9/18/2004 Hit

4 Little Al 71 Tin Roof 14 Knife 23 Sports Complex 165 Big Blue 10/1/2004 Hit

2 Tony the Tiger 33 Kelloggs Street 11 Fish Wire 23 Sports Complex 120 Fat Frank 10/15/2004 Hit

3 Lovely Linda 44 Hotts Road 13 Gun 23 Sports Complex 140 Slick Rick 11/10/2004 Hit

Jimmy got the meeting and quickly was told to create a database for The

Boss. Help Jimmy identify the redundant attributes in his table and design an

efficient database management system for The Boss.

Jimmy “the Body Bag” Biggs

Selling Mary Kay brand cosmetics and beauty aids was Jenny Jones’s passion in life. Working with

Mary Kay for over 10 years, Jenny had earned herself many accolades. She served one of the

largest and most lucrative suburban areas of all Mary Kay sales technicians, the Newtown,

Pennsylvania through Princeton, New Jersey area. Jenny was nationally known throughout the most

astute Mary Kay circles as being one of the company’s top sellers. However, there was one “brass

ring” that Jenny had yet to attain...the coveted BIG PINK CAR. In case you are not familiar with this

prestigious award, the BIG PINK CAR is only awarded to the Mary Kay sales technician with the

highest sales over a fiscal period. It is awarded at a national convention where all the Mary Kay

bigwigs hang out. To Jenny, this was her year to go for the gold, or the pink if you will. Jenny had no

problem upping her sales to all time high levels and she was well aware of the competition. Knowing

that the only way she could lose would be to beat herself, Jenny set out to determine how she

needed to clean up her own act to bring home the pink car.

After performing a detailed evaluation of all of her strengths and weaknesses, Jenny discovered that,

in order to win the award, she would have to become more organized. Presently, Jenny maintained

all of her sales records on manually generated carbon sheets. However, Jenny knew that in order to

keep up with Mary Kay’s high standards and adequately support her claim to the best ride in town,

she would have to improve her record-keeping. Jenny decided to use Microsoft Access to maintain

her sales records. Each time Jenny made a sale, specific information such as customer name,

customer number, address, level of skin sensitivity, order number, product number ordered, product

name, unit price, quantity ordered, catalog number ordered from and catalog edition was stored in

an Access table. Jenny soon realized that her table included many redundancies. Help Jenny

identify the redundant attributes in her table and design an efficient database management system.

Tavana : ACC-13

Mary Kay Cosmetics

Tavana : ACC-14

Order# Date Customer# Name Address Sensitivity Product# Product Quantity Unit Price Catalog# Edition

34 4/16/2010 270 Nancy Miller 6 Hollywood Dr Low 220Ruby red long-

lasting lipstick5 25 1215 Christmas in the City!

35 4/22/2010 210 Crissy Smith 20 Park Ave. High 310 Astringent 2 18 1215 Christmas in the City!

36 4/28/2010 250 DeeDee Jones 54 Candle Dr. Low 340

Neutral hues

cream eye

shadow

1 15 1216 Beating Old Man Winter!

37 5/2/2010 410 Gerry McMann 9 Willow Dr. Average 560Anti-aging

cream4 19 1217 Spring has Sprung!

38 5/10/2010 400 Gisele Hughs 89 Winder Dr. Low 640Sparkles body

glitter7 28 1215 Christmas in the City!

39 4/6/2010 300 Jenny Lowt 43 Summit Rd. High 760Exfoliating

cream1 12 1218 Here Comes Summer!

40 5/12/2010 300 Jenny Lowt 43 Summit Rd. High 980Make-up

remover pads2 12 1218 Here Comes Summer!

41 5/18/2010 200 Jill Worth 111 Main St High 220Ruby red long-

lasting lipstick2 16 1216 Beating Old Man Winter!

42 6/1/2010 100 Mary Decker 234 South St. Average 310 Astringent 3 18 1216 Beating Old Man Winter!

42 6/1/2010 100 Mary Decker 234 South St. Average 340

Neutral hues

cream eye

shadow

2 15 1217 Spring has Sprung!

43 6/10/2010 210 Crissy Smith 20 Park Ave. High 310 Astrigent 2 18 1215 Christmas in the City!

Mary Kay Cosmetics

Tavana : ACC-15

Mary Kay Cosmetics

Tavana : ACC-16

Mary Kay Cosmetics