A Relational Database organizes data into tables (relations) with rows and columns, following strict mathematical rules. It's the most popular DBMS type (90% of business apps use it). Think MySQL, PostgreSQL, Oracle. This guide covers concepts, structure, operations, and why it's powerful for beginners.

What is a Relational Database?

A relational database stores data in 2D tables where:

• Rows (Tuples/Records): Individual data entries.

• Columns (Attributes): Data fields (name, age).

• Keys: Unique identifiers (Primary Key).

• Relations: Tables linked via keys.

Core Idea: Data independence + mathematical foundation (Relational Algebra).

Real-life analogy: Excel spreadsheet, but supercharged with relationships and queries.

Key Components of Relational Model

1. Table (Relation)

Holds related data. No duplicate rows.

text
STUDENT Table:
Roll_No | Name | Age | Branch
1       | Aman | 20  | CSE
2       | Riya | 19  | ECE

2. Domain

Possible values for a column (e.g., Age: 18-30 integers).

3. Keys

• Super Key: Any set making rows unique.

• Candidate Key: Minimal super key.

• Primary Key: Chosen candidate key (e.g., Roll_No).

• Foreign Key: References primary key in another table.

text
ENROLLMENT Table:
Roll_No (FK) | Course_ID
1           | C101
2           | C102

4. Constraints

• Domain: Valid values.

• Key: Uniqueness.

• Referential Integrity: FK must exist in parent table.

• NOT NULL, UNIQUE, CHECK.

Entity-Relationship (E-R) Model

First step in design: Visual blueprint.

text
Entities: STUDENT (Roll_No, Name), COURSE (Course_ID, Name)
Relationship: ENROLLS (many-to-many)
E-R Diagram:
[STUDENT] --(enrolls)-- [COURSE]

Types:

• Strong Entity: Has primary key.

• Weak Entity: Depends on strong (no own PK).

• Relationship: 1:1, 1:M, M:N.

Relational Algebra (Query Foundation)

Mathematical operations on tables (SQL translates to these):

Creating Relational Database (SQL Example)

sql
-- Schema Creation
CREATE TABLE STUDENT (
    Roll_No INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18),
    Branch VARCHAR(20)
);

CREATE TABLE COURSE (
    Course_ID VARCHAR(10) PRIMARY KEY,
    CName VARCHAR(50)
);

CREATE TABLE ENROLLMENT (
    Roll_No INT,
    Course_ID VARCHAR(10),
    FOREIGN KEY (Roll_No) REFERENCES STUDENT(Roll_No),
    FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID),
    PRIMARY KEY (Roll_No, Course_ID)
);

-- Instance Population
INSERT INTO STUDENT VALUES (1, 'Aman', 20, 'CSE');
INSERT INTO COURSE VALUES ('C101', 'DBMS');
INSERT INTO ENROLLMENT VALUES (1, 'C101');

Query Example:

sql
-- Find CSE students enrolled in DBMS
SELECT S.Name 
FROM STUDENT S 
JOIN ENROLLMENT E ON S.Roll_No = E.Roll_No 
JOIN COURSE C ON E.Course_ID = C.Course_ID
WHERE S.Branch = 'CSE' AND C.CName = 'DBMS';

Advantages of Relational Databases

Limitations

• Rigid Schema: Hard to change structure.

• Vertical Scaling: Expensive hardware for big data.

• Joins Overhead: Complex queries slow down.

When to Use: Structured data, transactions (banking, ERP).

Relational Database vs Others

text
Relational: Tables + Strict Schema + SQL + ACID
vs NoSQL: Flexible docs/graphs + Scalable + BASE

Summary

Relational databases use tables, keys, constraints for structured, reliable data storage. Foundation: E-R model → Relational schema → SQL queries.