§-Syllabus dot point

NSWSoftware EngineeringSyllabus dot point

Inquiry Question 1: How are secure web applications developed?

Design a relational database schema and write SQL statements to create tables, insert data, query with joins, and update or delete rows

A focused answer to the HSC Software Engineering Module 2 dot point on relational databases. Schema design, primary and foreign keys, SELECT with JOIN, INSERT, UPDATE, DELETE, the worked example, and the traps markers look for.

Generated by Claude Opus 4.87 min answerUpdated 2026-06-18

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

In plain English

Think of a relational database as a set of clearly labelled filing cabinets instead of one giant messy pile of paper. One cabinet holds "Users", one holds "Notes", and so on, each drawer (row) is one record, and each folder tab (column) holds one fact about it. A primary key is like a unique staff ID stapled to every folder, so you can always find one exact person even if two people share a name. A foreign key is a sticky note on a "Notes" folder that says "belongs to staff ID 12," linking the two cabinets together without copying all of that person's details into every note they write. SQL is just the standard set of instructions for working the cabinets: CREATE builds a new cabinet, INSERT files a new folder, SELECT pulls folders out to read (optionally joining information from two cabinets at once), UPDATE edits a folder in place, and DELETE removes one. A transaction is a rule that says "either file BOTH of these related folders, or file neither," so the cabinets never end up half-updated if something goes wrong partway through.

What this dot point is asking

NESA wants you to model data as relational tables, declare primary and foreign keys, and write the standard SQL operations: CREATE, SELECT (with WHERE, JOIN, ORDER BY, aggregates), INSERT, UPDATE, DELETE.

The answer

Schema design

Each table represents one kind of thing. Columns are attributes. Rows are individual records.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_notes_user ON notes(user_id);

Key concepts:

Primary key: uniquely identifies a row. Usually an integer surrogate key.
Foreign key (REFERENCES): a column whose value must exist as a primary key in another table. Enforces referential integrity.
NOT NULL: the column must always have a value.
UNIQUE: no two rows can share this value.
DEFAULT: a value used when none is supplied.
INDEX: speeds up queries that filter or join on the column.

SELECT

SELECT id, title, created_at
FROM notes
WHERE user_id = 12
ORDER BY created_at DESC
LIMIT 20;

JOIN

Combine rows from two tables on a matching column:

SELECT n.id, n.title, u.username
FROM notes AS n
INNER JOIN users AS u ON u.id = n.user_id
WHERE n.created_at > '2026-01-01';

Variants:

INNER JOIN: only rows that match in both tables.
LEFT JOIN: every row from the left table, plus matches from the right (NULL if no match).
RIGHT JOIN / FULL JOIN: less common in HSC examples.

Aggregates and GROUP BY

SELECT user_id, COUNT(*) AS note_count
FROM notes
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY note_count DESC;

Common aggregate functions: COUNT, SUM, AVG, MIN, MAX.

INSERT

INSERT INTO notes (user_id, title, body)
VALUES (12, 'First note', 'This is the body.');

UPDATE

UPDATE notes
SET title = 'Renamed'
WHERE id = 42 AND user_id = 12;

Always include a WHERE clause. UPDATE notes SET title = 'X' without WHERE updates every row.

DELETE

DELETE FROM notes WHERE id = 42 AND user_id = 12;

Same warning as UPDATE: never run without WHERE except deliberately.

Transactions

Group multiple statements into one atomic operation:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If anything fails, ROLLBACK undoes the partial change. This is the ACID "A" (atomicity) at work.

ACID

Relational databases guarantee:

Atomicity: a transaction either fully succeeds or fully fails.
Consistency: constraints (NOT NULL, UNIQUE, foreign keys) are never violated.
Isolation: concurrent transactions do not see each other's intermediate state.
Durability: once committed, the data survives crashes.

Calling SQL from application code

Always use parameterised queries:

import sqlite3

with sqlite3.connect("app.db") as conn:
    cur = conn.execute(
        "SELECT id, title FROM notes WHERE user_id = ? ORDER BY id DESC",
        (user_id,),
    )
    notes = cur.fetchall()

Never interpolate user input with f-strings - that is the SQL injection door.

Worked schema

A booking site needs users, events and bookings.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL
);

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  starts_at TEXT NOT NULL,
  capacity INTEGER NOT NULL CHECK (capacity > 0)
);

CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  event_id INTEGER NOT NULL REFERENCES events(id),
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, event_id)
);

The UNIQUE constraint prevents a user double-booking the same event.

A typical query:

SELECT e.title, e.starts_at
FROM bookings AS b
INNER JOIN events AS e ON e.id = b.event_id
WHERE b.user_id = 12
ORDER BY e.starts_at;

Worked example

A students table and a grades table track marks per subject per student. Write SQL to (a) find the average mark for each student across all subjects, (b) list students whose average is over 80, sorted highest first.

Average per student:

SELECT s.id, s.name, AVG(g.mark) AS avg_mark
FROM students AS s
INNER JOIN grades AS g ON g.student_id = s.id
GROUP BY s.id, s.name;

Students with average above 80:

SELECT s.id, s.name, AVG(g.mark) AS avg_mark
FROM students AS s
INNER JOIN grades AS g ON g.student_id = s.id
GROUP BY s.id, s.name
HAVING AVG(g.mark) > 80
ORDER BY avg_mark DESC;

Use HAVING (not WHERE) to filter on an aggregate. WHERE filters rows before grouping; HAVING filters groups after aggregation.

Exam-style practice questions

Practice questions written in the style of NESA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

2025 HSC6 marksA library system stores books and the members who have borrowed them. Design a relational schema with at least two tables and write SQL to (a) create the tables, (b) list every book currently borrowed by the member with id 12, and (c) record that book 8 has been returned.

Show worked answer →

Schema:

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  borrower_id INTEGER REFERENCES members(id),
  borrowed_at TEXT
);

Query (b) - books borrowed by member 12:

SELECT b.id, b.title, b.author, b.borrowed_at
FROM books AS b
WHERE b.borrower_id = 12;

Update (c) - record return of book 8:

UPDATE books
SET borrower_id = NULL, borrowed_at = NULL
WHERE id = 8;

Markers reward sensible primary keys, a foreign key linking the two tables, NOT NULL on required fields, UNIQUE on email, and correct SQL syntax for each operation. The cleanest answer separates the loan history into its own table for full audit history, but a single denormalised books table also scores.

Practice questions

Original practice questions graded from foundation to exam level, each with a full worked solution. Try them before revealing the solution.

foundation3 marksWrite a CREATE TABLE statement for a `products` table with an integer primary key, a required `name`, a required `price` that must be greater than zero, and a `sku` that must be unique.

Show worked solution →

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL NOT NULL CHECK (price > 0),
  sku TEXT UNIQUE NOT NULL
);

Marking criteria: 1 mark for a correct primary key, 1 mark for NOT NULL on the required columns, 1 mark for correctly enforcing both the CHECK on price and UNIQUE on sku.

foundation2 marksExplain why `UPDATE customers SET status = 'inactive';` is dangerous, and rewrite it to safely deactivate only customer id 47.

Show worked solution →

It is dangerous because it has no WHERE clause, so it sets EVERY row's status to 'inactive', not just the intended customer.

UPDATE customers SET status = 'inactive' WHERE id = 47;

Marking criteria: 1 mark for correctly explaining the missing-WHERE risk, 1 mark for the corrected statement.

core5 marksA `students` table (id, name) and an `enrolments` table (student_id, subject, mark) store exam results. The table below is a sample of `enrolments`. | student_id | subject | mark | |---|---|---| | 1 | Software Engineering | 88 | | 1 | Mathematics | 74 | | 2 | Software Engineering | 65 | | 2 | Mathematics | 91 | | 3 | Software Engineering | 79 | Write one SQL query that lists each student's name alongside their average mark across all subjects, showing only students whose average exceeds 75, ordered from highest average to lowest.

Show worked solution →

SELECT s.name, AVG(e.mark) AS avg_mark
FROM students AS s
INNER JOIN enrolments AS e ON e.student_id = s.id
GROUP BY s.id, s.name
HAVING AVG(e.mark) > 75
ORDER BY avg_mark DESC;

Using the sample data: student 1 averages (88+74)/2 = 81, student 2 averages (65+91)/2 = 78, student 3 averages 79 (single subject). All three exceed 75, so the result lists student 1 (81), student 3 (79), student 2 (78) in that order.

Marking criteria: 1 mark for the correct JOIN, 1 mark for GROUP BY on the student, 1 mark for AVG as the aggregate, 1 mark for using HAVING (not WHERE) to filter the aggregate, 1 mark for correct ORDER BY direction.

core4 marksA `bookings` table has `UNIQUE (user_id, event_id)`. Explain what problem this constraint prevents, and write the INSERT statement that would be rejected if user 5 has already booked event 9.

Show worked solution →

The constraint prevents the same user from booking the same event more than once; the database enforces this automatically rather than relying on application code to check first.

INSERT INTO bookings (user_id, event_id) VALUES (5, 9);

If a row with user_id = 5 and event_id = 9 already exists, this INSERT is rejected with a UNIQUE constraint violation, because the composite (user_id, event_id) pair must be distinct across all rows.

Marking criteria: 1 mark for correctly explaining the duplicate-booking problem prevented, 1 mark for a correctly formed INSERT statement, 1 mark for correctly identifying that it duplicates an existing pair, 1 mark for stating that the database (not the application) rejects it.

exam6 marksA ride-share app needs to guarantee that transferring a fare payment from a rider's account to a driver's account either fully happens or does not happen at all, even if the server crashes mid-transfer. Explain which ACID property this requires, write the SQL to implement it, and explain what happens if the server crashes between the two UPDATE statements.

Show worked solution →

This requires Atomicity: the transaction must either complete both balance changes or leave the database exactly as it was before, with no partial transfer.

BEGIN;
UPDATE accounts SET balance = balance - 15.50 WHERE id = 'rider_204';
UPDATE accounts SET balance = balance + 15.50 WHERE id = 'driver_88';
COMMIT;

If the server crashes after the first UPDATE but before COMMIT, the transaction is never committed. On restart, the database's recovery process rolls back the uncommitted transaction automatically, restoring the rider's original balance; the driver's balance is never touched because that second UPDATE was never durably applied. No fare amount is lost or duplicated, because Atomicity guarantees an all-or-nothing outcome, and Durability guarantees that only a fully committed transaction survives a crash.

Marking criteria: 1 mark for naming Atomicity, 2 marks for correct BEGIN/UPDATE/UPDATE/COMMIT SQL, 2 marks for correctly explaining the rollback-on-crash behaviour, 1 mark for linking the explanation to why no money is lost or duplicated.

exam8 marksDesign a relational schema for a school's assignment-submission system: teachers create assignments for a class, students submit one file per assignment, and each submission is marked out of 100 by exactly one teacher. Write the CREATE TABLE statements with appropriate primary keys, foreign keys and constraints, and justify two of your design decisions.

Show worked solution →

This is an 8-mark extended response: markers reward a schema that correctly models the relationships (one assignment has many submissions; one submission has one mark) with justified constraint choices, not just syntactically valid SQL.

CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE assignments (
  id INTEGER PRIMARY KEY,
  teacher_id INTEGER NOT NULL REFERENCES teachers(id),
  title TEXT NOT NULL,
  due_at TEXT NOT NULL
);

CREATE TABLE submissions (
  id INTEGER PRIMARY KEY,
  assignment_id INTEGER NOT NULL REFERENCES assignments(id),
  student_id INTEGER NOT NULL REFERENCES students(id),
  file_path TEXT NOT NULL,
  submitted_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  mark INTEGER CHECK (mark BETWEEN 0 AND 100),
  marked_by INTEGER REFERENCES teachers(id),
  UNIQUE (assignment_id, student_id)
);

CREATE INDEX idx_submissions_assignment ON submissions(assignment_id);

Justification 1: UNIQUE (assignment_id, student_id). The problem states each student submits ONE file per assignment, so this composite constraint stops a duplicate row for the same student/assignment pair being inserted, enforcing the one-per-assignment rule at the database level rather than trusting application code alone.

Justification 2: mark is nullable with a CHECK, and marked_by is a separate nullable foreign key. A submission exists before it is marked, so mark cannot be NOT NULL; the CHECK still constrains any value that IS entered to the valid 0-100 range. Recording marked_by as its own foreign key (rather than assuming the assignment's teacher always marks it) supports the requirement that exactly one teacher marks each submission, while still allowing a different teacher (e.g. a relief teacher) to be the one who actually marks it.

Marker's note: top-band answers (1) correctly identify the one-to-many relationships with sensible foreign keys, (2) use a composite UNIQUE constraint to enforce the one-submission-per-assignment business rule, (3) correctly reason about which columns can be NULL and why, and (4) justify decisions in terms of the scenario, not just describe the SQL.