DBMS + SQL

Master Revision Notes

01 Why DBMS exists 02 Data modeling flow

03 ER Model 04 ER → Relational conversion

05 Relationships 06 Keys

07 Normalization 08 System architecture

09 ACID 10 Recovery

11 SQL execution order 12 JOINs

13 Subqueries 14 Views

15 Isolation levels 16 Locks + Deadlocks

17 Indexing 18 MVCC

19 Constraints 20 Responsibility map

01 Why DBMS exists

Core responsibilities
Problems it solves
Storage · Retrieval · Consistency · Concurrency · Recovery ·
Storage · Retrieval · Correctness · Scaling Scalability

02 Data modeling flow

Real World -> ER Diagram -> Relational Model -> Normalization -> SQL Tables

Design data correctly before storing it.

Bad Design -> Bad DB -> Bad Queries -> Bad Performance

03 ER Model

Identify Remember

Entities, Attributes, Relationships ER Diagram = Blueprint. DB not created yet.

04 ER -> Relational conversion

Strong entity Entity -> Table e.g. Student -> Student(id, name, age)

Key attribute -> PRIMARY KEY

Regular attribute -> Column

Composite attribute Address -> split into street, city, pincode (store atomic values)

Multivalued attribute Separate table e.g. StudentPhone(student_id, phone_number)

Derived attribute Store source, calculate value e.g. store DOB, calculate age

05 Relationships

1:1 1:N M:N

Person <-> Passport FK on dependent Dept -> Employee FK on MANY side Student <-> Course New junction table
side (UNIQUE FK) — Golden Rule — always

Unary relationships — entity relates to itself

1:1 unary Person(person_id, spouse_id FK UNIQUE)

1:N unary Employee(emp_id, manager_id FK) — most common

M:N unary Friendship(student_id1 FK, student_id2 FK) — same table referenced twice

Participation

Total (mandatory) NOT NULL FK — every employee must belong to a dept

Partial (optional) NULL FK allowed

06 Keys

Super Key CONTAINS Candidate Key CONTAINS Primary Key

Super Key Any set that uniquely identifies a row. Theoretical. (EmpID, EmpID+Email, ...)

Candidate Key Minimal super key — no redundant attrs. (student_id, email, aadhaar) — possible PK
choices

Primary Key Chosen candidate key. Unique + Not Null. Most important.

Foreign Key Connects tables. Maintains Referential Integrity.

Composite Key Multiple columns as PK. e.g. Enrollment(student_id, course_id)

07 Normalization

Each table should represent ONE type of fact.

2NF
1NF
No Partial Dependency. Non-key must depend on entire
One value per cell. No multivalued attributes. composite key.

3NF BCNF

No Transitive Dependency. A->B->C? Split. Usually = two Every determinant must be a Candidate Key. Stricter than
entities mixed. 3NF.

Lossless decomp After splitting, original table reconstructable via JOIN. No info loss.

08 System architecture

User -> Frontend -> App Server -> DB Server -> Storage

DB Server concern
App Server concern
Storage, Transactions, Recovery, Concurrency, Indexing,
Validation, Business Logic, APIs, Auth, Traffic Data correctness

09 ACID

Atomicity Consistency

All ops succeed or all fail. Transfer = Withdraw + Deposit — Valid state -> Valid state. PK, FK, Unique, Business rules
both or neither. never violated.

Isolation
Durability
Concurrent txns behave as if serial. No interference between
transactions. After COMMIT, data survives crashes. Period.

You USE ACID (BEGIN/COMMIT/ROLLBACK). DBMS IMPLEMENTS ACID (logging, locks, MVCC).
10 Recovery

Shadow Copy Deferred Update

Log-Based
Copy DB, apply on copy, commit by DB updated only AFTER COMMIT.
updating pointer. Simple but Store change history (1000->800). Crash before? Ignore. No UNDO
expensive. Basis for UNDO/REDO. needed.

UNDO REDO

Not committed -> restore old value. 800 -> 1000 Committed but not flushed -> reapply. 1000 -> 800

Crash -> Read Log -> Committed? YES -> REDO -> NO -> UNDO

11 SQL execution order

1 FROM Pick the table(s)

2 JOIN / ON Combine tables

3 WHERE Filter rows

4 GROUP BY Create groups

5 HAVING Filter groups

6 SELECT Pick columns / compute

7 ORDER BY Sort result

8 LIMIT Trim result

Most important SQL concept. Explains every WHERE vs HAVING confusion.

WHERE HAVING

Filters ROWS before grouping Filters GROUPS after GROUP BY

Memory: WHERE -> Rows | HAVING -> Groups

12 JOINs

Normalization splits data. JOINs reconstruct it.

INNER JOIN LEFT JOIN

Only matching rows survive. Unmatched disappear. All left rows survive. No match on right -> NULL.

RIGHT JOIN FULL OUTER JOIN

All right rows survive. No match on left -> NULL. Everything from both. No match -> NULL.

Golden Q: Which table must survive? -> Choose the join.

COUNT(*) Counts rows. Includes NULL rows.

COUNT(col) Counts non-NULL values only. Critical with LEFT JOIN.

Self join — relationship inside same table

SELECT [Link], [Link] AS manager
FROM Employee e
LEFT JOIN Employee m ON e.manager_id = m.emp_id;

Self Relationship -> Self FK -> Self Join

13 Subqueries

Scalar subquery Multi-row subquery

Correlated subquery
Returns ONE value. Use with =, >, < Returns multiple values. Use with IN.
e.g. salary > (SELECT AVG(salary) e.g. dept_id IN (SELECT dept_id Inner depends on outer row. Runs per
FROM ...) FROM ...) outer row. e.g. above-dept-avg salary.

Normal subquery runs ONCE. Correlated runs for EVERY outer row.

14 Views

View = Saved Query (not duplicated data — stores query definition)

CREATE VIEW EmployeeDept AS SELECT e.*, [Link] FROM Employee e JOIN Department d ...

Uses

Simplify complex queries, Security, Reusability

15 Isolation levels

Tradeoff: More safety <-> Less performance

Read Uncommitted Allows dirty reads. Fastest, least safe.

Read Committed Only committed data visible. Prevents dirty reads. Most common default.

Repeatable Read Same row read twice = same result. Prevents dirty + non-repeatable reads.

Serializable Highest isolation. Feels serial. Safest, slowest.

You choose isolation level when business needs it. DBMS implements it.

16 Locks + Deadlocks

Shared lock (S) Exclusive lock (X)

Used for reading. Multiple allowed simultaneously. Used for writing. Only one allowed at a time.

S lock X lock

S lock OK — allowed BLOCKED

X lock BLOCKED BLOCKED

Deadlock
T1 locks A, needs B. T2 locks B, needs A. -> Deadlock.

Wait-For Graph -> Cycle found? -> Rollback victim transaction

Your job Short transactions · lock in same order · avoid unnecessary locking

DBMS job Detect + resolve deadlocks automatically

17 Indexing

Avoid full table scan. Tradeoff: faster reads -> slower writes (index also needs updating).

Primary Index Data file physically sorted on search key.

Sparse Index One entry per block. Sorted data required. Less storage.

Dense Index Every search key indexed. More storage, faster lookup.

Primary on key Unique values -> usually sparse indexing.

Primary on non-key Repeated values -> dense-like (must reference all matching records).

Secondary Index On unsorted attribute. Usually dense. Index itself stays sorted.

Multi-Level Index Index on index. Used internally. Makes searching faster.

You write CREATE INDEX. DBMS builds + maintains B+ Tree.

18 MVCC — Multi Version Concurrency Control

Used by Core idea

PostgreSQL, MySQL InnoDB Readers see old version. Writers create new version.

Example Version 1 = 1000 (readers see this) | Version 2 = 800 (new write)

Benefit More concurrency, less blocking, better performance.

Your job Almost none. Use transactions correctly.

DBMS job Create versions, track visibility, remove old versions (vacuum).

19 Constraints

PRIMARY KEY Unique + Not Null

FOREIGN KEY Maintain relationships. Referential integrity.

UNIQUE No duplicates allowed.

NOT NULL Value mandatory.

CHECK Custom rule — e.g. CHECK(age >= 18)

DEFAULT Default value if none provided.

Can be inline or table-level. Name them with CONSTRAINT constraint_name.

20 Responsibility map

YOUR JOB SHARED DBMS JOB

ER Diagram · Relational Model · Transactions · Index selection · Isolation ACID · Recovery · Logging ·
Normalization · PK/FK/Constraints · SQL level (when needed) UNDO/REDO · Locking · Isolation ·
Queries · Joins · Subqueries · Views · MVCC · Deadlock detection · Constraint
Index choice · enforcement · Index maintenance ·
BEGIN/COMMIT/ROLLBACK · Crash recovery
@Transactional

You tell DBMS "what should happen". DBMS decides "how to make it safe, consistent, and fast".

Design correctly -> Normalize correctly -> Write queries correctly -> Use transactions correctly -> Choose
indexes correctly -> Let DBMS handle the heavy lifting