In the schedule,  “Practical” refers to our textbook Practical SQL while “Action” refers to our other text, PostGIS in Action. Other readings indicated in parentheses are stored on the library’s E-reserve page.

Readings should be completed before the class for which they are assigned. Exercises are due at the beginning of the following class.

The schedule is subject to revision throughout the semester

Week 1 (Jan 29) – Introduction
Topics: Course overview, technology setup, interfaces, database test drive
Readings: DMS article (Duckham)
Assignment 1: Database installation
Week 1 Slides

Week 2 (Feb 5) – SQL DML
Topics: Database fundamentals, data manipulation language, aggregates, joins
Readings: Practical Introduction, Chap 1 through 4
Optional Reading: SQL data manipulation (Connelly)
Assignment 2: Intro to SQL Part 1
Week 2 Slides

HOLIDAY – No class on Feb 12
Assignment 2 Due by 9:30pm

Week 3 (Feb 19) – SQL DDL
Topics: Database fundamentals, data definition language, data types, constraints
Readings: Practical Chap 5 through 8
Optional Reading: SQL data definition (Connelly)
Assignment 3: Intro to SQL Part 2
Week 3 Slides

Week 4 (Feb 26) – Database design
Topics: Entity relationships, normalization, keys, modeling
Readings: Database Design (Kreibich), Entity Relationship Model (Song)
Assignment 4: Design and normalization
Week 4 Slides

Week 5 (Mar 5) – Data processing
Topics: Data formats, processing, loading (ETL)
Readings: Practical Chap 9
Assignment 5: Data cleaning & SQL review
Week 5 Slides
GROUP BY Review Slides

Week 6 (Mar 12) – Spatial databases
Topics: Spatial data fundamentals, spatial data types
Readings: Action preface, Chap 1, 2, & 5
Assignment 6: Spatial data basics
Week 6 Slides

Week 7 (Mar 19) – Spatial reference systems
Topics: General overview, loading spatial data, coordinate transformation
Readings: Action Chap 3, 4, & 6
Assignment 7: Coordinate systems
Week 7 Slides

Week 8 (Mar 26) – Spatial relationships & analysis
Topics: Geographic relationships, indexes, spatial indexes
Readings: Action Chap 9 & 11
Assignment 8: Spatial analysis
Week 8 Slides

Week 9 (Apr 2) – Proximity analysis
MIDTERM QUIZ: At beginning of class
Topics: Buffers, nearest neighbor, coordinate system considerations
Readings: Action Chap 10
Assignment 9: Proximity analysis
Week 9 Slides

Week 10 (Apr 9) – Organizing spatial data
FINAL PROJECT: Assigned
Topics: Spatial data design
Readings: Practical Chap 18, Action Chap 14
Assignment 10: Spatial data design
Week 10 Slides

Week 11 (Apr 16) – Database management, SQL DCL
Topics: Users and permissions, data control language, maintenance and performance
Readings: Practical Chap 15 & 17
Optional Reading: SQL data definition (Connelly)
Week 11 Slides

SPRING BREAK – No class on Apr 23

Week 12 (Apr 30) – Rasters / Other database formats
Topics: Rasters, SQLite / Spatialite, ArcGIS formats
Readings: Action Chap 7
Week 12 Slides

Week 13 (May 7) – Database programming / Final project prep
Topics: Python for databases, final project help

Week 14 (May 14) – Presentations / Final project prep
FINAL PROJECT: Presentations

Week 15 (May 21) – Exam Day – Class does NOT meet
FINAL PROJECT: Due by 9:30pm