Lesson

MySQL RDBMS Concepts

10 min reading

Free Course

MySQL RDBMS: How Relational Databases Organize Data

A Relational Database Management System (RDBMS) stores data in structured two-dimensional tables made of rows and columns. Tables connect to each other through shared keys so you never duplicate customer details across every order.

How Relational Structure Prevents Duplication

In a flat spreadsheet, repeating a customer's email on fifty order rows wastes space and causes update errors. A relational schema splits customers and orders into separate tables linked by an integer identifier.

erDiagram
    DEMO_CUSTOMERS ||--o{ DEMO_ORDERS : places
    DEMO_CUSTOMERS {
        int id PK
        string full_name
        string email
    }
    DEMO_ORDERS {
        int id PK
        int customer_id FK
        decimal total_amount
    }

Core RDBMS Terms You Need

Table: A named collection of related records, such as demo_customers or demo_orders.
Row (Record): One complete entry inside a table representing a single customer or order.
Column (Attribute): A single typed field inside a table, such as email or total_amount.
Primary Key (PK): A unique, non-null column that identifies each row inside its own table.
Foreign Key (FK): A column in a child table that points to a primary key in a parent table.

Practical SQL Example

The example below creates a parent demo_departments table and a child demo_employees table connected by a foreign key.

CREATE TABLE demo_departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE demo_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES demo_departments(id)
);

Best Practices for Relational Schemas

Define Explicit Primary Keys: Add an id primary key to every table so joins and updates target exact rows.
Enforce Foreign Key Constraints: Foreign keys block orphan child rows when someone tries to reference a missing parent ID.
Keep One Fact Per Column: Store first name and last name or street and city separately so filtering stays simple.