• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL RDBMS

← Previous Next →

MySQL is based on the concept of an RDBMS, or Relational Database Management System. This system stores data in structured tables, where each table has rows and columns. The term “relational” means that data from different tables can be connected or related using common fields.

An RDBMS like MySQL ensures that data is not only stored but also organized, secure, and consistent, which makes it one of the most widely used database systems in the world.

What is an RDBMS?

RDBMS stands for Relational Database Management System. It’s a software system that manages databases by storing data in a structured format using tables. Each table contains records (rows) and fields (columns), and relationships can be established between these tables.

Think of it like multiple Excel sheets linked together. Each sheet (or table) stores specific information, and connections between sheets ensure the data stays accurate and meaningful.

Difference Between a Database and an RDBMS

A database is just a place where data is stored. It could be as simple as a text file or spreadsheet.

An RDBMS, on the other hand, provides a complete system to manage that data — including storing, organizing, securing, and retrieving it using SQL (Structured Query Language).

Key differences:

Feature Database RDBMS
Data Storage Flat files Tables with rows and columns
Data Relationships Not supported Supported using keys
Data Integrity Not ensured Maintained through constraints
Query Language Not available SQL used for operations
Multi-user Access Limited Fully supported

Core Components of an RDBMS

An RDBMS works through a few essential components that help manage data efficiently.

1. Tables

Tables are the foundation of an RDBMS. Each table represents a collection of related data, with rows for records and columns for fields.
Example: A students table might contain columns like id, name, age, and course_id.

2. Fields (Columns)

Each column defines a data attribute. For instance, name might store text, while age stores numbers.

3. Records (Rows)

Every row represents one complete entry in a table, such as a single student’s details.

4. Primary Key

A primary key uniquely identifies each record in a table. It prevents duplicate entries and ensures that each row can be accessed directly.
Example:

id INT AUTO_INCREMENT PRIMARY KEY

5. Foreign Key

A foreign key links one table to another. It creates a relationship between tables by referencing a primary key in another table.
Example:

FOREIGN KEY (course_id) REFERENCES courses(id)

6. Indexes

Indexes improve search performance. They allow the database to find data faster without scanning every row.

7. Views

A view is a virtual table created using a query. It doesn’t store data itself but presents data from one or more tables for easier access.

8. Schema

A schema defines the overall structure of the database — including tables, views, and relationships.

How MySQL Uses the RDBMS Model

MySQL fully supports the relational model. It allows you to:

  • Create multiple databases.

  • Establish relationships between tables.

  • Use constraints to maintain data integrity.

  • Run SQL queries to insert, delete, and update data.

Example:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Here, course_id links the students table to the courses table, ensuring that every student is connected to a valid course.

Advantages of Using an RDBMS like MySQL

Using MySQL as an RDBMS provides several key benefits:

1. Data Integrity

RDBMS maintains data accuracy through rules and constraints. Invalid or duplicate data is prevented automatically.

2. Data Relationships

Tables can be related to each other. This means you don’t need to repeat data — a student’s information can be stored once and linked to many records.

3. Security and Access Control

You can assign user permissions, encrypt data, and protect sensitive information.

4. Scalability

From small projects to enterprise-level systems, MySQL can handle millions of records efficiently.

5. SQL Support

MySQL uses the industry-standard Structured Query Language (SQL), which makes it easy to query and manage data.

6. Multi-user Support

Many users can access the database at the same time without corrupting data.

Relationships in an RDBMS

Relationships define how data in one table relates to data in another. MySQL supports three main types of relationships.

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

Each record in one table matches exactly one record in another.
Example: Each student has one profile.

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

A single record in one table is related to multiple records in another.
Example: One course can have many students.

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

Records in both tables can have multiple related entries.
Example: Students can enroll in many courses, and each course can have many students.
This is usually handled through a junction table, like enrollments.

ACID Properties in RDBMS

RDBMS systems like MySQL follow ACID properties to ensure reliable transactions.

Property Description
Atomicity Either all parts of a transaction occur, or none do.
Consistency Data remains valid before and after a transaction.
Isolation Transactions don’t affect each other’s results.
Durability Once a transaction is complete, the data is permanently saved.

These rules make sure that even during system crashes or multiple transactions, the database remains accurate and stable.

Example: RDBMS in Action

Let’s take a school management example.

Tables:

  1. students — contains student details.

  2. courses — lists available courses.

  3. enrollments — links students with the courses they join.

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

This setup ensures:

  • Each student must exist before being enrolled.

  • Each course must exist before a student can join it.

  • Duplicate enrollments are avoided.

This structure reflects the core principle of RDBMS — maintaining relationships without repeating data.

Why MySQL is the Most Popular RDBMS

MySQL is one of the world’s most trusted RDBMS platforms because it’s:

  • Open-source and free to use.

  • Cross-platform (Windows, Linux, macOS).

  • Compatible with many programming languages like PHP, Python, and Java.

  • Fast and reliable, even with large databases.

  • Widely supported, powering platforms like WordPress, Joomla, and Drupal.

Its combination of simplicity, performance, and flexibility makes it a top choice for developers.

Summary of the Tutorial

To summarize:

  • MySQL is an RDBMS that stores data in related tables.

  • It uses SQL to manage and query that data.

  • It maintains data integrity, accuracy, and relationships between tables.

  • MySQL supports the ACID model, ensuring data reliability.

  • Its scalability and open-source nature make it ideal for all kinds of applications.

Understanding the RDBMS concept gives you a strong foundation to move ahead. In the next tutorial, we’ll explore how to create and manage databases in MySQL.

Practice Questions

  1. Create a table named students with columns id, name, age, and course_id, where id should be the primary key and auto-incremented.

  2. Write a SQL query to create a courses table with columns id (primary key) and course_name, and ensure no duplicate course names are allowed.

  3. Insert five records into the students table with different course IDs.

  4. Create a foreign key relationship between students.course_id and courses.id to link each student to a valid course.

  5. Write a SQL query to display all students along with their course names using an INNER JOIN.

  6. Create an enrollments table that connects students and courses with two foreign keys: student_id and course_id.

  7. Insert records into the enrollments table to show which students are enrolled in which courses.

  8. Write a query to find the total number of students enrolled in each course.

  9. Create a view named student_course_view that shows student names with their corresponding course names.

  10. Write a query to delete a course from the courses table and ensure all related enrollments are also deleted using the ON DELETE CASCADE option.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top