Relational Schema Design

When tracking academic performance, a marks table typically requires references to both learners and courses rather than storing redundant textual data.

CREATE TABLE marks (
    mark_id INT PRIMARY KEY AUTO_INCREMENT,
    pupil_ref INT,
    course_ref INT,
    mark_value DECIMAL(5,2)
);

The pupil_ref and course_ref columns store identifiers that point to the primary keys of a pupils table and a courses table, respectively. This avoids duplication and preserves normalization.

Enforcing Referential Integrity

To prevent orphaned records, apply foreign key constraints that validate relational data during inserts or updates.

Adding a constraint to an existing table:

ALTER TABLE marks
ADD CONSTRAINT fk_pupil_mark
FOREIGN KEY (pupil_ref) REFERENCES pupils(pupil_id);

Defining constraints during table creation:

CREATE TABLE marks (
    mark_id INT PRIMARY KEY AUTO_INCREMENT,
    pupil_ref INT,
    course_ref INT,
    mark_value DECIMAL(5,2),
    FOREIGN KEY (pupil_ref) REFERENCES pupils(pupil_id),
    FOREIGN KEY (course_ref) REFERENCES courses(course_id)
);

Cascading Referential Actions

By default, deleting a parent record referenced by a child row triggers an error. You can alter this behavior using cascading rules:

ALTER TABLE marks
ADD CONSTRAINT fk_pupil_mark
FOREIGN KEY (pupil_ref) REFERENCES pupils(pupil_id)
ON DELETE CASCADE;

Available behaviors include:

Action	Behavior
RESTRICT	Rejects the deletion (default)
CASCADE	Automatically removes dependent child rows
SET NULL	Nullifies the foreign key column
NO ACTION	Similar to RESTRICT; no action taken

Joining Related Tables

To retrieve a unified result set spanning multiple tables, use join operations. Consider querying each learner's name, course title, and corresponding mark. The relationships are:

• pupils.pupil_id ↔ marks.pupil_ref
• courses.course_id ↔ marks.course_ref

SELECT p.full_name, c.course_name, m.mark_value
FROM marks m
INNER JOIN pupils p ON m.pupil_ref = p.pupil_id
INNER JOIN courses c ON m.course_ref = c.course_id;

Join Variants

• INNER JOIN: Returns only rows with matching keys in both tables.
• LEFT JOIN: Returns all rows from the left table, with unmatched right-table columns populated as NULL.
• RIGHT JOIN: Returns all rows from the right table, with unmatched left-table columns populated as NULL.

Use dot notation (table.column) for clarity. Aliases shorten verbose table names:

SELECT p.full_name, AVG(m.mark_value)
FROM marks m
JOIN pupils p ON m.pupil_ref = p.pupil_id
GROUP BY p.full_name;

Query Examples

Learners and their average marks:

SELECT p.full_name, AVG(m.mark_value) AS average_mark
FROM marks m
INNER JOIN pupils p ON m.pupil_ref = p.pupil_id
GROUP BY p.full_name;

Male learners and total marks:

SELECT p.full_name, SUM(m.mark_value) AS total_mark
FROM marks m
INNER JOIN pupils p ON m.pupil_ref = p.pupil_id
WHERE p.gender_code = 1
GROUP BY p.full_name;

Active courses and their statistics:

SELECT c.course_name, AVG(m.mark_value) AS mean_score, MAX(m.mark_value) AS highest_score
FROM marks m
INNER JOIN courses c ON m.course_ref = c.course_id
WHERE c.is_active = 1
GROUP BY c.course_name;

Self-Referencing Tables

Hierarchical data—such as geographic zones—can be stored in a single table containing a recursive foreign key.

CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    loc_name VARCHAR(40),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES locations(location_id)
);

Top-level regions have a NULL parent_id, while subordinate areas reference their parent's location_id.

Importing seed data:

SOURCE locations.sql;

Hierarchical Query Examples

Count all top-level regions (provinces):

SELECT COUNT(*) FROM locations WHERE parent_id IS NULL;

Find all cities within a specific province:

SELECT city.*
FROM locations AS city
INNER JOIN locations AS province ON city.parent_id = province.location_id
WHERE province.loc_name = 'California';

Retrieve districts under a specific city using multiple self-joins:

SELECT district.*
FROM locations AS district
INNER JOIN locations AS city ON district.parent_id = city.location_id
LEFT JOIN locations AS subdistrict ON district.location_id = subdistrict.parent_id
WHERE city.loc_name = 'Los Angeles';

Subqueries

Queries can be nested to produce scalar values for outer select lists or conditions.

Displaying subject-specific marks per learner:

SELECT 
    p.full_name,
    (SELECT m.mark_value FROM marks m JOIN courses c ON m.course_ref = c.course_id WHERE c.course_name = 'Literature' AND m.pupil_ref = p.pupil_id) AS literature,
    (SELECT m.mark_value FROM marks m JOIN courses c ON m.course_ref = c.course_id WHERE c.course_name = 'Algebra' AND m.pupil_ref = p.pupil_id) AS algebra,
    (SELECT m.mark_value FROM marks m JOIN courses c ON m.course_ref = c.course_id WHERE c.course_name = 'Physics' AND m.pupil_ref = p.pupil_id) AS physics
FROM pupils p;

Views

Encapsulate complex, frequently used queries as virtual tables to simplify maintenance.

CREATE VIEW pupil_mark_summary AS
SELECT p.*, m.mark_value
FROM marks m
INNER JOIN pupils p ON m.pupil_ref = p.pupil_id;

Querying the view:

SELECT * FROM pupil_mark_summary;

Transaction Control

When a business process requires multiple statements, transactions ensure atomic outcomes—either every change persists, or all are reverted.

ACID Properties:

• Atomicity: Operations complete entirely or not at all.
• Consistency: The database moves from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Committed changes survive system failures.

Tables must use the InnoDB (or BDB) engine:

SHOW CREATE TABLE pupils;
ALTER TABLE pupils ENGINE=InnoDB;

Transaction Syntax

START TRANSACTION;
-- or BEGIN;

COMMIT;
ROLLBACK;

Demonstration: Comitted Data

Terminal 1 — Initial read:

SELECT * FROM pupils;

Terminal 2 — Start transaction and insert:

BEGIN;
INSERT INTO pupils(full_name) VALUES ('John Doe');

Terminal 1 — Repeat read (uncommitted data not visible in standard isolation):

SELECT * FROM pupils;

Terminal 2 — Commit:

COMMIT;

Terminal 1 — Post-commit read:

SELECT * FROM pupils;

Demonstration: Rolled-Back Data

Terminal 2 — Begin and insert:

BEGIN;
INSERT INTO pupils(full_name) VALUES ('Jane Smith');

Terminal 1 — Verify absence:

SELECT * FROM pupils;

Terminal 2 — Revert:

ROLLBACK;

Terminal 1 — Confirm rollback:

SELECT * FROM pupils;

String Manipulation Functions

Return ASCII code:

SELECT ASCII('X');

Return character from ASCII:

SELECT CHAR(88);

Concatenate values:

SELECT CONCAT(10, 20, 'AB');

Character length:

SELECT LENGTH('Hello');

Extract substrings:

SELECT LEFT('Database', 4);
SELECT RIGHT('Database', 4);
SELECT SUBSTRING('Database', 3, 4);

Trim whitespace or custom characters:

SELECT TRIM('  data  ');
SELECT TRIM(LEADING 'x' FROM 'xxxdatxxx');
SELECT TRIM(BOTH 'x' FROM 'xxxdatxxx');
SELECT TRIM(TRAILING 'x' FROM 'xxxdatxxx');

Generate spaces:

SELECT SPACE(10);

Replace occurrences:

SELECT REPLACE('Hello2024', '2024', 'World');

Case conversion:

SELECT LOWER('Hello');
SELECT UPPER('Hello');

Mathematical Functions

Absolute value:

SELECT ABS(-45);

Modulo:

SELECT MOD(17, 5);
SELECT 17 % 5;

Floor and ceiling:

SELECT FLOOR(4.7);
SELECT CEILING(4.2);

Rounding:

SELECT ROUND(3.14159, 2);

Exponentiation:

SELECT POW(3, 4);

Constants and randomness:

SELECT PI();
SELECT RAND();

Date and Time Functions

Extract components:

SELECT YEAR('2023-10-15');
SELECT MONTH('2023-10-15');
SELECT DAY('2023-10-15');
SELECT HOUR('14:30:00');
SELECT MINUTE('14:30:00');
SELECT SECOND('14:30:00');

Date arithmetic:

SELECT '2023-10-15' + INTERVAL 7 DAY;

Format dates:

SELECT DATE_FORMAT('2023-10-15', '%Y-%m-%d');

Current timestamps:

SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT NOW();