Databases

File Systems vs Relational Databases

In a file-based system, data is kept in separate flat files, such as CSV or plain text files, where each individual program manages and reads its own files. Storing records this way quickly runs into trouble as systems grow.

A relational database stores all data in linked tables managed by a single Database Management System (DBMS). Using a relational database provides several distinct advantages:

• Less redundancy: Each fact is stored once and linked using keys.
• Better consistency: When a detail changes, updating it in one place updates it across the entire system.
• Easy retrieval: You can search, filter, sort, and combine data with SQL without writing a bespoke file-parsing program for every query.
• Integrity rules: Constraints like primary keys, foreign keys, and NOT NULL rules automatically block invalid or orphan records.
• Sharing and security: Multiple users can query data simultaneously, with permissions restricting who can view or alter sensitive tables.

Tables, Fields, and Procedural Data Types

A database provides persistent storage. In a relational database, data is organised into two-dimensional tables, historically called relations. Each table stores data about a single entity, such as a student, a book, or an order.

Textbooks and exam questions use different names for the same thing, so learn each pair:

Note on terminology: In database theory, a single row is called a tuple. In Python programming, a tuple is an immutable sequence written with parentheses, such as (101, 'Aoife'). Python's database library returns each database record as a Python tuple.

Each field should be given a data type that suits its data. Most database engines enforce these types strictly to reject invalid input. SQLite is more relaxed: it stores booleans as 1 or 0 and dates as text, so you must keep your dates in YYYY-MM-DD format yourself.

Under Learning Outcome 2.16, you must know how procedural types map to database fields:

• INTEGER: Whole numbers, such as student_id or quantity.
• REAL: Numbers with a fractional decimal part, such as exam_mark or price.
• TEXT / VARCHAR: Variable-length strings, such as surname or eircode.
• CHAR: A fixed-length string. For example, CHAR(1) stores a single character such as a status flag 'Y', while CHAR(2) could store a Leaving Certificate grade such as 'H1'.
• BOOLEAN: Logical truth values (TRUE or FALSE).
• DATE: Calendar dates. Storing them in the ISO format YYYY-MM-DD allows chronological sorting directly using standard text or numeric comparisons.
• Arrays: A field in a relational database should store an atomic value, not a list. To store an array or list of items (for instance, all the subjects a student takes), create a separate linked table with one row per item, such as StudentSubjects(student_id, subject_code).

Primary Keys, Foreign Keys, and Creating Tables

To locate records quickly and connect separate tables without ambiguity, relational databases rely on keys.

Primary Key

A primary key is a field, or combination of fields, that uniquely identifies each individual record in a database table. A primary key must satisfy two strict rules:

1. It must be unique for every record.
2. It can never be empty (NULL).

Good practice (not an enforced database rule): Choose an identifier that will not change over time, such as an allocated ID number rather than a phone number or email address.

Names and breeds are never suitable primary keys. Multiple students can share the surname Byrne, and thousands of dogs share the Golden Retriever breed. Instead, use a field guaranteed to be unique. This can be an identifier that already exists in the real world, such as a dog's microchip number, or a number generated by the system, such as student_id.

Composite Primary Key

When no single column is unique on its own, two or more columns can be combined to form a composite primary key. For example, in a table tracking student subject choices (exam_number, subject_code, level), neither exam_number nor subject_code is unique on its own because a student sits multiple subjects and many students take Computer Science. The pair (exam_number, subject_code) together forms a unique composite key for each row.

Foreign Key

A foreign key is a field in one table that references the primary key of another table, creating an explicit relationship between them.

Creating Tables in SQL

Before storing data, you define the schema of each table:

CREATE TABLE Owners (
    owner_id    INTEGER PRIMARY KEY,
    owner_name  TEXT NOT NULL,
    owner_phone TEXT
);

CREATE TABLE Pets (
    pet_id        INTEGER PRIMARY KEY,
    pet_name      TEXT NOT NULL,
    animal_type   TEXT,
    date_of_birth DATE,
    vaccinated    BOOLEAN,
    owner_id      INTEGER,
    FOREIGN KEY (owner_id) REFERENCES Owners(owner_id)
);

Notice that owner_phone is defined as TEXT, not INTEGER. Phone numbers, Eircodes, and PPS numbers should always be stored as text because an integer strips leading zeros (turning '0871234567' into 871234567) and you never perform arithmetic on them.

Data Redundancy and Data Inconsistency

Storing everything in one single flat file table causes two main problems.

Data redundancy is when the same piece of data is stored in more than one place in a database. It is usually the result of poor database design. Repeating the same details across multiple rows wastes storage space and makes maintenance error-prone.

Data inconsistency occurs when conflicting versions of what should be the same data exist in the database. Redundancy causes update inconsistencies: if author Arthur Conan Doyle changes his phone number, an administrator might update two book entries but overlook a third. The database now holds contradictory contact numbers for the same person.

Splitting tables so each entity is stored once reduces redundancy and stops update inconsistencies, so a change to an author's details is made in one row only.

However, not every inconsistency comes from redundancy. In Leaving Certificate exam questions, you are often asked to spot discrepancies in unvalidated data:

Splitting tables solves the author name problem. The other three are solved by choosing the correct data type and validating user input.

Writing SQL Queries and Joins

Structured Query Language (SQL) is the standard declarative language used to query and update relational databases. On written examination papers, write clauses in their standard order:

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC;

Conditions in WHERE use comparison operators (=, <, >, <=, >=, and <> or != for not equal), combined with AND or OR.

Consider this sample Students table:

Running this query:

SELECT student_id, surname, first_name
FROM Students
WHERE year_group = 6
ORDER BY surname ASC;

Trace:

1. FROM Students inspects the table.
2. WHERE year_group = 6 filters the rows, selecting records 101 and 103.
3. ORDER BY surname ASC sorts them alphabetically by surname: Byrne comes before Kelly.
4. Output:

• (103, 'Byrne', 'Darragh')
• (101, 'Kelly', 'Aoife')

Retrieving Data from Linked Tables

A foreign key lets you combine rows from two related tables in a single query using JOIN:

SELECT Pets.pet_name, Owners.owner_name, Owners.owner_phone
FROM Pets
JOIN Owners ON Pets.owner_id = Owners.owner_id
WHERE Pets.animal_type = 'Dog';

The query matches each pet with its owner by equating Pets.owner_id with Owners.owner_id. It filters for dogs, outputting the pet's name alongside the owner's contact details, even though they live in separate tables.

Modifying Data

• Adding a record:

INSERT INTO Students (student_id, surname, first_name, year_group)
VALUES (105, 'O''Connor', 'Niamh', 5);

Notice that an apostrophe in a text value like O'Connor is escaped by doubling it ('O''Connor').

• Updating records:

UPDATE Students
SET year_group = 6
WHERE student_id = 105;

Always include a WHERE clause when updating. Omitting it updates every single row in the table.

• Deleting records:

DELETE FROM Students
WHERE student_id = 105;

Database Programming in Python with sqlite3

Applied Learning Task 1 (ALT 1) asks you to build an interactive website that displays information from a database. Any language may be used for the applied learning tasks, but Python is the language assessed in the end-of-course examination, so this note uses Python's built-in sqlite3 module.

Connecting to a database in Python follows six clear steps:

1. Connect: Establish a connection using conn = sqlite3.connect("school.db").
2. Cursor: Create a cursor to run commands using cur = conn.cursor().
3. Execute: Send the SQL statement using cur.execute(query, params).
4. Fetch: Read results using cur.fetchall() (returns a list of tuples) or cur.fetchone() (returns one tuple, or None if no match was found).
5. Commit: If you changed data using INSERT, UPDATE, or DELETE, call conn.commit() to save your changes.
6. Close: Release the database lock using conn.close().

import sqlite3

conn = sqlite3.connect("school.db")
cur = conn.cursor()

# Parameterised query using ? placeholder
year = 6
cur.execute("SELECT surname, first_name FROM Students WHERE year_group = ?", (year,))

students = cur.fetchall()

for student in students:
    surname, first_name = student
    print(f"{surname}, {first_name}")

conn.close()

In web development (LO 3.3), that list of tuples is what your script loops through to build an HTML table or list for the user.

When passing variables into queries, always use the ? placeholder tuple. The execute() method treats the variable strictly as data, preventing attackers from injecting malicious SQL commands into your system.

Key terms

Relational Database

A database that stores information across multiple two-dimensional tables linked by shared keys.

Primary Key

A field or set of fields that uniquely identifies each record in a table, which cannot contain null values.

Foreign Key

A field in one table that references the primary key in another table to establish a link between them.

Composite Key

A primary key composed of two or more columns combined to create a unique identifier for a row.

Data Redundancy

Storing the same piece of data in more than one place, usually resulting from poor design and leading to wasted space and inconsistency.

Data Inconsistency

A state where different copies of the same data show conflicting values or formats due to redundancy or unvalidated input.

Flat File

A single data table or file without relational links, often leading to repeated data and update errors.

Field

A column in a database table representing a specific attribute of an entity.

Record

A single row in a database table containing all attribute values for one entity instance.

SQL

Structured Query Language; the standard declarative programming language used to manage and query relational databases.

Commit

An instruction (conn.commit()) that permanently saves all changes made during the current database transaction.