SQL: RDBMS Concepts

1. What is RDBMS?

A Relational Database Management System (RDBMS) is software that stores, manages, and retrieves data in a tabular (rows and columns) format, based on E.F. Codd’s relational model.

Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite.

Key Idea: Data is stored in tables (relations), each with a unique primary key, and relationships are established between tables using foreign keys.

2. Core RDBMS Concepts

a) Table

A collection of related data entries.
Organized into rows (records) and columns (fields/attributes).
Example:

StudentID Name Age

1 John Doe 20

2 Jane Doe 22

StudentID	Name	Age
1	John Doe	20
2	Jane Doe	22

b) Tuple / Row / Record

A single entry in a table.
Example: (1, 'John Doe', 20)

c) Attribute / Column / Field

A property or characteristic of the entity stored in a table.
Example: Name is an attribute of the Student table.

d) Primary Key

A unique identifier for each record in a table.
Cannot be NULL.
Example: StudentID in the Student table.

e) Foreign Key

A column (or set of columns) that links one table to another.
References a primary key in another table.
Example:
- Orders table may have CustomerID as a foreign key referencing CustomerID in the Customers table.

f) Candidate Key

All attributes that can uniquely identify rows in a table.
One of them is chosen as the primary key.

g) Composite Key

A primary key consisting of multiple columns.
Example: (OrderID, ProductID) as a combined primary key in an OrderDetails table.

h) Unique Key

Ensures all values in a column are unique (like primary key) but can have one NULL.

i) Index

Data structure that improves retrieval speed.
Like an index in a book, it helps find data quickly.

j) Constraints

Rules to enforce data integrity:

NOT NULL – column cannot have NULL values.
UNIQUE – all values must be unique.
PRIMARY KEY – unique + not null.
FOREIGN KEY – references primary key in another table.
CHECK – ensures a condition is met.
DEFAULT – assigns a default value when no value is specified.

k) Data Integrity

Ensuring accuracy and consistency of data:

Entity Integrity – each row must have a unique, non-null primary key.
Referential Integrity – foreign keys must match primary keys in related tables.
Domain Integrity – values must be within valid ranges/data types.

l) Relationships

RDBMS handles relations between tables:

One-to-One (1:1) – one row in table A relates to one row in table B.
One-to-Many (1:N) – one row in table A relates to many rows in table B.
Many-to-Many (M:N) – many rows in table A relate to many rows in table B (requires junction table).

m) Normalization

Process of organizing data to reduce redundancy and improve efficiency.
Forms:
1. 1NF – no repeating groups, atomic values only.
2. 2NF – 1NF + no partial dependency on composite key.
3. 3NF – 2NF + no transitive dependencies.
4. BCNF – stricter form of 3NF.

n) ACID Properties

Ensures reliable database transactions:

Atomicity – all steps in a transaction succeed or none do.
Consistency – data must be valid before and after the transaction.
Isolation – transactions don’t affect each other.
Durability – once committed, data persists even after a crash.

3. Example SQL Table Creation with Concepts

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18),
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

4. Advantages of RDBMS

Structured data storage.
Data integrity through constraints.
Supports complex queries.
Security via permissions.
Multi-user access with concurrency control.

5. Disadvantages

Can be slower with extremely large datasets compared to NoSQL.
Complex relationships can require expensive joins.
Rigid schema — changes require careful planning.

SQL Tutorial Menu