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
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
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
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 |
|
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 |
|
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 |
|
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