RDBMS Concepts
A Relational Database Management System (RDBMS) is the engine behind every SQL database. In this chapter you'll learn the core concepts that power it — tables, keys, relationships between tables, data integrity rules, ACID properties, and normalisation. These ideas are the foundation of everything you'll write in SQL.
What is an RDBMS?
An RDBMS (Relational Database Management System) is software that stores data in tables and uses relationships between those tables — based on Codd's 12 Rules of Relational Databases, first proposed by Dr. Edgar F. Codd at IBM in 1970.
The word "relational" comes from the mathematical concept of a relation — which is simply a table with rows and columns. The power of RDBMS comes from linking these tables together through shared key columns, eliminating data duplication and enabling complex queries.
| RDBMS | Creator | Best known for | Licence |
|---|---|---|---|
| MySQL | Oracle / Open Source | Web applications, WordPress, PHP apps | GPL / Commercial |
| PostgreSQL | PostgreSQL Global Dev Group | Complex queries, JSON, advanced types | MIT (Free) |
| SQLite | D. Richard Hipp | Embedded apps, mobile, local files | Public Domain |
| SQL Server | Microsoft | Enterprise, .NET, Azure ecosystem | Commercial |
| Oracle DB | Oracle Corporation | Banking, government, large enterprise | Commercial |
| MariaDB | MySQL fork | MySQL-compatible, community-driven | GPL (Free) |
Keys in RDBMS — Primary, Foreign & Unique
Keys are special columns (or groups of columns) that give each row an identity and link tables together. Understanding keys is the single most important concept in RDBMS — everything else builds on top of this.
|
student_id
|
name |
email
|
|---|---|---|
| 101 | Priya Sharma | priya@mail.com |
| 102 | Rahul Patel | rahul@mail.com |
| 103 | Anika Desai | anika@mail.com |
| course_id
|
title | credits |
|---|---|---|
| C01 | Python Basics | 3 |
| C02 | Web Dev | 4 |
| C03 | SQL Mastery | 3 |
| enroll_id | student_id | course_id |
|---|---|---|
| 1 | 101 | C01 |
| 2 | 102 | C02 |
| 3 | 101 | C03 |
Example:
student_id in the
students table — no two students share the same ID.
Example:
student_id in
enrollments points to student_id in students.
Example:
email —
two students can't register with the same email address.
Example: In enrollments, the combination (
student_id +
course_id) is unique — one student
can't enroll in the same course twice.
Example: Both
student_id
and email are candidate keys for a
students table.
Example: {
student_id},
{email}, and
{student_id, email} are all super keys.
Here is how you declare these keys when creating a table in SQL:
CREATE TABLE students (
student_id INT PRIMARY KEY, -- PK: unique + not null
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE -- Unique Key: no duplicates
);
CREATE TABLE enrollments (
enroll_id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id CHAR(3) NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Table Relationships — 1-to-1, 1-to-Many, Many-to-Many
The "relational" in RDBMS means tables are related to each other through keys. There are three types of relationships. Getting these right when designing your database prevents duplicate data and makes queries much cleaner.
student has exactly one
student_profile (bio, photo, social links). The profile is
stored separately to keep the main table lean.
customer can place many
orders, but each order belongs to only one customer.
The FK customer_id lives in the orders table.
student can enroll in many
courses, and each course can have many students.
The enrollments table is the junction that links them.
|
student_id
|
name |
|---|---|
| 101 | Priya Sharma |
| 102 | Rahul Patel |
| 103 | Anika Desai |
|
course_id
|
title |
|---|---|
| C01 | Python Basics |
| C02 | Web Dev |
| C03 | SQL Mastery |
| enroll_id | student_id | course_id | grade |
|---|---|---|---|
| 1 | 101 | C01 | A |
| 2 | 101 | C03 | A+ |
| 3 | 102 | C02 | B+ |
| 4 | 103 | C01 | A |
| 5 | 103 | C03 | B |
Priya (101) is enrolled in 2 courses. SQL Mastery (C03) has 2 students. The junction table handles the M:N without any duplication.
Data Integrity Rules
Data integrity means your database always holds accurate, consistent, and trustworthy data. RDBMS enforces integrity automatically through rules built into the table structure — you don't have to write code to check them every time.
CREATE TABLE orders (
order_id INT PRIMARY KEY, -- Entity integrity
customer_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL
CHECK (amount > 0), -- Domain + User-defined
status ENUM('pending','shipped','done') NOT NULL,
order_date DATE DEFAULT CURDATE(),
FOREIGN KEY (customer_id) -- Referential integrity
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
ON DELETE RESTRICT (default) — prevents deleting a customer who still has orders. The safer choice in production systems.
ACID Properties — What Makes RDBMS Reliable
Every RDBMS guarantees ACID properties for transactions. A transaction is a group of SQL operations that must all succeed together — or all fail together. ACID is the set of rules that make this guarantee possible, which is why banks and financial systems trust SQL databases.
START TRANSACTION;
-- Step 1: Debit ₹1,000 from Account A
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 'A001';
-- Step 2: Credit ₹1,000 to Account B
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 'B002';
COMMIT; -- Both updates saved permanently ✓
-- If anything goes wrong, use ROLLBACK
-- ROLLBACK; -- Undoes both updates, no money lost
Normalisation — Organising Your Tables Properly
Normalisation is the process of organising a database to reduce data redundancy (duplicate data) and improve data integrity. It works by splitting large, messy tables into smaller, well-structured tables and defining relationships between them.
Think of it like organising a messy room — instead of dumping everything in one pile, you put clothes in the wardrobe, books on the shelf, and stationery in the drawer. Each thing has its proper place.
| order_id | customer_name | customer_city | product_name | product_price | qty |
|---|---|---|---|---|---|
| 1001 | Priya Sharma | Vadodara | Laptop | 55000 | 1 |
| 1002 | Priya Sharma | Vadodara | Mouse | 800 | 2 |
| 1003 | Rahul Patel | Surat | Laptop | 55000 | 1 |
courses = "Python, SQL, HTML"
in one column.student_courses table.
student_name (depends only on
student_id) violates 2NF.student_name
to the students table.
customer_city — city depends on
customer_id, not on order_id (transitive dependency).city to
the customers table. Reference it
via customer_id.
Most professional databases are designed to 3NF. Going to BCNF or 4NF/5NF is done only when performance or data integrity demands it.
| customer_id | name | city |
|---|---|---|
| C1 | Priya Sharma | Vadodara |
| C2 | Rahul Patel | Surat |
| product_id 🔑 | name | price |
|---|---|---|
| P1 | Laptop | 55000 |
| P2 | Mouse | 800 |
| order_id 🔑 | customer_id 🔗 | product_id 🔗 | qty |
|---|---|---|---|
| 1001 | C1 | P1 | 1 |
| 1002 | C1 | P2 | 2 |
| 1003 | C2 | P1 | 1 |
Codd's Rules — What Makes a True RDBMS
Dr. Edgar F. Codd defined 12 rules (rules 0–12) that a database must follow to be considered a true RDBMS. Here are the most important ones in plain English:
In a Many-to-Many relationship between Students and Courses, what is the correct way to implement it in an RDBMS?
- An RDBMS stores data in structured tables and enforces relationships between them using keys — MySQL, PostgreSQL, SQLite, and Oracle are the most popular RDBMS engines.
- A Primary Key (PK) uniquely identifies every row; a Foreign Key (FK) links one table to another; a Unique Key prevents duplicate values in a column.
- There are 3 types of table relationships: One-to-One (1:1), One-to-Many (1:N) (most common), and Many-to-Many (M:N) — the last requires a junction table.
- Data integrity is enforced through 4 rules: Entity, Referential, Domain, and User-Defined integrity — all declared in SQL using constraints.
- ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee that transactions either fully succeed or fully fail — making RDBMS safe for financial and critical systems.
- Normalisation (1NF → 2NF → 3NF) eliminates data duplication and update anomalies by organising data into smaller, properly related tables. Most databases are designed to 3NF.
Rate Us on Google
Join Our Learning Community
Connect with thousands of learners, get daily tips, ask questions and stay updated with new tutorials.