01 | Relational database foundations: challenges

Separate headers, complete rows and individual field values. A key identifies a row even if descriptive values repeat.

There are three records and three fields. Student 102’s first_name is Leo. The two Mayas have different primary keys, 101 and 103. After adding student 104, there are four records and still three fields.

Choose based on meaning, not whether digits are present. Ordinary SQLite declared types are not complete validation rules.

Names, asset tags and phone numbers suit TEXT. Year level suits INTEGER with an appropriate whole-number and range rule. A measured mass can use REAL. NULL can represent an unassigned room; an empty string is an actual text value and zero is a known numeric value. Decide whether the room is optional in the schema.

Follow the membership ID values to matching parent records. Do not match on a name alone.

Student 101 joins Robotics and Game Design and leads Game Design. Students 101 and 102 are recorded in Robotics. Names can repeat or change, so a stable unique identifier avoids ambiguity.

One student can join many clubs and one club can contain many students. The relationship can carry its own facts.

The fixed columns impose an arbitrary club limit and make membership operations awkward. Store club details once in Clubs and one student–club pair per row in Memberships. Students.student_id and Clubs.club_id are primary keys. Memberships has required foreign keys to both and uses the pair as a primary key. Role belongs to Memberships because it can vary between the same student’s clubs.

Check required parent records, the uniqueness of the pair and the allowed role.

(103, 2, member) is accepted. (101, 1, leader) is rejected because the pair already exists, even though the role differs. (999, 1, member) is rejected because student 999 is absent. (103, 2, captain) is rejected by the role rule; this pair is unused in the original data, so it isolates the invalid role.

A comma separates definitions, but there is no final comma. NOT NULL and a value check do different jobs.

CREATE TABLE Workshops (
    workshop_id INTEGER PRIMARY KEY,
    workshop_name TEXT NOT NULL
        CHECK (length(trim(workshop_name)) > 0),
    places INTEGER NOT NULL
        CHECK (typeof(places) = 'integer' AND places >= 1)
);

The missing comma after workshop_id and the extra final comma are repaired. typeof checks the stored representation after SQLite affinity; it does not forbid every numeric text input from being converted into an integer.

Start a fresh in-memory database for each isolated test. An error stops the runner before its normal output loop.

INSERT INTO Workshops (workshop_id, workshop_name, places)
VALUES (1, 'Robotics', 12);

For an ordinary isolated test, replace the values and Run again. For the duplicate test, retain this statement and append another using ID 1.

Use an explicit INSERT column list. DEFAULT applies when the column is omitted, not when NULL is explicitly supplied.

equipment_count INTEGER NOT NULL DEFAULT 0
    CHECK (typeof(equipment_count) = 'integer' AND equipment_count >= 0)

Add this as a column definition with the appropriate separating comma. The original 01.4 club inserts name their columns, so they omit the new column and receive zero.

INSERT INTO Clubs (club_id, club_name, capacity)
VALUES (3, 'Art', 10);

INSERT INTO Clubs (club_id, club_name, capacity, equipment_count)
VALUES (4, 'Film', 8, 5);

Omission gives 0, and 5 succeeds. -1 and 2.5 fail the check; NULL fails NOT NULL. Test each invalid case separately.

Distinguish a value rule from a rule comparing several records. Names and declared types do not supply every intended behaviour.

The capacity CHECK only requires a positive whole number. It does not count membership rows. Enforcing the cross-row limit needs further application logic or a carefully designed database mechanism. TEXT alone does not validate a date. Foreign keys must be declared and enforced; SQLite enforcement must be enabled per connection before the transaction begins.

A borrower–equipment pair cannot be the only primary key if the same pair must appear in separate historical loan events.

PRAGMA foreign_keys = ON;

CREATE TABLE Borrowers (
    borrower_id INTEGER PRIMARY KEY,
    borrower_name TEXT NOT NULL
        CHECK (length(trim(borrower_name)) > 0)
);

CREATE TABLE Equipment (
    equipment_id INTEGER PRIMARY KEY,
    asset_tag TEXT NOT NULL UNIQUE
        CHECK (length(trim(asset_tag)) > 0),
    item_name TEXT NOT NULL
        CHECK (length(trim(item_name)) > 0)
);

CREATE TABLE Loans (
    loan_id INTEGER PRIMARY KEY,
    borrower_id INTEGER NOT NULL,
    equipment_id INTEGER NOT NULL,
    loan_date TEXT NOT NULL,
    returned INTEGER NOT NULL DEFAULT 0
        CHECK (typeof(returned) = 'integer' AND returned IN (0, 1)),
    FOREIGN KEY (borrower_id) REFERENCES Borrowers(borrower_id),
    FOREIGN KEY (equipment_id) REFERENCES Equipment(equipment_id)
);

INSERT INTO Borrowers (borrower_id, borrower_name)
VALUES (1, 'Maya'), (2, 'Leo');

INSERT INTO Equipment (equipment_id, asset_tag, item_name)
VALUES (10, 'AIR-010', 'Microcontroller kit'),
       (11, 'AIR-011', 'Microcontroller kit');

INSERT INTO Loans
    (loan_id, borrower_id, equipment_id, loan_date, returned)
VALUES (100, 1, 10, '2026-10-01', 1),
       (101, 1, 10, '2026-10-08', 0);

INSERT INTO Loans (loan_id, borrower_id, equipment_id, loan_date)
VALUES (102, 2, 11, '2026-10-08');

Borrowers and Equipment each have a one-to-many relationship to Loans. loan_id identifies an event, allowing repeated borrower–item pairs. Unique asset tags distinguish the two physical kits. Omitting returned for loan 102 produces 0.

The schema does not validate an actual calendar date or stop overlapping active loans. It also does not verify that a typed borrower name is genuine. Do not claim those rules have been implemented merely because the relevant fields exist.