BIAA Database Documentation

The Birds in Agricultural Areas database is designed in Microsoft Access and contains the following tables:

 

AuthorEditor – This table contains five columns of data which are:

  • Author/EditorID – an autonumber field which is used to uniquely identify each author and editor name.
  • CitationID – a number field which is used to associate the author or editor with the correct citation. CitationID is the primary key for the Citations Table.
  • Desig – a text field which designates whether the entry is for an author or an editor.
  • Primary – a text field which designates whether the author or editor is the primary (users need to know this because the documents are filed by primary author or editor last name).
  • LastName – a text field which contains the last name of the author or editor.
  • Initials – a text field which contains the initials of the author or editor.

 

BCR_Def – This table contains two columns of data which are:

  • BCR_ID – a number field which contains the numbers of all Bird Conservation Regions as defined by the North American Bird Conservation Initiative (NABCI).
  • BCR_Name – a text field which contains the names of all Bird Conservation Regions as defined by North American Bird Conservation Initiative (NABCI).

 

BirdCropAssoc

  • AssociationID – an autonumber field which is used to uniquely identify each bird crop association.
  • CommonName – a text field which contains the common bird name for this association. Common names are defined in the SpeciesRef table.
  • CropName – a text field which contains the crop for this association. Crops are defined in the CropNames_Def table.
  • Usage – a text field which describes how the bird uses the crop in this association. Usages are defined in the Usage_Def table.
  • CitationID – the ID of the citation which contains the bird to crop reference.
  • Comments – any special comments about this association.

 

CitationRelationships

  • CitationRel – autonumber field used to uniquely identify each relationship.
  • PrimaryCite – a number field containing the citationID for the primary citation.
  • SecondaryCite – a number field containing the citationID for the secondary citation.

 

Citations

  • CitationID – autonumber field used to uniquely identify each citation.
  • Title – a text field containing the title of the citation.
  • Year – a text field containing the publication year of the citation.
  • VolName – a text field containing the name of journal or edited volume.
  • Volume – a text field containing the volume if journal article.
  • Number – a text field containing the number if journal article.
  • Pages – a text field containing the inclusive pages of the citation
  • Publisher – a text field containing the publisher name for edited volumes or books.
  • PublisherLoc – a text field containing the publisher location for edited volumes or books.
  • DocumentFormat – a text field specifying whether ABC has the document in electronic or paper form.

 

CropNames_Def

  • CropID – a text field containing a four letter code for each crop.
  • CropName – a text field containing the names for all crops grown in the U.S. and Canada.

 

LocAssoc

  • LocAssocID – an autonumber field which serves as the unique identifier for the location associated with the AssociationID.
  • AssociationID – a number field containing the AssociationID for this bird crop association. AssocationID is defined in the BirdCropAssoc table.
  • StateID – a text field containing the name of the state (or Canadian Province) for this bird crop association. States are defined in the LocDef table.
  • CountyID – a text field containing the name of the county (if any) for this bird crop association. Counties are defined in the LocDef table.
  • LocDesc – a text field containing any other geographical location data about this bird crop association such as city or wildlife refuge.
  • BCR_ID – the BCR number associated with the location for this bird crop association.

 

LocDef

  • StateID – a text field which contains the two letter designations for all states and Canadian provinces.
  • CountyID – a text field which contains the names of all counties in the U.S.
  • FIPS – a text field containing the Federal Information Processing Standard (FIPS) code for all U.S. locations. These codes standardize identification of geographic locations within the U.S.
  • Acres – a number field containing the acres associated with each FIPS code.
  • Latitude – a number field containing the latitude associated with the FIPS code.
  • Longitude – a number field containing the longitude associated with the FIPS code.

 

MonthAssoc

  • MonthAssocID – an autonumber field which serves as the unique identifier for the months associated with an AssociationID.
  • AssociationID - a number field containing the AssociationID for this bird crop association. AssocationID is defined in the BirdCropAssoc table.
  • MonthID – a text field containing the name of the month for this association.

 

MonthDef

  • MonthID – a text field containing the name of each month of the year.
  • SeasonID – a text field which assigns each month to a season (Sep, Oct, Nov = fall, Dec, Jan, Feb = winter, Mar, Apr, May = spring, Jun, Jul, Aug = summer).

 

SpeciesRef

  • CommonName – a text field which contains the common names of all bird species accepted by AOU.
  • ScientificName – a text field which defines the scientific name for each common nam.
  • Family – a text field which contains the family for each common name.
  • Order – a text field which contains the order for each common name
  • ESA_listed – a text field specifying yes if the species is listed under the endangered species act and no if it is not.
  • ESA_status – a text field specifying if the species is threatened or endangered if it is listed on the ESA.
  • ESA_year – a text field containing the year the species was listed on the ESA.
  • Reference – a text field containing the reference which defines the common name.
  • REF_year – a number field containing the year of the reference which defines the common name.
  • GreenList – a text field specifying whether the bird is listed on ABC’s Green List or not.
  • GreenList_Status – a text field which specifies, if the species is listed on the green list, which of three Greenlist categories the species falls into: (1) the highest continental concern, (2) moderately abundant species with declines or high threats, or (3) species with restricted distributions and low population size.
  • PIF_Score – a number field containing the Partner’s in Flight (PIF) combined species assessment score for each species. These scores to objectively evaluate the conservation status of each bird species in North America.

 

StateDefs

  • StateID - a text field which contains the two letter designations for all states and Canadian provinces.
  • StateName – a text field which contains the full name of all states and Canadian provinces.

 

UsageDef

  • Usage – a text field which defines the usages (brood rearing, cover, edge, foraging, incidental, loafing, nesting, roosting).
  • UsageDesc – a text field which defines the usages.

Appendix A contains a diagram of the relationships in the Birds in Agricultural Areas Database.

 

Technical Information for Using the Offline BIAA Database

Queries – all queries are designed to support reports and should not be executed directly by the user.

  • Bird Crop Associations by Bird – supports the report of the same name.
  • Bird Crop Associations by Crop – supports the report of the same name.
  • Bird Crop Associations by BCR – supports the report of the same name.
  • Bird Crop Associations by State – supports the report of the same name.
  • Citation Search by Citation ID – supports the report of the same name.
  • qryBCR – returns a list of BCRs for which there is information in the database.
  • qryBirdCropAssociationsAll – returns all bird crop associations.
  • qryCitationRelationships – determines whether there is a citation relationship.
  • qryCitations – returns the current list of citation IDs.
  • qryCitationsAll – returns the current list of citations with citation information.
  • qryConservationStatus – returns the conservation status of a bird.
  • qryCrop – returns a list of crops for which there is information in the database.
  • qrySpecies – returns a list of birds for which there is information in the database.
  • qrySpeciesAll – returns a list of all birds defined by the AOU.
  • qryState – returns a list of states/provinces for which there is information in the database.
  • StateQuery – returns a list of all state/province names.

Forms – used to enter data. Citations must be added using the EnterCitations form before data can be added with the EnterBirdCropAssoc form. The CitationID obtained from the EnterCitations form is required to enter bird crop associations in the EnterBirdCropAssoc form. To enter a citation relationship, first enter both citations using the EnterCitations form and then establish

  • CitationRelationships – used to enter citation relationships. Fields are primary citationID and secondary citationID.
  • EnterCitations – used to enter citation data. Fields include citation ID (automatically generated), title, year, volume name, volume, number, pages, publisher, publisher location, document format (either paper or electronic). The Author/Editor subform allows you to input multiple authors or editors per citation. It includes an author/editor designation field, a primary author designation field, and last name and initials fields.
  • EnterBirdCropAssoc – used to enter bird crop associations, required fields include common name, usage, crop name, citation id (obtained from the EnterCitations form), state or province, BCR, and month(s). Other fields include county and location description (for additional location information such as city or wildlife refuge).
  • AuthorEditor Subform – used as a subform on the EnterCitations form for entering author and editor data. Should not be used directly.
  • LocAssoc Subform – used as a subform on the EnterBirdCropAssoc form for entering location data (state, county, location description and BCR). Should not be used directly.
  • MonthAssoc Subform – used as a subform on the EnterBirdCropAssoc form for entering month data. Should not be used directly.
  • frmSelectBCR – presents a list of available BCRs to select from when running the Bird Crop Associations by BCR report.
  • frmSelectCitation – presents a list of available citations to select from when running the Citation Search by Citation ID report.
  • frmSelectCrop – presents a list of available crops to select from when running the Bird Crop Associations by Crop report.
  • frmSelectSpecies – presents a list of available species to select from when running the Bird Crop Associations by Bird report.
  • frmSelectSpeciesAll – presents a list of all species to select from when running the Conservation Status report.
  • frmSelectStateProv – presents a list of available State/Provinces to select from when running the Bird Crop Associations by State report.
  • SwitchBoard – presents the opening screen when starting the database which provides a selection between Entering a Citation, Entering a Bird Crop Association, Running a Report, and Managing the Database.
  • ReportSwitchBoard – presents a selection screen of the different reports available.

Reports

  • Bird Crop Associations by Bird – presents a list of bird names which have data in the database and returns crop associations, usages, seasons, comments, and citation ID.
  • Bird Crop Associations by Crop – presents a list of crop names which have data in the database and returns common name, usages, seasons, citation ID.
  • Bird Crop Associations by BCR – presents a list of BCRs which have data in the database and returns common name, crop, usages, seasons, and citation ID.
  • Bird Crop Associations by State – presents a list of states and provinces which have data in the database and returns common name, crop, usages, seasons, and citation ID.
  • Citation Search by CitationID – presents the list of citationIDs in the database and returns article title, year, volume name, volume, number, pages, document format, authors and which author is the primary.
  • Conservation Status – presents a list of bird species defined by the AOU and returns the ESA status (endangered, threatened or none), ABC Green List status (yes or no), Partner's In Flight (PIF) combined species assessment score, and conservation priority (high, moderate, or low). The conservation priority ranking is based on the PIF scores.
 

Appendix A

Relationships in the Birds in Agricultural Areas Database

 

BIAA Database table diagram

 

Appendix B – Birds in Agricultural Areas Database Valid Crop Names

Agricultural Lands

Alfalfa

Almond

Apple

Apricot

Aquaculture

Artichoke

Asparagus

Austrian winterpeas

Avocado

Banana

Barley

Beet

Blackberry

Blueberry

Broccoli

Brussel sprouts

Buckwheat

Cabbage

Canola

Canteloupe

Carrot

Cauliflower

Celery

Cherry

Chicory

Christmas trees

Citrus

Coffee

Collards

Corn

Cotton

Cranberry

Crawfish

Cucumber

Currant

Dill

Eggplant

Endive

Fig

Filbert

Flaxseed

Garlic

Ginger root

Ginseng

Grains

Grape

Grapefruit

Grass Seed

Guar

Guava

Hay

Hazelnut

Herbs

Honeydew

Hops

Hot pepper

Irish potato

Jojoba

Kale

Kiwifruit

Kumquat

Lemon

Lentils

Lettuce

Lima bean

Lime

Loganberry

Lotus root

Macadamia nut

Mango

Millet

Mint

Mung bean

Mustard greens

Mustard seed

Nectarine

Oats

Okra

Olive

Onion

Orange

Orchards

Papaya

Parsley

Passion fruit

Peach

Peanut

Pear

Peas

Pecan

Persimmon

Pimiento

Pineapple

Pistachio

Plum

Pomegranate

Popcorn

Proso millet

Prune

Pumpkin

Radish

Rapeseed

Raspberry

Rhubarb

Rice

Romaine

Row Crops

Rye

Safflower

Snap bean

Sod

Sorghum

Soybean

Spinach

Squash

Strawberry

Sugarbeet

Sugarcane

Sunflower

Sweet corn

Sweet pepper

Sweet potato

Tangelo

Tangerine

Taro

Tobacco

Tomato

Triticale

Turnip

Walnut

Watercress

Watermelon

Wheat

Wild rice



Appendix C – Birds in Agricultural Areas Database Valid Bird Conservation Region Numbers and Names

BCR #    BCR Name

1

Aleutian/Bering Sea Islands

2

Western Alaska

3

Arctic Plains and Mountains

4

Northwestern Interior Forest

5

Northern Pacific Rainforest

6

Boreal Taiga Plains

7

Taiga Shield and Hudson Plains

8

Boreal Softwood Shield

9

Great Basin

10

Northern Rockies

11

Prairie Potholes

12

Boreal Hardwood Transition

13

Lower Great Lakes/St. Lawrence Plain

14

Atlantic Northern Forest

15

Sierra Nevada

16

Southern Rockies/Colorado Plateau

17

Badlands and Prairies

18

Shortgrass Prairie

19

Central Mixed-grass Prairie

20

Edwards Plateau

21

Oaks and Prairies

22

Eastern Tallgrass Prairie

23

Prairie Hardwood Transition

24

Central Hardwoods

25

West Gulf Coastal Plain/Ouachitas

26

Mississippi Alluvial Valley

27

Southeastern Coastal Plain

28

Appalachian Mountains

29

Piedmont

30

New England/Mid-Atlantic Coast

31

Peninsular Florida

32

Coastal California

33

Sonoran and Mohave Deserts

34

Sierra Madre Occidental

35

Chihuahuan Desert

36

Tamaulipan Brushlands

37

Gulf Coastal Prairie

 

This database documentation was compiled by Sue Heath. It was last updated on August 20, 2004.