---
title: "Database Fundamentals"
subtitle: "October 28 · Design the system your queries need"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## One question will organize today

::: {.question}
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

::: {.source-note}
This course follows and adapts [Alex Reinhart's MADS Computing course](https://www.refsmmat.com/courses/msp-computing/). Today's relational-model, schema, type, relationship, and client–server concepts follow his [Database Fundamentals chapter](https://www.refsmmat.com/courses/msp-computing/data-engineering/database-fundamentals.html); 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

```text
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

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

Start with four nouns:

```text
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

```sql
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

```sql
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

```sql
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:

```text
at most one observation per station per date
```

Encode that separately:

```sql
UNIQUE (station_id, date)
```

## Column rules can span values

```sql
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

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

```sql
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

```sql
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

```text
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

```text
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:

```text
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

::: {.checkpoint}
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:

1. Which stations have no observation for a requested date?
2. 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

```text
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.