Introduction to Relational Database Management Systems (RDBMS) – Complete Detailed Notes

Course: MySQL Training

Topic: Introduction to Relational Database Management Systems (RDBMS)

Course Link: JustAcademy MySQL Training Course

Demo Link: Register for MySQL Course Demo

1. Introduction to RDBMS

RDBMS stands for Relational Database Management System. It is a type of database management software that stores, organizes, retrieves, and manages data using tables consisting of rows and columns. It also allows relationships to be established between tables using keys and constraints.

An RDBMS helps applications manage structured information efficiently while maintaining data accuracy, consistency, security, and reliability. It uses Structured Query Language (SQL) to create database structures, insert records, retrieve information, update data, and perform other database operations.

For example, a college management system can use an RDBMS to store student details, course information, teacher records, attendance, and examination results in separate but related tables.

Example of an RDBMS

MySQL is a popular RDBMS used in website development, business applications, e-commerce systems, and many other software projects.

Other examples include PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite.

Why Is RDBMS Important?

• Organizes large amounts of structured information.
• Establishes relationships between related data.
• Reduces unnecessary data duplication through appropriate database design.
• Maintains data integrity through keys and constraints.
• Allows users to retrieve information using SQL queries.
• Supports concurrent access by multiple users.
• Provides transaction management and access control features.
• Helps applications store and process information reliably.

2. What Is a Database?

A database is an organized collection of related information stored electronically so that it can be accessed, managed, updated, and retrieved whenever required.

In an RDBMS, information is commonly organized into tables. Each table stores a particular type of information, and relationships connect data that belongs together.

Examples of Databases

• Student database: Stores student names, identification numbers, courses, and academic records.
• Banking database: Stores customer accounts, transactions, and account balances.
• Hospital database: Stores patient information, appointments, and billing records.
• E-commerce database: Stores products, customers, orders, and inventory details.
• Library database: Stores book details, authors, members, and borrowing records.

Database Example

A college database may contain the following tables:

• Students – Contains student information.
• Courses – Contains available courses.
• Teachers – Contains teacher information.
• Enrollments – Connects students with the courses they have selected.
• Examinations – Contains examination details and results.

Using separate related tables helps organize information without repeatedly storing all details in one large table.

3. What Is a DBMS?

A Database Management System (DBMS) is software used to create, store, retrieve, modify, and manage databases. It provides an interface between users or applications and the stored information.

A DBMS can use different data models. An RDBMS is a specific type of DBMS that organizes related data using the relational model.

Functions of a DBMS

1. Data storage: Stores information in an organized structure.
2. Data retrieval: Retrieves information based on user requirements.
3. Data modification: Supports inserting, updating, and deleting records.
4. Security management: Controls access through user accounts and permissions.
5. Integrity management: Helps ensure that stored data follows defined rules.
6. Transaction management: Coordinates related database operations.
7. Backup and recovery: Helps protect and restore data.
8. Concurrency management: Coordinates access when multiple users work with data simultaneously.

4. Understanding the Relational Model

The relational model organizes information into relations, which are commonly represented as tables in relational database systems. Each table represents a particular type of entity or relationship, such as a student, a course, or an enrollment.

The relational model was introduced by computer scientist Edgar F. Codd in 1970. It provides a systematic approach to organizing data and defining relationships between different sets of information.

Main Elements of the Relational Model

Note: In everyday database development, the terms table, row, and column are commonly used instead of relation, tuple, and attribute.

5. Structure of an RDBMS Table

A table is the basic structure used to store related data in an RDBMS. It consists of rows and columns.

Example: Students Table

Rows

A row represents one complete record in a table. For example, the row containing student ID 101 represents Rahul Sharma and his associated details.

Columns

A column represents a particular attribute of the stored information. For example, student_name stores student names, while age stores age values.

Cells

A cell is an individual value at the intersection of a row and a column. For example, the cell in the first student's course column contains MySQL.

Schema

A schema describes the structure of database objects, including tables, columns, data types, constraints, and relationships.

6. Important Features of RDBMS

6.1 Table-Based Data Storage

An RDBMS organizes structured data into tables containing rows and columns. Each table has a defined structure that describes the information it stores.

6.2 Relationships Between Tables

Related tables can be connected using primary keys and foreign keys. This makes it possible to retrieve information from multiple tables without repeatedly storing all the same details.

6.3 SQL Support

RDBMS software commonly uses SQL to create tables, insert records, retrieve information, update data, and manage permissions.

6.4 Data Integrity

Primary keys, foreign keys, unique constraints, data types, and other rules help maintain the validity and consistency of stored information.

6.5 Reduced Data Redundancy

Good relational database design separates information into appropriate tables to minimize unnecessary duplication. Normalization is one technique used to achieve this goal.

6.6 Transaction Management

RDBMS platforms can support transactions that group multiple operations into a single logical unit of work. Transaction capabilities depend on the database system and storage engine.

6.7 Security and Access Control

Database administrators can configure user accounts and privileges to control who can read or modify specific database objects.

6.8 Multi-User Access

RDBMS software can coordinate multiple users and applications accessing the same database, using concurrency-control mechanisms to help prevent conflicting operations.

6.9 Backup and Recovery

Database systems provide tools and procedures for backing up information and recovering it after accidental deletion, hardware failure, or other incidents.

6.10 Query Optimization

RDBMS query optimizers evaluate possible execution strategies to find an efficient way to retrieve or modify the required data.

7. Types of Keys in RDBMS

Keys are important because they help identify records, prevent unwanted duplication, and establish relationships between tables.

7.1 Primary Key

A primary key uniquely identifies every row in a table. Its values must be unique and cannot be NULL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course VARCHAR(50)
);

Here, student_id uniquely identifies each student record.

7.2 Foreign Key

A foreign key establishes a relationship between tables by referencing a suitable key in another table or the same table.

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

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course_id INT,
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

Here, course_id in the students table references the courses table.

7.3 Candidate Key

A candidate key is a minimal attribute or combination of attributes that can uniquely identify each record. A table may have multiple candidate keys, and one is selected as the primary key.

7.4 Composite Key

A composite key consists of two or more columns used together to uniquely identify a record.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

In this example, the combination of student_id and course_id uniquely identifies each student-course enrollment.

7.5 Unique Key

A UNIQUE constraint prevents duplicate values in the constrained column or combination of columns. Its handling of NULL values depends on the database system and constraint definition.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(150) UNIQUE
);

This example prevents duplicate non-NULL email values in the users table.

Key Comparison

8. Types of Relationships in RDBMS

Relationships describe how records in one table are associated with records in another table.

8.1 One-to-One Relationship

In a one-to-one relationship, one record in a table is associated with at most one record in another table, and vice versa, according to the defined constraints.

Example: A person and a separately stored identity profile, where each profile belongs to one person.

8.2 One-to-Many Relationship

In a one-to-many relationship, one record in the first table can be associated with multiple records in the second table.

Example: One department can have many employees, while each employee belongs to one department.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

8.3 Many-to-Many Relationship

In a many-to-many relationship, multiple records in one table can be associated with multiple records in another table. A junction table is commonly used to represent this relationship.

Example: A student can enroll in several courses, and each course can have several students.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

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

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

Relationship Comparison

9. What Is SQL in an RDBMS?

SQL stands for Structured Query Language. It is the standard language commonly used to define, query, and manipulate data in relational database systems.

SQL allows developers and database administrators to communicate with an RDBMS using statements that specify the required operation.

Common SQL Command Categories

Example SQL Commands

-- Create a database
CREATE DATABASE college;

-- Select the database
USE college;

-- Create a table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    age INT
);

-- Insert a record
INSERT INTO students
VALUES (1, 'Rahul Sharma', 21);

-- Retrieve all records
SELECT * FROM students;

-- Update a record
UPDATE students
SET age = 22
WHERE student_id = 1;

-- Delete a selected record
DELETE FROM students
WHERE student_id = 1;

Important: An UPDATE or DELETE statement without a suitable WHERE condition can affect all rows in the target table.

10. ACID Properties in RDBMS

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe important guarantees provided by transactional database systems when the relevant transaction and storage features are enabled.

10.1 Atomicity

Atomicity means that a transaction is treated as one logical unit. Either its operations complete successfully, or the transaction is rolled back so that partial changes are not retained.

Example: During a bank transfer, money should not be deducted from one account without the corresponding credit to the other account when both operations belong to the same transaction.

10.2 Consistency

Consistency means that a successfully completed transaction preserves the database rules and integrity constraints.

Example: A transaction should not create an enrollment that violates a required foreign-key relationship.

10.3 Isolation

Isolation controls how concurrent transactions interact with each other. Depending on the configured isolation level, it helps prevent one transaction from seeing or being affected by inappropriate intermediate changes made by another.

Example: Multiple customers purchasing products at the same time should not cause uncontrolled changes to inventory records.

10.4 Durability

Durability means that once a transaction is successfully committed, its changes are preserved against supported failures according to the database system's durability configuration.

Example: After a successful order is committed, its order information should remain stored even if the application later disconnects.

ACID Summary

11. Data Integrity in RDBMS

Data integrity refers to the accuracy, validity, and consistency of information stored in a database. RDBMS platforms provide several mechanisms for enforcing data integrity.

Types of Data Integrity

• Entity integrity: Ensures that each record can be uniquely identified, commonly through a primary key.
• Referential integrity: Ensures that foreign-key references follow the rules defined between related tables.
• Domain integrity: Restricts values to appropriate data types, ranges, and permitted values.
• User-defined integrity: Enforces additional business rules required by an application.

Example of Integrity Constraints

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price >= 0),
    sku VARCHAR(50) UNIQUE
);

In this example, the primary key identifies each product, NOT NULL requires a product name, CHECK restricts negative prices, and UNIQUE prevents duplicate non-NULL SKU values. The exact behavior of constraints can depend on the database product and version.

12. Database Normalization

Normalization is the process of organizing relational tables to reduce unnecessary duplication and prevent data anomalies. It involves dividing data into related tables and defining appropriate relationships.

Why Is Normalization Important?

• Reduces unnecessary data duplication.
• Improves data consistency.
• Helps prevent insertion anomalies.
• Helps prevent update anomalies.
• Helps prevent deletion anomalies.
• Makes database structures easier to maintain.

Example of an Unnormalized Design

This structure uses separate columns for courses. It becomes inconvenient when students enroll in more courses than the available columns can represent.

Improved Relational Design

Separate the information into Students, Courses, and Enrollments tables. Each enrollment can then be represented by one row, allowing students to enroll in multiple courses without adding new course columns.

Common Normal Forms

13. Indexes in RDBMS

An index is a database structure that helps the database locate records more efficiently for supported queries. It works similarly to an index in a book, which helps readers find information without reading every page.

Advantages of Indexes

• Can improve the speed of data retrieval.
• Can help queries that filter, sort, or join records.
• Can support uniqueness requirements through unique indexes.
• Can reduce the amount of data that must be examined for suitable queries.

Disadvantages of Indexes

• Require additional storage space.
• Can increase the cost of INSERT, UPDATE, and DELETE operations.
• Need to be selected and maintained according to query patterns.
• Too many or poorly chosen indexes can reduce overall performance.

Example

CREATE INDEX idx_student_name
ON students(student_name);

SELECT *
FROM students
WHERE student_name = 'Rahul Sharma';

The index may help the database locate matching student records more efficiently, depending on table size, data distribution, and the query execution plan.

14. Difference Between DBMS and RDBMS

Important: RDBMS is a subset of DBMS. The exact features and guarantees depend on the database product and its configuration.

15. Difference Between RDBMS and NoSQL Databases

Neither approach is universally better. The appropriate choice depends on data relationships, consistency requirements, application workload, scaling needs, and operational constraints.

16. Popular RDBMS Software

17. Advantages of RDBMS

• Structured organization: Stores information in clearly defined tables.
• Reliable relationships: Connects related data using keys.
• Data integrity: Supports constraints that help prevent invalid data.
• Reduced redundancy: Supports normalization and better data organization.
• Flexible querying: Allows users to retrieve information through SQL.
• Transaction support: Can protect the correctness of related database operations.
• Security: Provides mechanisms for controlling access to information.
• Concurrent access: Supports multiple users and applications working with shared data.
• Backup and recovery: Supports procedures for protecting and restoring data.
• Broad adoption: Is used in many industries and application types.

18. Limitations of RDBMS

• Designing a well-structured relational database requires planning and technical knowledge.
• Schema changes can require careful migration and application updates.
• Complex queries involving large tables may need optimization and appropriate indexes.
• Normalization can require joins across multiple tables for some queries.
• Scaling very large workloads across multiple servers may require additional architecture and administration.
• Licensing, infrastructure, administration, and maintenance costs vary by product and deployment.
• Relational tables may not be the most natural model for every type of unstructured or graph-oriented data.

19. Real-World Applications of RDBMS

19.1 Banking Systems

Banks use relational databases to manage customer accounts, transactions, account details, and related financial records. Transaction integrity and security are especially important in these systems.

19.2 E-Commerce Systems

Online shopping platforms store product catalogs, customer profiles, shopping carts, orders, payments, and inventory information in related tables.

19.3 Education Management

Educational institutions use relational databases to manage student admissions, courses, attendance, examinations, marks, and fee records.

19.4 Healthcare Systems

Healthcare applications can use relational databases for appointments, administrative records, billing, and other structured information, subject to appropriate privacy and security requirements.

19.5 Airline Reservation Systems

Reservation applications can manage flight schedules, passenger information, seat assignments, and booking records.

19.6 Inventory Management

Businesses use RDBMS software to track products, suppliers, stock levels, purchases, and sales.

19.7 Human Resource Management

Organizations can maintain employee information, department assignments, payroll records, and leave details using related tables.

19.8 Customer Relationship Management

CRM applications use structured databases to manage customer profiles, contacts, interactions, and sales opportunities.

20. Practical Example: Creating a College Database in MySQL

This practical exercise demonstrates how to create related tables and retrieve information using MySQL.

Step 1: Create a Database

CREATE DATABASE college_db;
USE college_db;

Step 2: Create the Courses Table

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

Step 3: Create the Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

Step 4: Create the Enrollments Table

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

Step 5: Insert Course Records

INSERT INTO courses (course_id, course_name)
VALUES
(101, 'MySQL'),
(102, 'Python'),
(103, 'Java');

Step 6: Insert Student Records

INSERT INTO students (student_id, student_name, email)
VALUES
(1, 'Rahul Sharma', '[email protected]'),
(2, 'Priya Verma', '[email protected]'),
(3, 'Amit Kumar', '[email protected]');

Step 7: Insert Enrollment Records

INSERT INTO enrollments
(student_id, course_id, enrollment_date)
VALUES
(1, 101, '2026-10-01'),
(1, 102, '2026-10-02'),
(2, 103, '2026-10-03'),
(3, 101, '2026-10-04');

Step 8: Retrieve Student Records

SELECT * FROM students;

Step 9: Retrieve Course Records

SELECT * FROM courses;

Step 10: Display Students with Their Courses

SELECT
    s.student_id,
    s.student_name,
    c.course_name,
    e.enrollment_date
FROM students AS s
JOIN enrollments AS e
    ON s.student_id = e.student_id
JOIN courses AS c
    ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_name;

Explanation: This query joins three related tables to display student names alongside the courses in which they are enrolled. The JOIN conditions use the primary-key and foreign-key relationships.

Step 11: Count Students Enrolled in Each Course

SELECT
    c.course_name,
    COUNT(e.student_id) AS total_students
FROM courses AS c
LEFT JOIN enrollments AS e
    ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

This query counts the students enrolled in each course. The LEFT JOIN also includes courses that currently have no enrollments.

Expected Learning Outcomes

• Create a relational database using MySQL.
• Create tables with primary keys and foreign keys.
• Understand one-to-many and many-to-many relationships.
• Insert data while respecting referential integrity.
• Retrieve related information using JOIN operations.
• Summarize data using COUNT() and GROUP BY.

21. Common RDBMS Terminology

22. Frequently Asked Questions (FAQs)

Q1. What is the full form of RDBMS?

RDBMS stands for Relational Database Management System.

Q2. What is an RDBMS?

An RDBMS is software that stores and manages relational data using tables, keys, constraints, and SQL-based operations.

Q3. What is the difference between DBMS and RDBMS?

DBMS is the general category of database management software, while RDBMS specifically uses the relational model to organize data and define relationships.

Q4. What is the relational model?

The relational model represents data using relations, commonly implemented as tables with rows and columns.

Q5. What is a primary key?

A primary key uniquely identifies every row in a table. Its values must be unique and cannot be NULL.

Q6. What is a foreign key?

A foreign key references a key in a related table or the same table to establish a relationship and help enforce referential integrity.

Q7. What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe key guarantees for reliable database transactions.

Q8. What is normalization?

Normalization organizes data into related tables to reduce unnecessary duplication and prevent data anomalies.

Q9. What is an index?

An index is a database structure that can improve data retrieval performance for appropriate queries.

Q10. What is SQL?

SQL stands for Structured Query Language. It is used to define, retrieve, and manipulate data in relational database systems.

Q11. Name some popular RDBMS software.

Popular examples include MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite.

Q12. Why are relationships important in an RDBMS?

Relationships connect information stored in different tables, reduce duplication, and allow related records to be retrieved together.

Q13. Can multiple users access an RDBMS simultaneously?

Yes. RDBMS platforms commonly support concurrent users and use transaction and concurrency-control mechanisms to manage access to shared data.

Q14. What is a many-to-many relationship?

A many-to-many relationship allows multiple records in one table to be associated with multiple records in another table. A junction table is commonly used to implement it.

23. Practice Questions

1. Define RDBMS and explain its purpose.
2. Explain the difference between DBMS and RDBMS.
3. What is the relational model, and who introduced it?
4. Explain rows, columns, relations, tuples, and attributes.
5. What are the main features of an RDBMS?
6. Explain primary keys, foreign keys, candidate keys, and composite keys.
7. Describe one-to-one, one-to-many, and many-to-many relationships.
8. What are the ACID properties of database transactions?
9. Explain normalization and its advantages.
10. What is an index, and how can it improve query performance?
11. Compare RDBMS and NoSQL databases.
12. Write SQL statements to create related Students and Courses tables.
13. Explain how a JOIN retrieves data from multiple tables.
14. List five real-world applications of RDBMS.
15. Explain how constraints help maintain data integrity.

24. Conclusion

A Relational Database Management System (RDBMS) is an essential technology for managing structured data in modern applications. It organizes information into tables, connects related records through keys, and provides SQL operations for retrieving and modifying data.

Concepts such as primary keys, foreign keys, relationships, normalization, constraints, indexes, and ACID transactions form the foundation of relational database development. Learning these concepts through practical MySQL exercises helps students build the skills needed to design, develop, and maintain reliable database-driven applications.

Explore the course: JustAcademy MySQL Training Course

Register for a demo: Register for MySQL Course Demo