Capstone Integration: Civic Data Analytics Project

Capstone Integration: Civic Data Analytics Project

This capstone lesson brings together every skill you have built in this course—schema design, SQL querying, query optimization, and tool integration—and applies them to real public data. Working with civic datasets is a practical rehearsal for professional data analytics: the data is messy, the schema does not yet exist, and the questions you answer can have genuine public value.

Choosing Your Dataset

Your first task is to select one dataset from the provided list of US open-data portals (such as data.gov or a local city data portal). Good candidates include city 311 service requests, county property tax assessments, and transit ridership logs. Choose a dataset that is large enough to be interesting—at least several thousand rows—but small enough to load and query in a reasonable time on your workstation. Download the data as CSV or JSON, inspect it in a spreadsheet or text editor, and note its columns, data types, and any obvious quality issues before you write a single line of SQL.

Designing a Normalized Schema

Raw civic data almost always arrives as a flat file. Your job is to decompose it into a normalized relational schema. Aim for at least Third Normal Form (3NF): every non-key column depends on the whole primary key and nothing but the primary key. Identify natural entities—for example, a 311 dataset might yield a `service_requests` fact table plus separate lookup tables for `departments`, `request_types`, and `neighborhoods`. Assign surrogate integer primary keys where natural keys are absent or unreliable. Use foreign key constraints to enforce referential integrity. Draw an entity-relationship (ER) diagram before you write DDL; this diagram will appear in your oral defense.

When you choose data types, be precise. Store dates as `DATE` or `TIMESTAMPTZ`, dollar amounts as `NUMERIC(12,2)`, and short codes as `VARCHAR` with a reasonable length limit. Avoid storing everything as `TEXT`; specific types enable better indexing and catch bad data at load time.

Loading and Cleaning the Data

Use PostgreSQL's `\COPY` command or a Python script (with `psycopg2` or `pandas` and `SQLAlchemy`) to load your cleaned CSV into the staging table first. Then use `INSERT … SELECT` statements to populate your normalized tables from the staging table, transforming and deduplicating as you go. Document every transformation decision: if you drop rows, state why; if you normalize a text field (for example, converting "N/A" to `NULL`), record that in your README.

Writing Analytical Queries

You must write a minimum of twelve analytical queries. Spread them across difficulty levels:

• Simple aggregations: counts, sums, and averages grouped by a category (for example, total 311 requests by department per month).
• Joins: combine two or more tables to add descriptive context to fact rows.
• Subqueries and CTEs: use a Common Table Expression (`WITH` clause) to break a complex question into readable steps. CTEs are preferred over nested subqueries for readability and maintainability.
• Window functions: use `RANK()`, `ROW_NUMBER()`, `LAG()`, or a running total with `SUM() OVER (PARTITION BY … ORDER BY …)` to compute within-group rankings or period-over-period change without collapsing rows.

Test every query: verify that the result makes sense against a small hand-checked sample. A query that runs without error but returns nonsense is still wrong.

Query Optimization and EXPLAIN ANALYZE

Add at least one index to your schema that improves a slow query. Run `EXPLAIN ANALYZE` on the query before and after adding the index, and save both output blocks. Include these in your README with a plain-English sentence explaining what changed (for example, a sequential scan became an index scan and execution time dropped from X ms to Y ms). Index foreign key columns and columns that appear frequently in `WHERE` or `JOIN ON` clauses. Be aware that indexes speed up reads but add overhead to writes; explain this trade-off briefly in your README.

Data Privacy and Responsible Handling

Public open data is not always fully anonymized. Before you begin analysis, scan your dataset for personally identifiable information (PII)—names, street addresses to the unit level, phone numbers, email addresses, dates of birth, or government ID numbers. Even though the data is publicly posted, handling PII carelessly in a work product is a professional and, in some contexts, a legal concern.

In your README, write a short data-privacy note: identify any PII columns you found, explain how you handled them (for example, excluded from queries, stored but not displayed in output visuals, or aggregated to a level that does not identify individuals), and note whether the dataset's portal terms of use permit the intended use. This habit maps directly to real workplace expectations and to the data ethics content tested by CompTIA Data+ and the Google Data Analytics certificate.

Creating an Output Visual

Produce at least one output visual using Python (matplotlib, seaborn, or plotly) or Power BI connected to your PostgreSQL database. The visual should answer a meaningful question from your dataset—not simply confirm a count. Label axes, include a title, and cite the data source. This visual does not need to be elaborate, but it must be honest: do not truncate axes or omit context in ways that mislead.

The Technical README

Your README (one to two pages, using the instructor-provided template) must cover: an ER diagram or table listing of your schema; a rationale for at least two schema design decisions; plain-English explanations of three representative queries; the EXPLAIN ANALYZE before-and-after evidence; and your data-privacy note. Write for a reader who is technically literate but unfamiliar with your specific project.

The Oral Defense

In a ten-minute session with your instructor and one peer, you will walk through your ER diagram and three queries of your choosing, then answer at least three follow-up questions. Be ready to explain why you chose a particular join type, what a window function partition achieves, or what would happen if you dropped an index. Speak clearly, refer to your diagram, and if you do not know an answer, say so and reason through it aloud—demonstrating how you think is as important as the answer itself.

Passing Standard

You must earn 80% of rubric points across schema correctness, query correctness, documentation completeness, and oral question responses. Any query that returns incorrect results must be corrected before a pass is recorded. Approach each section deliberately: correctness is non-negotiable.