Relational Database Management (RDBMS) & SQL: From ER Diagrams to Normalization (1NF, 2NF, 3NF)

The Importance of Databases in Modern Computing

Every dynamic application—from social media platforms to student information systems—relies on a Database Management System (DBMS) to store, organize, and retrieve data efficiently and securely. In the A/L ICT curriculum, Unit 8 is one of the highest-weightage topics in Paper 2.

1. Entity-Relationship (ER) Modeling

Before creating tables, database architects design a conceptual model using ER Diagrams:

• Entity: A real-world object or concept that can be distinctly identified (e.g., Student, Course, Teacher). Represented by a rectangle.
• Attribute: A property or characteristic of an entity (e.g., Student_ID, Name, DateOfBirth). Represented by an oval.
• Relationship: An association between two or more entities (e.g., Enrolls_In, Teaches). Represented by a diamond.
• Cardinality: The numerical relationship between instances: One-to-One (1:1), One-to-Many (1:N), and Many-to-Many (M:N).

2. The Normalization Process: Eliminating Redundancy

Normalization is a systematic approach to decomposing tables to eliminate data redundancy and prevent update, insertion, and deletion anomalies.

First Normal Form (1NF)

A table is in 1NF if:

• Each attribute contains only atomic (indivisible) values.
• There are no repeating groups or multi-valued attributes.
• A unique Primary Key is defined for each record.

Second Normal Form (2NF)

A table is in 2NF if:

• It is already in 1NF.
• It has no partial dependency: every non-key attribute must be fully functionally dependent on the entire composite primary key.

Third Normal Form (3NF)

A table is in 3NF if:

• It is already in 2NF.
• It has no transitive dependency: non-key attributes must not depend on other non-key attributes (i.e., non-key attributes depend only on the Primary Key).

3. Mastering Essential SQL Queries

Structured Query Language (SQL) is the standard language for relational databases. Candidates must know Data Definition Language (DDL) and Data Manipulation Language (DML) commands:

Creating Tables with Constraints

CREATE TABLE Students ( StudentID VARCHAR(10) PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, District VARCHAR(30), BatchYear INT );

Querying with Filters and Aggregates

-- Find all students from Jaffna enrolled in the 2026 batch SELECT StudentID, FirstName, LastName FROM Students WHERE District = 'Jaffna' AND BatchYear = 2026 ORDER BY LastName ASC;

Joining Related Tables

-- Get student names alongside the courses they have enrolled in SELECT S.FirstName, S.LastName, C.CourseName FROM Students S JOIN Enrollments E ON S.StudentID = E.StudentID JOIN Courses C ON E.CourseID = C.CourseID;