RDBMS (Relational Database Management System)

As data became more critical for organizations, simple file systems were no longer sufficient. This led to the development of Database Management Systems (DBMS). Over time, as applications grew more complex and required better structure, relationships, and data integrity, Relational Database Management Systems (RDBMS) emerged as an improved and more powerful evolution of DBMS.

Many beginners confuse DBMS and RDBMS or use the terms interchangeably. While both are used to store and manage data, they differ significantly in data structure, relationships, integrity, scalability, and real-world usage.

This chapter provides a clear, in-depth comparison between DBMS and RDBMS, explaining how they work, where they are used, their advantages, limitations, and why RDBMS dominates modern software systems.

What Is DBMS?

A Database Management System (DBMS) is software that allows users to store, retrieve, update, and manage data in a database. In a DBMS, data is usually stored as files or simple tables, and relationships between data are either weak or not enforced at all.

DBMS focuses mainly on data storage and basic data manipulation.

Examples of DBMS:

1. File-based database systems
2. XML-based storage
3. Older desktop databases

Key Characteristics of DBMS

1. Data is stored in files or simple tables
2. Limited or no support for relationships
3. Minimal data integrity enforcement
4. Typically supports single-user or limited multi-user environments
5. Security features are basic
6. Suitable for small-scale applications

What Is RDBMS?

A Relational Database Management System (RDBMS) is an advanced form of DBMS where data is stored in multiple related tables. These tables are connected using keys, and the system strictly enforces data integrity through constraints and rules.

RDBMS follows relational model principles, introduced by E.F. Codd, which define how data should be structured and related.

Examples of RDBMS:

1. MySQL
2. PostgreSQL
3. Oracle Database
4. SQL Server
5. MariaDB

Key Characteristics of RDBMS

1. Data stored in tables (relations)
2. Strong relationships using primary and foreign keys
3. Enforces data integrity and consistency
4. Supports multi-user and concurrent access
5. Uses SQL as a standard query language
6. Designed for large-scale and enterprise systems

Structural Difference Between DBMS and RDBMS

DBMS Structure

1. Data stored as files or single tables
2. No strict schema enforcement
3. Relationships managed manually by applications

RDBMS Structure

1. Data stored in multiple related tables
2. Fixed schema with defined data types
3. Relationships enforced by the database itself

Example:

DBMS-style data storage

RDBMS-style data storage

1. Employees table
2. Departments table
3. Relationship using department_id

Relationship Handling

DBMS

1. No built-in relationship enforcement
2. Data relationships are handled by application logic
3. High risk of inconsistency

RDBMS

1. Supports:
2. One-to-One
3. One-to-Many
4. Many-to-Many
5. Relationships enforced using foreign keys
6. Prevents invalid data entry

Data Integrity

DBMS

1. Does not enforce strong integrity rules
2. Duplicate and inconsistent data is common
3. No constraints like primary key or foreign key

RDBMS

1. Enforces integrity using:
2. Primary Key
3. Foreign Key
4. Unique
5. Not Null
6. Check constraints
7. Ensures accurate and reliable data

Normalization Support

DBMS

1. No built-in normalization concepts
2. Data redundancy is common

RDBMS

1. Strong support for normalization
2. Eliminates:
3. Redundant data
4. Update anomalies
5. Insertion and deletion anomalies

Normalization makes RDBMS suitable for complex systems.

Transaction Management

DBMS

1. Limited or no transaction support
2. No guarantee of atomic operations

RDBMS

1. Fully supports transactions
2. Follows ACID properties:
3. Atomicity
4. Consistency
5. Isolation
6. Durability

This is critical for systems like banking and payments.

Concurrency Control

DBMS

1. Poor handling of multiple users
2. Data corruption risk if multiple users access the same data

RDBMS

1. Advanced concurrency control mechanisms
2. Supports:
3. Locking
4. Isolation levels
5. Multi-version concurrency control (MVCC)

Allows thousands of users to work simultaneously.

Security Features

DBMS

1. Basic password protection
2. Limited access control

RDBMS

1. Advanced security mechanisms:
2. User roles
3. Privileges
4. Authentication
5. Authorization
6. Fine-grained access control at:
7. Table level
8. Column level
9. Row level

Scalability

DBMS

1. Suitable for small datasets
2. Performance degrades quickly as data grows

RDBMS

1. Designed for large datasets
2. Supports:
3. Indexing
4. Query optimization
5. Partitioning
6. Can scale vertically and, in some systems, horizontally

Query Language

DBMS

1. May not support a standard query language
2. Data access is often manual or application-specific

RDBMS

1. Uses Structured Query Language (SQL)
2. Standardized and widely supported
3. Enables complex queries with joins, subqueries, and aggregations

Performance Comparison

Real-World Use Cases

DBMS Use Cases

1. Simple desktop applications
2. Small tools with limited data
3. Single-user systems

RDBMS Use Cases

1. Banking systems
2. E-commerce platforms
3. Hospital management
4. ERP and CRM systems
5. Government databases

Advantages of DBMS

1. Simple design
2. Easy to set up
3. Low resource usage
4. Suitable for small applications

Limitations of DBMS

1. No strong data integrity
2. Poor scalability
3. Weak security
4. Not suitable for enterprise systems

Advantages of RDBMS

1. Strong data integrity
2. Structured relationships
3. ACID-compliant transactions
4. High security
5. Excellent performance for large systems

Limitations of RDBMS

1. More complex to design
2. Requires careful schema planning
3. Slight overhead for small applications

Why RDBMS Is Preferred Today

Modern applications demand:

1. Accuracy
2. Security
3. Scalability
4. Reliability
5. Multi-user access

RDBMS fulfills all these requirements, which is why it is the backbone of most production systems today.

Key Takeaways

1. DBMS is a basic data management system
2. RDBMS is an advanced, relational form of DBMS
3. RDBMS enforces relationships and integrity
4. SQL is central to RDBMS
5. Most modern applications rely on RDBMS
6. Understanding this difference is crucial for backend development

The Relational Model is the theoretical foundation of all Relational Database Management Systems (RDBMS). It defines how data is organized, stored, and related inside a database. Introduced by E. F. Codd in 1970, the relational model transformed data management by introducing a structured, mathematical, and logical way to handle data.

At the core of the relational model are tables, also known as relations. These tables store data in rows and columns, and relationships between tables are established using keys. This chapter explains the relational model in depth, explores table structure, attributes, tuples, domains, keys, and explains why this model is so powerful and widely adopted.

Understanding this chapter is critical because every RDBMS concept—normalization, joins, constraints, transactions—depends on the relational model.

What Is the Relational Model?

The relational model represents data in the form of relations (tables). Each table consists of:

1. Rows (Tuples) – individual records
2. Columns (Attributes) – properties of the data

The relational model is based on set theory and predicate logic, which allows databases to perform complex operations reliably and consistently.

Key principles of the relational model:

1. Data is stored only in tables
2. Tables are connected using keys
3. Each value is atomic (indivisible)
4. Relationships are explicitly defined

Basic Components of the Relational Model

1. Relation (Table)

A relation is a two-dimensional table with rows and columns.

Example: Students table

Rules of a relation:

1. Each table has a unique name
2. Rows are unordered
3. Columns are unordered
4. Each cell contains a single value

2. Attribute (Column)

An attribute represents a property or characteristic of an entity.

Examples:

1. student_id
2. name
3. email
4. salary

Each attribute:

1. Has a unique name within the table
2. Belongs to a specific domain
3. Stores atomic values

3. Tuple (Row)

A tuple is a single record in a table.

Example:

Each tuple:

1. Represents one entity
2. Must be unique (ideally identified by a key)
3. Contains values for all attributes

4. Domain

A domain defines the set of valid values for an attribute.

Examples:

1. Age → integers between 0 and 120
2. Email → valid email format
3. Salary → positive decimal numbers

Domains ensure data validity and consistency.

Schema and Instance

Database Schema

A schema is the logical structure of the database.

It defines:

1. Table names
2. Column names
3. Data types
4. Constraints

Example schema definition:

The schema is static and changes rarely.

Database Instance

An instance represents the actual data stored in the database at a specific moment in time.

1. Schema = structure
2. Instance = data

Instances change frequently as records are inserted, updated, or deleted.

Keys in the Relational Model

Keys are essential to uniquely identify records and establish relationships.

1. Super Key

A super key is any set of attributes that uniquely identifies a row.

Example:

1. {student_id}
2. {student_id, name}

2. Candidate Key

A candidate key is a minimal super key.

1. No unnecessary attributes
2. Multiple candidate keys can exist

Example:

1. student_id
2. email

3. Primary Key

The primary key is the candidate key chosen to uniquely identify tuples.

Rules:

1. Must be unique
2. Cannot be NULL
3. Only one per table

Example:

4. Alternate Key

Candidate keys that are not chosen as the primary key.

Example:

1. email (if student_id is primary key)

5. Foreign Key

A foreign key creates a relationship between two tables.

Example:

1. student_id in Enrollments table referencing Students

Foreign keys enforce referential integrity.

Relationships Between Tables

The relational model supports structured relationships.

1. One-to-One (1:1)

Each record in table A relates to one record in table B.

Example:

1. User ↔ UserProfile

2. One-to-Many (1:N)

One record in table A relates to many records in table B.

Example:

1. Customer → Orders

3. Many-to-Many (M:N)

Many records in table A relate to many records in table B.

Example:

1. Students ↔ Courses
2. Implemented using a junction table.

Integrity Rules in the Relational Model

1. Entity Integrity Rule

1. Primary key cannot be NULL
2. Ensures each record is uniquely identifiable

2. Referential Integrity Rule

1. Foreign key values must exist in the referenced table
2. Prevents orphan records

3. Domain Integrity Rule

1. Values must belong to their defined domain
2. Enforced using data types and constraints

Relational Algebra (Conceptual Overview)

Relational algebra defines operations on relations.

Common operations:

1. Selection (σ)
2. Projection (π)
3. Union (∪)
4. Intersection (∩)
5. Difference (−)
6. Join (⨝)

These operations form the theoretical base of SQL queries.

Why Tables Are So Powerful

Tables:

1. Are easy to understand
2. Represent real-world entities
3. Allow structured querying
4. Support complex relationships
5. Scale efficiently with indexes

This simplicity is the main reason the relational model dominates data systems.

Advantages of the Relational Model

1. Clear structure
2. Strong data integrity
3. Eliminates redundancy
4. Supports complex queries
5. Standardized query language (SQL)
6. Easy to maintain and scale

Limitations of the Relational Model

1. Fixed schema (less flexible)
2. Schema changes can be costly
3. Not ideal for unstructured data
4. Requires careful design

Despite these limitations, the relational model remains the most reliable choice for structured data.

Real-World Example

E-commerce database:

1. Users table
2. Products table
3. Orders table
4. Payments table

Each table is related using foreign keys, forming a complete relational system.

Best Practices

1. Always define a primary key
2. Use meaningful attribute names
3. Normalize tables properly
4. Avoid storing duplicate data
5. Use foreign keys to enforce relationships

Key Takeaways

1. The relational model organizes data into tables
2. Tables consist of rows and columns
3. Keys uniquely identify records and create relationships
4. Schema defines structure, instance defines data
5. Integrity rules ensure accuracy and consistency
6. This model is the foundation of all RDBMS systems

What is SQL?

SQL stands for Structured Query Language.

It is used to communicate with relational databases.

With SQL, you can:

1. Create databases and tables
2. Insert data
3. Read data
4. Update data
5. Delete data

SQL works with all major RDBMS systems like MySQL, Oracle, PostgreSQL, and SQL Server.

SQL is a declarative language, which means you tell the database what you want, and the database decides how to do it.

Types of SQL Commands

SQL commands are divided into five main types:

1. DDL – Data Definition Language
2. DML – Data Manipulation Language
3. DQL – Data Query Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language

In this chapter, we focus on DDL.

What is DDL?

Data Definition Language (DDL) is used to create and manage database structure.

DDL commands work on:

1. Databases
2. Tables
3. Columns
4. Constraints

DDL does not work on actual data.

It only defines how data is stored.

Main DDL Commands

The most important DDL commands are:

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

CREATE Command

Create a Database

This creates a new database.

Create a Table

This defines:

1. Table name
2. Column names
3. Data types
4. Primary key

SQL Data Types

Numeric

1. INT
2. BIGINT
3. DECIMAL

Text

1. CHAR
2. VARCHAR
3. TEXT

Date and Time

1. DATE
2. DATETIME
3. TIMESTAMP

Boolean

1. BOOLEAN

Choosing the right data type helps improve performance and storage.

Constraints in DDL

Constraints apply rules to table columns.

Common Constraints

1. PRIMARY KEY – Unique and not null
2. NOT NULL – Value cannot be empty
3. UNIQUE – No duplicate values
4. FOREIGN KEY – Connects tables
5. CHECK – Applies condition
6. DEFAULT – Sets default value

Example:

ALTER Command

Used to change table structure.

Add Column

Modify Column

Drop Column

DROP Command

Used to delete database objects permanently.

Drop Table

Drop Database

⚠️ Data is permanently deleted.

TRUNCATE Command

Removes all records from a table.

1. Faster than DELETE
2. Cannot be rolled back
3. Keeps table structure

RENAME Command

Used to rename a table.

DDL Important Points

1. DDL commands auto-commit
2. Changes are permanent
3. Cannot rollback in most cases
4. Used mainly for database design

Best Practices

1. Always define primary keys
2. Use meaningful names
3. Avoid frequent ALTER commands
4. Take backup before DROP
5. Test schema changes first

Real-Life Example

In a school system:

1. CREATE tables for students and teachers
2. ALTER tables when new fields are needed
3. TRUNCATE old logs
4. DROP unused tables

Key Summary

1. SQL is used to manage databases
2. DDL defines database structure
3. CREATE, ALTER, DROP are core commands
4. Constraints maintain data integrity
5. DDL changes are permanent

Introduction

To understand how an RDBMS works in real-world applications, it is important to understand its architecture and core components. Architecture explains how data flows, how queries are processed, and how different parts of the system work together to store, retrieve, and manage data efficiently.

An RDBMS is not just a set of tables. Internally, it consists of multiple layers and components that handle user queries, manage memory, control transactions, maintain security, and ensure data consistency. This chapter explains the internal structure of an RDBMS in a simple and clear way.

What Is RDBMS Architecture?

RDBMS architecture defines the design and working structure of a relational database system. It explains:

1. How users connect to the database
2. How SQL queries are processed
3. How data is stored and retrieved
4. How security and transactions are handled

Most RDBMS systems (MySQL, PostgreSQL, Oracle, SQL Server) follow a layered architecture to keep the system organized, scalable, and reliable.

Levels of RDBMS Architecture

RDBMS architecture is commonly divided into three levels:

1. External Level (View Level)

2. Conceptual Level (Logical Level)

3. Internal Level (Physical Level)

This separation helps in data abstraction, meaning users do not need to know how data is physically stored.

1. External Level (View Level)

Purpose

The external level defines how users see the data.

Key Points

1. Different users can have different views of the same database
2. Users see only the data they are authorized to access
3. Helps improve security and simplicity

Example

A company database may have:

1. HR view: employee name, salary, department
2. Manager view: employee name, performance, attendance
3. Accountant view: employee name, salary, tax details

All data comes from the same database, but each user sees a different view.

Benefits

1. Data security
2. Simplified access
3. Customized views for different roles

2. Conceptual Level (Logical Level)

Purpose

The conceptual level defines what data is stored and how data is related.

Key Points

1. Describes tables, columns, data types, and relationships
2. Independent of physical storage
3. Common for all users

Example

At this level, the database defines:

1. Tables: Employees, Departments
2. Columns: employee_id, name, department_id
3. Relationships: Employees belong to Departments

This level focuses on database design rather than storage details.

3. Internal Level (Physical Level)

Purpose

The internal level defines how data is physically stored on disk.

Key Points

1. Handles indexes, files, memory allocation
2. Optimizes storage and performance
3. Hidden from users

Example

1. Data stored in binary format
2. Indexes created using B-Trees or Hashing
3. Data split across multiple files

Users never interact directly with this level.

Core Components of an RDBMS

An RDBMS consists of several important components that work together.

1. Database Engine

What Is It?

The database engine is the core component of an RDBMS.

Responsibilities

1. Stores and retrieves data
2. Manages memory and disk storage
3. Executes SQL commands

Types of Engines

Examples:

1. MySQL: InnoDB, MyISAM
2. SQL Server: Storage Engine
3. Oracle: Database Instance

Why It Matters

Different engines offer different features like:

1. Transactions
2. Indexing
3. Foreign key support

2. Query Processor

What Is It?

The query processor converts SQL queries into executable operations.

Steps Involved

1. Query Parsing
2. Checks SQL syntax
3. Query Optimization
4. Finds the most efficient execution plan
5. Query Execution
6. Fetches data from storage engine

Example

SQL Query:

The query processor decides:

1. Which index to use
2. How many rows to scan
3. Best execution strategy

3. Storage Manager

Purpose

The storage manager controls how data is stored, updated, and retrieved.

Responsibilities

1. File management
2. Buffer management
3. Index handling
4. Disk access

Why It Is Important

Efficient storage management:

1. Improves performance
2. Reduces disk I/O
3. Supports large databases

4. Transaction Manager

What Is a Transaction?

A transaction is a set of operations executed as a single unit.

Responsibilities

1. Ensures ACID properties
2. Manages commits and rollbacks
3. Handles concurrent access

ACID Properties

1. Atomicity: All or nothing
2. Consistency: Valid state
3. Isolation: Transactions don’t interfere
4. Durability: Data is saved permanently

Example

Bank transfer:

1. Debit from Account A
2. Credit to Account B
3. Both must succeed or fail together.

5. Concurrency Control Manager

Purpose

Manages multiple users accessing data at the same time.

Problems It Solves

1. Dirty reads
2. Lost updates
3. Inconsistent data

Techniques Used

1. Locking (shared and exclusive locks)
2. Timestamp ordering
3. Multiversion concurrency control (MVCC)

6. Security Manager

Purpose

Controls who can access what data.

Security Features

1. Authentication (login credentials)
2. Authorization (permissions)
3. Roles and privileges

Example

1. Admin: full access
2. User: read-only access
3. Manager: read and update access

Security is critical for protecting sensitive data.

7. Recovery Manager

Purpose

Ensures data recovery in case of system failure or crash.

Techniques Used

1. Logging
2. Checkpoints
3. Backup and restore

Example

If a power failure occurs:

1. Committed transactions are restored
2. Incomplete transactions are rolled back

Client-Server Architecture in RDBMS

Most modern RDBMS follow client-server architecture.

Client

1. Sends SQL queries
2. Examples: Web app, mobile app, admin tools

Server

1. Processes queries
2. Stores and manages data

Benefits

1. Scalability
2. Centralized data
3. Better security

How All Components Work Together

1. User sends SQL query
2. Query processor parses and optimizes it
3. Transaction manager starts transaction
4. Storage manager fetches data
5. Security manager verifies permissions
6. Results are returned to the user
7. Transaction is committed or rolled back

This coordination ensures data accuracy, speed, and safety.

Advantages of RDBMS Architecture

1. Clear separation of responsibilities
2. Better performance optimization
3. Strong data security
4. Reliable transaction handling
5. Easy scalability

Summary

In this chapter, we learned:

1. RDBMS follows a layered architecture
2. Three levels: External, Conceptual, Internal
3. Core components like database engine, query processor, and transaction manager
4. How RDBMS handles security, concurrency, and recovery
5. Why architecture is important for performance and reliability

Understanding RDBMS architecture helps developers design efficient databases and write better queries.

Introduction

In an RDBMS, tables are the foundation of everything. All data is stored in tables, and every operation—insert, update, delete, or retrieve—works on tables. To understand RDBMS properly, you must clearly understand tables, rows, columns, and data types, because these concepts define how data is structured and stored.

This chapter explains these core concepts in a simple and practical way, suitable for beginners as well as developers who want a strong foundation.

What Is a Table in RDBMS?

A table is a structured collection of data stored in rows and columns. It represents a real-world entity such as students, employees, orders, or products.

Key Characteristics of a Table

1. Each table has a unique name
2. Data is organized in rows and columns
3. Each column has a defined data type
4. Each row represents one record

Example

A table named Employees:

This table stores employee-related information.

Rows in RDBMS

What Is a Row?

A row represents a single record in a table. Each row contains values for all columns.

Key Points About Rows

1. Also called a tuple or record
2. Each row is unique
3. Number of rows can increase dynamically

Example

In the Employees table:

This single row represents one employee.

Columns in RDBMS

What Is a Column?

A column represents a specific attribute of an entity. It defines the type of data stored.

Key Points About Columns

1. Each column has a name
2. Each column has a data type
3. All values in a column must follow the same data type

Example

Columns in Employees table:

1. employee_id
2. name
3. department
4. salary

Each column stores one type of information.

Relationship Between Tables, Rows, and Columns

1. Table → Collection of rows and columns
2. Row → One complete record
3. Column → One attribute of the record

Think of a table like an Excel sheet:

1. Sheet = Table
2. Row = One entry
3. Column = One field

What Are Data Types?

A data type defines the kind of data that can be stored in a column. It helps the database:

1. Store data efficiently
2. Validate input values
3. Perform operations correctly

Choosing the correct data type is very important for:

1. Performance
2. Accuracy
3. Storage optimization

Categories of Data Types in RDBMS

Most RDBMS systems support the following categories:

1. Numeric Data Types
2. Character/String Data Types
3. Date and Time Data Types
4. Boolean Data Types
5. Binary Data Types

1. Numeric Data Types

Numeric data types are used to store numbers.

Integer Types

Used for whole numbers.

Common examples:

1. INT
2. SMALLINT
3. BIGINT

Example

Used for IDs, counts, and numeric values without decimals.

Decimal and Floating-Point Types

Used for numbers with decimals.

Common examples:

1. DECIMAL
2. FLOAT
3. DOUBLE

Example

Used for prices, salary, and financial data.

2. Character and String Data Types

Used to store textual data.

CHAR

1. Fixed-length string
2. Faster access
3. Wastes space if data is shorter

Example:

VARCHAR

1. Variable-length string
2. Saves space
3. Most commonly used

Example:

TEXT

1. Used for long text
2. Blogs, descriptions, comments

Example:

3. Date and Time Data Types

Used to store date and time values.

Common Types

1. DATE
2. TIME
3. DATETIME
4. TIMESTAMP

Example

Used to track record creation and updates.

4. Boolean Data Types

Used to store true or false values.

Common Representations

1. BOOLEAN
2. TINYINT (0 or 1)

Example

Used for status flags.

5. Binary Data Types

Used to store binary data such as images or files.

Examples

1. BLOB
2. VARBINARY

Example

Usually avoided for large files; instead, file paths are stored.

NULL Values in RDBMS

What Is NULL?

NULL means no value or unknown value. It is not the same as zero or empty string.

Example

1. Salary = NULL → Salary not known
2. Salary = 0 → Salary is zero

Important Points

1. NULL represents missing data
2. Special handling in queries
3. Comparisons with NULL require IS NULL

Table Creation Example

This statement defines:

1. Table structure
2. Column names
3. Data types

Importance of Proper Table Design

Good table design:

1. Improves performance
2. Reduces data redundancy
3. Makes data easier to manage

Bad table design:

1. Causes data duplication
2. Slows down queries
3. Leads to inconsistent data

Naming Conventions

Best practices:

1. Use meaningful table names
2. Use lowercase or snake_case
3. Avoid special characters
4. Be consistent

Example:

1. user_profiles
2. order_items

How Data Types Affect Performance

1. Smaller data types use less memory
2. Correct types improve indexing
3. Wrong types slow queries

Example:

1. Use INT instead of VARCHAR for IDs
2. Use DATE instead of VARCHAR for dates

Real-World Example

E-commerce database:

1. Users table → user_id, name, email
2. Orders table → order_id, user_id, total_amount
3. Products table → product_id, price, stock

Each table uses:

1. Rows for records
2. Columns for attributes
3. Proper data types for accuracy

Summary

In this chapter, we learned:

1. Tables store data in rows and columns
2. Rows represent records
3. Columns represent attributes
4. Data types define what kind of data can be stored
5. Choosing the right data type is critical
6. NULL represents missing or unknown values

These concepts form the core foundation of RDBMS. Without understanding them, it is impossible to design efficient databases.

Introduction

Keys are one of the most important concepts in RDBMS. Without keys, a database would become messy, slow, and unreliable. Keys help the database identify records uniquely, maintain relationships between tables, and ensure data integrity.

In real-world applications like banking systems, e-commerce platforms, hospital management systems, or school databases, keys play a critical role in keeping data accurate and connected.

This chapter explains all types of keys in RDBMS in a simple, beginner-friendly way with clear examples.

What Is a Key in RDBMS?

A key is an attribute (column) or a set of attributes that is used to:

1. Identify a record uniquely
2. Prevent duplicate data
3. Create relationships between tables
4. Maintain data consistency

In short, keys are used to control and organize data.

Why Keys Are Important

Keys are important because they:

1. Avoid duplicate records
2. Improve query performance
3. Maintain relationships between tables
4. Enforce data integrity rules
5. Help in indexing and faster searching

Without keys, managing large databases would be extremely difficult.

Types of Keys in RDBMS

RDBMS supports several types of keys. The most commonly used are:

1. Primary Key
2. Candidate Key
3. Super Key
4. Alternate Key
5. Foreign Key
6. Composite Key
7. Unique Key

Let’s understand each one in detail.

1. Primary Key

What Is a Primary Key?

A Primary Key is a column (or combination of columns) that uniquely identifies each record in a table.

Characteristics of Primary Key

1. Must be unique
2. Cannot be NULL
3. Only one primary key per table
4. Ensures entity integrity

Example

Table: Students

Here, student_id is the primary key.

SQL Example

Why Primary Key Is Important

1. Identifies each record uniquely
2. Prevents duplicate entries
3. Makes table reliable

2. Candidate Key

What Is a Candidate Key?

A Candidate Key is a column (or set of columns) that can qualify as a primary key.

In a table, there can be multiple candidate keys, but only one is chosen as the primary key.

Example

Table: Users

Candidate keys:

1. user_id
2. email
3. username

Any of these can uniquely identify a record.

3. Super Key

What Is a Super Key?

A Super Key is a set of one or more columns that can uniquely identify a record.

It may contain extra attributes, so it is not always minimal.

Example

Using the same Users table:

Super keys:

1. user_id
2. email
3. user_id + email
4. user_id + username

All can uniquely identify records, but some are unnecessary combinations.

4. Alternate Key

What Is an Alternate Key?

An Alternate Key is a candidate key that is not selected as the primary key.

Example

If:

1. Primary key = user_id
2. Other candidate keys = email, username

Then:

1. email and username become alternate keys

Use Case

Alternate keys are useful when:

1. Searching by email or username
2. Enforcing uniqueness besides primary key

5. Foreign Key

What Is a Foreign Key?

A Foreign Key is a column in one table that refers to the primary key of another table.

It is used to create relationships between tables.

Example

Table: Orders

Here:

1. user_id is a foreign key referencing Users(user_id)

SQL Example

Why Foreign Key Is Important

1. Maintains referential integrity
2. Prevents invalid data
3. Connects related tables

6. Composite Key

What Is a Composite Key?

A Composite Key is a primary key made up of two or more columns.

It is used when a single column is not enough to uniquely identify a record.

Example

Table: Enrollments

Here:

1. student_id alone is not unique
2. course_id alone is not unique
3. student_id + course_id together are unique

SQL Example

7. Unique Key

What Is a Unique Key?

A Unique Key ensures that all values in a column are unique, but unlike primary key, it can allow NULL values (depends on RDBMS).

Example

Used when:

1. Email must be unique
2. But not primary identifier

Difference Between Primary Key and Unique Key

Keys and Data Integrity

Keys help maintain:

1. Entity Integrity → Primary key ensures unique records
2. Referential Integrity → Foreign key ensures valid relationships

Without keys:

1. Duplicate records occur
2. Orphan records appear
3. Data becomes unreliable

Real-World Example

E-commerce Database:

1. Users → user_id (Primary Key)
2. Products → product_id (Primary Key)
3. Orders → order_id (Primary Key)
4. Orders.user_id → Foreign Key
5. Users.email → Alternate / Unique Key

This structure keeps data connected and clean.

Common Mistakes with Keys

1. Not using primary keys
2. Using VARCHAR as primary key unnecessarily
3. Missing foreign key constraints
4. Overusing composite keys
5. Allowing NULL in important fields

Best Practices for Using Keys

1. Always define a primary key
2. Use integer-based primary keys when possible
3. Apply foreign keys to maintain relationships
4. Use unique keys for email, username, etc.
5. Keep keys simple and meaningful

Summary

In this chapter, you learned:

1. What keys are and why they matter
2. Different types of keys in RDBMS
3. How primary and foreign keys work
4. How keys maintain data integrity
5. Real-world use cases and best practices

Keys are the backbone of relational databases. Without them, RDBMS would lose its structure and reliability.

Introduction

One of the biggest strengths of an RDBMS is its ability to store data in multiple tables and connect them logically. These connections are called relationships. Relationships allow us to break large data into smaller tables while still keeping everything connected.

In real-world systems like schools, hospitals, banks, and e-commerce platforms, data is never stored in a single table. Instead, it is divided into multiple tables and linked using relationships. Understanding relationships is essential for designing efficient, scalable, and clean databases.

This chapter explains types of relationships in RDBMS with simple explanations and practical examples.

What Is a Relationship in RDBMS?

A relationship defines how two or more tables are logically connected using keys.

Relationships are created using:

1. Primary Key
2. Foreign Key

Example

A student belongs to a class.

1. Student table
2. Class table

Both tables are connected using a key.

Why Relationships Are Important

Relationships help to:

1. Reduce data duplication
2. Maintain data integrity
3. Improve database structure
4. Make data easier to update and manage
5. Support complex queries

Without relationships, databases become large, slow, and difficult to maintain.

Types of Relationships in RDBMS

There are three main types of relationships:

1. One-to-One (1:1)
2. One-to-Many (1:N)
3. Many-to-Many (M:N)

Let’s understand each in detail.

1. One-to-One Relationship

What Is One-to-One Relationship?

In a one-to-one relationship, one record in Table A is associated with only one record in Table B, and vice versa.

Example

1. One person has one passport
2. One employee has one employee profile

Table Example

Employees Table

Employee_Details Table

Here:

1. employee_id is primary key in Employees
2. employee_id is also primary + foreign key in Employee_Details

SQL Example

When to Use One-to-One Relationship

1. When optional or sensitive data should be separated
2. To improve security
3. To split large tables

2. One-to-Many Relationship

What Is One-to-Many Relationship?

In a one-to-many relationship, one record in Table A can be related to multiple records in Table B, but each record in Table B relates to only one record in Table A.

This is the most common relationship in RDBMS.

Example

1. One customer can place many orders
2. One department has many employees
3. One teacher teaches many students

Table Example

Departments Table

Employees Table

Here:

1. department_id is primary key in Departments
2. department_id is foreign key in Employees

SQL Example

Advantages of One-to-Many

1. Simple design
2. Reduces data duplication
3. Easy to query and maintain

3. Many-to-Many Relationship

What Is Many-to-Many Relationship?

In a many-to-many relationship, multiple records in Table A relate to multiple records in Table B.

RDBMS cannot implement many-to-many directly, so a junction table (bridge table) is used.

Example

1. Students enroll in many courses
2. Courses have many students

Table Structure

Students Table

Courses Table

Enrollments Table (Junction Table)

SQL Example

Why Junction Table Is Needed

1. Breaks many-to-many into two one-to-many relationships
2. Maintains data integrity
3. Allows additional fields (grade, enrollment_date)

Relationship Cardinality

Cardinality defines how many records are associated.

Types:

1. 1:1
2. 1:N
3. M:N

Correct cardinality ensures proper database design.

Referential Integrity

Referential integrity ensures that:

1. Foreign key value must exist in parent table
2. No orphan records are allowed

Example:

1. Cannot insert order with invalid customer_id

Cascading Actions

Cascading controls what happens when:

1. Parent record is deleted
2. Parent record is updated

Types

1. ON DELETE CASCADE
2. ON UPDATE CASCADE
3. SET NULL
4. RESTRICT

Example

Deletes child records automatically.

Common Relationship Design Mistakes

1. Not using foreign keys
2. Creating unnecessary many-to-many relationships
3. Storing repeated data
4. Ignoring cascading rules
5. Poor naming conventions

Best Practices for Relationships

1. Always define foreign keys
2. Use junction tables properly
3. Keep relationships simple
4. Normalize data
5. Use indexes on foreign keys

Real-World Database Example

E-commerce System:

1. Users → Orders (One-to-Many)
2. Orders → Products (Many-to-Many)
3. Products → Categories (One-to-Many)

This structure supports scalability and performance.

Summary

In this chapter, you learned:

1. What relationships are in RDBMS
2. Types of relationships
3. How to implement each relationship
4. Role of foreign keys
5. Real-world use cases

Understanding relationships is critical for database design. Most real-world systems depend heavily on well-defined relationships.

Introduction

Structured Query Language (SQL) is the standard language used to interact with an RDBMS. No matter which database system you use—MySQL, PostgreSQL, Oracle, or SQL Server—SQL is the primary way to create, read, update, and manage data.

SQL is divided into different categories based on the type of operations it performs. Understanding these categories is essential for working efficiently with relational databases.

This chapter explains SQL basics, its main components, and how each category works using simple examples.

What Is SQL?

SQL (Structured Query Language) is a standard language used to:

1. Create databases and tables
2. Insert, update, and delete data
3. Retrieve data from tables
4. Control user access
5. Manage transactions

SQL is declarative, meaning you specify what you want, not how to get it.

Categories of SQL Commands

SQL commands are mainly divided into four categories:

1. DDL – Data Definition Language
2. DML – Data Manipulation Language
3. DCL – Data Control Language
4. TCL – Transaction Control Language

Each category serves a specific purpose.

1. Data Definition Language (DDL)

What Is DDL?

DDL commands are used to define and modify the structure of database objects such as tables, schemas, and indexes.

DDL commands affect database structure, not the data itself.

Common DDL Commands

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

CREATE Command

Used to create database objects.

Example: Create Table

Creates a new table with defined columns and data types.

ALTER Command

Used to modify an existing table structure.

Examples

Add a column:

Modify column:

DROP Command

Used to permanently delete database objects.

⚠️ This removes both structure and data.

TRUNCATE Command

Used to remove all records from a table but keep its structure.

1. Faster than DELETE
2. Cannot be rolled back in most databases

RENAME Command

Used to rename a table.

2. Data Manipulation Language (DML)

What Is DML?

DML commands are used to manipulate data stored in tables.

They affect records but not table structure.

Common DML Commands

1. INSERT
2. SELECT
3. UPDATE
4. DELETE

INSERT Command

Used to add new records.

SELECT Command

Used to retrieve data from tables.

You can filter data using WHERE clause:

UPDATE Command

Used to modify existing records.

DELETE Command

Used to remove records.

Difference Between DELETE and TRUNCATE

3. Data Control Language (DCL)

What Is DCL?

DCL commands are used to control access and permissions in the database.

They ensure data security.

Common DCL Commands

1. GRANT
2. REVOKE

GRANT Command

Used to give permissions.

REVOKE Command

Used to remove permissions.

4. Transaction Control Language (TCL)

What Is TCL?

TCL commands are used to manage transactions.

A transaction is a sequence of SQL operations executed as a single unit.

Common TCL Commands

1. COMMIT
2. ROLLBACK
3. SAVEPOINT

COMMIT

Saves all changes permanently.

ROLLBACK

Undoes changes since last commit.

SAVEPOINT

Creates a point within a transaction.

Allows partial rollback.

Transactions and ACID Properties

SQL transactions follow ACID principles:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

These ensure reliable data operations.

SQL in Real-World Applications

Examples:

1. Creating tables during app setup
2. Inserting user data
3. Updating order status
4. Controlling admin access
5. Managing financial transactions

SQL is used in almost every software system.

Common SQL Mistakes

1. Forgetting WHERE clause in UPDATE/DELETE
2. Not committing transactions
3. Using DROP instead of DELETE
4. Poor permission management

Best Practices for SQL Usage

1. Always test queries before running on production
2. Use transactions for critical operations
3. Grant minimum required permissions
4. Use meaningful table and column names
5. Backup before running DDL commands

Summary

In this chapter, you learned:

1. What SQL is and why it is important
2. Categories of SQL commands
3. DDL, DML, DCL, and TCL commands
4. Practical examples for each
5. Real-world use cases and best practices

SQL is the language of RDBMS. Mastering SQL basics is essential before moving to advanced database concepts.

Introduction

In an RDBMS, just storing data is not enough. The database must also ensure that the data is correct, meaningful, and consistent. This is where constraints come into play.

Constraints are rules applied on table columns to control the type of data that can be stored. They help prevent invalid data entry and protect the integrity of the database. Without constraints, a database can easily contain duplicate, missing, or incorrect data.

This chapter explains constraints in RDBMS, their types, usage, and real-world importance in a simple and easy-to-understand manner.

What Are Constraints in RDBMS?

A constraint is a rule that enforces restrictions on the values allowed in a column or table.

Constraints ensure:

1. Data accuracy
2. Data consistency
3. Data integrity
4. Valid relationships between tables

Constraints are applied at the time of table creation or later using ALTER command.

Why Constraints Are Important

Constraints help to:

1. Prevent duplicate records
2. Avoid NULL values where not allowed
3. Maintain relationships between tables
4. Enforce business rules
5. Protect database from invalid input

Example:

1. Email should be unique
2. Salary should not be negative
3. Order must belong to an existing customer

All these rules are enforced using constraints.

Types of Constraints in RDBMS

Common constraints used in RDBMS are:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT

Let’s understand each one in detail.

1. NOT NULL Constraint

What Is NOT NULL?

The NOT NULL constraint ensures that a column cannot store NULL values.

If NOT NULL is applied, every row must have a value for that column.

Example

Here:

1. name cannot be NULL
2. Inserting a row without name will fail

When to Use NOT NULL

1. Mandatory fields (name, email, phone)
2. Primary key columns
3. Important business fields

2. UNIQUE Constraint

What Is UNIQUE?

The UNIQUE constraint ensures that all values in a column are distinct.

It prevents duplicate values.

Example

Here:

1. No two users can have the same email

Key Points

1. UNIQUE allows NULL values (usually only one)
2. Multiple UNIQUE constraints can exist in one table
3. Used for email, username, phone number

3. PRIMARY KEY Constraint

What Is PRIMARY KEY?

The PRIMARY KEY uniquely identifies each record in a table.

It is a combination of:

1. NOT NULL
2. UNIQUE

Example

Characteristics

1. Only one primary key per table
2. Cannot be NULL
3. Ensures entity integrity

Composite Primary Key

Used when one column is not enough.

4. FOREIGN KEY Constraint

What Is FOREIGN KEY?

A FOREIGN KEY establishes a relationship between two tables.

It ensures that:

1. The value exists in the referenced table

Example

Here:

1. user_id in Orders must exist in Users table

Why FOREIGN KEY Is Important

1. Maintains referential integrity
2. Prevents orphan records
3. Keeps data connected

Cascading Options

These automatically update or delete child records.

5. CHECK Constraint

What Is CHECK?

The CHECK constraint ensures that values meet a specific condition.

Example

Here:

1. Salary cannot be zero or negative

More Examples

Use Cases

1. Age limits
2. Salary ranges
3. Status validation
4. Rating limits

6. DEFAULT Constraint

What Is DEFAULT?

The DEFAULT constraint assigns a default value if no value is provided.

Example

If status is not provided, it becomes active.

Benefits

1. Reduces NULL values
2. Simplifies inserts
3. Enforces consistent default behavior

Adding Constraints Using ALTER

Constraints can also be added after table creation.

Example

Removing Constraints

(Syntax may vary by database)

Constraint Naming

Best practice:

1. Give meaningful names to constraints

Example:

Helps in debugging and maintenance.

Real-World Examples of Constraints

Banking System

1. Account number → UNIQUE
2. Balance → CHECK (balance >= 0)

E-Commerce

1. Email → UNIQUE
2. Order → FOREIGN KEY
3. Status → DEFAULT ‘pending’

School Database

1. Roll number → PRIMARY KEY
2. Age → CHECK (age >= 5)

Common Mistakes with Constraints

1. Not using constraints at all
2. Relying only on application validation
3. Forgetting foreign keys
4. Allowing NULL in important fields
5. Using wrong constraint type

Constraints vs Validation in Application

1. Constraints work at database level
2. Application validation works at UI level
3. Best practice: Use both

Database constraints protect data even if application fails.

Performance Impact of Constraints

1. Slight overhead during insert/update
2. Improves long-term data quality
3. Prevents costly data cleanup later

Overall, benefits outweigh performance cost.

Best Practices for Using Constraints

1. Always define primary keys
2. Use foreign keys for relationships
3. Apply NOT NULL where required
4. Use CHECK for business rules
5. Avoid unnecessary constraints

Summary

In this chapter, you learned:

1. What constraints are in RDBMS
2. Different types of constraints
3. How constraints enforce data integrity
4. SQL examples for each constraint
5. Real-world use cases and best practices

Constraints act as guardrails for your database. They ensure that only valid, meaningful, and consistent data is stored.