Chapter 1 · Database Systems

Database Concepts & Relational Architecture

5 topics in 4 parts

Key ideas

Evaluation Parameter	Standard Relational Pattern	Production Optimization Impact
Storage Engine Layer	Disk page blocks & buffer pool cache	Minimizes random disk I/O via sequential prefetching
Index Utilization	B+ Tree logarithmic search $O(\log N)$	Avoids full table scans across millions of rows
Concurrency & Locks	Row-level shared (S) and exclusive (X) locks	Prevents race conditions while maximizing read throughput
Failure Recovery	Write-Ahead Logging (WAL) / Undo-Redo	Guarantees durability and instant crash recovery

Level / Tier	Governing Role	Target Audience	Primary Definition Tool	Independence Guarantee
External (View) Level	Virtual schema projections	End-users, Web Apps, BI analysts	SQL CREATE VIEW queries	Isolates user apps from schema evolution
Conceptual (Logical) Level	Entities, relationships, constraints	Database Administrators (DBA), Software Architects	SQL DDL (CREATE TABLE, ALTER)	Logical Data Independence (protects views)
Internal (Physical) Level	Block allocation, indexes, B+ trees	Storage Engine Developers, System DBAs	Storage Engine / OS File Management	Physical Data Independence (protects conceptual schema)

DBMS Architecture Interview Problem: Physical vs Logical Independence

Question: Why is Logical Data Independence significantly harder to achieve than Physical Data Independence in production database systems? Answer: In Physical Data Independence, internal optimizations (like adding an index or moving table files to a faster SSD) do not alter the column names, constraints, or relations at all; the logical schema remains 100% identical. However, in Logical Data Independence, changes to the conceptual schema (such as partitioning an `Employees` table into `Emp_Personal` and `Emp_Job` to remove nulls) require modifying the view mappings or adding triggers/updatable view rules. If an external view involves complex joins across altered tables, keeping it fully updatable without breaking legacy client queries requires substantial mapping overhead.

Executable Code Blueprint & Implementation (DBMS & SQL)

-- Relational Keys Implementation in SQL DDL
CREATE TABLE Departments (
    dept_id INT PRIMARY KEY,              -- Primary Key
    dept_name VARCHAR(50) NOT NULL UNIQUE -- Alternate Key
);

CREATE TABLE Students (
    student_id INT,                      -- Candidate Key 1
    roll_no VARCHAR(20),                 -- Candidate Key 2
    email VARCHAR(100),                  -- Candidate Key 3
    phone VARCHAR(15),
    dept_id INT,                         -- Foreign Key referencing Departments
    
    -- Designate Primary Key
    CONSTRAINT pk_student PRIMARY KEY (student_id),
    
    -- Designate Alternate Keys with UNIQUE constraints
    CONSTRAINT uq_roll UNIQUE (roll_no),
    CONSTRAINT uq_email UNIQUE (email),
    
    -- Foreign Key Constraint enforcing Referential Integrity
    CONSTRAINT fk_student_dept FOREIGN KEY (dept_id) 
        REFERENCES Departments(dept_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

Database Concepts & Relational Architecture

Relational Key Types Comparison Matrix

Key Type	Definition	Allows NULL?	Multiplicity in Table	Minimality Requirement
Super Key	Any attribute combination guaranteeing unique rows	Dependent on attributes	Unlimited possible subsets	No (can contain redundant columns)
Candidate Key	Minimal super key with zero redundant attributes	Strictly UNIQUE (rarely null)	One or more per table	Yes (Strictly minimal)
Primary Key	The designated unique identifier chosen by DBA	STRICTLY NO (NOT NULL enforced)	Exactly ONE per table	Yes (Must be a Candidate Key)
Foreign Key	References Primary Key of another relation	Yes (unless defined NOT NULL)	Zero, one, or multiple	Matches referenced parent key

Relational Design Interview Problem: Minimal Super Key Derivation

Question: Given a relation $R(A, B, C, D)$ with functional dependencies $A \to B, B \to C, C \to D$. Find all candidate keys. Answer: Compute attribute closure of $A$: $A^+ = \{A, B, C, D\}$. Since $A^+$ includes all attributes of $R$, $A$ is a Super Key. Since $A$ is a single attribute, no subset can be removed, making $A$ minimal. Therefore, $A$ is the sole Candidate Key.

Checkpoint

1. What distinguishes a Candidate Key from a general Super Key?

All 5 topics in Database Concepts & Relational Architecture

Foundations
- File System vs DBMS
Architecture
- 3-Tier Schema Architecture
- Data Independence
Data Modeling
- ER Modeling & Cardinality
Relational Model
- Database Keys