-
-
- database (DB)
- DBMS
- record
- field / field name / value
- primary key (unique identifier)
- secondary key (foreign key)
- database data models
- flatfile
- hierarchial
- network
- relational (90+%)
- each "subject" has its own table
- Student Contact Info
- Student Academic Record
- Students Accounts
- tables can be related
- i.e. check academic record - passed all courses
- check accounts - no $$ owing
- retrieve address to mail diploma
- object-oriented
- DB analyses
- sort
- query / filter (structured & precise)
- search (unstructured ... Google)
- numeric terms
- integer v. real
- precision
- # of digits
- independent of decimal
- includes "-" if necessary
- includes "0 placeholder" if a fraction
- scale
- # of decimal places
- examples
- 99
- p = 2
- s = 0
- 320,000
- p = 6
- s = 0
- 0.0012
- p = 5
- s = 4
- -123,456.78
- p = 9
- s = 2
- 1,234.5678
- p = 8
- s = 4
- Advantages of Database
- simple structure: tables (records & fields)
- easy to update (maintain) data
- redundancy is minimized - data consistent (not multi-versions)
- reduced data errors - error checking
- ready access to data (variety of people, variety of uses)
- data security (restricted rights to view and/or edit)
- data stored independent of software
- Database Creation
- Needs Analysis (data investigation)
- ... "conceptual model"
- Subjects (tables: contact info, academics, $$)
- entities ("individuals") ... --> records
- attributes (characteristics: street address, phone, email) ... --> fields
- relationships (btwn the tables)
- questions that need to be answered
- example: Woodlot
- subjects & entities & attributes
- inventory
- stand ID
- species
- age
- ht
- etc.
- logging
- block ID
- area
- volume
- year logged
- yarding
- treatments
- trtmt ID
- type of trtmt (plant, fert, CT, JS, etc)
- year
- area (ha)
- cost
- relationships?
- forest - logging
- forest - trtmt
- questions
- is forest volume related to trtmts?
- % logged blocks that get planted with Cw
- Logical Design (data model)
- design of the data tables
- attribute (field) types (see below)
- example for Forest Stands
- species - text
- age - integer
- ht - real number
- determine "key fields" to allow relationships
- key field (primary key) = unique ID, no repeats
- examples
- your student # at VIU
- Block ID for logging
- Stand ID for forest cover
- Physical Design (database design)
- ... use the software to create the empty data tables
- Implementation (do it)
- "populate the database" ... = enter the data
- ... use it (do queries, searches & sorts)
- Field Types
- text / string
- character-based data (words and codes)
- may contain "numbers", but calculations make no sense (student ID)
- numbers
- integer
- short
- for values btwn -32,767 to 32,767
- long
- for values btwn -2 billion to 2 billion
- real numbers (decimal)
- float
- a.k.a. single precision
- for precision (# of digits) of 6 or less
- double
- a.k.a. double precision
- 7+ digits
- what field type for
- stand label (FC 841)
- stand height (35.6)
- site index (24)
- area - ha (12.3)
- area - m2 (123,000)
- when in doubt ...
- Basic Analyses
- Sort
- ascending or descending
- right click on field name
- Query (Filter)
- criteria in form of logical expression
- Program = Music
- 3 parts
- field name
- relational operator
- value
- complex
- more than 1 criteria
- Boolean operators
- AND
- "both" criteria have to match
- more restrictive
- e.g. Program=Music AND Name=George
- George Harrison only
- OR
- "either" criteria can match
- more inclusive
- e.g. Program=Music OR Name=George
- the Fab Four plus George Costanza
- NOT
- "exclude" 2nd criteria
- more restrictive
- e.g. Program=Music NOT Name=George
- only John, Paul & Ringo
- GIS Context
- attributes are organized in database tables
- spatial data is also managed in database tables (we don't see them)
- spatial & attribute data are linked
- when we query the attribute data ...
- when we query the map data ...
- when we digitize and add new feature ...
-
- key field
- primary key
- secondary (foreign) key
- destination & source
- destination table
- the table you are working in
- you want to "bring data to this table"
- in GIS it is ALWAYS the "map table"
- source table
- "outside source" data you want to use
-
- one-one (1:1)
- many-to-one (M:1)
- one-to-many (1:M)
- join vs. relate (link)
-
