STANDALONE NOTES

Relational Database Concepts

A focused guide to how relational databases organize data with tables, fields, records, keys, relationships, integrity rules, normalization, indexes, and SQL.

8 sections Relational model Student records example

RDBMS

Relational DB Map

01

Introduction to Relational Databases

A relational database organizes data into tables. Each table represents a subject or entity, rows represent individual records, and columns represent fields or attributes. Relationships connect records in different tables through keys.

Core idea

Instead of storing everything in one large block, a relational database separates related subjects into tables and then connects those tables using identifiers. This reduces unnecessary duplication and makes the data easier to query, validate, and maintain.

Table

A collection of related records arranged in rows and columns. Examples: Students, Courses, and Enrollment.

Field / Column

An attribute describing the entity. Examples: StudentID, Name, Email, Program, and CourseCode.

Record / Row

One complete instance stored in a table. One row in Students represents one student.

Relationship

An association between tables, usually created by matching a foreign key to a primary key.

02

Tables, Fields & Records

The table is the basic storage structure of the relational model. Use this student table to see how rows, columns, and individual values fit together.

Students

StudentID PK	Name	Program	Email
1001	Alya Rahman	Computer Science	alya@example.edu
1002	Hafiz Iskandar	Data Analytics	hafiz@example.edu
1003	Siti Amira	Business	siti@example.edu

Table

Students

The whole grid represents one table focused on the student entity.

Field

Program

The Program column stores the same kind of attribute for every student record.

Record

Student 1002

The entire row for Hafiz is one record.

Value

Data Analytics

This is one value at the intersection of a record and a field.

03

Primary & Foreign Keys

Primary keys uniquely identify records. Foreign keys reference primary keys in other tables, creating the links that allow related relational data to be stored separately and combined when needed.

Primary Key (PK)

A field—or combination of fields—whose value uniquely identifies each record. Students.StudentID and Courses.CourseID are primary keys.

UniqueIdentifier

Foreign Key (FK)

A field containing a reference to a related record. Enrollment.StudentID references Students, while Enrollment.CourseID references Courses.

ReferenceRelationship

Visual Illustration — How the Tables Are Linked

Follow the highlighted record for Hafiz Iskandar (StudentID 1002). Enrollment acts as the linking table between a student and the courses that student takes.

Students

StudentID (PK)	Name	Program
1001	Alya Rahman	CS
1002	Hafiz Iskandar	Data Analytics
1003	Siti Amira	Business
1004	Kumar Naidu	Engineering

PK: StudentID
Uniquely identifies each student.

Enrollment

EnrollmentID (PK)	StudentID (FK)	CourseID (FK)	Semester
E500	1001	C101	Fall
E501	1002	C101	Fall
E502	1002	C205	Fall
E503	1003	C101	Fall
E504	1004	C220	Fall

PK: EnrollmentID
FKs: StudentID → Students  ·  CourseID → Courses

Courses

CourseID (PK)	CourseName	Credits
C101	Database Fundamentals	3
C205	Data Management	3
C220	Data Visualization	3
C310	Analytics Project	4

PK: CourseID
Uniquely identifies each course.

Read the links: Students.StudentID 1002 → Enrollment.StudentID 1002. The two matching enrollment rows then contain CourseID C101 and C205 → Courses.CourseID C101 and C205.

How the Tables Are Linked

1The Students table gives Hafiz the unique primary key StudentID 1002.

2Enrollment contains StudentID 1002 as a foreign key in two records: E501 and E502.

3Those enrollment rows contain CourseID C101 and C205 as foreign keys.

4The CourseID values point to the matching primary keys in Courses, revealing which courses Hafiz takes.

Example SQL Join

Because the key values match, SQL can combine columns from all three tables:

SELECT s.StudentID, s.Name,
       c.CourseID, c.CourseName
FROM Students AS s
JOIN Enrollment AS e
  ON s.StudentID = e.StudentID
JOIN Courses AS c
  ON e.CourseID = c.CourseID
WHERE s.StudentID = 1002;

Result

1002 · Hafiz Iskandar → C101 Database Fundamentals
1002 · Hafiz Iskandar → C205 Data Management

Key takeaway

The primary key identifies a record in its own table. A foreign key carries that identifier into another table. Enrollment therefore connects Students and Courses without repeatedly storing the student's or course's full details.

04

Relationships & Cardinality

Cardinality describes how many records in one table can be associated with records in another table. The three common patterns are one-to-one (1:1), one-to-many (1:M), and many-to-many (M:M).

Pattern 1 — One-to-One (1:1)

One record in the first table relates to only one record in the second table, and vice versa.

Example

Student ↔ Student Profile

Each student has one profile, and each profile belongs to one student.

How the key works

StudentID

StudentProfile.StudentID references Students.StudentID and is unique in StudentProfile, preventing multiple profiles for the same student.

Pattern 2 — One-to-Many (1:M)

One record in the first table can relate to many records in the second table, but each record on the many side belongs to one record on the one side.

Example

Student → Enrollment

One student can have many enrollment records. Each enrollment record refers to one student.

Example records

Student 1002

Hafiz can have enrollment E501 for C101 and E502 for C205. Both rows contain StudentID 1002 as a foreign key.

Pattern 3 — Many-to-Many (M:M)

Many records in the first table can relate to many records in the second table. In a relational database, this is normally implemented using a junction (bridge) table.

Example

Students ↔ Courses

A student can take many courses, and a course can contain many students.

Resolution

Enrollment Bridge Table

Enrollment stores StudentID and CourseID as foreign keys. Each row represents one student-course association.

Enrollment — junction table

EnrollmentID PK	StudentID FK	CourseID FK
E501	1002	C101
E502	1002	C205
E503	1003	C101

Quick comparison

1:1: one student → one profile.   1:M: one student → many enrollment records.   M:M: many students ↔ many courses, resolved through the Enrollment junction table.

Practice with the sample relational database

Download the SQLite database used in these examples. It contains the Students, Enrollment, and Courses tables, including primary keys, foreign keys, indexes, and cascade rules.

↓ Download SQLite Sample Database

SQLite file · 3 linked tables · Foreign-key constraints enabled in the schema

05

Referential Integrity

Referential integrity protects relationships between tables. A foreign-key value should refer to an existing primary-key value. Use the animation below to see what happens when references are valid, broken, updated, or deleted.

Valid Reference

A foreign key matches an existing primary key. Example: Enrollment.StudentID 1002 points to Students.StudentID 1002.

Orphaned Record

A child record points to a parent record that does not exist. This broken relationship is what referential-integrity rules are designed to prevent.

Cascade Update

If a referenced key changes, cascade update propagates the new key value to related foreign-key records.

Cascade Delete

If a parent record is deleted, cascade delete automatically removes related child records. This can also remove dependent history, so it must be used carefully.

Interactive Referential Integrity Demonstration

Start with Student 1002 and two Enrollment records that reference that student. Select a concept to animate the relationship.

Students — Parent Table

StudentID (PK)Name

1002Hafiz Iskandar

PK 1002 = FK 1002

Enrollment — Child Table

EnrollmentStudentID (FK)

E5011002

E5021002

VALID Both foreign keys contain 1002, and Students contains primary key 1002. The relationship is valid.

What the animation demonstrates

Valid: PK and FK match. Orphan: the parent disappears while child references remain. Cascade update: changing PK 1002 → 2002 also changes both foreign keys. Cascade delete: deleting the parent also removes the dependent enrollment rows.

06

Normalization

Normalization reorganizes relational data to reduce unnecessary duplication and improve integrity. The example below begins with a denormalized student table and progressively restructures it into First, Second, and Third Normal Form.

Starting Point — Denormalized Students Table

This table mixes student, program, and course information in one place. Course values repeat across columns, while student and program details are duplicated whenever a student has multiple courses.

StudentCourses_Denormalized

StudentID	StudentName	ProgramID	ProgramName	Course1	Course1Name	Course2	Course2Name
1002	Hafiz Iskandar	P20	Data Analytics	C101	Database Fundamentals	C205	Data Management
1003	Siti Amira	P30	Business	C101	Database Fundamentals	C310	Analytics Project

Problems to notice

Repeating groups: Course1/Course2. Redundancy: course and program names may appear repeatedly. Update risk: changing a course or program name may require editing many rows.

Interactive Transition: Denormalized → 1NF → 2NF → 3NF

Select each stage or use Play Transition to watch the structure improve step by step.

Denormalized

REPEATING GROUPS

StudentID	StudentName	ProgramID	ProgramName	Course1	Course1Name	Course2	Course2Name
1002	Hafiz Iskandar	P20	Data Analytics	C101	Database Fundamentals	C205	Data Management
1003	Siti Amira	P30	Business	C101	Database Fundamentals	C310	Analytics Project

Problem: Course1 and Course2 are repeating groups. Adding a third course would require adding more columns.

First Normal Form (1NF)

ONE VALUE PER FIELD

StudentID	StudentName	ProgramID	ProgramName	CourseID	CourseName
1002	Hafiz Iskandar	P20	Data Analytics	C101	Database Fundamentals
1002	Hafiz Iskandar	P20	Data Analytics	C205	Data Management
1003	Siti Amira	P30	Business	C101	Database Fundamentals
1003	Siti Amira	P30	Business	C310	Analytics Project

1NF change: Course1 and Course2 become separate rows. Each field now holds one value. A student-course row can be identified by the composite key (StudentID, CourseID).

Second Normal Form (2NF)

SEPARATE PARTIAL DEPENDENCIES

Students

StudentID (PK)	StudentName	ProgramID	ProgramName
1002	Hafiz Iskandar	P20	Data Analytics
1003	Siti Amira	P30	Business

Courses

CourseID (PK)	CourseName
C101	Database Fundamentals
C205	Data Management
C310	Analytics Project

Enrollment

StudentID (FK)	CourseID (FK)
1002	C101
1002	C205
1003	C101
1003	C310

2NF change: Student details depend on StudentID, while course details depend on CourseID. They are separated into Students and Courses; Enrollment keeps only the keys needed to link them.

Third Normal Form (3NF)

REMOVE TRANSITIVE DEPENDENCY

Students

StudentID (PK)	StudentName	ProgramID (FK)
1002	Hafiz Iskandar	P20
1003	Siti Amira	P30

Programs

ProgramID (PK)	ProgramName
P20	Data Analytics
P30	Business

Courses

CourseID (PK)	CourseName
C101	Database Fundamentals
C205	Data Management
C310	Analytics Project

Enrollment

StudentID (FK)	CourseID (FK)
1002	C101
1002	C205
1003	C101
1003	C310

3NF change: ProgramName depends on ProgramID rather than directly on StudentID. Programs becomes its own table, and Students stores ProgramID as a foreign key.

What Changes at Each Stage?

Denormalized

Repeating Data

Course1, Course2, duplicated descriptions

→

1NF

Atomic Rows

One course per student-course row

→

2NF

Split by Key

Students, Courses, Enrollment

→

3NF

Split Dependencies

Programs becomes a separate table

Normalization vs. denormalization

Normalization separates data into related tables to reduce redundancy and improve integrity. Denormalization intentionally combines some data again, often for reporting, analytics, or situations where simpler/faster reads are more important than eliminating every repeated value.

07

Indexes, SQL & ACID

Relational databases are more than tables. Indexes help locate records efficiently, SQL is used to work with relational data, and ACID principles describe reliable transaction behavior.

Indexing

An index helps the database locate matching records without examining every row. Primary keys are commonly indexed automatically.

SQL

Structured Query Language is used to retrieve and manipulate relational data. Queries can join related tables so separate data appears together in a result.

Animation: Table Scan vs. Indexed Lookup

Find StudentID 1005. Compare checking rows one by one with using an index to jump to the matching record.

Students TableDATA ROWS

1001Alya RahmanAnalytics

1002Hafiz IskandarDatabase

1003Siti AmiraNetworking

1004Daniel LeeSecurity

1005Nur AisyahData Science

1006Jason TanCloud

→

StudentID IndexKEY → ROW

1001→ row 1

1002→ row 2

1003→ row 3

1004→ row 4

1005→ row 5

1006→ row 6

Ready. Choose a lookup method to see how an index can reduce row-by-row searching.

Animation: SQL JOIN in Action

Run the query to see SQL filter StudentID 1002, match the foreign key, and combine data from two related tables.

Students

1001Alya RahmanAnalytics

1002Hafiz IskandarDatabase

1003Siti AmiraNetworking

⇄

Enrollment

1001C1102026A

1002C1012026A

1002C2052026B

Query Result

1002Hafiz IskandarC1012026A

1002Hafiz IskandarC2052026B

Ready. SQL can select, filter, and join related data into one result.

Example: joining student and enrollment data

SELECT s.StudentID, s.Name, e.CourseID, e.Semester
FROM Students AS s
JOIN Enrollment AS e
  ON s.StudentID = e.StudentID
WHERE s.StudentID = 1002;

The JOIN uses the relationship between Students.StudentID and Enrollment.StudentID to combine columns from both tables.

Animation: ACID Transaction in Action

Transfer $100 from Account A to Account B. Watch the four ACID principles support a reliable database transaction.

Account A

$500

$100 →

Account B

$300

Ready. The transfer will be handled as one reliable transaction.

BEGIN TRANSACTION

Atomicity

A transaction is treated as all-or-nothing: all required changes complete, or the transaction does not complete.

Consistency

A transaction must preserve the database's defined validity and integrity rules.

Isolation

Concurrent transactions should not interfere with each other or expose incomplete work.

Durability

Once a transaction is committed, its changes persist even after a failure or restart.

08

Relational Database Summary

Use this map to connect the major ideas rather than memorizing each term in isolation.

Concept chain

Entity → Table → Record → Field → Primary Key → Foreign Key → Relationship → Referential Integrity. Normalization determines how the tables are organized; cardinality describes how records relate; indexes improve lookup efficiency; SQL retrieves and combines the data; ACID principles support reliable transactions.

Storage

Tables contain rows (records) and columns (fields).

Identity

Primary keys uniquely identify records.

Connection

Foreign keys connect related tables.

Quality

Normalization and referential integrity help protect consistency.

09

Knowledge Check — 20 Quiz Questions

Test your understanding of tables, keys, relationships, referential integrity, normalization, indexes, SQL, and ACID. Choose one answer for each question, then submit to see your score.

1. Which statement best defines a relational database?

A. It stores related data in tables connected by keys.B. It stores all data in one unstructured document.C. It requires every table to contain identical columns.D. It cannot represent relationships between records.

Answer: A. It stores related data in tables connected by keys.
Relational databases organize data into tables and use keys to connect related records.

2. In a Students table, what does one row normally represent?

A. One field definitionB. One student recordC. One database relationshipD. One SQL command

Answer: B. One student record
A row represents one record—in this example, one student.

3. What is the main purpose of a primary key?

A. To format a tableB. To uniquely identify each recordC. To duplicate values across tablesD. To sort every query automatically

Answer: B. To uniquely identify each record
A primary key provides a unique identifier for each record.

4. Which field in Enrollment is a foreign key referencing Students?

A. EnrollmentIDB. SemesterC. StudentIDD. CourseName

Answer: C. StudentID
Enrollment.StudentID points to Students.StudentID.

5. Which field in Enrollment can reference Courses.CourseID?

A. CourseIDB. StudentIDC. EnrollmentIDD. Program

Answer: A. CourseID
Enrollment.CourseID stores the identifier of a related course.

6. If one student can have many enrollment records, the relationship is:

A. One-to-oneB. One-to-manyC. Many-to-many without a bridgeD. No relationship

Answer: B. One-to-many
One Students record can be referenced by multiple Enrollment records.

7. How is a many-to-many Students–Courses relationship implemented in the examples?

A. By merging both tablesB. By using Enrollment as a junction tableC. By removing all keysD. By storing Course1 and Course2 columns

Answer: B. By using Enrollment as a junction table
Enrollment acts as the junction table linking students and courses.

8. What does referential integrity require for a foreign-key value?

A. It must have a matching referenced key when the relationship requires one.B. It must always equal the table's primary key.C. It must contain text only.D. It must be different in every row.

Answer: A. It must have a matching referenced key when the relationship requires one.
Referential integrity keeps references valid by requiring matching referenced keys.

9. What is an orphaned record?

A. A row with a valid primary keyB. A child record whose foreign key no longer has a matching parentC. A duplicated course nameD. A table without an index

Answer: B. A child record whose foreign key no longer has a matching parent
An orphan occurs when a child still references a parent record that no longer exists.

10. What does ON UPDATE CASCADE demonstrate?

A. Deleting every tableB. Propagating a changed referenced key to dependent foreign keysC. Converting rows into columnsD. Creating a new database

Answer: B. Propagating a changed referenced key to dependent foreign keys
Cascade update propagates the changed parent key to dependent foreign-key values.

11. What does ON DELETE CASCADE do in the lesson example?

A. Deletes dependent child rows when the referenced parent is deletedB. Prevents every DELETE statementC. Renames the parent keyD. Creates an orphan intentionally

Answer: A. Deletes dependent child rows when the referenced parent is deleted
Cascade delete removes dependent child rows along with the referenced parent.

12. What is the main problem with Course1 and Course2 columns in the denormalized example?

A. They create repeating groupsB. They are primary keysC. They enforce 3NFD. They are indexes

Answer: A. They create repeating groups
Course1 and Course2 are repeating groups, making the design harder to extend.

13. What key change is illustrated when moving to First Normal Form (1NF)?

A. Put multiple courses into one cellB. Represent each student-course occurrence as a separate rowC. Remove StudentIDD. Combine all tables into one

Answer: B. Represent each student-course occurrence as a separate row
The 1NF illustration removes repeating course columns and uses one course value per row.

14. In the 1NF example, which pair can identify a student-course row?

A. StudentName and ProgramNameB. StudentID and CourseIDC. ProgramID and ProgramNameD. CourseName and Semester

Answer: B. StudentID and CourseID
The example uses the composite key StudentID plus CourseID to identify a student-course occurrence.

15. What happens in the illustrated transition to Second Normal Form (2NF)?

A. Students, Courses, and Enrollment are separatedB. Course1 and Course2 are addedC. All foreign keys are removedD. Programs and Courses are merged

Answer: A. Students, Courses, and Enrollment are separated
The 2NF stage separates student details, course details, and their linking records.

16. Why is Programs separated from Students in the illustrated Third Normal Form (3NF) stage?

A. ProgramName depends on ProgramID rather than directly on StudentIDB. StudentID is no longer uniqueC. CourseID must become textD. Enrollment needs repeating groups

Answer: A. ProgramName depends on ProgramID rather than directly on StudentID
ProgramName is determined by ProgramID, so the Programs data is separated.

17. What is the purpose of an index?

A. To improve lookup/query access for indexed valuesB. To replace all primary keysC. To create repeating groupsD. To guarantee every query returns one row

Answer: A. To improve lookup/query access for indexed values
Indexes are data structures used to improve lookup and query access.

18. Which SQL operation combines related rows from Students and Enrollment using matching keys?

A. DROPB. JOINC. DELETED. ALTER

Answer: B. JOIN
JOIN combines related rows by matching key values such as StudentID.

19. Which ACID property means a transaction is all-or-nothing?

A. AtomicityB. ConsistencyC. IsolationD. Durability

Answer: A. Atomicity
Atomicity means the transaction completes as a unit or does not take effect.

20. Which ACID property means committed changes persist?

A. IsolationB. ConsistencyC. DurabilityD. Atomicity

Answer: C. Durability
Durability means committed transaction results persist.

RELATIONAL DATABASE CONCEPTS · INTERACTIVE NOTES · MARBLE LIGHT BLUE EDITION