MySQL RDBMS

Relational Database Management Systems (RDBMS) Foundations

An RDBMS is database software that stores data in structured two-dimensional tables consisting of rows and columns. Tables connect to each other using key relationships.

RDBMS Relational Structure

Relational databases eliminate data duplication by normalizing schemas and establishing relationships through Primary Keys and Foreign Keys.

erDiagram
    USERS ||--o{ ORDERS : places
    USERS {
        int id PK
        string name
        string email
    }
    ORDERS {
        int id PK
        int user_id FK
        decimal total_amount
    }

Essential RDBMS Terminology

• Table: A structured grid of records representing a domain entity (e.g. customers, orders).
• Row (Record): A single individual data entry inside a table.
• Column (Attribute): A specific field of information assigned a defined data type (e.g. email, price).
• Primary Key (PK): A unique identifier column that prevents duplicate records.
• Foreign Key (FK): A column referencing the primary key of another table to link records.

Practical SQL Code Example: Table Relationships

-- Create parent table
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- Create child table linked by Foreign Key
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Best Practices & Common Gotchas

• Always Define Primary Keys: Every production table must have a primary key for fast indexing and row identification.
• Enforce Referential Integrity: Use foreign key constraints to prevent orphaned child records when parent rows are deleted.

Self-Check Challenge

Explain how a Foreign Key in a child table maintains relational integrity with a parent table.