🏛️Database concepts

The contest explicitly asks for "database concepts". Expect questions about schema design, indexes, transactions, and the OLTP vs OLAP split, either as MCQs or hidden inside a problem ("design a table so this query is fast").

Keys & constraints

• Primary key: unique and not null. Use a natural key (email, ISBN) or a surrogate key (auto-increment id, UUID). Warehouses use surrogate keys for dimensions.
• Foreign key: referential integrity (ON DELETE CASCADE / SET NULL / RESTRICT).
• UNIQUE, NOT NULL, CHECK (amount >= 0), DEFAULT.
• Composite key: e.g. PRIMARY KEY (order_id, product_id) on order_items.

Normalization (OLTP) in one table

Denormalization (OLAP) is the deliberate opposite. It duplicates data to avoid joins at read time, which suits warehouses and wide tables for BI.

Indexes

-- sargable rewrite
-- BAD:  WHERE DATE(created_at) = '2026-10-23'
-- GOOD: WHERE created_at >= '2026-10-23' AND created_at < '2026-10-24'
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);  -- serves WHERE customer_id=? ORDER BY order_date
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1 ORDER BY order_date;  -- MySQL/PG: EXPLAIN (ANALYZE)

Transactions: ACID

Isolation levels & anomalies

Lost update: two transactions read-modify-write the same row. Fix it with UPDATE t SET x = x + 1 (atomic), SELECT … FOR UPDATE, or optimistic locking with a version column. Deadlock: two transactions each wait for the other's lock. The database kills one, so retry it, and always lock rows in a consistent order.

MVCC (Postgres, InnoDB, Oracle): readers see a snapshot and don't block writers.

OLTP vs OLAP

Dimensional modelling (Kimball)

• Fact tables hold measurements at a declared grain ("one row per order line"), plus FKs to dimensions. They can be additive (amount), semi-additive (balance), or non-additive (ratio).
• Dimension tables hold descriptive context: customer, product, date. The star schema has denormalized dimensions; the snowflake schema normalizes them.
• Slowly Changing Dimensions: Type 0 never changes · Type 1 overwrites (no history) · Type 2 adds a new row with valid_from / valid_to / is_current (full history) · Type 3 adds a "previous value" column · Type 4 uses a separate history table.
• Date dimension: a precomputed calendar (weekday, holiday, fiscal quarter).

-- SCD2: point-in-time join, i.e. which city was the customer in when they ordered?
SELECT f.order_id, d.city
FROM fact_orders f
JOIN dim_customer d
  ON d.customer_id = f.customer_id
 AND f.order_ts >= d.valid_from
 AND (f.order_ts < d.valid_to OR d.valid_to IS NULL);

Scaling a database

• Partitioning (one database, one table split by range/list/hash, e.g. by month) enables partition pruning and cheap drops of old data.
• Sharding: data spread across servers by a shard key. Cross-shard joins are hard.
• Replication: primary → read replicas. Replication lag means eventual consistency for reads.
• CAP: during a network Partition, choose Consistency or Availability. BASE vs ACID.
• NoSQL families: key-value (Redis, DynamoDB), document (MongoDB), wide-column (Cassandra), graph (Neo4j), time-series.

How the database runs your query

The optimiser turns SQL into a plan of physical operators and picks the cheapest one based on table statistics (row counts, distinct values, histograms). Stale statistics → bad plans, which is why ANALYZE exists.

Reading EXPLAIN: look for full table scans on big tables (type=ALL in MySQL, Seq Scan in Postgres, SCAN in SQLite), the estimated vs actual row counts (EXPLAIN ANALYZE), and sorts or temporary tables (Using filesort, Using temporary) that an index could avoid.

Data types that matter

• Money: DECIMAL(12, 2), never FLOAT. Or store integer cents.
• Time: store TIMESTAMP WITH TIME ZONE / UTC, and convert at display time. DATE for calendar days. MySQL TIMESTAMP converts to UTC and is limited to 2038; DATETIME stores the value as given.
• Strings: VARCHAR(n) vs TEXT; utf8mb4 in MySQL (plain utf8 can't store emoji).
• IDs: BIGINT auto-increment (compact, ordered) vs UUID (globally unique, no coordination, but random inserts fragment a B-tree; UUIDv7 is time-ordered).
• Semi-structured: JSON/JSONB columns for flexible attributes; extract and index the fields you filter on.

Views, materialized views, temp tables

Worked example: design the schema

"A food-delivery app: customers place orders at restaurants; an order has several menu items; a courier delivers it. Analysts need daily revenue per city and average delivery time."

-- OLTP side (3NF)
CREATE TABLE cities      (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE customers   (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, city_id INTEGER REFERENCES cities(id));
CREATE TABLE restaurants (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city_id INTEGER NOT NULL REFERENCES cities(id));
CREATE TABLE menu_items  (id INTEGER PRIMARY KEY, restaurant_id INTEGER NOT NULL REFERENCES restaurants(id),
                          name TEXT NOT NULL, price DECIMAL(10, 2) NOT NULL CHECK (price >= 0));
CREATE TABLE orders      (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id),
                          restaurant_id INTEGER NOT NULL REFERENCES restaurants(id), courier_id INTEGER,
                          status TEXT NOT NULL CHECK (status IN ('placed', 'delivered', 'cancelled')),
                          placed_at TIMESTAMP NOT NULL, delivered_at TIMESTAMP);
CREATE TABLE order_lines (order_id INTEGER REFERENCES orders(id), menu_item_id INTEGER REFERENCES menu_items(id),
                          quantity INTEGER NOT NULL CHECK (quantity > 0),
                          unit_price DECIMAL(10, 2) NOT NULL,          -- price AT ORDER TIME, not a join to today's menu
                          PRIMARY KEY (order_id, menu_item_id));
CREATE INDEX idx_orders_restaurant_placed ON orders (restaurant_id, placed_at);

-- OLAP side (star): grain = one row per order line
--   fact_order_lines(date_key, city_key, restaurant_key, customer_key, quantity, revenue, delivery_minutes)
--   dim_date, dim_city, dim_restaurant, dim_customer (SCD2 if the customer's city history matters)