A relational database keeps data in tables, also called relations. Each table describes one kind of thing, such as a customer or a claim; each row is one of them; each column is one fact about it, with a type that says what values it may hold. The idea, from Edgar Codd in 1970, is that data is stored once, in plain tables, and every question is answered by combining them with a query language.
Keys
A primary key identifies each row: unique, and never null. claims.claim_id is the primary key of claims.
A foreign key is a column that refers to another table's primary key: claims.policy_id says which policy a claim is made on. The database refuses a claim on a policy that does not exist.
A natural key is a value from the business, such as a policy number; a surrogate key is a number the database makes up, such as policy_id. Harbour Mutual uses surrogate keys, and keeps the business numbers as unique columns.
Types
Type
Holds
In Harbour Mutual
INT, BIGINT, SERIAL
whole numbers; SERIAL numbers new rows itself
ids, counts
NUMERIC(p, s)
exact decimals: p digits, s after the point
premiums and claim amounts: money is never a floating-point number
TEXT, CHAR(n)
text of any length, or exactly n characters
names, causes, country codes
DATE
a calendar day
policy periods, loss and payment dates
BOOLEAN
true, false or null
results of checks
JSONB
a JSON document
the adjusters' claim files
Null
Null means unknown or not applicable: a policy sold directly has no broker, so its broker_id is null. Null is not zero and not an empty text, and it needs care in every query (see How a query is evaluated).
The parts of SQL
Part
Statements
Day
Querying
SELECT
1 to 4, 6
Defining (DDL)
CREATE, ALTER, DROP
1, 5, 6, 7
Changing data (DML)
INSERT, UPDATE, DELETE
1, 5, 7
Controlling access (DCL)
GRANT, REVOKE
10
Transactions (TCL)
BEGIN, COMMIT, ROLLBACK
10
SELECT table_name, COUNT(*) AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY table_name;
The database describes itself: information_schema lists every table and column.Try it in the playground
SQL is declarative: you describe the result you want, and the database decides how to compute it. Day 8 shows how it decides.