Build a Cricket Tournament Schema

What You'll Build

In this capstone-style exercise you will design, create, and populate a complete six-table IPL tournament schema from scratch, then exercise it with data modification statements, transactions, and indexes. You will translate real cricket entities, teams, venues, players, matches, innings, and deliveries, into properly typed, constrained, and related tables, the kind of foundational data model every cricket application is built upon.

By the end you will have a single SQL file that builds a fully relational schema with primary keys, foreign keys, and CHECK constraints, seeds it with valid data, performs a transactional player transfer, and adds indexes verified with EXPLAIN. This brings together everything in the module: DML, transactions, schema design, and performance, applied to one coherent, realistic database.

The focus is on integrity by design and safe modification. You will let the schema reject invalid data, wrap a multi-step change in a transaction, and confirm your indexes are actually used. Predict how each statement behaves, including which bad inserts the constraints will refuse, then verify, so you experience the database enforcing correctness rather than merely hoping your data is clean.

Prerequisites

• A working PostgreSQL setup with the psql client, as built in earlier exercises, ready to run SQL files against a local database.
• Completion of this module's lessons on INSERT/UPDATE/DELETE, transactions and ACID, CREATE TABLE with types and constraints, and indexes.
• Familiarity with joins and aggregates from Modules 2 and 3, since you will query the new schema to verify it behaves as designed.
• A text editor to maintain a single tournament_schema.sql file so the entire build is reproducible and can be rebuilt from scratch.

Setup & Project Structure

You will create a fresh database and build the whole schema in one rerunnable script, dropping any existing tables first so the build is fully reproducible. A separate operations file holds the DML, transaction, and indexing statements. This separation, structure in one file and modifications in another, mirrors how real projects keep schema migrations distinct from data operations.

Building everything from scripts means the entire tournament database, structure, constraints, and seed data, can be recreated identically at any time with a single command. This reproducibility is the hallmark of professional database work: the schema is a version-controlled artifact, not a fragile hand-built state, so anyone can stand up an identical environment for development, testing, or debugging.

# Fresh database for the tournament schema.
dropdb ipl_tournament 2>/dev/null; createdb ipl_tournament
mkdir -p ipl_tournament_lab && cd ipl_tournament_lab
touch schema.sql        # the six-table schema + seed data
touch operations.sql    # DML, transaction, and index statements

psql ipl_tournament -f schema.sql
psql ipl_tournament -f operations.sql

# Confirm all six tables exist:
psql ipl_tournament -c "\dt"
psql ipl_tournament -c "SELECT COUNT(*) FROM deliveries;"   # expect 8

Step 1 — Foundation

Step one designs and creates the six related tables with deliberate types and constraints. Teams and venues are reference tables; players reference a team; matches reference two teams and a venue; innings reference a match and a batting team; deliveries reference an innings and a batsman. Every relationship is a foreign key, and CHECK constraints forbid impossible values like negative runs.

This normalised design stores each fact once and wires the tables together with keys, exactly as a production cricket database would. Defining precise types, NOT NULL where required, UNIQUE where appropriate, and CHECK constraints throughout means the schema itself guarantees validity. Run this script once to establish the integrity-enforcing foundation that every later operation and query depends on.

-- schema.sql : run with  psql ipl_tournament -f schema.sql
DROP TABLE IF EXISTS deliveries, innings, matches, players, venues, teams CASCADE;

CREATE TABLE teams (
    team_id   INTEGER PRIMARY KEY,
    team_name TEXT NOT NULL UNIQUE,
    city      TEXT
);
CREATE TABLE venues (
    venue_id   INTEGER PRIMARY KEY,
    venue_name TEXT NOT NULL UNIQUE,
    capacity   INTEGER CHECK (capacity > 0)
);
CREATE TABLE players (
    player_id   INTEGER PRIMARY KEY,
    player_name TEXT NOT NULL,
    team_id     INTEGER NOT NULL REFERENCES teams(team_id),
    jersey_no   INTEGER CHECK (jersey_no BETWEEN 1 AND 99),
    UNIQUE (team_id, jersey_no)
);
CREATE TABLE matches (
    match_id    INTEGER PRIMARY KEY,
    venue_id    INTEGER NOT NULL REFERENCES venues(venue_id),
    home_team   INTEGER NOT NULL REFERENCES teams(team_id),
    away_team   INTEGER NOT NULL REFERENCES teams(team_id),
    match_date  DATE NOT NULL,
    CHECK (home_team <> away_team)            -- a team cannot play itself
);
CREATE TABLE innings (
    innings_id   INTEGER PRIMARY KEY,
    match_id     INTEGER NOT NULL REFERENCES matches(match_id),
    batting_team INTEGER NOT NULL REFERENCES teams(team_id),
    innings_no   INTEGER NOT NULL CHECK (innings_no IN (1,2))
);
CREATE TABLE deliveries (
    delivery_id INTEGER PRIMARY KEY,
    innings_id  INTEGER NOT NULL REFERENCES innings(innings_id),
    batsman_id  INTEGER NOT NULL REFERENCES players(player_id),
    runs_scored INTEGER NOT NULL CHECK (runs_scored BETWEEN 0 AND 6),
    dismissal   TEXT
);

-- Seed reference data, then dependent rows.
INSERT INTO teams VALUES (1,'Mumbai Indians','Mumbai'),(2,'Chennai Super Kings','Chennai');
INSERT INTO venues VALUES (10,'Wankhede',33000),(11,'Chepauk',38000);
INSERT INTO players VALUES
    (1,'Rohit Sharma',1,45),(2,'Suryakumar Yadav',1,63),
    (3,'MS Dhoni',2,7),(4,'Ruturaj Gaikwad',2,31);
INSERT INTO matches VALUES (100,10,1,2,DATE '2024-04-01');
INSERT INTO innings VALUES (1000,100,1,1),(1001,100,2,2);
INSERT INTO deliveries VALUES
    (1,1000,1,4,NULL),(2,1000,1,6,NULL),(3,1000,2,2,'caught'),
    (4,1000,2,0,'bowled'),(5,1001,3,1,NULL),(6,1001,3,4,NULL),
    (7,1001,4,6,NULL),(8,1001,4,0,'run out');

SELECT COUNT(*) AS deliveries_loaded FROM deliveries;   -- expect 8

Step 2 — Core Logic

Now you exercise the schema with DML and prove its constraints work. You will insert valid rows, attempt inserts that the constraints must reject, and use precise UPDATE and DELETE statements with RETURNING to confirm the affected rows. Predict which bad inserts will fail and why before running them, so you witness the schema actively defending data integrity.

These operations show the schema as a living guarantee rather than a static definition. The valid inserts succeed, the invalid ones, a duplicate jersey, a non-existent team, an out-of-range score, are refused, and the targeted update and delete touch exactly the intended rows. Save these in operations.sql and verify each outcome against your prediction.

-- operations.sql : run with  psql ipl_tournament -f operations.sql

-- VALID insert: a new player with a free jersey number.
INSERT INTO players (player_id, player_name, team_id, jersey_no)
VALUES (5, 'Hardik Pandya', 1, 33)
RETURNING player_id, player_name;

-- THESE ARE REJECTED BY THE SCHEMA (try each; predict the failure):
-- 1) Duplicate jersey within the same team (UNIQUE team_id, jersey_no):
-- INSERT INTO players VALUES (6, 'Clash', 1, 45);     -- jersey 45 taken at MI
-- 2) Non-existent team (FOREIGN KEY):
-- INSERT INTO players VALUES (7, 'Ghost', 999, 10);   -- team 999 missing
-- 3) Out-of-range delivery (CHECK runs BETWEEN 0 AND 6):
-- INSERT INTO deliveries VALUES (9, 1000, 1, 7, NULL); -- 7 runs impossible
-- 4) A team playing itself (CHECK home_team <> away_team):
-- INSERT INTO matches VALUES (101, 10, 1, 1, DATE '2024-04-05');

-- Precise UPDATE: correct a recorded score, confirm the touched row.
UPDATE deliveries SET runs_scored = 6 WHERE delivery_id = 3
RETURNING delivery_id, runs_scored;

-- Precise DELETE: remove one delivery, confirm what was removed.
DELETE FROM deliveries WHERE delivery_id = 8
RETURNING delivery_id, dismissal;

Step 3 — Integration & Enhancement

Step three adds a transactional player transfer and performance indexes, integrating transactions and indexing with the schema. You will move a player between teams inside a BEGIN ... COMMIT block so the change is atomic, then create indexes on the foreign keys and verify with EXPLAIN that a join query uses them. This combines every concept in the module on one schema.

Watch the transactional safety and index verification closely. The transfer either fully applies or rolls back, never leaving the player in an inconsistent state, and EXPLAIN confirms the foreign-key indexes are genuinely used rather than assumed. Saving these alongside the earlier operations gives you a complete, reproducible build that demonstrates real command of data modification, integrity, and performance together.

-- operations.sql (continued)

-- TRANSACTIONAL transfer: move Suryakumar (player 2) from MI to CSK.
-- A jersey clash check is implicit via UNIQUE (team_id, jersey_no).
BEGIN;
    -- Give a free CSK jersey first to avoid a UNIQUE clash, then move.
    UPDATE players SET jersey_no = 99 WHERE player_id = 2;
    UPDATE players SET team_id   = 2  WHERE player_id = 2;
    -- Verify before committing:
    -- SELECT player_name, team_id, jersey_no FROM players WHERE player_id = 2;
COMMIT;   -- both updates apply together, or ROLLBACK undoes both

-- INDEXES on foreign keys for fast joins (not auto-created in PostgreSQL).
CREATE INDEX idx_players_team    ON players (team_id);
CREATE INDEX idx_deliveries_inn  ON deliveries (innings_id);
CREATE INDEX idx_deliveries_bat  ON deliveries (batsman_id);
CREATE INDEX idx_innings_match   ON innings (match_id);

-- VERIFY an index is used by a join query.
ANALYZE;   -- refresh statistics first
EXPLAIN
SELECT p.player_name, SUM(d.runs_scored) AS runs
FROM deliveries d
JOIN players p ON d.batsman_id = p.player_id
GROUP BY p.player_name
ORDER BY runs DESC;

Step 4 — Testing & Verification

Finally, rebuild and run everything, then verify the schema, constraints, transaction, and indexes behave as designed. Because the whole build lives in scripts, you can recreate the identical tournament database in seconds and confirm that valid data loads, invalid data is refused, the transfer is atomic, and the indexes are used, proving the design is both correct and reproducible.

# Rebuild and run end to end.
dropdb ipl_tournament && createdb ipl_tournament
psql ipl_tournament -f schema.sql
psql ipl_tournament -f operations.sql

# EXPECTED KEY RESULTS (verify a few):
# schema -> deliveries_loaded = 8
# Six tables created: teams, venues, players, matches, innings, deliveries.
# VALID insert: Hardik Pandya added (player_id 5).
# Each commented bad insert, when uncommented, must FAIL with a clear error:
#   duplicate jersey -> unique_violation
#   team 999         -> foreign_key_violation
#   7 runs           -> check_violation
#   team plays itself-> check_violation
# UPDATE delivery 3 -> runs_scored now 6.
# DELETE delivery 8 -> removed (run out).
# Transfer: player 2 now team_id = 2 (CSK), jersey 99.
# EXPLAIN should show index usage on the join (idx_deliveries_bat).

# Confirm the transfer atomically applied:
psql ipl_tournament -c \
"SELECT player_name, team_id, jersey_no FROM players WHERE player_id = 2;"
# Expected: Suryakumar Yadav | 2 | 99

• A normalised six-table schema stores each fact once and wires entities together with foreign keys, the foundation every cricket application is built upon.
• Precise types plus PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK constraints make the schema itself reject invalid data automatically.
• DML must respect the schema: valid inserts succeed while duplicates, phantom references, and out-of-range values are refused at write time.
• A multi-step change like a player transfer belongs in a transaction so it applies atomically or rolls back, never leaving an inconsistent state.
• Index foreign keys explicitly in PostgreSQL and verify with EXPLAIN that joins use them, since foreign keys are not indexed automatically.
• Insert parent rows before children and drop with CASCADE or in dependency order, since foreign keys enforce the order in which related data is loaded.