Designing Databases for Biological Research

1
Designing Databases for Biological Research

• Brian R. Mitchell
• Fall 2006

2
Exercise Bird Count Database

• Tables Fields
• Species Species_ID, Common_Name
• Location Location_ID, Forest, Grassland,
  Developed X_Coord, Y_Coord
• Visit Visit_ID, Location_IDF, Visit_Time,
  Visit_Date, Wind, Sky, Count
• Observations Observation_ID, Visit_IDF, Minute,
  Species_ID, Detect_Type
• 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)

3
Bird Count Database - Grid

• HOW ARE THE TABLES RELATED?
• What is the key?
• Species Location Visit Survey
• Location NO N/A -- --
• Visit NO 1 - Many N/A --
• Loc - Vis
• KeyLocation_ID
• Observations 1 - Many NO 1 - Many N/A
• Sp Obs Vis - Obs
• Key Species_ID Key Visit_ID

4
Bird Count Database - Species

• Document the Species Table
• Table Name tbl_Species
• Table Description List of bird species and
  common names
• Table Fields Species_ID, Common_Name
• Relationships 1 to Many with Observations,
  based on Species_ID

5
Bird Count Database - Species

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

6
Bird Count Database - Location

• Document the Location Table
• Table Name tbl_Location
• Table Description Locations where counts are
  conducted
• Table Fields Location_ID, Forest, Grassland,
  Developed, X_Coord, Y_Coord
• Relationships 1 Many with Visit, based on
  Location_ID

7
Bird Count Database - Location

• Document the Location Fields
• Field Names Location_ID, Forest, Grassland,
  Developed, X_Coord, Y_Coord
• Field Descriptions Location_ID Primary Key
• Forest forest within 300 m
• Grassland grassland within 300 m
• Developed developed within 300 m
• X_Coord East coordinate, VT state plane
• Y_Coord North coordiante, VT state Plane
• Data Type Text Real Real Real Integer Integer
• Constraints 5 chrs lt 100 lt 100 lt 100 420000
  520000 120000 - 240000
• Is there a primary key? Location_ID
• Is any data duplicated (value depends only on a
  field other than the primary key), indicating the
  need for a separate table? NO
• Are there any rules (constraints based on more
  than a single field) that relate to information
  in this table? Forest Grassland Developed lt
  100

8
Bird Count Database - Visit

• Document the Visit Table
• Table Name tbl_Visit
• Table Description A visit to a location in order
  to conduct a bird count
• Table Fields Visit_ID, Location_IDF,
  Visit_Time, Visit_Date, Wind, Sky, Count
• Relationships Many 1 with Location
• 1 Many with Observations

9
Bird Count Database - Visit

• Document the Visit Fields
• Field Names Visit_ID, Location_IDF, Visit_Time,
  Visit_Date, Wind, Sky, Count
• Field Descriptions Visit_ID Primary key
• Location_IDF Foreign key, location table
• Visit_Time time of count
• Visit_Date Date of count
• Wind Wind code (describe)
• Sky Sky code (describe)
• Count Count number for that location
• Data Type Auto Text Date/Time Date/Time
  Byte Byte Byte
• Constraints 5 char 0-4 0-4 1-3
• Is there a primary key? Visit_ID
• Is any data duplicated (value depends only on a
  field other than the primary key), indicating the
  need for a separate table? NO
• Are there any rules (constraints based on more
  than a single field) that relate to information
  in this table? NO

10
Bird Count Database - Observations

• Document the Observations Table
• Table Name tbl_Observations
• Table Description Bird detection records
• Table Fields Observation_ID, Visit_IDF, Minute,
  Species_ID, Detect_Type
• Relationships Many to 1 with Species
• Many to 1 with Visit

11
Bird Count Database - Observations

• Document the Observations Fields
• Field Names Observation_ID, Visit_IDF, Minute,
  Species_ID, Detect_Type
• Field DescriptionsObservation_ID Primary Key,
  Visit_IDF Foreign key from visit table, Minute
  Time detected during count, Species_ID code for
  bird species detected, Detect_Type (V)isual or
  (A)uditory
• Data Type Auto Integer Byte Text Text
• Constraints 0-11 4 char V or A
• Is there a primary key? Observation_ID
• Is any data duplicated (value depends only on a
  field other than the primary key), indicating the
  need for a separate table? NO
• Are there any rules (constraints based on more
  than a single field) that relate to information
  in this table? NO