London Underground Event Database

01 — OVERVIEW

From transport events to a relational database.

The project models transport activity using individual passenger check-in and check-out events. Rather than assuming that complete journeys already existed in the source data, events were modelled explicitly and relationships were created between passengers, purchase media, stations, lines and transport events.

This provided the foundation for querying passenger activity, station usage, payment media and completed or incomplete journeys.

02 — CONCEPTUAL DESIGN

Entity Relationship Model

The database was first modelled conceptually using a Classic Chen Entity Relationship diagram. The model identifies the major entities, their attributes, relationships, cardinalities and participation constraints.

Conceptual ER model showing passengers, purchase media, stations, lines and transport events. Click the diagram to view it at full size.

03 — RELATIONAL DESIGN

Relational Table Schema

The conceptual model was converted into a relational schema with primary keys, foreign keys and an associative StationLine relation. StationLine resolves the many-to-many relationship between stations and lines using a composite key.

Relational schema showing table references and primary and foreign key relationships. Click the diagram to view it at full size.

04 — NORMALISATION

Normalised to Third Normal Form

The relational design was analysed through First, Second and Third Normal Form. Attributes were separated into appropriate relations and dependencies were considered to reduce duplication and avoid insertion, update and deletion anomalies.

05 — IMPLEMENTATION

Oracle SQL Implementation

The relational design was implemented in Oracle SQL using table definitions, primary keys, foreign keys and integrity constraints. Test data was then used to validate relationships and query the resulting database.

CREATE TABLE StationLine (
    SID VARCHAR2(10),
    LID VARCHAR2(10),

    CONSTRAINT pk_stationline
        PRIMARY KEY (SID, LID),

    CONSTRAINT fk_stationline_station
        FOREIGN KEY (SID)
        REFERENCES Station(SID),

    CONSTRAINT fk_stationline_line
        FOREIGN KEY (LID)
        REFERENCES Line(LID)
);

Example of using a composite primary key and foreign-key constraints to represent the Station-Line relationship.

06 — DATA QUERYING

SQL Queries & Analysis

SQL queries were developed to retrieve, filter, combine and aggregate transport data. The work included filtering with WHERE, sorting, date formatting, aggregate functions, GROUP BY, inner joins, left joins and queries across related tables.

SELECT
    p.PID,
    p.FullName,
    COUNT(e.EID) AS TotalEvents
FROM Passenger p
LEFT JOIN PurchaseMedium pm
    ON p.PID = pm.PID
LEFT JOIN Event e
    ON e.TID = pm.TID
GROUP BY p.PID, p.FullName;

SELECT
    LID,
    COUNT(*) AS TotalEvents
FROM Event
GROUP BY LID;

SELECT
    p.PID,
    p.FullName,
    pm.TID,
    pm.MediumType
FROM Passenger p
JOIN PurchaseMedium pm
    ON p.PID = pm.PID
WHERE pm.MediumType = 'Card';

07 — DATABASE IN OPERATION

Queries & Database Views

The implemented database was tested in Oracle SQL using queries across related tables and reusable views. These outputs demonstrate joins, aggregation and higher-level analysis of transport events.

08 — DATABASE LOGIC

Views, Constraints & Validation

The implementation extended beyond individual queries by creating reusable database views and validation logic for common transport analysis tasks.

09 — REPRODUCE THE DATABASE

Full SQL Source

The complete Oracle SQL implementation is included with this portfolio project. The source contains the database structure, relationships, constraints, sample data, validation logic and database views used by the project.

10 — TECHNOLOGIES & CONCEPTS

What the project covered