Relational Database 🗄️ — Understanding SQL Databases

A Relational Database stores data in structured tables made up of rows and columns. Tables can be related to each other using keys.

flowchart LR
    A["Application"] --> DB["Relational Database"]

    DB --> C["Customer Table"]
    DB --> O["Order Table"]
    DB --> P["Product Table"]

    C --> O
    O --> P

1. Table, Row and Column

A table represents an entity or business concept. A row represents one record, while a column represents an attribute.

erDiagram
    CUSTOMER {
        int CustomerId
        string Name
        string Email
    }

Example:

CustomerId | Name       | Email
-----------|------------|------------------
1          | Rahul      | rahul@example.com
2          | Priya      | priya@example.com

2. Relationships Between Tables

The main strength of a relational database is the ability to represent relationships between entities.

erDiagram
    CUSTOMER ||--o{ ORDER : places

    CUSTOMER {
        int CustomerId
        string Name
    }

    ORDER {
        int OrderId
        int CustomerId
        decimal Amount
    }

Here, one customer can have many orders.

3. Primary Key

A Primary Key uniquely identifies each row in a table.

erDiagram
    CUSTOMER {
        int CustomerId PK
        string Name
        string Email
    }

For example, CustomerId can uniquely identify a customer.

4. Foreign Key

A Foreign Key references a key in another table and establishes a relationship between the tables.

erDiagram
    CUSTOMER ||--o{ ORDER : places

    CUSTOMER {
        int CustomerId PK
    }

    ORDER {
        int OrderId PK
        int CustomerId FK
    }

5. Normalization

Normalization organizes data to reduce unnecessary duplication and improve data consistency.

flowchart LR
    A["Customer + Order Data"] --> N["Normalized Tables"]

    N --> C["Customer"]
    N --> O["Order"]
    N --> P["Product"]

Instead of repeatedly storing the same customer information in every order, customer information can be stored separately and referenced using a key.

6. Joins

Relational databases can combine related data from multiple tables using joins.

flowchart LR
    C["Customer"] --> J["JOIN"]
    O["Order"] --> J
    J --> R["Customer + Order Result"]

Common joins include:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

7. Transactions

Relational databases provide strong transaction support for operations that must succeed or fail as a unit.

sequenceDiagram
    participant A as Application
    participant DB as Relational DB

    A->>DB: BEGIN TRANSACTION
    A->>DB: Update Account A
    A->>DB: Update Account B
    DB-->>A: COMMIT
    A-->>A: Transaction Complete

Transactions are particularly important for systems such as banking, payments, inventory, and order processing.

8. Relational Database Scaling

A relational database can scale using several techniques.

flowchart LR
    A["Application"] --> DB["Primary Database"]

    DB --> R1["Read Replica 1"]
    DB --> R2["Read Replica 2"]

    DB --> S["Sharding / Partitioning"]

• Vertical scaling
• Read replicas
• Partitioning
• Sharding
• Caching

9. When Is a Relational Database a Good Fit?

• Structured data
• Strong relationships between entities
• Complex queries and joins
• Transactional workloads
• Strong consistency requirements

Important Interview Questions

• What is a relational database?
• What are rows, columns and tables?
• What is a primary key?
• What is a foreign key?
• Why do we normalize data?
• What are SQL joins?
• Why are transactions important?
• How can a relational database scale?
• When would you choose a relational database?

Key Takeaways

• Data is organized into tables, rows, and columns.
• Primary keys uniquely identify records.
• Foreign keys establish relationships between tables.
• Joins allow related data to be queried together.
• Transactions provide reliable multi-step operations.
• Relational databases can scale using replicas, partitioning, sharding, and caching.

Conclusion

For interviews, remember: Tables → Keys → Relationships → Joins → Transactions → Scaling.