What is a Relational Database?

A Relational Database is a type of database that stores and organizes data in the form of tables (relations) consisting of rows and columns. Each table represents a real-world entity, and tables are connected to each other through relationships defined by keys.

The concept of the Relational Model was introduced by Dr. Edgar F. Codd in 1970 in his famous research paper "A Relational Model of Data for Large Shared Data Banks." This revolutionary idea became the foundation of every modern RDBMS like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

What is an RDBMS?

An RDBMS (Relational Database Management System) is software that manages relational databases. It allows users to create, read, update, and delete data using SQL (Structured Query Language) while maintaining data integrity, security, and relationships.

Core Concepts of Relational Database

Visualizing a Relation (Table)

Here's a simple Students relation that explains all the terminology together:

Keys in Relational Databases

Keys are the most important concept in relational databases. They uniquely identify records and establish relationships between tables.

1. Primary Key

A column (or combination of columns) that uniquely identifies each row. It cannot be NULL and cannot have duplicates.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

2. Candidate Key

A column or set of columns that could qualify as a primary key. Every primary key is a candidate key, but not every candidate key becomes the primary key.

Example: In a Students table, both student_id and email can uniquely identify a student — both are candidate keys.

3. Super Key

A superset of attributes that can uniquely identify a row. Every candidate key is a super key, but not every super key is a candidate key.

Example: {student_id}, {student_id, name}, {student_id, email} — all are super keys.

4. Alternate Key

All candidate keys that are NOT chosen as the primary key are called Alternate Keys.

5. Foreign Key

A column in one table that refers to the primary key of another table. It maintains referential integrity and creates relationships between tables.

CREATE TABLE Enrollments (
    enroll_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);

6. Composite Key

A primary key made up of two or more columns combined to uniquely identify a row.

CREATE TABLE CourseEnrollment (
    student_id INT,
    course_id INT,
    enroll_date DATE,
    PRIMARY KEY (student_id, course_id)
);

7. Unique Key

Ensures that all values in a column are unique, but unlike primary key, it can accept one NULL value.

8. Surrogate Key

An artificially generated key (like AUTO_INCREMENT ID) that has no business meaning. It's used purely for unique identification.

All Keys at a Glance

Integrity Constraints

Constraints are the rules enforced on data columns to maintain the accuracy, validity, and reliability of data in the database.

Common SQL Constraints

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,        -- Primary Key
    name VARCHAR(100) NOT NULL,                   -- NOT NULL Constraint
    email VARCHAR(100) UNIQUE,                    -- Unique Constraint
    age INT CHECK (age >= 18),                    -- Check Constraint
    department VARCHAR(50) DEFAULT 'General',     -- Default Constraint
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES Employee(emp_id)  -- Foreign Key
);

Types of Relationships

Tables in a relational database are connected through different types of relationships:

Example: Many-to-Many Relationship

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

-- Junction Table linking Students and Courses
CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    enroll_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

ACID Properties

Every reliable relational database follows the ACID properties to ensure data integrity and consistency during transactions.

Normalization (Brief Overview)

Normalization is the process of organizing data in a database to eliminate redundancy and ensure data integrity. It involves dividing large tables into smaller related tables and defining relationships between them.

Relational vs Non-Relational Databases

Advantages vs Limitations

Real-World Use Cases

Relational Database Best Practices