Design a normalised relational database with primary and foreign keys and appropriate data types, and query it with SQL using SELECT, WHERE, JOIN, ORDER BY and aggregate functions

What this dot point is asking

QCAA wants you to design a relational database that stores data without redundancy and to retrieve data from it with SQL. You must understand tables, fields, records, primary and foreign keys, data types, and normalisation, then write queries that filter, join and summarise data. Data-driven solutions in Unit 3 depend on this: the front end is only as good as the data model behind it.

Tables, keys and data types

A table holds records (rows), each made of fields (columns). Every field has a data type (INTEGER, TEXT, REAL, DATE, BOOLEAN) chosen to match the data and keep storage efficient.

• A primary key uniquely identifies each record (for example student_id). It cannot be null or duplicated.
• A foreign key is a field that references the primary key of another table, creating the relationship between them.

CREATE TABLE Student (
    student_id   INTEGER PRIMARY KEY,
    first_name   TEXT    NOT NULL,
    year_level   INTEGER NOT NULL
);

CREATE TABLE Enrolment (
    enrolment_id INTEGER PRIMARY KEY,
    student_id   INTEGER NOT NULL,
    subject      TEXT    NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Student(student_id)
);

Normalisation

Normalisation organises fields so each fact is stored once, which prevents update anomalies.

• First normal form (1NF): atomic values, no repeating groups.
• Second normal form (2NF): 1NF and every non-key field depends on the whole primary key.
• Third normal form (3NF): 2NF and no non-key field depends on another non-key field.

Storing a student's name in every enrolment row is redundant; if the name changes you must edit many rows and risk inconsistency. Splitting Student and Enrolment into two tables linked by student_id removes that redundancy. QCAA expects you to justify your design to at least 3NF.

Querying with SQL

SQL retrieves and manipulates data. The core SELECT pattern:

SELECT first_name, year_level
FROM Student
WHERE year_level = 12
ORDER BY first_name ASC;

A JOIN combines rows from two tables on their key relationship:

SELECT Student.first_name, Enrolment.subject
FROM Student
JOIN Enrolment ON Student.student_id = Enrolment.student_id
WHERE Enrolment.subject = 'Digital Solutions';

Other aggregate functions are SUM, AVG, MIN and MAX. The clause order is fixed: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY.

Performance and integrity

Key choices affect performance. A primary key is indexed automatically, so lookups by key are fast. Querying or joining on unindexed text fields over large tables is slow. Validation rules (NOT NULL, data-type constraints, ranges) protect data integrity at the point of entry, so the stored data stays reliable for retrieval and reporting.

How this appears in IA1 and IA2

The IA1 technical proposal often requires an entity relationship design with keys justified to 3NF and sample SQL for the required reports. IA2 then implements the database inside a working solution, so your queries must return exactly the data the interface needs. Markers reward a normalised schema, correctly typed fields, and SQL that demonstrably answers the stated requirements with the right filtering, joining and sorting.