Relational database Study Guide

📖 Core Concepts Relational Database – Stores data as relations (tables) of rows (tuples) and columns (attributes). RDBMS – Software that manages relational databases; handles storage, query processing, and transaction control. SQL – The standard language for querying and updating relational data. Data Independence – Logical schema (tables, keys) is separated from physical storage, so changes to storage don’t affect applications. Tuple – A single row; always unique within a relation. Superkey / Key – Any set of attributes that uniquely identifies a tuple; a primary key is the chosen minimal superkey, while alternate keys are other candidate keys. Foreign Key – Column(s) that reference a primary key in another table, creating relationships. ACID Transactions – Guarantees for a group of operations: Atomicity, Consistency, Isolation, Durability. Normalization – Process of organizing tables to eliminate redundancy and update anomalies (1NF → 2NF → 3NF). --- 📌 Must Remember Entity Integrity: Primary key columns cannot be NULL. Referential Integrity: Every foreign‑key value must match an existing primary‑key value or be NULL. Codd’s 12 Rules – Criteria a DBMS must meet to be truly relational (exam may ask a few key rules). One‑to‑Many: Primary key in parent appears as foreign key in child. Many‑to‑Many: Implement with a junction table containing the two foreign keys. Derived Relation (View): Computed from base tables using relational operators. Index Benefits: Primary‑key and foreign‑key indexes dramatically speed joins & lookups. B‑Tree Lookup: $O(\log N)$ time; Hash Index: $O(1)$ when it fits in memory. --- 🔄 Key Processes Designing a Relational Schema Identify entities → tables. Choose a primary key (unique, minimal). Add foreign keys to represent relationships. Apply normalization (1NF → 2NF → 3NF). Executing a Transaction (ACID) Begin → perform reads/writes. Commit → all changes become durable; or Rollback → none persist. Creating a Many‑to‑Many Relationship Create junction table with two foreign keys (PK may be composite of both). Querying with Relational Operators Selection $\sigma{condition}(R)$ → filter rows. Projection $\pi{col1,\dots,colk}(R)$ → choose columns. Natural Join $R \bowtie S$ → combine on common attributes. --- 🔍 Key Comparisons Primary Key vs. Alternate Key PK: Chosen unique identifier; often indexed automatically. Alternate: Any other candidate key; may be natural (e.g., SSN) and can be used as alternate index. One‑to‑One vs. One‑to‑Many 1‑to‑1: PK of Table A appears as unique foreign key in Table B. 1‑to‑Many: PK of Table A appears as non‑unique foreign key in Table B. B‑Tree Index vs. Hash Index B‑Tree: Works for range queries, $O(\log N)$. Hash: Constant‑time lookups, but no range support. Base Relation vs. Derived Relation Base: Physically stored table. Derived: Virtual view computed on demand via operators. --- ⚠️ Common Misunderstandings NULL in Primary Key – Illegal; violates entity integrity. Foreign Key = Primary Key – Not required; foreign key may be non‑unique (one‑to‑many). Normalization Removes All Redundancy – It eliminates functional redundancy; some duplication (e.g., lookup tables) can be intentional. “Relation” ≠ “Table” – A relation is a mathematical set; a table is its physical implementation (order not guaranteed). --- 🧠 Mental Models / Intuition Table as a Spreadsheet: Rows = records, columns = attributes. Think of keys as “row identifiers” that let you jump directly to a row. ACID as a Safety Net: Imagine a bank transfer—either both accounts update (commit) or none (rollback). Normalization as Packing: 1NF packs atoms, 2NF ensures each attribute belongs fully to its primary key, 3NF removes “inside‑the‑box” dependencies. --- 🚩 Exceptions & Edge Cases Global Unique Identifier (GUID): Used when uniqueness must span multiple databases; may be overkill for single‑system designs. Check Constraints can reference only columns of the same row; they cannot enforce cross‑row conditions (use triggers instead). Hash Index Limitations: If the index spills to disk, performance degrades to near linear scan. --- 📍 When to Use Which Choose Primary Key → Prefer synthetic (auto‑increment) keys for simplicity; use natural keys only if they are stable and short. Use Junction Table → Whenever a many‑to‑many relationship is needed (e.g., students ↔ courses). Pick B‑Tree Index → For columns used in range queries, ORDER BY, or joins. Pick Hash Index → For equality searches on high‑cardinality columns that fit in memory. Apply Normal Form → Stop at 3NF for most OLTP systems; go to BCNF only if specific anomalies persist. --- 👀 Patterns to Recognize FK Column Name = PrimaryKeyName\ID – Signals a relationship. Composite Primary Key → Often indicates a junction table (many‑to‑many). Repeated Attribute Across Tables → Red flag for possible violation of 3NF (transitive dependency). SELECT … FROM A JOIN B ON A.id = B.id → Typical one‑to‑many join pattern. --- 🗂️ Exam Traps “NULL primary key is allowed” – Wrong; primary keys must be NOT NULL. “Hash index works for LIKE ‘%text’ searches” – Incorrect; hash indexes only support exact matches. “Normalization eliminates need for indexes” – False; normalized tables still benefit from indexes for performance. “Foreign key must be unique” – Misleading; only true for one‑to‑one relationships. “Derived relations store data permanently” – Wrong; they are virtual views, not physically stored unless materialized. ---