Exercise 1 — design the CricketVerse schema

What You'll Build

In this exercise you design and implement the complete CricketVerse PostgreSQL schema: a normalised 3NF logical design covering all nine entities and their relationships, followed by the physical design (indexes and partitioning) that meets the non-functional requirements. This is the foundation on which Exercises 2 and 3 build. Attempt the design yourself before reading the reference solution — sketch the ER model, decide embedding versus referencing, identify the partition key for the deliveries fact table, and plan the index strategy from the functional requirements.

The exercise has three parts. Part A is the logical schema: CREATE TABLE statements for all nine entities with primary keys, foreign keys, NOT NULL and CHECK constraints, and UNIQUE constraints on natural identifiers. Part B is the physical schema: indexes on all foreign keys and analytical filter columns, plus range partitioning on the deliveries table by match date. Part C is the verification: an audit confirming 3NF compliance, foreign key index coverage, and correct partition configuration.

Analogy🏏Cricket

🏏 Think of it like cricket: This exercise runs like a proper tournament bureau's production week, and the step order is the point. Step 1 is the pitch inspection before play: you verify the ground truth — no orphaned scorecard lines, no impossible totals — because an analysis built on a corrupt book is a match played on a dangerous pitch: everything after it is invalidated. Step 2 is the specialist coaches' reports: batting summaries with rankings and form lines, each an independent, checkable piece of work using window functions over the validated data. Step 3 is the selectors' composite: batting and bowling folded into one all-rounder view via conditional aggregation — the wide wall chart built from the long book. Step 4 is the match referee's reconciliation: the chart's totals must re-add to the book's totals exactly, or something was dropped or double-counted on the way. Validate, analyse, combine, reconcile — every production pipeline plays in that order.

Requirements and Acceptance Criteria

Part A — Reference Solution: Logical Schema

The reference schema demonstrates several deliberate design decisions. The deliveries table records batter_id, bowler_id, non_striker_id, and fielder_id as four separate foreign keys to the same players table — a single player entity playing multiple roles per delivery. The fielder_id is nullable because not every delivery involves a fielder (a dot ball or a boundary has no fielder). The match_date column is denormalised into the innings and deliveries tables specifically to support the composite foreign key to the partitioned matches table — required because PostgreSQL mandates that foreign keys to partitioned tables include the partition key.

The DO block at the end programmatically creates yearly partitions for all three fact tables (matches, innings, deliveries) using a nested loop — generating CREATE TABLE PARTITION OF statements for years 2020 through 2025 plus a DEFAULT partition for each. This automation pattern is cleaner than writing 21 individual CREATE TABLE statements and demonstrates how partition management can be scripted. In production, the pg_partman extension would automate ongoing partition creation as new years arrive.

Analogy🏏Cricket

🏏 Think of it like cricket: The deliveries table carrying four separate player references is one ball wearing four hats: someone bowled it, someone faced it, someone stood at the non-striker's end, and perhaps someone caught it. All four columns point at the same players table — the same register consulted four times per row — which is why each needs its own foreign key and, later, its own index: 'all deliveries Bumrah bowled' and 'all deliveries Kohli faced' are different questions arriving through different columns. The partition-creating DO block is the groundsman's pre-season routine done programmatically: rather than hand-painting the creases on every pitch of a ten-year archive, you write the loop once — for each season, for each fact table, create the year's partition — and let it stamp out identical, correctly-bounded sections. Manual repetition is where boundary-date typos breed; generated DDL makes every partition's fences provably consistent.

Part B — Reference Solution: Physical Design

The index strategy in Part B is driven directly by the functional requirements. The composite index idx_deliveries_h2h on (batter_id, bowler_id) with INCLUDE (runs_batter, is_wicket) directly supports FR2 (head-to-head analysis) — it allows an index-only scan to compute how a batter performs against a bowler without reading the heap. The partial index idx_deliveries_wickets on wicket deliveries only supports bowling analysis efficiently by indexing the small subset of deliveries that resulted in wickets. The three separate role indexes (batter, bowler, fielder) support the different player-centric analytical queries independently.

The fielder_id index uses a partial WHERE fielder_id IS NOT NULL clause because the fielder_id column is nullable — many deliveries (dot balls, boundaries) have no fielder. A partial index that excludes NULL values is smaller and faster than a full index, and fielding analysis queries always filter for non-null fielder_id anyway. This is the partial index pattern from Module 3 applied to a real requirement: index only the rows that queries actually need.

Analogy🏏Cricket

🏏 Think of it like cricket: A partial index is a fielding drill run only for the players it concerns. The fielder_id column is null for most deliveries — dot balls and boundaries involve no catcher — so a full index would be a register where most pages just say 'nobody', bulkier to store and slower to maintain for zero benefit: no query ever asks 'find deliveries where the fielder is unknown' through that index. The WHERE fielder_id IS NOT NULL clause keeps only the meaningful entries, like a catching-practice roster that lists just the slip cordon instead of the entire touring party plus hotel staff. The composite head-to-head index is the other targeted choice: (batter_id, bowler_id) ordered to match the question the platform actually asks — 'this batter against this bowler' — the way a scorer pre-sorts duel summaries by the exact pairing the press box requests every single day.

Exercise 1 — design the CricketVerse schema

What You'll Build

Requirements and Acceptance Criteria

Part A — Reference Solution: Logical Schema

Part B — Reference Solution: Physical Design

Part C — Reference Solution: Verification Audit