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:

Library Sign-Out Sheet (unnormalized)

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:

After 1NF

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:

STUDENTS

ID	Name	Grade
S1	Mia	3rd
S2	Sam	4th

CHECKOUTS (after 2NF)

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:

BOOKS

ID	Title	Author
B1	Charlotte’s Web	E.B. White
B2	Frog and Toad	Arnold Lobel

CHECKOUTS (after 3NF)

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.

Normal Forms at a Glance

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?

1. 2NF

2. 3NF

3. 1NF

4. 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?

1. 1NF, because names aren’t atomic

2. 2NF, because it’s a partial dependency on part of the composite key

3. 3NF, because it’s a transitive dependency

4. 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?

1. Delete the ProductCategory column entirely

2. Merge all three columns into one

3. Move ProductCategory into a separate PRODUCTS table keyed by ProductID

4. 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?

1. It repeats the student’s name and book title in every row

2. It stores the StudentID and BookID as foreign keys

3. It doesn’t need to know — that data is looked up manually

4. 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.