Tutorials SQL RDBMS Concepts
Ch 9 / 78
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above
Chapter 3 Beginner 7 min read SQL

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 you'll learn
What RDBMS means and how it differs from a database
Primary keys, foreign keys, unique keys explained clearly
The 3 types of table relationships with diagrams
Data integrity rules that protect your database
ACID properties — what makes SQL databases reliable
Normalisation (1NF, 2NF, 3NF) made simple

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.

R — Relational
Data is stored in tables (relations). Each table has a fixed structure. Tables can be linked using shared key columns — avoiding data duplication.
DB — Database
The organised collection of all tables and the data they hold — the actual stored information your application reads and writes.
MS — Management System
The software engine (MySQL, PostgreSQL, Oracle) that accepts SQL queries, enforces rules, manages storage, controls security, and returns results.
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)
This tutorial
All SQL examples in this tutorial use standard SQL that works on MySQL and PostgreSQL. Minor syntax differences will be noted where they exist.

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.

Keys in action — students, courses, enrollments
students
student_id name email
101 Priya Sharma priya@mail.com
102 Rahul Patel rahul@mail.com
103 Anika Desai anika@mail.com
courses
course_id title credits
C01 Python Basics 3
C02 Web Dev 4
C03 SQL Mastery 3
enrollments
enroll_id student_id course_id
1 101 C01
2 102 C02
3 101 C03
Primary Key (PK) — unique row identifier
Foreign Key (FK) — references another table's PK
Unique Key — no duplicates allowed, can be NULL
Primary Key (PK)
A column (or combination of columns) that uniquely identifies every row in a table. Rules: must be unique, cannot be NULL, and a table can have only one primary key.

Example: student_id in the students table — no two students share the same ID.
Foreign Key (FK)
A column in one table that points to the Primary Key of another table. This is what creates the "relationship" between tables. The FK value must exist in the referenced table (or be NULL).

Example: student_id in enrollments points to student_id in students.
Unique Key (UK)
Ensures all values in a column are different — like a Primary Key, but it can accept one NULL value, and a table can have multiple unique keys.

Example: email — two students can't register with the same email address.
Composite Key
A Primary Key made of two or more columns together. Used when no single column is unique on its own.

Example: In enrollments, the combination (student_id + course_id) is unique — one student can't enroll in the same course twice.
Candidate Key
Any column (or set of columns) that could serve as a Primary Key — it is unique and non-null. The one you choose becomes the PK; the rest become alternate keys.

Example: Both student_id and email are candidate keys for a students table.
Super Key
Any set of columns that uniquely identifies a row — including combinations with extra unnecessary columns. Every Primary Key is a Super Key, but not every Super Key is a Primary Key.

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:

SQL — Declaring Primary Key, Foreign Key & Unique
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)
);
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

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.

1 : 1   One-to-One
One-to-One (1:1)
One row in Table A links to exactly one row in Table B — and vice versa. Used to split a large table into two for security or performance.
Example: Each student has exactly one student_profile (bio, photo, social links). The profile is stored separately to keep the main table lean.
1 : N   One-to-Many
One-to-Many (1:N)
One row in Table A links to many rows in Table B — but each row in B links back to only one row in A. This is the most common relationship in databases.
Example: One customer can place many orders, but each order belongs to only one customer. The FK customer_id lives in the orders table.
M : N   Many-to-Many
Many-to-Many (M:N)
Many rows in Table A link to many rows in Table B. This requires a junction table (bridge table) that holds both foreign keys — turning the M:N into two 1:N relationships.
Example: A student can enroll in many courses, and each course can have many students. The enrollments table is the junction that links them.
Many-to-Many resolved with a Junction Table
students
student_id name
101 Priya Sharma
102 Rahul Patel
103 Anika Desai
courses
course_id title
C01 Python Basics
C02 Web Dev
C03 SQL Mastery
↕ Junction Table (enrollments) ↕
enrollments (junction)
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.

Design Rule
Whenever you have a Many-to-Many relationship, always create a junction table. Never store comma-separated IDs in a single column — that breaks the relational model and makes queries extremely painful.
📊
Free Resource
Download our SQL Cheat Sheet
All essential commands, clauses and functions — one printable page.
Get Sheet

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.

Entity Integrity
Every table must have a Primary Key, and that key must be unique and never NULL. This ensures every row can always be uniquely identified.
PRIMARY KEY → NOT NULL + UNIQUE
Referential Integrity
A Foreign Key value must exist in the referenced table's Primary Key. You can't create an order for a customer that doesn't exist in the customers table.
FOREIGN KEY → must match referenced PK
Domain Integrity
Every column only holds values of the correct data type and within a valid range. A date column can't store text; a percentage column can't exceed 100.
CHECK, NOT NULL, data type constraints
User-Defined Integrity
Custom business rules enforced at the database level — for example: a salary must be greater than zero, or a discount cannot exceed 50%.
CHECK (salary > 0), triggers
SQL — All four integrity types in one table
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 / ON UPDATE
ON DELETE CASCADE — if a customer is deleted, all their orders are automatically deleted too.
ON DELETE RESTRICT (default) — prevents deleting a customer who still has orders. The safer choice in production systems.
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

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.

A
Atomicity
A transaction is all-or-nothing. Either every operation in the transaction succeeds, or none of them are applied. If any step fails, the entire transaction is rolled back to its original state — as if it never happened.
💡 Transferring ₹1,000: debit account A AND credit account B. If the credit fails, the debit is also undone automatically.
C
Consistency
A transaction takes the database from one valid state to another valid state. All rules, constraints, and integrity checks are satisfied before and after every transaction — no partial or broken data is ever committed.
💡 If a CHECK constraint says salary > 0, a transaction trying to set salary = -500 is rejected — consistency is maintained.
I
Isolation
Concurrent transactions execute independently of each other — they don't interfere. The result is the same as if transactions ran one at a time, sequentially. Prevents "dirty reads" of uncommitted data from other transactions.
💡 Two users booking the last flight seat simultaneously — isolation ensures only one booking succeeds, not both.
D
Durability
Once a transaction is committed, it is permanent. Even if the server crashes immediately after, the data will still be there when it restarts. The RDBMS uses write-ahead logs (WAL) to ensure committed data is never lost.
💡 After your UPI payment shows "Success", that transaction is durable — a server restart won't undo it.
SQL — A bank transfer using a transaction (ACID in action)
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.

Unnormalised Table — all data in one messy table
orders_messy (before normalisation)
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
Duplication: "Priya Sharma" and "Vadodara" are repeated for every order she places Update anomaly: If Priya moves cities, you must update every row — miss one and data is inconsistent Insert anomaly: Can't add a new customer until they place an order Delete anomaly: Deleting the last order of a customer erases their info permanently
1st 1NF
First Normal Form (1NF)
Rule: Every column holds a single atomic (indivisible) value. No repeating groups or arrays.
Each cell must contain one value only — not a list or comma-separated values. Every row must be unique (have a primary key).

Bad: courses = "Python, SQL, HTML" in one column.
Good: Each course gets its own row in a separate student_courses table.
2nd 2NF
Second Normal Form (2NF)
Rule: Must be in 1NF + every non-key column must depend on the ENTIRE primary key (no partial dependency).
Applies when your table has a composite primary key. Every non-key column must rely on both key columns — not just part of the key.

Bad: In an enrollments table with PK (student_id, course_id), storing student_name (depends only on student_id) violates 2NF.
Good: Move student_name to the students table.
3rd 3NF
Third Normal Form (3NF)
Rule: Must be in 2NF + no transitive dependency (non-key column depending on another non-key column).
Non-key columns must depend only on the primary key, not on other non-key columns.

Bad: An orders table storing customer_city — city depends on customer_id, not on order_id (transitive dependency).
Good: Move city to the customers table. Reference it via customer_id.
BCNF 3.5
Boyce-Codd Normal Form (BCNF)
Rule: A stronger version of 3NF. Every determinant must be a candidate key.
Handles edge cases that 3NF misses when there are multiple overlapping candidate keys. Rarely needed for everyday databases — 3NF is sufficient for most applications.

Most professional databases are designed to 3NF. Going to BCNF or 4NF/5NF is done only when performance or data integrity demands it.
After Normalisation (3NF) — clean, linked tables
customers
customer_id name city
C1 Priya Sharma Vadodara
C2 Rahul Patel Surat
products
product_id 🔑 name price
P1 Laptop 55000
P2 Mouse 800
orders
order_id 🔑 customer_id 🔗 product_id 🔗 qty
1001 C1 P1 1
1002 C1 P2 2
1003 C2 P1 1
✅ No duplication — "Priya Sharma" stored once ✅ Easy updates — change city in one row only ✅ Clean JOINs — query orders + customer info together

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:

Rule 1 — Information Rule
All data must be stored in tables. No hidden files, no special fields — everything is a value in a row and column.
Rule 2 — Guaranteed Access
Every piece of data must be accessible using table name + primary key + column name. No row should ever be unreachable.
Rule 3 — NULL Support
The database must support NULL values to represent missing or unknown information — distinct from zero, empty string, or any other value.
Rule 5 — Comprehensive Language
The system must support at least one well-defined relational language (SQL) that can handle data definition, manipulation, integrity, and access control.
Rule 10 — Integrity Independence
Integrity constraints (like PRIMARY KEY, FOREIGN KEY, CHECK) must be stored in the database catalogue — not in the application code. They are enforced by the RDBMS itself.
Rule 12 — Non-Subversion
If a lower-level interface exists, it cannot be used to bypass the integrity rules defined in the database. Security and integrity cannot be circumvented.
In Practice
No commercial database perfectly follows all 12 rules — even Oracle and PostgreSQL only satisfy most of them. Codd's rules serve as a theoretical benchmark to guide RDBMS design, not a strict checklist every vendor must complete.
Quick Check — Chapter 3

In a Many-to-Many relationship between Students and Courses, what is the correct way to implement it in an RDBMS?

Chapter Summary
  • 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.
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

Rate Us on Google

4.8 / 5
Enjoying this tutorial?
Your 5-star review helps other learners discover our free tutorials and encourages us to keep creating quality content.
80+ reviews
Write a Google Review
Join WhatsApp Channel