Designing Databases for Biological Research Brian R Mitchell

Designing Databases for Biological Research Brian R. Mitchell Fall 2006

Exercise – Bird Count Database Tables Species Location Visit Observations Fields Species_ID, Common_Name Location_ID, Forest, Grassland, Developed X_Coord, Y_Coord Visit_ID, Location_IDF, Visit_Time, Visit_Date, Wind, Sky, Count 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)

Bird Count Database - Grid HOW ARE THE TABLES RELATED? What is the key? Species Location Visit Survey Location NO N/A -- -- Visit NO Observations 1 - Many N/A Loc - Vis Key: Location_ID 1 - Many NO 1 - Many Sp – Obs Vis - Obs Key: Species_ID Key: Visit_ID -N/A

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

Bird Count Database - Species Document the Species Field Names: Species_ID Common_Name Field Descriptions: Species_ID: Primary Key Common_Name: AOU accepted common name Data Type: 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. : Count 1, Count 2, Count 3, or Species 1, Species 2, Species 3)? NO Are there any rules (constraints based on more than a single field) that relate to information in this table? NO

Bird Count Database - Location Document the Location Table Name: Table Description: tbl_Locations where counts are conducted Location_ID, Forest, Grassland, Developed, X_Coord, Y_Coord Table Fields: Relationships: 1 – Many with Visit, based on Location_ID

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 Integer Constraints: 5 chrs < 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

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

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 Constraints: Text Date/Time 5 char Date/Time Byte 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

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

Bird Count Database Observations Document the Observations 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: Constraints: Auto Integer Byte 0 -11 Text 4 char Text 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