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 Key:Location_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 < 100 < 100 < 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 <= 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 Descriptions:Observation_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