SQL Scratchpad

Three tables and two helper views.

• pnw_towns — one row per town. Columns: town_id (primary key), town, state, primary_county, secondary_county, tertiary_county, primary_county_id, secondary_county_id, tertiary_county_id (foreign keys), population_2020_census, population_2010_census, land_area_sq_mi.
• pnw_counties — one row per county. Columns: county_id (primary key), county, state, fips_short, county_seat, county_seat_town_id (foreign key; NULL if the seat is not in pnw_towns), year_established, origin, etymology, population_2022, land_area_sq_mi.
• fips — FIPS codes for every US state and county (3,195 rows). Columns: fips (state code × 1000 + county code, so 41001 is Baker County, Oregon), name (OREGON, Baker County), state (the abbreviation, such as OR; blank on the state rows).

Relationships: a town's primary_county_id (and optionally secondary_county_id/tertiary_county_id) matches a county_id in pnw_counties; the name columns (primary_county and so on) hold the same county by name. A county's county_seat_town_id matches a town_id in pnw_towns. fips has no key column linking it to the other two tables: its state holds abbreviations (OR) where theirs holds full names (Oregon), so the diagram shows it on its own. A county matches its FIPS row when fips equals the state code times 1000 plus fips_short.

Helper views: towns_with_county and towns_with_counties both project (town, primary_county) from pnw_towns.

Five tables centred on flights.

• airlines — one row per carrier. Columns: carrier (PK), name.
• airports — one row per airport. Columns: faa (PK), name, lat, lon, alt, tz, dst, tzone.
• flights — one row per scheduled flight. Columns: year, month, day, dep_time, sched_dep_time, dep_delay, arr_time, sched_arr_time, arr_delay, carrier, flight, tailnum, origin, dest, air_time, distance, hour, minute, time_hour.
• planes — one row per aircraft. Columns: tailnum (PK), year, type, manufacturer, model, engines, seats, speed, engine.
• weather — hourly observations per origin airport. Columns: origin, year, month, day, hour, temp, dewp, humid, wind_dir, wind_speed, wind_gust, precip, pressure, visib, time_hour.

Relationships: flights.carrier → airlines.carrier; flights.origin → airports.faa; flights.dest → airports.faa; flights.tailnum → planes.tailnum; weather.origin → airports.faa.