Given the three tables below, answer the following queries
n What is the result if you associate STATE_CENSUS table with STATE table?
n What is the result if you associate STATE table with STATE_CENSUS table?
n What is the result if you associate STATE table with CITY table?
n What is the result if you associate CITY table with STATE table?
n We will examine and manipulate these tables in MS-Access: lec5_cities.mdb
CITY table
.
.
STATE table
.
STATE_CENSUS table
.
n Using MS-Access to query these tables: lec5_cities.mdb
n Get feel for MS-Access basics - we will use it for large census tables in two weeks
n Focus on basic joins and querys: graphical query interface and equivalent SQL
n Shortchange discussion of best table structure (normalization) and complex join issues
n ArcGIS has basic join/query tools but runs out of gas for complex data manipulation
n MS-Access is better but still has problems with multi-user sharing and complex queries
n Next Monday's Lab #4 uses ArcGIS data manipulation tools to compute MA density
n Following Monday's Lab #5 uses MS-Access with census data (driving alone to work)
Other (abstract) Database Concepts (generally beyond scope of class)
Distributed databases and Federated databases
n Distributed databases
refer to one database (or data replicates) that are
distributed across multiple sites.
n Federated databases refer to many similar databases that are distributed
across multiple sites but are more loosely coupled and additional rules may
be needed to cross-reference tables meaningfully
n Federated databases are also called distributed relational database
with fragmentations.
Relational data model (our focus)
n Collection of interrelated tables
n
Highly structured data schemas and data types with data dictionary
n
We'll examine US Census Bureau examples in Lab #5
Relational Database as Entity-Relationship Model
n E-R model sees the world as inter-related entities (tables);
n Use MS-Access to build E-R diagram for the three city/state tables: lec5_cities.mdb.
n Entities (or entity types like cities and states in our example) are related with each other by a relationship (linking primary and foreign keys - e.g, which city is in which state)
n E-R model uses E-R diagrams to describe relations between entity
types (generally one-to-many connections)
n E-R model describes the static state of the entity types.
Object-Oriented Model - one alternative to E-R for RDBMS
n Object-oriented model sees the world as
inter-related objects.
n Object is dynamic and has its own lifespan. Hence OO model is used
to deal with the dynamic nature of real-world object.
n Object = static state + functionality
n Object with similar behaviors are organized into types,
a semantic concept.
n Object Class = data structure
+ methods, an implementation construct.
Joining and Relating Tables in ArcGIS
n
Join in ArcGIS appends the attributes of the non-spatial table to the spatial (layer) attribute table.
n
Relate in ArcGIS does not append attributes; only establishes a logical relationship so that when you select one record in one table you can see the matching records in the other table.
Don't get confused between ArcGIS 'relate' (which describes the relationship between two tables, and RDBMS terminology where 'relation' = a table)
When to Use Join and Relate
n
Relate is preferred if the non-spatial table is maintained and updated constantly while the spatial data is not.
(e.g., Mass towns shapefile plus summary table with data for each town)
n
Use relate when the relationship is many-to-many.
n
Use relate when you have a very large non-spatial table and you don't need all the attributes in the table.
n
Columns in 'related' table cannot be thematically mapped - they must first be 'joined' in
n
In other situations, you could use either.
