?️ MySQL RDBMS Concepts

MySQL RDBMS Database Basics

⚡ Quick Overview

RDBMS (Relational Database Management System) is a type of database system where data is stored in related tables using rows and columns. MySQL is a popular open-source RDBMS.

In this topic you’ll learn:

• What tables, rows, and columns are
• What keys and relationships mean (Primary Key, Foreign Key, etc.)
• How MySQL enforces rules using constraints
• Why relational databases are powerful for real-world applications

? Key Concepts in RDBMS

• Table – Logical structure that stores data in rows and columns.
• Row / Record – One complete set of data in a table (e.g., one student).
• Column / Field – A specific attribute of the data (e.g., name, email).
• Primary Key (PK) – A column (or group of columns) that uniquely identifies each row.
• Foreign Key (FK) – A column that creates a link between two tables.
• Relationship – Logical connection between tables (1–1, 1–Many, Many–Many).
• Constraint – Rule applied on columns (NOT NULL, UNIQUE, CHECK, etc.).
• Normalization – Process of organizing data to reduce redundancy.

? Syntax & Core Theory

Although RDBMS concepts are logical, MySQL implements them using SQL commands such as CREATE DATABASE, CREATE TABLE, and ALTER TABLE.

1. Define a database – Logical container for tables.
2. Create tables with columns and data types.
3. Choose a Primary Key for each table.
4. Connect tables using Foreign Keys.
5. Query data using SELECT and joins.

? Example 1 – Creating a Database & Table

Let’s create a simple school_db database with a students table.

-- Create a database and basic students table
CREATE DATABASE school_db;

USE school_db;

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  date_of_birth DATE
);

? Relationships Between Tables

RDBMS is powerful because you can connect tables instead of duplicating data everywhere.

• One-to-One (1:1) – One record in table A is linked to one record in table B.
• One-to-Many (1:N) – One record in table A can be linked to many records in table B.
• Many-to-Many (M:N) – Many records in A relate to many records in B (implemented via a bridge table).

? Example 2 – One-to-Many with Foreign Key

One student can enroll in many courses. One course can have many students. We’ll start by modeling a One-to-Many relationship using students and enrollments.

-- Students table (already created earlier)
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(100) NOT NULL
);

-- Courses table with its own primary key
CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(100) NOT NULL
);

-- Enrollments table uses foreign keys to link students and courses
CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  enrollment_date DATE,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

? Constraints in MySQL RDBMS

Constraints ensure data integrity (correctness and reliability of the data).

• PRIMARY KEY – Uniquely identifies each row.
• FOREIGN KEY – Makes sure related rows exist in another table.
• NOT NULL – Column cannot be empty.
• UNIQUE – All values must be different.
• CHECK – Validates data based on a condition (limited support in older MySQL versions).
• DEFAULT – Sets a default value when no value is provided.

? Example 3 – Table with Multiple Constraints

Here is an example of a payments table with different constraints applied.

-- Payments table with various constraints
CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  student_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_date DATE DEFAULT (CURRENT_DATE),
  status VARCHAR(20) DEFAULT 'PENDING',
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

? Normalization & Advantages of RDBMS

Normalization is the process of structuring tables to reduce data duplication.

• 1NF (First Normal Form) – No repeating groups, each cell holds a single value.
• 2NF (Second Normal Form) – 1NF + all non-key columns depend on the whole primary key.
• 3NF (Third Normal Form) – 2NF + no column depends on another non-key column.

Advantages of using MySQL RDBMS:

• Data is consistent and accurate.
• Data redundancy (duplication) is minimized.
• Easy to query related data using joins.
• Scales well for large applications (e.g., e-commerce, banking, LMS).

? Example 4 – Querying Related Data with JOIN

To see which student enrolled in which course, we need to fetch data from multiple tables using JOIN.

-- List students with the courses they are enrolled in
SELECT
  s.student_id,
  s.full_name,
  c.course_name,
  e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id
ORDER BY s.student_id;

? Real-World Use Cases of MySQL RDBMS

• E-commerce – Products, customers, orders, payments tables all linked by keys.
• School / College ERP – Students, teachers, courses, attendance, fees.
• Banking Systems – Customers, accounts, transactions, cards.
• Learning Platforms – Users, courses, enrollments, quizzes, scores.