TCX2003 | Notebook

Taking TCX2003 Database Systems this Special Term with Prof Jiang Kan. Flipped classroom, so most material is pre-watched and the live lecture is for clarification. This page is my running notebook, grows as the term goes.

For general SQL coverage (MySQL vs PostgreSQL, injection, ROLLUP, joins), see my SQL Notebook. For database design (normalization, dependency theory), see 3NF and BCNF.

1. MySQL Integer Types

Size & range reference

📖 Canonical reference: LeetCode SQL Explore: Data Structure covers full byte sizes, signed/unsigned ranges, and the wider CHAR/VARCHAR/TEXT/BLOB/DATE/DATETIME/TIMESTAMP family.

Personal version of this table will be hand-written later in my own style.

Decision tree

1Need negative numbers?
2├─ Yes  → use signed range
3└─ No   → add UNSIGNED → doubles your positive ceiling
4
5Max value < 256?      → TINYINT
6Max value < 65K?      → SMALLINT
7Max value < 16M?      → MEDIUMINT  (skip in practice, jump to INT)
8Max value < 2.1B?     → INT         ← safe default
9Beyond 2.1B?          → BIGINT

Storage math: every row saves 4 bytes by picking INT over BIGINT. At 10M rows that is 40 MB, doubled by indexes. On large tables this matters. On small tables, just use INT and move on.

Three gotchas worth memorizing

1. There is no SQL keyword LONG. People coming from Java or C reach for it. The 64-bit SQL integer is BIGINT.
2. INT(11) parentheses are display width, not storage size. Both INT(2) and INT(11) store 4 bytes. The number only affects ZEROFILL padding behavior, and the syntax was deprecated in MySQL 8.0.17+.
3. INT UNSIGNED joined against signed INT can drop indexes. MySQL inserts an implicit cast on one side, killing the index lookup. In production code, BIGINT is often safer than INT UNSIGNED for that reason.

2. Why DECIMAL Beats FLOAT for Money

Short version: FLOAT and DOUBLE cannot represent 0.1 exactly. Anything involving money, percentages, or values that must round trip through a database must use DECIMAL (a.k.a. NUMERIC, same type, different name).

The IEEE 754 problem

FLOAT (4 bytes) and DOUBLE (8 bytes) follow IEEE 754 binary floating point. They store numbers as sign × mantissa × 2^exponent. The mantissa is binary, so any decimal value that is not a sum of negative powers of 2 has no exact representation.

1-- MySQL demo
2SELECT 0.1 + 0.2;                                  -- → 0.3 (Decimal context, OK)
3SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE);  -- → 0.30000000000000004
4SELECT 0.1 + 0.2 = 0.3;                            -- → 1  (Decimal compare)
5SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) = 0.3;  -- → 0  (float compare fails)

0.1 in binary is the repeating fraction 0.0001100110011..., so the closest double is 0.1000000000000000055511151231257827021181583404541015625. Add two such approximations and the error compounds.

DECIMAL stores exact base-10 digits

DECIMAL(M, D) packs the digits themselves into binary-coded decimal form:

• M = total digit count (precision), max 65 in MySQL
• D = digits after the decimal point (scale), max 30
• Storage: 4 bytes per 9 digits, plus 1 byte per partial group

1CREATE TABLE invoice (
2  id          INT UNSIGNED PRIMARY KEY,
3  amount_sgd  DECIMAL(10, 2) NOT NULL  -- up to 99,999,999.99
4);

There is no rounding artifact. 0.10 + 0.20 stored in DECIMAL returns exactly 0.30.

When DECIMAL is required (not optional)

When FLOAT/DOUBLE is fine

Floats are not banned, just narrow purpose. They are good for:

• Scientific computing where relative error matters more than absolute error (e.g. 0.1 ± 0.00001 is acceptable)
• ML feature columns where the model itself tolerates float noise
• Sensor data logged at thousands of points per second where exactness is impossible anyway
• Graphics, geospatial intermediates where DOUBLE precision is wide enough

If your column will appear in a financial statement, choose DECIMAL. If it represents a measurement of the physical world, DOUBLE is usually correct.

Storage and speed tradeoff

DECIMAL is slower than FLOAT because the database does base-10 arithmetic, not hardware floating-point. For typical OLTP workloads (writes, lookups, simple SUMs) the difference is invisible. For tight numerical loops (millions of multiplications per query) it matters. Most application queries fall in the first bucket.

MySQL-specific notes (relevant for TCX2003)

• NUMERIC and DECIMAL are synonyms in MySQL. SQL standard names are interchangeable. Pick whichever the course slides use and stay consistent.
• The MySQL default if you write DECIMAL with no precision is DECIMAL(10, 0) (integer-like). Always specify (M, D).
• FLOAT in MySQL is single precision (4 bytes). DOUBLE, DOUBLE PRECISION, and REAL are all 8-byte double precision.

3. CREATE TABLE & the 6 Constraints

Skeleton

1CREATE TABLE table_name (
2  column1 TYPE [constraints],
3  column2 TYPE [constraints],
4  ...
5  [table_level_constraints]
6);

• Two-word keyword CREATE TABLE, never just CREATE.
• Column list ends with ) directly, no trailing comma (trailing comma is a genuine syntax error in SQL, not just style).
• Table-level constraints (composite PK, multi-column FK) go after the column list.
• For column types, see the integer reference in §1 and the DECIMAL/FLOAT decision in §2.

The 6 column constraints

1CREATE TABLE customers (
2  id        INT          PRIMARY KEY,                 -- 1. PRIMARY KEY
3  email     VARCHAR(128) UNIQUE NOT NULL,             -- 2. UNIQUE, 3. NOT NULL
4  age       INT          CHECK (age >= 18),           -- 4. CHECK
5  status    VARCHAR(16)  DEFAULT 'active',            -- 5. DEFAULT
6  store_id  INT          REFERENCES stores(id)        -- 6. FOREIGN KEY (inline form)
7);

MySQL gotcha: the inline col INT REFERENCES parent(id) form above parses fine but InnoDB silently ignores it — no foreign key is actually created. Only a table-level FOREIGN KEY (col) REFERENCES parent(id) enforces the constraint. Use the table-level form (next section) for any real FK.

Composite & table-level constraints

When a constraint spans multiple columns, put it at the table level:

1CREATE TABLE order_items (
2  order_id   INT,
3  product_id INT,
4  quantity   INT NOT NULL,
5  PRIMARY KEY (order_id, product_id),                 -- composite PK
6  FOREIGN KEY (order_id) REFERENCES orders(id),
7  FOREIGN KEY (product_id) REFERENCES products(id)
8);

FK referential actions (ON DELETE / ON UPDATE)

1FOREIGN KEY (store_id) REFERENCES stores(id)
2  ON DELETE CASCADE
3  ON UPDATE CASCADE

Mnemonic: C-S-R-N-D (Cascade, Set null, Restrict, No action, Default). In MySQL you’ll realistically only use the first three.

CHECK with REGEXP

CHECK combines with REGEXP (MySQL) for pattern validation:

1CREATE TABLE users (
2  username VARCHAR(32)  CHECK (username REGEXP '^[a-zA-Z0-9_]+$'),
3  email    VARCHAR(128) CHECK (email REGEXP '^[^@]+@[^@]+\\.[^@]+$'),
4  phone_sg VARCHAR(8)   CHECK (phone_sg REGEXP '^[89][0-9]{7}$')
5);

Anchors: ^ start, $ end, [a-z] class, [^abc] negated class, {n} exactly n, + 1+, * 0+, ? 0 or 1. In a SQL string literal the backslash is usually doubled (\\d). PostgreSQL uses ~ instead of REGEXP; this course is MySQL.

ALTER / DROP / RENAME

1ALTER TABLE customers ADD COLUMN phone VARCHAR(8);
2ALTER TABLE customers DROP COLUMN phone;
3ALTER TABLE customers MODIFY COLUMN age SMALLINT;     -- change type
4ALTER TABLE customers ADD CONSTRAINT chk_age CHECK (age >= 0);
5
6DROP TABLE IF EXISTS customers;                       -- safe form
7RENAME TABLE customers TO clients;

Worked example

A small bookstore schema covering most of the above:

 1CREATE TABLE authors (
 2  id    INT PRIMARY KEY,
 3  name  VARCHAR(64) NOT NULL,
 4  email VARCHAR(128) UNIQUE CHECK (email REGEXP '^[^@]+@[^@]+\\.[^@]+$')
 5);
 6
 7CREATE TABLE books (
 8  isbn      CHAR(13) PRIMARY KEY,
 9  title     VARCHAR(256) NOT NULL,
10  price     NUMERIC(8, 2) DEFAULT 0.00 CHECK (price >= 0),
11  author_id INT,
12  FOREIGN KEY (author_id) REFERENCES authors(id)
13    ON DELETE SET NULL
14    ON UPDATE CASCADE
15);
16
17CREATE TABLE reviews (
18  book_isbn CHAR(13),
19  reviewer  VARCHAR(64),
20  rating    TINYINT CHECK (rating BETWEEN 1 AND 5),
21  posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
22  PRIMARY KEY (book_isbn, reviewer),
23  FOREIGN KEY (book_isbn) REFERENCES books(isbn) ON DELETE CASCADE
24);

Covers: all 6 constraints, composite PK, multi-table FK chain, CHECK BETWEEN, CHECK REGEXP, DEFAULT CURRENT_TIMESTAMP, both CASCADE and SET NULL.

Gotchas worth memorizing

• PK implies NOT NULL — id INT PRIMARY KEY is enough; the NOT NULL is automatic.
• FK type must match parent’s PK type exactly — a mismatched type silently breaks joins.
• Fixed-length data deserves a fixed type — CHAR(2) for ISO country codes, CHAR(13) for ISBN, not VARCHAR.
• Composite uniqueness is a constraint, not a computed check — use UNIQUE (a, b) (or PRIMARY KEY (a, b)), not a hand-rolled CHECK with COUNT.

4. SQL Queries (SELECT)

Clause order — written vs evaluated

1SELECT   col, AGG(col)      -- 5. projected last
2FROM     t JOIN u ON ...    -- 1. tables assembled first
3WHERE    row_condition      -- 2. filter ROWS (before grouping)
4GROUP BY col                -- 3. collapse into groups
5HAVING   group_condition    -- 4. filter GROUPS (after aggregation)
6ORDER BY col                -- 6. sort
7LIMIT    n;                 -- 7. cut

You write SELECT first, but the engine runs FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That order explains the two most common mistakes below.

• WHERE filters rows, HAVING filters groups. A condition on a raw column → WHERE; a condition on an aggregate (COUNT(*) > 3) → HAVING. WHERE COUNT(*) > 3 is an error (aggregate doesn’t exist yet at WHERE-time).
• A column in SELECT alongside GROUP BY must be either grouped or aggregated — you can’t project a raw non-grouped column.

JOINs

Anti-join (rows in A with no match in B) — the idiom is a LEFT JOIN filtered on the NULL:

1SELECT a.id
2FROM   a LEFT JOIN b ON a.id = b.a_id
3WHERE  b.a_id IS NULL;        -- "a's that never appear in b"

Subqueries: IN vs EXISTS

1-- IN: compares a value against a ONE-COLUMN list
2WHERE dept_id IN (SELECT id FROM active_dept)
3
4-- EXISTS: checks whether a correlated subquery returns any row
5WHERE EXISTS (SELECT * FROM orders o WHERE o.cust_id = c.id)

• IN needs the subquery to project exactly one column; SELECT * fails.
• EXISTS ignores the columns entirely — SELECT * / SELECT 1 are equivalent and idiomatic.
• The NOT IN + NULL trap: if the subquery returns even one NULL, x NOT IN (…, NULL) evaluates to UNKNOWN for every row → zero results. Prefer NOT EXISTS (NULL-safe) whenever the subquery column is nullable.

Set operators

Both arms must have the same number of columns, compatible types, same order. Use whichever keyword the course slides use (MINUS vs EXCEPT).

Query-side gotchas worth memorizing

• English “or” under a negation → UNION, not INTERSECT. “seekers who did not claim or verify a skill” = NOT IN (claimed UNION verified). De Morgan: NOT(A OR B) excludes the union.
• NOT IN + nullable subquery → use NOT EXISTS (the NULL trap above).
• Anti-join = LEFT JOIN … WHERE right IS NULL (not NOT IN, which the NULL trap can break).
• UNION arms mirror each other — same column, other table; the difference is only the source, not the shape.

For deeper query coverage (window functions, ROLLUP, injection defence) see the engine-agnostic SQL Notebook.

Cross-references

• SQL Notebook: engine-agnostic SQL coverage, injection, set ops, ROLLUP
• 3NF and BCNF: normalization theory, relevant for TCX2003 ER design unit
• NUS Progress: live mastery tracker
• LeetCode SQL Explore card: Data Structure