Practice — design a cricket league schema — SQL & Relational Databases

What You'll Build

In this exercise you will design and implement a complete, production-quality relational database schema for the CricketLeague Management System — a fictional platform that manages tournaments, teams, players, venues, matches, innings, and statistics for a national cricket body. You will apply every concept from Module 3: draw an ER model first, normalise the schema to 3NF, create appropriate indexes (B-tree, partial, functional), define views for the reporting layer, design a range-partitioned fact table for match statistics, and implement a data quality validation procedure. The final schema should handle 10 years of historical data and support efficient analytical queries.

The exercise is structured as a real database project: requirements first, ER model second, normalised schema third, physical design (indexes, partitioning) fourth, and reporting layer (views, functions) fifth. This sequence mirrors how database projects are executed in the industry — design before coding, normalisation before optimisation, logical before physical. Each step produces a named deliverable that subsequent steps build on.

The CricketLeague system must support four classes of queries: transactional (registering new players, recording match results), operational (generating team squads, validating player eligibility), analytical (batting leaderboards, bowling economy reports, venue utilisation), and administrative (archiving old seasons, enforcing data retention policies). These four query classes drive different physical design decisions — which columns need indexes, which tables need partitioning, which queries need materialised views.

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.

Prerequisites

Step 1 — ER Model (Design Before Coding)

Before writing any CREATE TABLE statements, identify the entities and relationships for the CricketLeague system. From the requirements, identify at least eight entities: Country, Player, Team, Season, Tournament, Venue, Match, and Innings. Identify the cardinality of each relationship (one-to-many, many-to-many). Draw the ER diagram on paper or using dbdiagram.io. Verify that every many-to-many relationship has a junction table. Document the ER model as comments at the top of your SQL file before writing any DDL.

Analogy🏏Cricket

🏏 Think of it like cricket: sketching the ER model before writing DDL is exactly like a captain planning field placements on the whiteboard before walking out — you don't rearrange fielders ball-by-ball once play starts; you decide the plan first. Just as a coach lists the squad roles — openers, spinners, keeper — before picking the XI, you list the entities (Country, Player, Team, Season, Tournament, Venue, Match, Innings) before creating a single table. Cardinality is the team-sheet logic: one country supplies many players (one-to-many), but players and teams across seasons is many-to-many — like the IPL auction, where a player may represent several franchises over the years and each franchise signs many players, which is precisely why a junction table (the contract register) must exist for every many-to-many link. Just as the match referee signs off the playing conditions before the toss, documenting the ER diagram as comments at the top of your SQL file makes the plan the official record the implementation must honour. The payoff: when you finally write CREATE TABLE, every relationship already has a home, and no mid-innings restructuring is needed.

Step 2 — 3NF Schema Implementation

Implement the full 3NF schema as a series of CREATE TABLE statements, in dependency order (referenced tables before referencing tables). Include: all NOT NULL constraints, CHECK constraints for domain validation, UNIQUE constraints for natural identifiers, DEFAULT values for commonly-defaulted columns, and comments on each table explaining its role in the schema. Create a separate database cricket_league for this exercise to keep it isolated from previous module databases.

Analogy🏏Cricket

🏏 Think of it like cricket: Building tables in dependency order is squad assembly protocol. You cannot record 'Kohli plays for the Royal Challengers' before the franchise exists in the league register and Kohli exists in the player register — the contract references both, so both registers must be created and populated first. CREATE TABLE order works identically: parent tables (teams, players, venues) precede child tables (contracts, matches, innings) because every foreign key must point at something that already exists, just as a scorecard cannot cite an unregistered player. Getting the dependency order wrong fails immediately and loudly — the database refuses the forward reference, the way a league office bounces a contract naming a franchise that hasn't been chartered. The practical habit: sketch the arrows first (who references whom), then create from the arrow tails to the arrow heads, and tear down in exactly the reverse order.

Step 3 — Physical Design: Indexes

Apply the indexing patterns from Lesson 2 to the schema. Create indexes for: all foreign key columns (both composite foreign keys), common analytical filter columns (match_date, format, player_id), the player name for case-insensitive search (functional index), a partial index for active players only, and the innings runs_scored for high-score filtering. Also add GIN indexes for any full-text or JSONB columns if they exist in your schema extension.

Analogy🏏Cricket

🏏 Think of it like cricket: adding indexes to your schema is like a coaching staff preparing their pre-match dossiers — the raw footage (table data) already exists; indexes are the organised quick-reference sheets that make questions fast. Indexing every foreign key cross-references each innings card back to its match and player files, so joins never rifle the whole archive. The functional index on lower(player_name) is like filing players phonetically so 'de villiers', 'De Villiers' and 'DE VILLIERS' all lead to the same card regardless of how the analyst writes it. A partial index on active players only is the shrewdest trick: like a selector's shortlist that tracks only currently available players rather than every cricketer who ever played — smaller, faster, and covering exactly the queries that matter. Indexing runs_scored for high-score filtering mirrors the honours board at Lord's: centuries are looked up constantly, so keep them pre-sorted. And GIN indexes on JSONB or text columns are the keyword-tagged video library for free-form searching. The payoff: each production query pattern gets a purpose-built lookup, just as each opposition batter gets a purpose-built plan.

Step 4 — Reporting Layer: Views and Functions

Create the three reporting objects that the CricketLeague analytics team uses daily. First, a view v_batting_summary that encapsulates the career statistics computation for each player. Second, a materialised view mv_season_leaderboard for the pre-computed season batting rankings. Third, a PL/pgSQL function fn_team_performance(team_id INT, season_year INT) that returns a team's match record for a given season. Each object should include comments documenting its purpose and expected refresh frequency.

Analogy🏏Cricket

🏏 Think of it like cricket: The reporting layer is the difference between the scorers' raw books and the polished stats the press box actually consumes. A view like v_batting_summary is the bureau's standing report format: journalists ask for 'the career summary' by name and the bureau assembles it fresh from the underlying books each time — the joins, filters and calculations are encapsulated once, so every consumer gets identical logic instead of each reporter re-deriving averages their own slightly-different way. That is the real win: when the definition of 'batting average' needs a correction, you fix the view once and every downstream report is corrected simultaneously. Functions are the bureau's parameterised services — 'give me player X's form over the last N matches' — the same encapsulation but accepting inputs, like a stats desk that answers structured queries rather than handing out one fixed page.

Step 5 — Verification: Schema Audit

Run the schema audit queries below to verify the final schema meets the design requirements. Every foreign key should have an index, every table should have a primary key, the matches table should have the correct number of partitions, and the normalisation audit should confirm no obvious transitive dependencies. Document any findings and their resolutions before considering the schema complete.

Analogy🏏Cricket

🏏 Think of it like cricket: the schema audit is the match referee's pre-game inspection — no toss happens until the pitch, boundary ropes, and stumps are all checked against the playing conditions. Just as the referee walks a checklist (covers off? sightscreens working? boundary at regulation distance?), you run audit queries against a checklist: every foreign key has an index, every table has a primary key, the matches table has exactly the expected partitions, and no transitive dependencies lurk in the normalisation audit. Skipping the audit is like assuming the net sessions went well so the stadium must be ready — the failures only surface under match pressure, when a missing FK index turns a production join into a full scan in front of a live crowd. And just as the referee's report documents every defect found and how it was fixed before the match is sanctioned, you record each audit finding and its resolution before declaring the schema complete. The payoff: verification converts 'I think the design is right' into inspected, documented proof — the difference between a practice pitch and one certified for a real fixture.