System Design Chapter 3

Relational DBs (RDBMS) & SQL Joins

Schemas, Foreign Keys, and Entity Relationships

★ Relational schemas enforce data consistency across multiple normalized tables!

Relational databases use SQL (Structured Query Language) for data definition and manipulation. Data is organized strictly into rows and columns.

SQL: Create & Insert Example

CREATE TABLE Customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    email VARCHAR(100)
);

INSERT INTO Customers (id, name, age, email)
VALUES (123, 'John Doe', 30, 'johndoe@example.com');

🔗 Complex Join Operations & Foreign Keys

SQL excels at joining related entities. For example, an Orders table links to Customers and Products via Foreign Keys.

SQL: Orders Table Schema with Foreign Keys

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(id),
    FOREIGN KEY (product_id) REFERENCES Products(id)
);

Customers

id PK

name String

email String

──(1:N)──►

Orders

id PK

customer_id FK

product_id FK

order_date Date

◄──(N:1)──

Products

id PK

name String

price Decimal