RDBMS in MySQL (Relational Database Management System)

What is RDBMS?

A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured format using tables, rows, and columns. MySQL is an example of an RDBMS.

Key Features of RDBMS in MySQL

✅ 1. Tables Store Data in a Structured Way

• Data is organized in tables consisting of rows (records) and columns (fields).
• Each table has a primary key that uniquely identifies each row.

📌 Example Table: employees

CREATE TABLE employees (    emp_id INT PRIMARY KEY,    name VARCHAR(50),    department VARCHAR(50),    salary DECIMAL(10,2));

✅ 2. Data Integrity and Constraints

RDBMS enforces data integrity using constraints like:

• Primary Key (PRIMARY KEY) – Uniquely identifies each row.
• Foreign Key (FOREIGN KEY) – Maintains relationships between tables.
• Not Null (NOT NULL) – Ensures values cannot be NULL.
• Unique (UNIQUE) – Prevents duplicate values in a column.
• Check (CHECK) – Restricts allowed values.

CREATE TABLE departments (    dept_id INT PRIMARY KEY,    dept_name VARCHAR(50) UNIQUE NOT NULL);

✅ 3. Relationships Between Tables

RDBMS supports relationships between tables using foreign keys.

📌 Example: Employees belong to Departments

CREATE TABLE employees (    emp_id INT PRIMARY KEY,    name VARCHAR(50),    dept_id INT,    FOREIGN KEY (dept_id) REFERENCES departments(dept_id));

✅ 4. SQL Queries for Data Manipulation

RDBMS allows various SQL operations:

• INSERT: Add data

INSERT INTO employees (emp_id, name, dept_id) VALUES (1, 'Alice', 1);

• SELECT: Retrieve data

SELECT * FROM employees WHERE dept_id = 1;

• UPDATE: Modify data

UPDATE employees SET salary = 7500 WHERE emp_id = 1;

• DELETE: Remove data

DELETE FROM employees WHERE emp_id = 1;

✅ 5. Transactions for Data Consistency

• RDBMS ensures data consistency using transactions (COMMIT, ROLLBACK).

START TRANSACTION;UPDATE employees SET salary = 8000 WHERE emp_id = 2;ROLLBACK;  -- Undo changesCOMMIT;    -- Save changes

✅ 6. Scalability and Performance

• Indexing (INDEX) for faster searches

CREATE INDEX idx_name ON employees(name);

• Normalization to reduce redundancy
• Joins (JOIN) for combining tables

SELECT employees.name, departments.dept_nameFROM employeesJOIN departments ON employees.dept_id = departments.dept_id;

Key Takeaways

✅ MySQL is an RDBMS that stores data in tables with relationships.
✅ Supports constraints (PRIMARY KEY, FOREIGN KEY) for data integrity.
✅ Uses SQL for querying (SELECT, INSERT, UPDATE, DELETE).
✅ Ensures data consistency with transactions (COMMIT, ROLLBACK).
✅ Supports indexing and optimization for better performance.