A guide to the NCVS database

1. A guide to the NCVS database Plus some nifty stuff about Access Michael Lee 11/29/2001

2. A database is an organized structure for: Storing Manipulating Entering and Reporting data Each of these is performed by: Tables Queries Forms Reports What is a database? .......................... ................ ........................ ...... A database exists as a single file on a computer. In MS Access, the files are named with extension “.mdb”

3. The Database Window • Shows all the objects in the database. • Select different types of objects. • Has large icon, small icon, list, and details views of objects (like Windows). • Hit F11 to get there.

4. Tables • Many different tables can be in each database – open by double clicking on icon. • Here, the Categories table contains column headers (=Field Names) that tell you about the information below. • Click the “straight edge” to go to design view and find out more about the table.

5. Tables – Design View • Design view shows the field names and their properties – which restrict what kind of data and how much can be entered into a field. • Data types can be text, number, memo (very long text), hyperlinks, etc. • Field sizes limit how long the data can be. • Descriptions tell you more about what the field actually is (very helpful later in the NCVS database).

6. Relationships – connecting tables • You can see the relationships by clicking the relationships icon, or by menu Tools|Relationships. • Relationships connect 2 tables [Categories] and [products] through one field [CategoryID] in each table. • The two fields don’t need to have the same name, but must be of the same data type. • Often, one field is aprimary key (bold), whichhas unique values (that is, only one occurrence of each value in the table). • Relationships are most useful if they are 1 to 1 or 1 to many. Here, all are 1 to many.

7. Show Product List Table Drag or double click fields on table to display here Datasheet view shows results of query Limit which rows are displayed by using criteria Queries • The simplest query is a SELECT query – you select which table(s) and fields you would like displayed. • You can also limit which records (rows) are displayed by specifying criteria.

8. Expression Builder icon [table name]. [field name] if ambiguous [field name] in brackets if unambiguous Queries - calculations • You can add fields which will calculate values based on other fields, here from two tables, linked by a relationship. • Name a field with the field name and a colon, then an expression. • Click the “Expression Builder” icon to better see or write a field’s expression. • Built-in functions are quite useful when creating expressions.

9. Sigma icon Group by these Category Names Total:line Sum of sales by each grouped Category name Queries - totals • Click the “Sigma” icon to see “Total:” line below. • “Group by” fields will show unique values in rows. • Other fields will have calculations performed on all records that match the “group by” fields from the original table.

10. Queries – other types • Append queries • Add records to a table from another table. • Crosstab queries • like Pivot Tables in Excel. • Values become field names. • Fields are calculated based on row and column headings. • Union queries • Similar to “stacking” datasets in SAS. • “Stacks” multiple tables so that all records in all tables are present in the query. • Make-table queries • Create a table that stores the data currently queried. • Useful with complex queries than run slowly. • The NCVS database contains all these types of queries. • There are other types of queries that may become useful to you – see Access Help. • Update queries • Update certain fields of a table to new values based on an expression

11. All orders for a comp-any The details of the order Forms • Based on one or multiple tables or queries. • Simpler view of complex data. • Used for data entry. • Fields can be “locked” so that editing them is prevented. • Forms can have “buttons” that can sort, print, edit, etc.

12. Reports • Based on one or multiple tables or queries. • Used to view data, often summarized by groups, here by category.

13. That’s the basics of Access • MS Access provides a good help menu that will guide you through how to use Access. • The sample database is also helpful in figuring out how things work. It is located here: • \\Uniola\C\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

14. NCVS Database Overview Tables

15. NCVS Database database folder location: • C:Data\NCVS\Copy_NCVS_Database\In-Out\ • The database is named according to the copy date in this In-Out folder, • like: Nov29_2001_NCVSProto4.mdb • If you wish to use the database, copy it from the In-Out folder to your own computer space (i.e. \users\YourName\ or your own computer) • This allows all of us to be working with a fresh copy

16. NCVS Database stats The NCVS Database currently contains: • 4,648 plots • 321 USGS Quadrangles in 5 states • 40 different projects • 3,135 different taxa • 171,933 woody stems • 423 people participating (still incomplete)

17. NCVS Database basic structure • There are about 23 tables that contain information directly relating to the plots. • Another 13 tables contain “support” information that help interpret the tables that relate directly to the plots. i.e. The [Counties] table contain names of counties, from which [File1] can select to assign a plot to a county.

18. This report (rel_ver5 ) is in the NCVS database. Colored boxes represent groups of similar tables NCVS Relationships

19. Master Lists All_Plots Project Vegetative Data HerbData TreeDataSml TreeDataBig Vegetative Attributes File3_VegAttr StratumPlot Environment File2_Site_Attributes File4_McNab_Indices GroundCoverPlot DisturbancePlot Soil Data File5_SoilDepth Soil_Nutr Soil_Text Plot Location and Layout File1_Plot_Summary PlotPlace Classification ClassEvent ClassAssign ClassContributor Contribution by People PlotContributor ProjectContributor Documentation FieldDefn Notes Overview of NCVS tables

20. Format of Following Slides: Group Name Graphic of group with group name, tables, and fields. • [Table Name] (in italics) • Primary key:[FieldName] • Relates to [table(s)] (by [FieldName] or pk=primary key) • If “(by [FieldName])” is absent, then the tables link by [project_team_plot] (a.k.a. [plotID]) • General information about table and useful tips about table

21. Master Lists (p1) • [All_Plots] • Primary key:[Project_Team_Plot] • Relates to [Project] (by [Project])andmost tables that have direct data on plots • One record per plot • No plot data can exist in a table without a corresponding plot in [All_Plots] • Contains [Project], [Team], and [Plot] in separate fields

22. Master Lists (p2) • [Project] • Primary key: [Project_ID] (=project number) • Relates to[All_Plots], [Notes], [Field_Defn], [ProjectContributor] – (ALL by pk) • One record per project • Contains useful descriptions about project • Specifically [ProjRegion], which divides projects into CP, LL, M, PD, OT

23. Documentation (p1) • [Field_Defn] • Primary key: [fieldDefnID] • Relates to[Project] (by [Project]) • One record per field definition • [FieldDesc] defines field values • Often interprets codes • Gives ranges of values • If project is blank, then definition refers to all projects, otherwise, only the project mentioned in [project]

24. Documentation (p2) • [Notes] • Primary key: [NoteID] • Relates to[All_Plots], [Project] (by [Project_entire]) • One record per note (multiple notes per project and/or plot) • Contains notes about plots and/or projects. • [NoteTypes] • Primary key: [NoteTypeID] • Relates to[Notes] (by pk) • Contains valid types of notes

25. Vegetative Data (p1) • [HerbData] • Primary key: [HerbDataID] • Relates to[CarSpList] (by [SppID]), [All_Plots] • One record per species per module per plot • Summary module “S” shows overall composition of each plot • Spatial scale information for up to 5 corners [c1]-[c5] • Cover classes [cov] • Cover classes for up to 7 strata [ns1]-[ns7] • Cover classes are according to NCVS scale (see [Field_Defn])

26. Vegetative Data (p2) • [TreeDataSml] • Primary key: [TreeLineID] • Relates to[CarSpList] (by [SppID]), [All_Plots], [TreeDataBig] (by pk) • One record per species per module per plot • Summary module “S” shows overall composition of each plot • 10 fields for number of stems in each size class (standard NCVS classes) [d0],[d1],...[d35] • overall subsampling percent for each species in each module: • [NewSubS] (for saplings: [d0],[d1]) • [NewSubT] (for trees: all others)

27. Vegetative Data (p3) • [TreeDataBig] • Primary key: [TreeDataBigID] • Relates to[TreeDataSml] (by [TreeLineID]) • One record per stem • [Module], [plotID], [SppID] and other relavent info (like [NewSubT) are found in linked [TreeDataSml] • [bigtree] is the dbh of the large stem (>40cm)

28. Species Datatables (p1) • [CarSpList] • Primary key: [SppID] • Relates to[HerbData] (by pk), [TreeDataSml] (by pk), [LatestVersionCarolSpDB] (by pk) • One record per taxon • Contains taxonomic data to interpret [SppID] in VegData tables • Updated via an Action Query to include any changes in [LatestVersionCarolSpDB]

29. Species Datatables (p2) • [LatestVersionCarolSpDB] • Primary key: [SppID] • Relates to[CarSpList] (by pk) • One record per taxon • A linked table (window into this database) from the Carolina Species Database

30. Vegetative Attributes (p1) • [File3_VegAttr] • Primary key: [Project-Team-Plot] • Relates to[All_Plots], [StratumPlot] • One record per plot • Contains data about what the vegetation of the entire plot is like • Physiognomic Class is overall type of vegetation, i.e. Forest, Savanna, Shrubland, etc. • [Field_Defn] has more field info • Some fields have been replaced by a new table [StratumPlot], but we aren’t sure if the new format will stick, so they are still (mostly) preserved (i.e. [EMaxHt])

31. Vegetative Attributes (p2) • [StratumPlot] • Primary key: [StratumPlotID] • Relates to[File3_VegAttr], [StratumType] (by [StratumTypeID]) • One record per stratum per plot • Contains definitions of the vertical strata of a plot that may be referred to in [HerbData].[ns1], .[ns2],... • [StratumType] • Primary key: [StratumTypeID] • Relates to[StratumPlot] (by pk) • One record per stratum type • Provides a list of standard strata that may used to describe a plot

32. Environment (p1) • [File2_Site_Attributes] • Primary key: [Project-Team-Plot] • Relates to[GroundCoverPlot], [All_Plots], [DisturbancePlot] • One record per plot • Contains information about many environmental variables, such as • slope, aspect, elevation • soil description and types (soil series and other variables) • hydrologic variables • Fields relating to Ground Cover and Disturbance now are in separate tables • Many fields are blank for many plots

33. Environment (p2) • [GroundCoverPlot] • Primary key: [GroundCoverPlotID] • Relates to[File2_Site_Attributes], [GroundCoverType] (by [GCTypeID]) • One record per Ground Cover Type per plot • Contains percent cover for each Ground Cover Type for each plot • [GroundCoverType] • Primary key: [GroundCoverTypeID] • Relates to[GroundCoverPlot] (by pk) • One record per Ground Cover type • Provides a list of standard Ground Cover Types that may used to describe a plot (Bedrock, Litter, Water, etc.)

34. Environment (p3) • [DisturbancePlot] • Primary key: [DisturbancePlotID] • Relates to[File2_Site_Attributes], [DisturbanceType] (by [DisturbanceTypeID]) • One record per Disturbance Type per plot • Contains severity and description of Disturbance for each type on a plot • [DisturbanceType] • Primary key: [DisturbanceTypeID] • Relates to[DisturbancePlot] (by pk) • One record per Disturbance Type • Provides a list of standard Disturbance Types that may used to describe a plot (Human, Natural, Fire, Animal)

35. Environment (p4) • [File4_McNab_Indices] • Primary key: [Project-Team-Plot] • Relates to[All_Plots] • One record per plot (Mtn plots only) • McNab Indices measure the “bowl-shaped-ness” or “ridge-shaped-ness” • Contains LFI and TSI inclinations (in degrees) at 8 angles or the calculated LFI and TSI (if individual angles are not available) • LFI is LandForm Index • (angle to horizon) • TSI is Terrain Shape Index • (angle formed by local slope shape, ~10m scale)

36. Soil Data (p1) • [File5_SoilDepth] • Primary key: [DepthID] • Relates to[All_Plots] • One record per corner per module per plot (16 records for a standard plot) • Depth is to impermeable layer, in cm • Some [module] or [corner] values are text to indicate max, min, or avg (where raw data unavailable)

37. Soil Data (p2) • [Soil_Nutr] • Primary key: [Project_Team_Plot] • Relates to[All_Plots] • One record per module per horizon per plot • Contains results of nutrient analysis of soil samples • [Module]=C means that the values for that record are from a composite of the different modules’ soil (or values)

38. Soil Data (p3) • [Soil_Text] • Primary key: [Project_Team_Plot] • Relates to[All_Plots] • One record per module per horizon per plot • Contains results of texture analysis of soil samples • [Module]=C means that the values for that record are from a composite of the different modules’ soil (or values)

39. Plot Method and Location (p1) • [File1_Plot_Summary] • Primary key: [Project-Team-Plot] • Relates to[All_plots], [PlotPlace], [States] (by [State Abrv]), [Counties] (by [County ID]), [MapQuadrangles] (by [Quadrangle ID]) • One record per plot • Contains Location Information • UTM Easting, Northing, and Zone • Latitude and Longitude • Estimated Error in Coordinates • County, State, Quadrangle (foreign keys) • Methodology • Plot size (herb and tree), Date, Photo Data • [CoverMethod] is method of herb sampling

40. Plot Method and Location (p2) • [MapQuadrangles] • Primary key: [QuadrangleID] • Relates to[File1_Plot_Summary], (by pk) • One record per Quadrangle • Contains Quadrangle Information • Quadrangle Name and State(s) • Quadrangle Base Coordinates • [QuadrangleID] (number) is stored in [File1_Plot_Summary],not [Quadrangle Name] • [Quadrangle Name] appears in File1 because of settings on Lookup table • [Quadrangle name] can be queried from [MapQuadrangles] table

41. Plot Method and Location (p3) • [Counties] • Primary key: [County ID] • Relates to[File1_Plot_Summary], (by pk) • One record per county per state • Contains County Name and State • As with [MapQuadrangles], [County ID] (number) is stored in [File1_Plot_Summary] • [State] • Primary key: [Abbrev] • Relates to[File1_Plot_Summary], (by pk) • One record per state • Contains State Abbreviation and State • As with [MapQuadrangles], [Abbrev] is stored in [File1_Plot_Summary]

42. Plot Method and Location (p4) • [PlotPlace] • Primary key: [PlotPlaceID] • Relates to[File1_Plot_Summary], [PlaceNames] (by [NamedPlace]) • One record per Place Name per plot • Assigns a plot to one or more named Places • [PlaceID] is stored from [PlaceNames] • [PlaceNames] • Primary key: [PlaceID] • Relates to[PlotPlace], (by pk) • Contains valid Place names from which [PlotPlace] can select

43. Party • [Party] • Primary key: [PartyID] • Relates to[PlotContributor] (by pk), [ClassContributor] (by pk), [ProjectContributor] (by pk) • One record per person • Contains names and contact information for people who have contributed to the NCVS dataset in some manner (see [Roles] for different contribution types)

44. Contributor (p1) • [PlotContributor] • Primary key: [PlotContributorID] • Relates to[All_Plots], [Party] (by [PartyID]), [Roles] (by [RoleID]) • One record per person (per role) per plot • Credits a person ([Party]) with contributing to a plot in a particular role ([Roles]) • [Roles] • Primary key: [RoleID] • Relates to[PlotContributor] (by pk), [ProjectContributor] (by pk) • Contains the valid possible roles to contribute – either to plot or project

45. Contributor (p2) • [ProjectContributor] • Primary key: [ProjContribID] • Relates to[Project] (by [ProjectNumber], [Party] (by [PartyID]), [Roles] (by [RoleID]) • One record per person (per role) per project • Very similar to [PlotContributor], but for projects instead of plots • Credits a person ([Party]) with contributing to a project in a particular role ([Roles]) • Mainly for recording status of projects in data entry

46. Classification (p1) • [ClassEvent] • Primary key: [ClassEventID] • Relates to[All_Plots] (by [PlotObsID] =[PlotID]), [ClassAssign] (by pk), [ClassContributor] (by pk) • One record per Classification Event per plot • A Classification Event is an effort by one or more people to classify a plot • Contains: • Method of classification • Notes on overall classification event • Date of classification event

47. Classification (p2) • [ClassAssign] • Primary key: [ClassAssignID] • Relates to[ClassEvent] (by [ClassEventID]), [ClassCodes] (by [ClassCode]) • One record per Classification Assignment per plot • A Classification Assignment is a plot assigned to a particular CEGL code, Alliance, or Association • Contains: • Fit- how closely plot matches typal classification community • Confidence- how sure the classification and fit are • Notes on the particular assignment

48. Classification (p3) • [ClassCodes] • Primary key: [CEGL_All_Assn_Code] (that is CEGL code, Alliance code, or Association code) • Relates to[ClassAssign] (by pk) • One record per classification type, (Community, Alliance, or Association) • Contains: • Formation –5 strings (. delimited) that show the lower resolution groups of the particular classification type (IV.A.2.N.a) • Common names of classification types • Other miscellaneous support info

49. Classification (p4) • [ClassContributor] • Primary key: [ClassContribID] • Relates to[ClassEvent] (by [ClassEventID], [Party] (by [PartyID]) • One record per classification event per person contributing • Very similar to [PlotContributor] • Contains party members who contributed to the classification event

50. Tables – more information • To find out more about a particular table, click the details view on the database window • There you can see the description field for each table, which describes each important table • To find out more about each field in a table, click on design view and read the description field there • Description of a field also appears in lower left hand corner of the window when the cursor is in a field of a table in datasheet view