Describe the organisation of a relational database into tables, records, fields, keys and relationships, and construct SQL queries to retrieve and manipulate data

What this dot point is asking

NESA wants you to describe how a relational database is organised and to read and write SQL that retrieves and manipulates data. You need the vocabulary (table, record, field, key, relationship), an understanding of how tables link through keys, and the ability to construct a SELECT query with conditions, sorting and joins.

The answer

The relational model

A relational database organises data into two-dimensional tables, also called relations. Each table describes one kind of thing, for example Students or Subjects. A row in the table is a record, representing one instance (one student). A column is a field, representing one attribute (the student's surname). Every field has a data type, such as text, number, date or Boolean.

Keys and relationships

A primary key is a field (or combination of fields) whose value is unique for every record, so it identifies that record without ambiguity. A StudentID is a typical primary key. A foreign key is a field in one table that holds the primary key value of a record in another table. By matching a foreign key to a primary key, the database links related records, which is what makes the model relational. For example, an Enrolments table can hold a StudentID foreign key and a SubjectID foreign key, connecting students to subjects.

Relationships come in three kinds: one to one, one to many, and many to many. A many to many relationship (students to subjects) is implemented with a linking table that holds the two foreign keys, because a single field cannot store a list.

Schemas and data dictionaries

The schema is the design of the database: the tables, their fields, the data types and the relationships. A data dictionary documents each field with its name, type, size, and validation rules. Entity relationship diagrams draw the tables as boxes and the relationships as connecting lines, showing the structure at a glance.

Normalisation

Normalisation is the process of organising fields into tables so that each piece of data is stored once. Without it, repeating a customer's address on every order wastes space and risks inconsistency when the address changes in one place but not another. Normalisation splits data into related tables linked by keys, removing this redundancy and the update anomalies it causes.

Retrieving data with SQL

SQL (Structured Query Language) is the standard language for querying relational databases. A basic retrieval has three clauses:

• SELECT lists the fields you want returned.
• FROM names the table.
• WHERE filters the records to those meeting a condition.

For example, to list the names of students in Year 12:

SELECT FirstName, Surname FROM Student WHERE Year = 12;

ORDER BY sorts the result, ascending by default or descending with DESC. Comparison operators (=, >, <, >=, <=, <>) and the logical operators AND, OR and NOT build richer conditions. LIKE with wildcards matches patterns in text.

Joining tables

When the data you need spans two tables, a join combines them on a matching key. To list each student with the names of the subjects they take, you join Student to Subject through the Enrolments linking table, matching StudentID to StudentID and SubjectID to SubjectID. The join is the SQL expression of a relationship.

Manipulating data

Beyond retrieval, SQL updates the stored data. INSERT adds a new record, UPDATE changes fields in existing records (usually with a WHERE clause so it does not change every row), and DELETE removes records. These map onto the processing and storing information processes.