What is a database?

Every app, every service, every research dataset you interact with is backed by a database.

A database is an organized collection of data.

Managing databases

A Database Management System (DBMS) is the software that sits between you and the data.

Why use a DBMS?

Three elements are needed from your databases:

Note: Check the 🔎 Self-Study for the ACID properties: the four guarantees that keep database transactions reliable.

Data lives in tables

The fundamental idea of relational databases: data is organized into relations (tables).

Data lives in tables

The fundamental idea of relational databases: data is organized into relations (tables).

Each table/relation has:

• A (relation) schema: the table name, column names, and their types (or domain)
• Columns (attributes): one variable per column
• Rows (records / tuples): one entry per observation

We can write the relation schema as:

Table(attribute_1: INT, attribute_2: VARCHAR, ...)

Check the 🔎 Self-Study for a quick note on the terminology of the relational model.

Data lives in tables

The fundamental idea of relational databases: data is organized into relations (tables).

Countries(country_id: INT, name: VARCHAR, region: VARCHAR, gdp_usd: FLOAT)
Trade_flows(flow_id: INT, exporter_id: INT, importer_id: INT, year: INT, value_usd: FLOAT)

Relational databases are built on relationships

A relational database is a collection of relations (tables) that are linked together through keys.

Relationships are built on keys

Primary key

• Column (or set of columns) that uniquely identifies each row. If two tuples agree on the value(s) of the key, then they must be the same tuple.
• It is NOT NULL and UNIQUE by definition
• It allows other tables to refer to this table

Note: A good primary key is stable (never changes), unique, and meaningless on its own: a number, not the country name, which could change. If no natural attribute works as a key, you create a surrogate key (an artificial integer ID). When a single column isn’t enough, a composite key combines two or more columns.

Relationships are built on keys

Primary keys in our model?

Relationships are built on keys

Primary keys in our model?

Relationships are built on keys

Foreign key

• A column (or set of columns) that refers to a primary key in another table. It creates a link between the two tables.
• Always in the referencing table (the “many” side of a one-to-many relationship)
• A foreign key constraint means: you cannot insert a row with a foreign key value that does not exist in the referenced table.
• This is known as referential integrity.

Relationships are built on keys

What are the foreign keys in our model?

Relationships are built on keys

What are the foreign keys in our model?

Why split data into multiple tables?

Yes, why?

Why split data into multiple tables?

Imagine storing everything in one table:

The star schema ⭐

• Most widely used to develop data warehouses (databases optimized for analytics)
• Consists of one or more fact tables referencing any number of dimension tables.

The star schema ⭐

• Moves repeated metadata into separate tables and replaces strings with integer IDs.
  -> normalization
• Upside: consistency, compactness, flexibility. Downside: you need JOINs to get readable labels.

Note: Check the 🔎 Self-Study for a quick note on the star schema and normalization.

The star schema ⭐

We will explore a simple star schema with FRED data in the exercise session.

Think in entities and relationships

Before creating a database, sketch the structure of your data. Think visually about your data structure and your schema design.

Note: The concept of “entity” is close to the idea of “unit of observation” in statistics. It is the “thing” that you are collecting data about.

Write your schema in a simple text format first

For instance:

Countries(country_id: INT PK, name: VARCHAR, region: VARCHAR, gdp_usd: FLOAT)

Trade_flows(flow_id: INT PK, exporter_id: INT FK->Countries, importer_id: INT FK->Countries, year: INT, value_usd: FLOAT)

• PK = primary key, FK = foreign key
• FK→Countries means it references the Countries table
• This is an informal shorthand for sketching schemas, not a formal standard. Different textbooks use different conventions.

Or use an ER diagram

Mermaid 🧜‍♀️ is a text-based diagram language for entity-relationship diagrams (ERDs), which renders directly in Quarto.

erDiagram
    COUNTRIES {
        int     country_id  PK
        varchar name
        varchar region
        float   gdp_usd
    }
    TRADE_FLOWS {
        int   flow_id      PK
        int   exporter_id  FK
        int   importer_id  FK
        int   year
        float value_usd
    }
    COUNTRIES ||--o{ TRADE_FLOWS : "exports"
    COUNTRIES ||--o{ TRADE_FLOWS : "imports"

erDiagram
    COUNTRIES {
        int     country_id  PK
        varchar name
        varchar region
        float   gdp_usd
    }
    TRADE_FLOWS {
        int   flow_id      PK
        int   exporter_id  FK
        int   importer_id  FK
        int   year
        float value_usd
    }
    COUNTRIES ||--o{ TRADE_FLOWS : "exports"
    COUNTRIES ||--o{ TRADE_FLOWS : "imports"

Thinking in entities and relationships

SQL — Structured Query Language

Note: Originally based upon relational algebra and tuple relational calculus. We won’t go into details in this introductory course. I recommend Marco Venturini’s lecture for more details.

SQL — Structured Query Language

SQL has five main parts, or five main purposes:

• DDL — Data Definition Language: CREATE TABLE, DROP TABLE, ALTER TABLE
• DML — Data Manipulation Language: INSERT, UPDATE, DELETE records
• DQL — Data Query Language: SELECT
• DCL — Data Control Language (authorization): GRANT, REVOKE
• TCL — Transaction Control Language (manages changes in transactions): COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

In this course, we’ll focus on DDL, DML and DQL.

The database universe

Hundreds of database products exist.

Source: A Primer on Databases

👓 The most important difference among the dbms is OLTP vs. OLAP

• Both have different internal architectures, optimised for different goals.
• Both have a SQL query interface.

Note: See Kleppmann’s Designing Data-Intensive Applications (O’Reilly, 2017, Chapter 3). The distinction boils down to three dimensions: storage layout (row-oriented vs column-oriented), indexing strategy, concurrency model.

In this course…

We will use DuckDB as DBMS

• DuckDB is usable from Python and R. The project.db file can be accessed from both.
• A typical workflow 🔄 : create a connection to a project.db file, create tables, query, get results, then close the connection.

import duckdb

# Open (or create) a .db file
conn = duckdb.connect("project.db")

# Query → pandas DataFrame
df = conn.execute("""
    SELECT name, gdp_usd
    FROM   countries
    WHERE  region = 'Europe'
    ORDER BY gdp_usd DESC
""").df()

conn.close()

library(duckdb)
library(DBI)

# Open (or create) a .db file
con <- dbConnect(duckdb(), "project.db")

# Query → data.frame
df <- dbGetQuery(con, "
    SELECT name, gdp_usd
    FROM   countries
    WHERE  region = 'Europe'
    ORDER BY gdp_usd DESC
")

dbDisconnect(con, shutdown = TRUE)

Use the companion python script

It contains all the code to create the database and run queries.

OLTP, OLAP, and the ETL pipeline

Kleppmann, 2017, p. 88

Data is extracted from OLTP databases, transformed into an analysis-friendly schema, cleaned up, and then loaded into the data warehouse.

This process is known as Extract-Transform-Load (ETL)

Extract-Transform-Load (ETL)

A common pipeline that fits well with the type of work we do in (Econ) research.

👓 The ETL pipeline

Why not load raw data directly into a database?

• Raw data is often wide, messy, missing IDs
• SQL has no melt(), no (advanced) cleaning
• pandas or tidyverse/data.table in R are the right tool for reshaping

Why separate the raw data from the data warehouse?

• Database is optimized for analytic access
• No interference with the operation of the business

Conduct the Extract and Transform in python

Use the companion script.

Building a database is a two-step process

1. 🦴 Set up the architecture of the data through a data model.

   • Use SQL to create tables, define columns, and set constraints.
   • This is the “skeleton” of your database, the structure that holds your data together.

2. Populate/Load the model with the transformed data (ETL)

   • Use python/R to clean and reshape your raw data, assign IDs, and prepare it for loading.
   • Use SQL INSERT statements to add rows to your tables.

CREATE our first TABLE

• Creating a table means defining its schema: the column names, their types, and any constraints.
• The schema is self-documenting: anyone reading it knows the rules.

It follows a simple syntax:

CREATE TABLE <table name> (
    <var name>  <var type> <constraint> ,
    <var name>  <var type> <constraint> ,
    ...
);

Atomic data types in SQL

• The SQL standard defines a core set of data types.
• Each dialect has its own extensions, but these are the most common:
  • Characters: VARCHAR[(length)] (Variable-length string, max n)
  • Numeric: INTEGER/INT (four-byte integers), BIGINT (64-bit integers), SMALLINT (16-bit integers), FLOAT (approximate floating-point), DECIMAL(p, s) (Exact fixed-point number with precision p and scale s)
  • Others: DATE, TIME, DATETIME/TIMESTAMP, INTERVAL, BOOLEAN

Note: FLOAT is used in this course for simplicity. For monetary values in production, prefer DECIMAL(p, s) to avoid floating-point rounding errors (e.g. DECIMAL(20, 2)). Refer to the documentation of each SQL dialect for the full list of supported types.

SQL constraints

Constraints enforce rules on your data at the database level. They are optional (but highly recommended to ensure data integrity and consistency).

*When a single column is not enough to identify a row uniquely, use a composite primary key:

-- e.g. for an observations table: one row per (state, indicator, date)
PRIMARY KEY (state_id, indicator_id, date)

Create the table “countries”

CREATE TABLE countries (
    country_id INTEGER PRIMARY KEY,
    name       VARCHAR NOT NULL UNIQUE,
    region     VARCHAR,
    gdp_usd    FLOAT   CHECK (gdp_usd >= 0)
);

• country_id: integer, the primary key: NOT NULL + UNIQUE by definition
• name: text, required (NOT NULL), no duplicates (UNIQUE)
• region: text, optional (no constraint — NULL is allowed)
• gdp_usd: number, constraint: must be non-negative

Create the table “trade_flows”

CREATE TABLE trade_flows (
    flow_id     INTEGER PRIMARY KEY,
    exporter_id INTEGER NOT NULL REFERENCES countries(country_id),
    importer_id INTEGER NOT NULL REFERENCES countries(country_id),
    year        INTEGER NOT NULL,
    value_usd   FLOAT   CHECK (value_usd >= 0)
);

• REFERENCES countries(country_id): foreign key constraint.
• The value of exporter_id must match an existing country_id in the countries table. Same for importer_id.
• Will reject any insert where exporter_id has no matching row in countries
• Both exporter_id and importer_id point to the same table

Load using INSERT

The INSERT statement adds rows to a table. It must respect all constraints defined in the schema.

INSERT INTO <table> VALUES (1, 'Switzerland', 'Europe', 905000000000);

Or, DuckDB allows to import from CSV, Parquet, JSON directly:

INSERT INTO countries SELECT * FROM read_csv('data/countries_raw.csv');

Or from a pandas DataFrame (DuckDB-Python only: DuckDB recognises the variable df_flows in your Python scope):

INSERT INTO trade_flows SELECT * FROM df_flows

Load using INSERT in our example

• Populate the tables with some rows using INSERT

-- Populate countries first (the referenced table must exist)
INSERT INTO countries VALUES (1, 'Switzerland', 'Europe', 905000000000);
INSERT INTO countries VALUES (2, 'Germany',     'Europe', 4072000000000);
INSERT INTO countries VALUES (3, 'Brazil',      'LATAM',  2081000000000);

-- Now populate trade flows
INSERT INTO trade_flows VALUES (1, 1, 2, 2022, 45000000);
INSERT INTO trade_flows VALUES (2, 2, 3, 2022, 120000000);

👓 What the foreign key actually prevents

1. Inserting a row with a non-existent reference

-- This works: country 1 exists in countries
INSERT INTO trade_flows VALUES (1, 1, 2, 2022, 45000000);

-- This fails: country 99 does not exist
INSERT INTO trade_flows VALUES (2, 99, 2, 2022, 10000000);
-- Error: FOREIGN KEY constraint failed

👓 What the foreign key actually prevents

2. Deleting a referenced row

The constraint also works in the other direction: you cannot delete a row that is still referenced.

-- This fails: country 1 is referenced by trade_flows
DELETE FROM countries WHERE country_id = 1;
-- Error: Violates foreign key constraint

-- You must delete the child rows first
DELETE FROM trade_flows WHERE exporter_id = 1 OR importer_id = 1;
DELETE FROM countries  WHERE country_id = 1;  -- now this works

Querying a relational database

A query is a request for specific information from the database. It takes one or more tables as input and returns a new table as output.

What is Switzerland’s GDP?

SELECT gdp_usd
FROM   countries
WHERE  name = 'Switzerland';

Structure of a SQL query

Everything starts with SELECT

SELECT column1, column2
    FROM   table_name

Filtering with WHERE

SELECT column1, column2
    FROM   table_name
    WHERE  condition

ORDER BY and LIMIT

SELECT gdp_usd
    FROM     countries
    WHERE    region = 'Europe'
    ORDER BY gdp_usd DESC
    LIMIT    5

The basic query structure

-- Comment: the basic structure of a SQL query
SELECT   column1, column2   -- which columns to return
FROM     table_name         -- which table
WHERE    condition          -- filter rows (optional)
ORDER BY column ASC/DESC    -- sort (optional)
LIMIT    n                  -- truncate output (optional)
;

👓 UPDATE, ALTER TABLE, DELETE

ALTER TABLE: modify the structure of a table

ALTER TABLE countries ADD COLUMN income_group VARCHAR;

UPDATE: modify existing rows

UPDATE countries
SET    income_group = 'High income'
WHERE  region = 'Europe';

DELETE: remove rows

DELETE FROM countries
WHERE  name = 'Switzerland';

Write SQL queries with DuckDB from Python

import duckdb

# Create a connection to a .db file
conn = duckdb.connect("week_09/trade.db")

# Query
conn.execute("""
    SQL QUERY
""").df()

# Close the connection when done
conn.close()

👓 Some useful operators and functions

COUNT, * and UNION ALL

• The * operator in SELECT * means “all columns”.
• The COUNT(*) function counts the number of rows in the result, regardless of NULLs.
• UNION ALL combines results from two queries (keeping duplicates). UNION removes duplicates. It is like row binding in R or pandas.

Dates

• Use EXTRACT(YEAR FROM date_column) to get the year from a date variable. Works with MONTH, DAY, etc.
• Example: WHERE EXTRACT(YEAR FROM date) = 2020

👓 Some useful operators and functions

Strings

• String functions: UPPER(), LOWER(), CONCAT(), SUBSTRING(), etc.

SELECT CONCAT(name, ' (', region, ')') AS label FROM countries;
-- → 'Switzerland (Europe)'

SELECT SUBSTRING(name, 1, 3) AS code FROM countries;
-- → 'Swi'

👓 Some observations on SQL syntax

Queries

• All queries start with SELECT even if you don’t want to select any column (e.g. SELECT COUNT(*)).
• Queries don’t modify data: only INSERT, UPDATE, DELETE do

Formatting

• SQL ignores whitespace and line breaks: formatting is purely for readability
• Strings use single quotes (‘states’), not double quotes
• All keywords are case-insensitive, but it is a common convention to write them in CAPS to distinguish them from column names.
• Column/table names are case-insensitive by default, but values are case-sensitive (‘CA’ ≠ ‘ca’)

👓 Some observations on SQL syntax

You are now a SQL user!

✅ You understand why we use databases

• Why databases exist: memory limits, multiple users, data integrity
• The relational model: tables, keys, relationships

✅ You can build a schema

• Design entities and relationships with an ER diagram
• Write CREATE TABLE with constraints (PK, FK, NOT NULL, CHECK)

✅ You can query

• SELECT, WHERE, ORDER BY, LIMIT
• Connect to DuckDB from Python and run queries with conn.execute()

Sources

• Simon Aubury & Ned Letcher, Getting Started with DuckDB (Packt, 2024)
• Jacob Montiel, sql-intro for the motivation and intro structure
• Marco Venturini, Data Handling: Databases (HSG, 2025). Course on relational model and schema concepts, 6 lectures going deeper in the topic of relational models and relational algebra
• DuckDB documentation: https://duckdb.org/docs/
• Kleppmann’s Designing Data-Intensive Applications (O’Reilly, 2017)

A note on self-study

In lecture 9 on databases, none of the self-study sections are exam relevant.

🔎 Self-study: ACID properties

ACID is the set of four guarantees that make database transactions reliable:

In practice for this course: the constraints you define with PRIMARY KEY, NOT NULL, REFERENCES enforce Consistency. The rest (Atomicity, Isolation, Durability) are handled automatically by the DBMS.

🔎 Self-study: Terminology

The relational model has formal names.

You will see all these terms used interchangeably in documentation, Stack Overflow, and textbooks. They are the same concepts.

🔍 Self-study: The star schema ⭐ and normalization

A star schema sits in the middle of the normalization spectrum:

This slide is just a hint at the topic of normal forms and database formalizations (0NF, 1NF, 2NF, 3NF). We won’t have time to cover this in this introductory course.

🔎 Self-study: More on DuckDB

DuckDB speaks SQL, but it is an analytical database.

🔎 Self-study: Where does each tool fit?

Two axes place every database product:

• SQLite and DuckDB share the same architecture: both embedded, no server, single file
• They differ on workload: SQLite stores app state (OLTP), DuckDB analyses data (OLAP)
• DuckDB’s unique position: the only mature embedded OLAP engine

🔎 Self-study: What the foreign key actually prevents

3. Handling the deletion of a referenced row

The foreign key also controls what happens when you delete a referenced row.

CREATE TABLE trade_flows (
    flow_id     INTEGER PRIMARY KEY,
    exporter_id INTEGER NOT NULL
                REFERENCES countries(country_id) ON DELETE CASCADE,
    ...
);