Step 1:  Identify TYPES of data you need (Field Names)

 

First

Last

Middle

Street

City

State

Zip

Phone

WorkPhone

PetName

Species

Canine Breed

Feline Breed

Gender

Spay/Neuter

Color

Notations

Deceased

Procedure

PreferredAnesthetic

ChargeDifficulty1

ChargeDifficulty2

ChargeDifficulty3

Date

Vaccination

Temp

Weight

Parvo

Worms

Nails Clipped

Teeth

Date

Difficulty

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


 

 

2.  Group these for the least amount of data repetition around a singular “topic”:

First                                                     Sandra

Last                                                      Summers

Middle                                                 A.

Street

City                                                      Pasadena

State                                                    TX

Zip                                                       77505 

Phone                                                  

WorkPhone                                         

PetName                                              PeekABoo

Species                                                Canine

Canine Breed                                       Pomeranian

Feline Breed

Gender                                                 M

Spay/Neuter                                         N

Color                                                   Grey

Notations                                             I want a lot of room to write here

Deceased                                             unchecked

Procedure                                            Spaying

PreferredAnesthetic                              KnockEmOutCold

ChargeDifficulty1                                  $50

ChargeDifficulty2                                  $150

ChargeDifficulty3                                  $200

Date

RabiesVaccination                                checkmark, information about mfg, date, batch#

Temp                                                   102

Weight                                                 12

Parvo                                                   check

Worms                                                 no

Nails Clipped                                       yes      

Teeth                                                    some tartar, but not much

Difficulty                                               level1

 

 

 

 

 

 

 

 

 

 

 

 


3.  Insert or define an UNIQUE Identifier

 

First

Last

CustID

 
 Middle

Street

City

State

Zip

Phone

WorkPhone

PetName

PetID

 
Species

Canine Breed

Feline Breed

Gender

Spay/Neuter

Color

Notations

Deceased

Date

VisitID

 
Vaccination

Temp

Weight

Parvo

Worms

Nails Clipped

Teeth

Difficulty

 

ProcedureID

 
Procedure

PreferredAnesthetic

ChargeDifficulty1

ChargeDifficulty2

ChargeDifficulty3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Determine Primary Keys for each Table:

 

CustID

PetID

VisitID

ProcedureID

First

Name

Date

Procedure

 Last

Species

 Vaccination

PreferredAnesthetic

 Middle

Canine Breed

 Temp

ChargeDifficulty1

 Street

Feline Breed

 Weight

ChargeDifficulty2

 City

Gender

 Parvo

ChargeDifficulty3

 State

Spay/Neuter

 Worms

Date

 Zip

Color

Nails Clipped

Difficulty

 Phone

Notations

Teeth

 

 WorkPhone

Deceased

Procedure1

 

 

 

Procedure2

 

 

 

Procedure3

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Connect Tables by adding Primary Keys to other tables as Foreign Keys:

 

CustID

PetID

VisitID

ProcedureID

First

Name

Date

Procedure

 Last

Species

 Vaccination

PreferredAnesthetic

 Middle

Canine Breed

 Temp

ChargeDifficulty1

 Street

Feline Breed

 Weight

ChargeDifficulty2

 City

Gender

 Parvo

ChargeDifficulty3

 State

Spay/Neuter

 Worms

Date

 Zip

Color

Nails Clipped

Difficulty

 Phone

Notations

Teeth

 

 WorkPhone

Deceased

Procedure1

 

 

 

Procedure2

 

Which One?

OR

Procedure3

 

PetID

CustID

 

 

 

 

 

Think: 

 

For ONE Pet, Are there are Multiple Customers? 

 

No, each pet has ONE owner. 

 

However, each ONE Owner might have Multiple Pets! 

 

So put the CustID on the Pet Table. 

 

 

The Primary Key from the Customer Table (ONE SIDE)

 

is added to the Pet’s Table  (MANY SIDE)    

 

where it is called a Foreign Key. 

 

As a Foreign Key, it is able to be placed on that table as many times as needed. 

 

Only one field “spot” is required to hold it, even though it is there multiple times. 
Connect Tables by adding Primary Keys to other tables as Foreign Keys:

 

CustID

PetID

VisitID

ProcedureID

First

Name

Date

Procedure

 Last

Species

 Vaccination

PreferredAnesthetic

 Middle

Canine Breed

 Temp

ChargeDifficulty1

 Street

Feline Breed

 Weight

ChargeDifficulty2

 City

Gender

 Parvo

ChargeDifficulty3

 State

Spay/Neuter

 Worms

Date

 Zip

Color

Nails Clipped

Difficulty

 Phone

Notations

Teeth

 

 WorkPhone

Deceased

Procedure1

 

 

 

Procedure2

 

 

 

Procedure3

 

 

CustID

 

 

 

Repeat the above process for the other tables:  Join each to another table by foreign keys. 

 

 

 

 

Homework:  Copy this procedure and create the tables, fields, and joins for your semester project.

 

Minimum of 3 tables, 5 or more fields per table, some Lookup Tables (list of cities)

 

Input NO DATA at this time! 

 

Turn this in electronically in WORD or EXCEL. 

 

All files must have your name and a description of the item. 

 

SKennedyTablesFieldsJoins.xls