Case Study 4: EasyDrive Insurance (DDL & DML)

exercises
tutorial
case-study
databases
sql
ddl
dml

Practice for 2026-03-30-basic-sql-ddl-and-dml. This case study builds practical experience implementing a database in phpMyAdmin (a GUI for MySQL) — running real DDL/DML, and learning to break-test a schema and check edge cases.

Correspondence 1

Sarah Chen, Head of Technology Operations at EasyDrive Insurance (a direct-to-consumer car insurer), emails asking for help migrating their schema to MySQL/phpMyAdmin. When a customer signs up they create a profile (Customer, Address), then may purchase a Policy for a Vehicle, insuring it over consecutive years.

Relational schema:

Customer    [CustomerID, Name, DateOfBirth, Email, Occupation, AddressID]
Address     [AddressID, StreetName, Number, Suburb, Postcode, State, Country]
Vehicle     [VehicleID, VehicleCode, VehiclePurpose, EstYearlyKm]
VehicleType [VehicleCode, Make, Model, Year, MarketValue]
Policy      [PolicyID, CustomerID, VehicleID, PolicyStartYear, PolicyPurchaseDate, Excess, Premium]

Customer.AddressID references Address.AddressID
Policy.VehicleID references Vehicle.VehicleID
Policy.CustomerID references Customer.CustomerID
Vehicle.VehicleCode references VehicleType.VehicleCode

Data types (as specified by Sarah):

  • Address: AddressID (≤4 chars), StreetName (≤255), Number (≤10, to allow e.g. "4/12"), Suburb (≤255), Postcode (≤10), State (≤10), Country (≤50).
  • Customer: CustomerID (integer), Name (≤255), DateOfBirth (date), Email (≤255), Occupation (≤255), AddressID (≤4).
  • VehicleType: a reference table mapping a VehicleCode (≤50) to Make/Model/Year — the combination of Make, Model, Year must be unique, since each distinct vehicle configuration maps to exactly one code (used to determine MarketValue, the accident payout).
  • Vehicle: VehicleID (≤6), VehicleCode (references VehicleType), VehiclePurpose (only 'Private' or 'Business'), EstYearlyKm (integer).
  • Policy: PolicyID (≤6), CustomerID (integer), VehicleID (≤6), PolicyStartYear (integer), PolicyPurchaseDate (date), Excess (integer), Premium (decimal, 2 dp, ≤10 digits total).

Section A — Data Definition Language

Using phpMyAdmin, create a new database EasyDrive and implement the five tables above, with all data types/keys/constraints as described.

CREATE DATABASE EasyDriveInsurance;
USE EasyDriveInsurance;

CREATE TABLE Address (
    AddressID  VARCHAR(4),
    StreetName VARCHAR(255),
    Number     VARCHAR(10),
    Suburb     VARCHAR(255),
    Postcode   VARCHAR(10),
    State      VARCHAR(10),
    Country    VARCHAR(50),
    PRIMARY KEY (AddressID)
);

CREATE TABLE Customer (
    CustomerID  INT,
    Name        VARCHAR(255),
    DateOfBirth DATE,
    Email       VARCHAR(255),
    Occupation  VARCHAR(255),
    AddressID   VARCHAR(4),
    PRIMARY KEY (CustomerID),
    FOREIGN KEY (AddressID) REFERENCES Address(AddressID)
);

CREATE TABLE VehicleType (
    VehicleCode VARCHAR(50),
    Make        VARCHAR(50),
    Model       VARCHAR(50),
    Year        INT,
    PRIMARY KEY (VehicleCode),
    UNIQUE (Make, Model, Year)
);

CREATE TABLE Vehicle (
    VehicleID      VARCHAR(6),
    VehicleCode    VARCHAR(50),
    VehiclePurpose ENUM('Private', 'Business'),
    EstYearlyKm    INT,
    PRIMARY KEY (VehicleID),
    FOREIGN KEY (VehicleCode) REFERENCES VehicleType(VehicleCode)
);

CREATE TABLE Policy (
    PolicyID           VARCHAR(6),
    CustomerID         INT,
    VehicleID          VARCHAR(6),
    PolicyStartYear    INT,
    PolicyPurchaseDate DATE,
    Excess             INT,
    Premium            DECIMAL(10, 2),
    PRIMARY KEY (PolicyID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
);

Note: modern AI tools can draft CREATE TABLE statements from a schema description, but the draft must be checked carefully against the description before running it — AI-generated SQL can contain errors or omissions (see 2026-04-13-nested-queries-views-and-generative-ai’s “Generative AI & SQL” section).

Section B — Analysis of Data Manipulation Language

Sarah asks the students to run five INSERT operations, in order, and record for each: whether it succeeded/failed, the error message (if any), and which integrity constraint was violated and why.

-- Operation 1
INSERT INTO Address (AddressID, StreetName, Number, Suburb, Postcode, State, Country)
VALUES ('A001', 'Main Street', '12', 'Brisbane', '4000', 'QLD', 'Australia');

-- Operation 2
INSERT INTO Customer (CustomerID, Name, DateOfBirth, Email, Occupation, AddressID)
VALUES (1001, 'Allan Smith', '2000-01-01', 'allan@uq.edu.au', 'Student', 'A999');

-- Operation 3
INSERT INTO Customer (CustomerID, Name, DateOfBirth, Email, Occupation, AddressID)
VALUES (1001, 'Rebecca Johnson', '1995-06-15', 'rebecca@gmail.com', 'Engineer', 'A001');

-- Operation 4
INSERT INTO VehicleType (VehicleCode, Make, Model, Year)
VALUES ('VT001', 'Toyota', 'Corolla', 2020);
INSERT INTO Vehicle (VehicleID, VehicleCode, VehiclePurpose, EstYearlyKm)
VALUES ('V00001', 'VT001', 'Personal', 15000);

-- Operation 5
INSERT INTO Policy (PolicyID, CustomerID, VehicleID, PolicyStartYear, PolicyPurchaseDate, Excess, Premium)
VALUES ('P00001', 1001, 'V00001', 2024, '2024-03-01', 500, 1200.00);
Op Result Error / constraint
1 Succeeded —
2 Failed #1452 - Cannot add or update a child row: a foreign key constraint fails. Referential integrity — AddressID = 'A999' doesn’t exist in Address.
3 Succeeded — (a different row, correctly referencing A001; note it reuses CustomerID = 1001 from the failed Op 2, which is fine since Op 2 never committed)
4 Failed #1265 - Data truncated for column 'VehiclePurpose' at row 1. Domain constraint — 'Personal' is not a valid VehiclePurpose value (only 'Private'/'Business').
5 Failed #1452 - ... FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID). Referential integrity — since Op 4 failed, VehicleID = 'V00001' was never inserted into Vehicle.

This is a good illustration of cascading failure: a single domain violation in Op 4 also causes Op 5 to fail, even though Op 5’s own values look superficially fine.

Correspondence 2 (Challenge)

Sarah’s team requests further changes now the core schema is in place:

  1. A new InsuranceClaim table: an auto-incrementing claim ID; a reference to the Policy the claim is against (claims should be automatically deleted if their policy is deleted); the incident date/time; the claimed amount (2 dp decimal); a status restricted to 'Pending', 'Approved', or 'Rejected'; and a description (≤255 chars).
  2. Remove the Occupation column from Customer (no longer collected).
  3. Add two constraints to Customer: a named UniqueEmail uniqueness constraint on Email, and a check constraint requiring customers to be at least 18 years old (based on DateOfBirth).

Task 4 — Creating InsuranceClaim

CREATE TABLE InsuranceClaim (
    ClaimID          INT AUTO_INCREMENT,
    PolicyID         VARCHAR(6),
    ClaimDate        DATE,
    ClaimAmount      DECIMAL(10, 2),
    ClaimStatus      ENUM('Pending', 'Approved', 'Rejected'),
    ClaimDescription VARCHAR(255),
    PRIMARY KEY (ClaimID),
    FOREIGN KEY (PolicyID) REFERENCES Policy(PolicyID) ON DELETE CASCADE
);

Task 5 — Removing Occupation

ALTER TABLE Customer DROP COLUMN Occupation;

Task 6 — Fraud prevention constraints

ALTER TABLE Customer
    ADD CONSTRAINT UniqueEmail UNIQUE (Email);

ALTER TABLE Customer
    ADD CONSTRAINT AgeOver CHECK (
        TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) >= 18
    );