Massachusetts Institute of Technology - Department of Urban Studies and Planning

11.520: A Workshop on Geographic Information Systems

11.188: Urban Planning and Social Science Laboratory

Lecture 3: Relational Databases

September 22, 2010, Joseph Ferreira, Jr.

(including notes by Visting Prof. Zhong-Rhen Peng who taught the class Fall, 2003)

Administrative notes:

Lab Exercise #3 due next Monday, Sept. 27, at 2 pm
- upload text, Word, RTF, or PDF formatted answer sheet to Stellar
- Editing suggeestion: save webpage answer sheet as text file and then add your answers
Homework set #1 now online and due in 2 weeks (Wed. Oct 6 ^th via Stellar)
- Examine relationships among eastern Mass Shopping Centers, major roads, and residential locations
- Long before it is due, read it over, check out the datasets, try the methods - spread out the work
- Waiting until the end will be frustrating and stressful!
ESRI 'Virtual Campus' ArcGIS tutorials: access codes available via MIT Libraries
- Email gishelp@mit.edu with names of tutorials you wish to run
Optional supervised lab time in 37-312 on Friday 10:30-1:30 pm and Sunday 4-7 pm
Optional RECITATION Session this week: (Go to one of them)
- Wed. 4-5 pm (primarily for undergrads) in Room 2-131 *TODAY*
- Thurs. 2-3 pm (primarily for MCP1s) in Room 9-450A
- Fri. 9:30 - 10:30 am (primarily for MCP2s and all others) in Room 9-450A
- We will hold *FOUR* recitations this semester:
  - Sept. 22/23/24: General GIS motivation/discussion, class feedback, etc.
  - Oct. 6/7/8: Database management and MS-Access
  - Oct. 27/28/29: Overlay and 'site suitability' analysis (homework set #2, part 2 issues)
  - Nov. 10/11/12: Review before in-class test

Unfinished topics from Lecture 2 last week

n Intro to Geoprocessing

n Example: Site Selection for Low Cost Grocery Store Chain

Powerpoint slides used by commercial firm to market site selection tools
by Edens & Avant and RPM consulting

n Think about these marketing slides? (discuss in recitation)

n Is the methodology or analytic scope overstated?

n What considerations are omitted, shortchanged, badly measured?

n From whose point of view is the siting service helpful or hurtful?

n How might you do a different analysis for a different audience?

Major topics for today -Database concepts and issues in GIS

n Relational Database modeling

n Structured Query Language (SQL)

n Entity-Relationship Diagrams

n SQL and ER-diagrams in MS-Access

n Joins and Relates in ArcGIS

Limitations of 'flat-file' data model & motivation for relational model (for attributes)

One 'flat file' data table for each map layer is too simple a data model
- From lab exercises #1,2,3: one-to-many issues
  - some houses in sales89 may have sold more than once!
  - but, map has only one dot per house
  - what sale price is correct? average, most recent sale, inflation adjusted average, ...?
- From lab exercise #4 (next Monday): more one-to-many issues
  - Add matown00.shp shapefile from data locker
  - Plot thematic map of density = pop90/area
  - What is wrong with thematic map of population density?
  - Mass towns with rivers and islands have more than one polygon
  - Data often are aggregated at city/town level
  - Hence, hard to compute population density = people / sum(town area)
- Typical urban planning context: many-to-many issues
  - Parcels can be owned by one or more owner

Strategy: Relational Data Model (i.e., linked tables)
- Retain tabular model for textual data but allow many tables that can be related to one another via common columns (attributes)
  - Allows augmenting attribute tables by adding additional data
  - Handles one-to-many issues (where rows have different meaning in joined tables)
- ArcMap strategy: (subset of fully relational model)
  - Maintain one-to-one link between textual data and a class of objects
  - Ignore one-to-many issues when joining tables OR create collapsed 'summary' table first
  - For Mass density example:
    - create 'summary' table with one row per town and a column with sum of area
    - then join back via town ID to shapefile with one row per polygon
  - For multiple sales example:
    - create 'summary' table with one row per house and columns for number of sales, maximum price, average price, etc.
    - then join summary table via house ID to the original sales table

Relational Database Management (RDBMS) is underneath most computing
- database-driven web pages: e-business catalogues, online newspapers
- most transaction processing: airline reservation, ATM transaction, cellphone call logging
- structured query language (SQL) for joining tables and specifying queries is the lingua franca of distributed database operations
- Big business: Oracle, IBM (DB2), Microsoft (MS-Access, SQL-Server), ...

Rest of Lecture
- Intro to theory and terminology of relational database management
- Illustrate RDBMS using MS-Access and Lab #4 datasets

A Table (relation)

Attribute name

tuples

Domains: data types like integer, strings, floats, dates, etc.

Relational Databases

n A relational database is a collection of tabular relations, or tables.

n A relation schema defines the structure of the relationship, and is composed of a set of attribute names and a mapping from each attribute name to a domain (that is, the possible values of that attribute)

n A relation is a finite set of tuples associated with a relational schema in a relational database (that is, a 'table' where each row is a tuple and the columns are the things that are related)

n A database schema is a set of relation schemas.

n A relational database is a set of relations.

n BEWARE: ArcGIS (and most lay folks) use different terms:

ArcGIS teminology:
- Join two tables into one combined table by specifying the columns that link them
- Relate two tables (but keep them separate) by specifying the columns that link them
Relational Database terminology:
- Join two (or more) relations by matching tuples (rows) based on common values in the corresponding columns

'Select' statements generate new relations (tables); 'views' save the query that will generate the combined relation (table)

A Relational database example

n Relational schema:

n State (State_code, state_name, state_capital)

n Database schema:

n State (State_id, state_name, state_capital)

n city (city_code, City_name, State_code)

n State_census (state, population, housing_unit_count, median_household_income, …)

n Primary Key: a minimal set of attributes whose value uniquely identifies each row (tuple).

Entity-Relation diagram for three sample tables (in MS-Access):

Three sample tables in MS-Access:

Operations on Relations

Project operation [pick columns] applies to a single relation & returns a subset of attributes of the original.
- SELECT state, population FROM census_table
Restrict operation [pick rows] return those tuples (rows) that match a 'where clause' condition
- SELECT state, population FROM census_table
- WHERE population > 0

n Join takes two relations as input and returns a single relation.

n Join (rel1, rel2, att1, att2)

Joins can get complicated (drop rows if no match?, join 3+ tables, ...)

Order of operation can affect results and and performance.

Language varies slightly across vendors:

SELECT state, population, state_code, state_capital

FROM census_table c, state_table s

WHERE population > 0

AND c.state = state_name

Structured Query Language (SQL)

n To define the database scheme (data definition),

n To insert, modify, and retrieve (SELECT) data from the database (data manipulation).

SELECT columns

FROM tables

WHERE row conditions and joins are matched

GROUP BY non-aggregated columns

ORDER BY certain columns

n Can get complex (subqueries in 'from' and 'where' clause, 3+ tables, ...)

n Can be especially powerful when rows in each table have different meaning

e.g., join city table to state table, parcel table to owner table, or house sales table to house table

Given the three city/state/census tables above, answer the following queries

n (1a) What is the result if you associate STATE_CENSUS table with STATE table?

n (1b) What is the result if you associate STATE table with STATE_CENSUS table?

n (2) What is the result if you associate STATE table with CITY table?

n (2b) What is the result if you associate CITY table with STATE table?

n (3) List each city with its state, state capital, and statewide population density

n We will examine and manipulate these tables in MS-Access: lec5_cities.mdb

Example #2b (ms-access SQL query):

Query view:



Table of results: (datasheet view)


SQL query: (SQL view)

This SQL can be simplified via abreviations and doing 'join' in where clause:

SELECT c.City_code, c.City_name, c.State_code, s.State_name, s.State_Capital
  FROM CITY c, State s
WHERE c.State_code = s.State_Code;

Example #3: List state, city name, and statewide population density:

Query view: (Note, the expression builder window shows the population density computation.)

Table of results: (datasheet view)

Notice that there is one row per city with the state info duplicated for all the cities in the same state. If we wanted to list one row per state, we could replace the 'city_name' column with, say, a count of the number of cities in the city table that are in each state. To do that in ms-access, you need to add the 'totals' row to the query design and select the appropriate 'group by' and aggregate functions (such as 'count' or 'sum' or 'avg') that should be applied to the attributes in each column. Can you see how to do this? (It is not that hard, just more than we have spent time explaining at this point.)

SQL query: (SQL view)

This 3-way join is ugly and seems complicated but is straightforward with standard SQL and relational algebra. If we changed the query to get one row per state with the count of cities in each state (as suggested above), we would have to use a 'count' function in the 'select' clause and also add a 'group by' clause. (Again, this is not hard, just more than we have spent time explaining at this point.)

We want to get comfortable enough with MS-Access to be able to do basic join-summarize-manipulate queries on a few tables in order to prepare data for mapping and spatial analysis. Lab #4 on Monday will get us started.

n Using MS-Access to query 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 Help with MS-Access basics - sign up for Element-K online training

element k - MIT info to request account: http://web.mit.edu/ist/services/training/wbt.html

element k site to login: http://www.elementk.com/

element k site for MS-Access Level 1: http://knowledge.elementk.com/catalog/overview.jsp?type=1&key=13035&search=access

element k provides training on many other topics including relational database design

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)

Illustrate use of relational tables, joins, and ER diagrams

Use MS-Access to do data manipulation for Lab #4 (mapping Mass population density): lec5_mass.mdb
- lab #4: 351 Mass cities and towns but 600+ polygons in shapefile - find density
- Import DBF attribute table for Masss Town boundaries into MS-Access
- Write query to sum areas of each part of a town
  - Note data type issues (integer could be too large...)
  - Try Plan B - read 'dbf' dataset into Excel and adjust data precision/scale/names/etc.
    - Adhere to DBF rules: 10-character names, set font to courier/12-pt and adjust column width,...
    - save with different name!
- Create new table with Town name, total area, pop90 and pop90 density
- Read MS-Access from ArcGIS and map density
Generate ER-diagram (of table relationships) for matown00.dbf and your new table
Understand SQL queries and graphical interface to assist in building/saving queries
ArcMap can import tables (and queries) from MS-Access but can be troublesome
- Regular 'Add-Data' option sees MS-Access (mdb) tables but not queries
- ODBC database connection sees queries as well but has data type problems (floats come across as integers)

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.

Other Noters about RDBMS and relational tables in ArcGIS

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.

Last modified 21 September 2010 [jf]. Back to the 11.520 Home Page.
Back to the CRON Home Page.