Exercise

1
Exercise Bird Count Database

• Tables Fields
• Species Species_ID, Common Name
• Location Habitat, Coordinates
• Visit Time, Weather, Date
• Survey Minute, species, visual/auditory
• Multiple visits to each location
• 10 minute count during each visit listens for
  each individual of each species that can see or
  hear, records species, minute during count,
  visual/auditory
• Only one observer (otherwise an observer table
  would be needed)

2
Bird Count Database - Grid

• HOW ARE THE TABLES RELATED?
• What is the key?
• Species Location Visit Survey
• Location N/A -- --
• Visit N/A --
• Survey N/A

3
Bird Count Database - Species

• Document the Species Table
• Table Name
• Table Description
• Table Fields
• Relationships

4
Bird Count Database - Species

• Document the Species Fields
• Field Names
• Field Descriptions
• Data Type
• Constraints
• Is there a primary key?
• Is any data duplicated (value depends only on a
  field other than the primary key), indicating the
  need for a separate table?
• Are there any groups of fields (e.g. Count1,
  Count2, Count3, or Species1, Species2, Species3)?
• Are there any rules (constraints based on more
  than a single field) that relate to information
  in this table?

5
Bird Count Database - Location

• Document the Location Table
• Table Name
• Table Description
• Table Fields
• Relationships

6
Bird Count Database - Location

• Document the Location Fields
• Field Names
• Field Descriptions
• Data Type
• Constraints
• Is there a primary key?
• Is any data duplicated (value depends only on a
  field other than the primary key), indicating the
  need for a separate table?
• Are there any rules (constraints based on more
  than a single field) that relate to information
  in this table?

7
Bird Count Database - Visit

• Document the Visit Table
• Table Name
• Table Description
• Table Fields
• Relationships

8
Bird Count Database - Visit

• Document the Visit Fields
• Field Names
• Field Descriptions
• Data Type
• Constraints
• Is there a primary key?
• Is any data duplicated (value depends only on a
  field other than the primary key), indicating the
  need for a separate table?
• Are there any rules (constraints based on more
  than a single field) that relate to information
  in this table?

9
Bird Count Database - Survey

• Document the Survey Table
• Table Name
• Table Description
• Table Fields
• Relationships

10
Bird Count Database - Survey

• Document the Survey Fields
• Field Names
• Field Descriptions
• Data Type
• Constraints
• Is there a primary key?
• Is any data duplicated (value depends only on a
  field other than the primary key), indicating the
  need for a separate table?
• Are there any rules (constraints based on more
  than a single field) that relate to information
  in this table?