Relations, Keys and Basic SQL

1A relation is a table of facts

A relation is a table whose rows are facts of the same kind and whose columns are named attributes. Sale is a relation: every row is one purchase, and every row has sale_id, student_id, item_id and qty. The heading (those four names) is the schema. The four purchase rows are the instance — the facts that happen to be true today.

Order of rows does not change the relation. Sale listed 103 first is the same Sale. Duplicate rows are not a second fact in the pure model: a set does not contain the same tuple twice. SQL will later keep duplicates unless we ask it not to. That gap between the model and SQL is why we name both.

Figure. Four Sale rows at the campus kiosk: sale_id, who bought, what, how many.

Read Sale as a relation

1. Schemasale_id, student_id, item_id, qty — the attributes every sale must have.
2. InstanceThe four rows 100–103 that are true at the kiosk today.
3. Set vs SQLAlgebra treats rows as a set; SQL SELECT keeps a duplicate until DISTINCT.

2Keys: which column identifies a row

A key is a column (or a small set of columns) that names a row so we can find it again. sale_id 100 is enough to mean "Asha's two samosas" and nothing else. That is a primary key: unique, never empty, the official handle of the row. student_id is the primary key of Student; item_id is the primary key of Item.

A foreign key is a copy of someone else's primary key, stored so a row can point. Sale.student_id = 1 means "this sale belongs to the Student whose key is 1" — Asha. It is not a second primary key of Sale. Two sales can share student_id 1; they must not share sale_id.

Figure. Sale has one primary key and two foreign keys. The arrows land on the tables those ids name.

Keys on the three tables

1. Primary on Salesale_id is unique: 100, 101, 102, 103.
2. Primary on the nounsStudent.student_id; Item.item_id.
3. Foreign on Salestudent_id and item_id point; they may repeat (Asha appears twice).

3Relational algebra core operators

Relational algebra is a closed kit of operators: each one takes relations and returns a relation. Selection \sigma keeps rows that satisfy a condition — \sigma_{\text{item\_id}=11}(\text{Sale}) is the two tea sales. Projection \pi keeps named columns — \pi_{\text{student\_id}}(\text{Sale}) is the buyer ids. Union, difference, Cartesian product and rename complete the primitives.

A join is not a sixth primitive. It is product (every Sale row paired with every Item row), then selection on the matching ids, then projection to drop the duplicated item_id. SQL's FROM / WHERE / SELECT is the same three moves with different spelling.

Figure. Select filters rows, project keeps columns, join is built from those primitives — not a new primitive.

Tea sales from primitives

1. Select\sigma_{\text{item\_id}=11}(\text{Sale}) keeps rows 101 and 103.
2. Project\pi_{\text{student\_id, qty}} on that result keeps (2,1) and (3,3).
3. Join laterPairing Sale with Item is product + select on item_id + project.

4SELECT and WHERE on Sale

SQL is the language we type to ask a relation a question. SELECT names the columns we want back. FROM names the table we start from. WHERE names the row test. "Which sales are tea?" is: look at Sale, keep rows whose item_id is 11, return sale_id and qty.

WHERE runs on each row by itself. It cannot see a total across rows — that needs grouping, in the next lesson. It also cannot see a column we invent in SELECT (an alias), because WHERE happens before SELECT computes output names.

Figure. FROM starts with four Sale rows. WHERE item_id = 11 keeps the two tea rows.

Ask Sale for the tea rows

1. FROMStart with the Sale table — all four purchases.
2. WHEREKeep the rows where item_id = 11 (101 and 103).
3. SELECTReturn sale_id and qty for those two rows.

Tea sales at the kiosk

SELECT sale_id, qty
FROM sale
WHERE item_id = 11;
-- rows: (101, 1) and (103, 3)

Coding lab. Ask the kiosk for Asha's sales runs in the app, with checks on your output.

5SQL logical query order

We write SELECT first, but the engine does not start there. Logical order is FROM (build the working table), then WHERE (row filters), then GROUP BY, then HAVING, then SELECT (output expressions and aliases), then ORDER BY. That is why an alias invented in SELECT is invisible to WHERE.

On the kiosk: FROM sale WHERE student_id = 1 SELECT sale_id works. FROM sale WHERE teas > 0 SELECT qty AS teas fails, because teas does not exist until SELECT runs. Put the test on qty in WHERE, or filter the alias in a later clause.

Figure. Written SELECT-first; evaluated FROM-first. Aliases appear only at step 4.

Logical evaluation order

1. FROMResolve tables and joins into one working relation.
2. WHERE → GROUP BY → HAVINGFilter rows, group them, then filter groups.
3. SELECT → ORDER BYCompute output columns and aliases, then sort.

Skeleton showing clause roles

SELECT student_id, COUNT(*) AS n
FROM sale
WHERE qty > 0
GROUP BY student_id
HAVING COUNT(*) >= 1
ORDER BY n DESC;