.. include:: ../global.rst *************************** Databases *************************** Database Normalization ======================= **Normalization** is the process of organizing a database so that every fact is stored in exactly one place. A table that hasn't been normalized tends to repeat data across many rows, which wastes space and — much worse — lets the same fact disagree with itself if only one copy gets updated. Normalization fixes this by splitting one messy table into several small, focused tables that are linked together with keys. .. note:: For a fully illustrated, picture-book walkthrough of this exact example, see the `Tidy Bookshelf `_ visual guide. The Problem: A Messy Table --------------------------- Imagine a school library keeps one big spreadsheet for tracking book checkouts: .. list-table:: Library Sign-Out Sheet (unnormalized) :widths: 15 10 30 25 15 :header-rows: 1 * - Student - Grade - Books Checked Out - Author - Due Date * - Mia - 3rd - Charlotte's Web, Frog and Toad - E.B. White, Arnold Lobel - Sep 10 * - Mia - 3rd - The Cat in the Hat - Dr. Seuss - Sep 15 * - Sam - 4th - Charlotte's Web - E.B. White - Sep 12 This single table has three separate problems: * **Repeating groups:** the "Books Checked Out" and "Author" columns can hold more than one value, so the data isn't *atomic* (broken down as far as it can go). * **Partial dependency:** "Grade" depends only on the student, not on the specific checkout event, yet it gets retyped on every row. If Mia is promoted to 4th grade, we have to remember to update *every* row she appears in — miss one, and the table now contradicts itself (an **update anomaly**). * **Transitive dependency:** "Author" depends on the book, not on the checkout. It's copied wherever that book appears, so a single typo can make the sheet disagree with itself about who wrote a book. First Normal Form (1NF): Atomic Values ---------------------------------------- **Rule:** every column must hold a single, indivisible value — no lists, no repeating groups. Mia's "Books Checked Out" cell held two books at once. To reach 1NF, we give each book its own row: .. list-table:: After 1NF :widths: 20 40 :header-rows: 1 * - Student - Book * - Mia - Charlotte's Web * - Mia - Frog and Toad Second Normal Form (2NF): Remove Partial Dependencies -------------------------------------------------------- **Rule:** every non-key column must depend on the *whole* key, not just part of it. (This only matters once a table has a composite key.) "Grade" depends on the student alone, not on which book was checked out — that's a partial dependency. We pull student data into its own table with an ``ID`` primary key, and reference that ``ID`` from a separate checkouts table: .. list-table:: STUDENTS :widths: 10 20 10 :header-rows: 1 * - ID - Name - Grade * - S1 - Mia - 3rd * - S2 - Sam - 4th .. list-table:: CHECKOUTS (after 2NF) :widths: 10 30 15 :header-rows: 1 * - Student ID - Book - Due Date * - S1 - Charlotte's Web - Sep 10 * - S1 - Frog and Toad - Sep 10 Now if Mia is promoted, we update her grade in exactly **one** row: her own record in ``STUDENTS``. Third Normal Form (3NF): Remove Transitive Dependencies ----------------------------------------------------------- **Rule:** every non-key column must depend *directly* on the key — not on another non-key column. "Author" depends on the book title, not on the checkout event itself — that's a transitive dependency (``Checkout -> Book -> Author``). We give books their own table too: .. list-table:: BOOKS :widths: 10 30 20 :header-rows: 1 * - ID - Title - Author * - B1 - Charlotte's Web - E.B. White * - B2 - Frog and Toad - Arnold Lobel .. list-table:: CHECKOUTS (after 3NF) :widths: 10 10 15 :header-rows: 1 * - Student ID - Book ID - Due Date * - S1 - B1 - Sep 10 * - S1 - B2 - Sep 10 The Result: Three Clean Tables --------------------------------- ``STUDENTS`` and ``BOOKS`` each hold facts about one thing, written down exactly once. ``CHECKOUTS`` is a linking table that just points at both, using their ``ID`` values as **foreign keys**. .. list-table:: Normal Forms at a Glance :widths: 15 45 40 :header-rows: 1 * - Form - Fixes - In our example * - 1NF - Multi-valued / repeating columns - One book per row, not a comma-separated list * - 2NF - Columns that depend on only *part* of the key - Student's grade moved out to ``STUDENTS`` * - 3NF - Columns that depend on another *non-key* column - Book's author moved out to ``BOOKS`` Why Bother? ------------- * **No update anomalies** — change a fact in one place and it's correct everywhere. * **No insertion anomalies** — you can add a new book to ``BOOKS`` even before anyone checks it out. * **No deletion anomalies** — deleting Sam's last checkout doesn't erase the only record that Sam is a 4th grader. * **Less wasted space** — a book's title and author are stored once, no matter how many times it's checked out. Quiz ====== 1: Atomic Values ------------------ A column named ``Phone Numbers`` stores ``"555-0101, 555-0199"`` for a single customer. Which normal form does this violate? A. 2NF B. 3NF C. 1NF D. It doesn't violate any normal form 2: Partial Dependency ------------------------ In a table keyed by ``(StudentID, CourseID)``, a column called ``StudentName`` depends only on ``StudentID``. What does this violate? A. 1NF, because names aren't atomic B. 2NF, because it's a partial dependency on part of the composite key C. 3NF, because it's a transitive dependency D. Nothing — this is perfectly normalized 3: Transitive Dependency ---------------------------- A table has columns ``OrderID``, ``ProductID``, and ``ProductCategory``, where ``ProductCategory`` depends on ``ProductID`` rather than ``OrderID``. What's the fix? A. Delete the ``ProductCategory`` column entirely B. Merge all three columns into one C. Move ``ProductCategory`` into a separate ``PRODUCTS`` table keyed by ``ProductID`` D. Nothing needs to change 4: Foreign Keys ------------------ After normalizing the library example, how does the ``CHECKOUTS`` table know which student and which book a row refers to? A. It repeats the student's name and book title in every row B. It stores the ``StudentID`` and ``BookID`` as foreign keys C. It doesn't need to know — that data is looked up manually D. It merges ``STUDENTS`` and ``BOOKS`` back into one table Answer Key & Explanations ============================ Answer to Question 1 ----------------------- **Correct Answer: C** *Explanation:* 1NF requires every column to hold a single, atomic value. A comma-separated list of phone numbers packs multiple values into one cell, which violates 1NF before either 2NF or 3NF even apply. Answer to Question 2 ----------------------- **Correct Answer: B** *Explanation:* 2NF is only relevant when a table has a composite key. ``StudentName`` depends on just ``StudentID`` — one piece of the key — not on the full ``(StudentID, CourseID)`` pair, which is exactly what a partial dependency is. Answer to Question 3 ----------------------- **Correct Answer: C** *Explanation:* ``ProductCategory`` depends on ``ProductID``, a non-key column, rather than directly on the table's key — a transitive dependency. Moving it into its own ``PRODUCTS`` table, referenced by ``ProductID``, resolves it and reaches 3NF. Answer to Question 4 ----------------------- **Correct Answer: B** *Explanation:* Normalized tables stay connected through **foreign keys** — small ID values that reference a row in another table — instead of duplicating that table's data inline. Database Class Topics: Prioritization Guide =========================================== Tier 1: Core Fundamentals (Must Master) ======================================= These topics represent 80% of daily engineering work across backend development, DevOps, and database administration. Linux Server Access & CLI ------------------------- * **Why:** You will almost never interact with a production database or server through a GUI. Master SSH keys, basic command-line navigation, file manipulation (``find``, ``grep``, ``awk``), and background process management (``systemctl``, ``journalctl``). User Management & Permissions ----------------------------- * **Why:** Security starts here. Understanding file permissions (``chmod``, ``chown``, ``umask``) and user/group privilege escalation (``sudo``) is non-negotiable. Bad permission management is a leading cause of broken production deployments and security breaches. Web Server Options & Reverse Proxies ------------------------------------ * **Why:** Modern databases are typically exposed to applications behind web servers like Nginx or Caddy acting as reverse proxies. Understanding how a reverse proxy handles SSL termination, routes traffic, and forwards headers to backend application servers is crucial for web architecture. Firewall & Security ------------------- * **Why:** Leaving database ports (like PostgreSQL's ``5432`` or MySQL's ``3306``) wide open to the internet is a major security hazard. Learning how to configure firewalls (``ufw``, ``iptables``) to restrict access exclusively to trusted app servers is essential. Backup ------ * **Why:** An untested backup is not a backup. You need to understand how automated backup scripts work, basic database dump tools (``pg_dump``, ``mysqldump``), offsite storage syncing, and—most importantly—how to restore from a backup. Tier 2: Force Multipliers (High Career ROI) =========================================== These topics elevate you from someone who merely manages a server to someone who automates and scales infrastructure. Server Configuration Management ------------------------------- * **Why:** Manual server setup does not scale. Learning concepts around configuration management (or infrastructure-as-code tools like Ansible) teaches you how to make infrastructure reproducible, version-controlled, and reliable. Domain Management & Encryption ------------------------------ * **Why:** Understanding DNS records (A, CNAME, TXT) and how automated TLS/SSL encryption works (e.g., Let's Encrypt / Certbot) is foundational for deploying secure applications to the public. Editors (vi/vim) ---------------- * **Why:** ``nano`` is fine for quick local edits, but ``vi``/``vim`` exists on *every* Linux environment by default, including stripped-down Docker containers and remote recovery shells. Knowing basic ``vi`` navigation makes you functional on any system instantly. Tier 3: Contextual Knowledge (Learn Enough to Get By) ===================================================== Useful for foundational understanding, but less critical for daily specialized focus. * **OS Overview, Components & OS Options:** Focus specifically on enterprise Linux distributions (Ubuntu Server, Debian, RHEL/Rocky Linux). You don't need to memorize kernel internals immediately—just understand package management (``apt``/``dnf``) and service management (``systemd``). * **Stack Server Setup:** Good for seeing the "big picture" (how the DB, web server, and backend code connect), but modern workflows often abstract this using containers (Docker). * **Git Server:** Understanding core ``git`` workflows is mandatory, but hosting your *own* Git server is rarely done in industry today (companies rely on GitHub, GitLab, or Bitbucket). Tier 4: Low Priority / Outdated Paradigms ========================================= Spend minimal time here unless required by a specific project. * **Web Users via .htaccess:** Apache-specific file-level overrides are largely outdated in modern scalable architectures, where central proxy configurations (like Nginx) or application-level middleware are preferred. * **Wiki Setup:** Useful as a hands-on lab exercise to practice setting up a database + web app, but not a standalone skill you'll be hired for. * **Notepad++ and Desktop Editors:** Desktop text editors are personal preference tools rather than infrastructure concepts. Suggested Learning Path ======================= To build your practical intelligence systematically: 1. **Phase 1 (The Access Layer):** Linux Server Access -> CLI -> ``vi`` basics -> User Management & Permissions. 2. **Phase 2 (The System Layer):** Linux Server Setup -> Firewall & Security -> Backup strategies. 3. **Phase 3 (The Application Layer):** Web Servers -> Reverse Proxies -> Domain Management & Encryption. 4. **Phase 4 (Automation):** Server Configuration Management.