The schedule is being revised for the current term (Fall 2022). Material may shift depending on how quickly or slowly certain topics are covered. Please check back often, refer to Canvas, and pay attention to announcements in class.

This schedule is the model schedule for the course, expressed in the week number of the term. Actual dates of class meetings, exams, and assignment due dates, will be available in the course LMS.

In the following list:

• “PiA” refers to the PostGIS in Action, 3e by Obe & Hsu
• “RDDI” refers to Relational Database Design and Implementation, 4e by Harrington
• “PG” refers to the PostgreSQL Documentation—note that this links to the documentation for the currently released version of PostgreSQL; if the documentation changes in a way that breaks the link, please notify the professor so that it can be updated
• “Workshop” refers to the Introduction to PostGIS Workshop—the files for this workshop are also available as a download in Canvas
  • Note that the PostGIS Workshop is listed under “Reading” because the exercises include answers, and are not turned in for a grade; you should do the exercises and check your own answers

Week 1 – Introduction and Basic Queries

• Reading:
  • RDDI 1, 4, 10, 16
  • PG 7.3: Select Lists
• Exercise:
  • SQL SELECT Statements
  • DataCamp – Intro to SQL for Data Science, Chapters 1 & 2

Week 2 – SQL Functions and Data Types

• Reading:
  • RDDI 7 – “Normal Forms”, “First Normal Form”
  • RDDI 18 – “Performing Arithmetic”, “String Manipulation”, “Date & Time Manipulation”, “Case Expressions”
  • PiA 2, 3, 6 (skim)
  • Workshop 9, 10 (exercises), 11, 16, 17 (exercises)
• Exercise:
  • TBD (Spatial Reference Systems?)

Week 3 – Aggregate Queries

• Reading:
  • RDDI 7 – “Second Normal Form”
  • RDDI 19
• Exercise:
  • SQL Aggregate Query
  • DataCamp – Intro to SQL for Data Science, Chapters 3 & 4
• Term Project:
  • Milestone 1 due, consultation in class

Week 4 – Table Join

• Reading:
  • RDDI 7 – “Third Normal Form”
  • RDDI 17
  • PG 7.2: Table Expressions
  • Workshop 13, 14 (exercises)
• Exercise:
  • SQL Joins
  • DataCamp – Joining Data in PostgreSQL

Week 5 or 6 – Data Modification Language (DML) and Keys

• Reading:
  • RDDI 5, 20
  • PG 6, all sections: Data Manipulation
• Exercise:
  • SQL Inserts

Week 5 or 6 – Normalization and Data Definition Language (DDL)

• Reading:
  • RDDI 5, 7 (review), 11, possibly one of 13, 14, 15
  • PG 5.1: Table Basics; 5.2 Default Values; 5.3 Constraints

Week 7 – Spatial Relationships

• Reading:
  • PiA 8
  • Workshop 11, 12 (exercises), 22
• Exercise:
  • Spatial Relationships

Week 8 – Spatial Analysis

• Reading: PiA 9
• Exercise:
  • Nearest Neighbor Analysis

Week 9 or 10 – Indexing and Performance Tuning (tentative)

• Reading:
  • RDDI 8
  • PiA 14, 15
  • Workshop 15

Weeks 9-14 – Student Tutorials, Additional Topics

The remainder of the semester will be devoted to three things:

• Student-led tutorials
• Additional topics presented by the professor, including query optimization, role management and security, backup/restore, etc.
• Term project work