MySQL RDBMS

Understanding Relational Database Management Systems

📊 What is RDBMS?

RDBMS (Relational Database Management System) organizes data into tables with relationships between them. MySQL is an RDBMS that stores data in structured format using rows and columns, enabling efficient data management. 


-- Tables with relationships
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50)
);

RDBMS Key Concepts

📋

Tables

Tables store data in rows and columns. Each table represents a specific entity like customers or products. Columns define data types while rows contain actual records. Tables are the fundamental structure in relational databases.

🔑

Primary Keys

Primary keys uniquely identify each row in a table. They cannot be null or duplicate. Every table should have a primary key to ensure data integrity and enable efficient data retrieval and relationships between tables.

🔗

Foreign Keys

Foreign keys create relationships between tables by referencing primary keys in other tables. They maintain referential integrity, ensuring that relationships remain valid. This prevents orphaned records and maintains data consistency across related tables.

📐

Normalization

Normalization organizes data to reduce redundancy and improve integrity. It divides large tables into smaller, related tables. This process eliminates duplicate data, saves storage space, and makes databases easier to maintain and update.

🔹 Tables and Relationships

In RDBMS, tables are connected through relationships. A customer can have multiple orders, creating a one-to-many relationship. Foreign keys link tables together, allowing you to retrieve related data from multiple tables using joins. 

-- Create related tables
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Output: 

Query OK, 0 rows affected (0.02 sec)

🔹 Primary Key Example

Primary keys ensure each record is unique and identifiable. They're essential for data integrity and creating relationships. AUTO_INCREMENT automatically generates unique values, making it easy to add new records without manually assigning IDs. 

-- Table with primary key
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

-- Insert data (emp_id auto-generates)
INSERT INTO employees (first_name, last_name, department)
VALUES ('John', 'Smith', 'Sales');

SELECT * FROM employees;

Output:

emp_id first_name last_name department
1 John Smith Sales

🔹 Foreign Key Relationships

Foreign keys maintain data consistency by linking tables. When you reference a customer in an order, the foreign key ensures that customer exists. This prevents invalid data and maintains referential integrity throughout your database. 

-- Insert customer
INSERT INTO customers (customer_name, email)
VALUES ('Alice Johnson', '[email protected]');

-- Insert order for that customer
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (1, '2024-01-15', 299.99);

-- View related data
SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Output:

customer_name order_date total_amount
Alice Johnson 2024-01-15 299.99

🔹 RDBMS Advantages

RDBMS provides structured data storage with strong consistency guarantees. It supports complex queries, transactions, and maintains data integrity through constraints. The relational model is proven, well-understood, and supported by extensive tools and documentation.

Key Benefits:

  • Data Integrity: Constraints ensure data accuracy and consistency
  • Reduced Redundancy: Normalization eliminates duplicate data
  • Easy Querying: SQL provides powerful data retrieval capabilities
  • Security: User permissions control data access
  • ACID Transactions: Reliable data processing
  • Scalability: Handles growing data efficiently

🧠 Test Your Knowledge

What does a primary key do?