Database Fundamentals

October 28 · Design the system your queries need

MaDS Databases & SQL

One question will organize today

What schema can accept tomorrow’s weather data without contradicting today’s data?

Good queries begin with decisions made before the first row is loaded.

Course source and adaptation

This course follows and adapts Alex Reinhart’s MADS Computing course. Today’s relational-model, schema, type, relationship, and client–server concepts follow his Database Fundamentals chapter; the project schema and checkpoints are Fall 2026 applications.

By the end of class

You should be able to:

define an entity and the grain of its table,
choose useful PostgreSQL data types,
assign primary, foreign, and unique keys,
use constraints to reject impossible records,
sketch an ER diagram for the project database.

A giant flat file repeats facts

station_id | station_name | state | date       | precip | storm_name | storm_year
-----------+--------------+-------+------------+--------+------------+-----------
USW...     | PITTSBURGH   | PA    | 2025-09-01 | 12.4   | ERIN       | 2025
USW...     | PITTSBURGH   | PA    | 2025-09-02 |  2.1   | ERIN       | 2025

Station and storm facts repeat whenever an observation repeats.

Repetition creates contradictions

If a station name appears in 10,000 rows:

which row is authoritative?
must every spelling be updated together?
can two rows disagree about the state?
what happens when a station has no observation yet?

The storage design should prevent these questions from becoming data-cleaning emergencies.

Checkpoint 1: find the entities

Split the giant flat file into tables. For each table, state what one row represents and choose a candidate key.

Start with four nouns:

station     observation     storm     track point

Checkpoint 1: one defensible split

Table	One row	Candidate key
`stations`	one station	`station_id`
`observations`	one station-date	`(station_id, date)`
`storms`	one named storm-season	`storm_id`
`storm_tracks`	one storm timestamp	`(storm_id, datetime)`

Different schemas can work if their grain and rules are explicit.

One fact should have one home

Station name and elevation belong in stations.
Daily precipitation belongs in observations.
Storm name and season belong in storms.
Wind and location at a moment belong in storm_tracks.

This is the practical core of normalization: reduce redundancy while preserving relationships.

Relational design starts before data

A schema specifies:

tables,
columns,
data types,
keys and relationships,
constraints on valid values.

PostgreSQL then rejects rows that violate the design.

Types encode allowed operations

Meaning	PostgreSQL type
station identifier	`TEXT`
observation date	`DATE`
track timestamp	`TIMESTAMPTZ`
latitude/longitude	`DOUBLE PRECISION`
count	`INTEGER`
yes/no flag	`BOOLEAN`

Do not store every incoming field as text merely because the source file is text.

Missing values require a decision

NULL can mean “unknown,” “not observed,” or “not applicable.”

Ask for every column:

Is missing allowed?
Does the source use a sentinel such as -9999?
Should a missing value reject the row or remain NULL?

A type alone cannot answer these questions.

Primary keys identify rows

CREATE TABLE stations (
  station_id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  state CHAR(2),
  lat DOUBLE PRECISION NOT NULL,
  lon DOUBLE PRECISION NOT NULL,
  elevation NUMERIC(7, 2)
);

station_id must be unique and non-missing.

Constraints turn assumptions into rules

CHECK (lat BETWEEN -90 AND 90),
CHECK (lon BETWEEN -180 AND 180),
CHECK (state IS NULL OR state ~ '^[A-Z]{2}$')

Without a constraint, “latitude is valid” is only documentation.

With a constraint, invalid data cannot enter unnoticed.

Foreign keys protect relationships

CREATE TABLE observations (
  obs_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  station_id TEXT NOT NULL REFERENCES stations(station_id),
  date DATE NOT NULL,
  tmax NUMERIC,
  tmin NUMERIC,
  precip NUMERIC
);

An observation cannot reference a station that does not exist.

A surrogate key does not define the grain

The identity column generates an obs_id that uniquely identifies the stored row.

But the business rule is still:

at most one observation per station per date

Encode that separately:

UNIQUE (station_id, date)

Column rules can span values

CHECK (precip IS NULL OR precip >= 0),
CHECK (tmax IS NULL OR tmin IS NULL OR tmax >= tmin)

Constraints should reject impossible states, not every unusual state.

An extreme temperature may be real; a negative precipitation total is not.

Checkpoint 2: repair the table

Repair this definition so it represents one observation per station per date and enforces the station relationship.

CREATE TABLE observations (
  obs_id TEXT,
  station_id TEXT,
  date TEXT,
  precip NUMERIC,
  PRIMARY KEY (station_id),
  FOREIGN KEY station_id REFERENCES stations
);

Checkpoint 2: one solution

CREATE TABLE observations (
  obs_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  station_id TEXT NOT NULL
    REFERENCES stations(station_id),
  date DATE NOT NULL,
  precip NUMERIC
    CHECK (precip IS NULL OR precip >= 0),
  UNIQUE (station_id, date)
);

Relationships determine join behavior

stations       1 ───────< observations
storms         1 ───────< storm_tracks
storms         1 ───────< storm_impacts >─────── 1 stations

The bridge table resolves the many-to-many relationship between storms and stations.

The server stores the canonical database

VS Code client ──SQL──> Azure PostgreSQL server
Python client  ──SQL──> same database
R client       ──SQL──> same database

The schema and permissions apply regardless of the client language.

Transactions protect multi-step changes

Relational databases aim to keep changes:

atomic: all or nothing,
consistent: rules remain true,
isolated: concurrent work does not corrupt results,
durable: committed changes survive failures.

These are the four ACID guarantees emphasized in Reinhart’s chapter; we will practice transactions later.

Design for questions and updates

A useful schema supports the questions and the update process.

For each source, record:

update cadence,
stable identifier,
revision behavior,
missing-value codes.

Project launch: the minimum schema

Your team will begin with:

stations        observations
storms          storm_tracks
storm_impacts   ingestion_runs

You may revise this design, but every table needs an explicit purpose and grain.

Checkpoint 3: draft the ER diagram

Sketch the first project ER diagram. Label relationships and define each table’s grain.

Mark the primary and foreign keys, one uniqueness rule, and one data-quality constraint.

Test the design with two queries

Your schema should make these possible without guesswork:

Which stations have no observation for a requested date?
Which stations were within a chosen distance of a named storm?

If the join keys or result grain are unclear, revise the diagram now.

Project Part 1 begins today

Before the next project checkpoint, submit:

source inventory and update cadence,
ER diagram,
table-by-table grain statements,
proposed keys and constraints,
two queries the schema is designed to answer.

The schema is a draft—but it must be specific enough to critique.

The pattern to keep

identify entities
  → define one row
  → choose keys
  → choose types
  → encode valid relationships
  → prevent contradictions
  → test with real questions

Next: compose multi-step queries from the schema you designed.