Database Normalization (1NF, 2NF, 3NF, 4NF, 5NF, BCNF)

What is the requirement(s) of 1NF

Requirements: Row order should not convey information; Data types within a single column must be consistent (no mixing); Every table must have a primary key; Repeating groups are not allowed.

What is the requirement(s) of 2NF

Each non-key attribute in the table must be dependent on the entire primary key. [Example: A "Player Inventory" table with a primary key of (Player, Item Type) includes a "Player Rating" column. "Player Rating" depends only on "Player," not the full (Player, Item Type) key, that violates 2NF. Solution: Create a separate "Player" table for "Player ID" and "Player Rating."]

What is the requirement(s) of 3NF

Each non-key attribute in a table must depend on the key, the whole key, and nothing but the key (no transitive dependencies). [Example: A "Player" table includes "Player Skill Level" and "Player Rating." If "Player Rating" is determined by "Player Skill Level," it's a transitive dependency. Solution: Create a new "Player Skill Levels" table that maps skill levels to ratings.]

What is the requirement(s) of BCNF

Every attribute in a table should depend on the key, the whole key, and nothing but the key. [Example: This is a stronger version of 3NF, often achieved when 3NF is met, especially with multiple overlapping candidate keys.]

What is the requirement(s) of 4NF

The only kinds of multi-valued dependencies allowed in a table are multi-valued dependencies on the key. [Example: A table (Model, Color, Style) where Model has independent multiple Colors and multiple Styles. Solution: Split into "Model Colors Available" (Model, Color) and "Model Styles Available" (Model, Style).]

What is the requirement(s) of 5NF

It must not be possible to describe the table as being the logical result of joining some other (smaller, more fundamental) tables together without loss of information. [Example: A table showing (Person, Brand, Flavor) for ice cream, where this is derived from Person-Brand, Person-Flavor, and Brand-Flavor relationships. Solution: Store these three fundamental relationships in separate tables.]

Which normal form requires that row order should not convey information?

1NF. This means the order of rows in a table should not inherently represent any data or meaning. For example, if a table lists "The Beatles" members, their order shouldn't imply anything about their height unless an explicit "Height" column is provided.

Which normal form requires that data types within a single column must be consistent (no mixing)?

1NF. All values within a single column must be of the same data type. For instance, a "Height in Centimeters" column should only contain numerical values (e.g., 180), not a mix of numbers and text like "Tall."

Which normal form requires that every table must have a primary key?

1NF. Each table needs a unique identifier for its rows, known as a primary key. Without a primary key, it would be impossible to uniquely identify individual records, leading to potential data duplication and inconsistencies. For example, a table of "Beatles' Heights" needs a primary key like "Beetle" to ensure each Beatle has only one height entry.

Which normal form requires that repeating groups are not allowed?

1NF. This means you should not have multiple columns representing similar attributes (e.g., Item1, Item2) or store multiple values in a single column. Instead, each distinct item should be on its own row. For example, a player's inventory shouldn't be a single text string ("Arrows, Shields") or multiple columns ("Item1", "Item2"). Instead, each item should be a separate row linked to the player, with columns like "Player," "Item Type," and "Item Quantity."

Which normal form requires that each non-key attribute in the table must be dependent on the entire primary key? Explain using an example.

2NF. This rule applies to tables with composite primary keys (keys made of multiple columns). If a non-key attribute depends on only part of the composite key, it violates 2NF. For example, in a "Player Inventory" table with a primary key of (Player, Item Type), if "Player Rating" is also included, it violates 2NF because "Player Rating" depends only on "Player," not on the full (Player, Item Type) key. This can lead to deletion, update, and insertion anomalies. The solution is to create a separate "Player" table for "Player ID" and "Player Rating."

Which normal form requires that each non-key attribute in a table must depend on the key, the whole key, and nothing but the key (no transitive dependencies)? Explain using an example.

3NF. This means that a non-key attribute should not depend on another non-key attribute. For instance, in a "Player" table, if "Player Skill Level" determines "Player Rating" (e.g., Skill Level 1-3 = Beginner, 4-6 = Intermediate), then "Player Rating" transitively depends on "Player Skill Level" (which depends on the primary key, "Player ID"). This can cause inconsistencies if skill levels change but ratings are not updated. The solution is to remove "Player Rating" from the "Player" table and create a new "Player Skill Levels" table that maps skill levels to ratings.

Which normal form requires that every attribute in a table should depend on the key, the whole key, and nothing but the key? Explain using an example.

BCNF. BCNF is a stronger version of 3NF. While 3NF focuses on non-key attributes not depending on other non-key attributes, BCNF extends this to all attributes, including those that are part of candidate keys. This often addresses more complex scenarios with multiple overlapping candidate keys. In many practical cases, achieving 3NF will also result in BCNF.

Which normal form requires that the only kinds of multi-valued dependencies allowed in a table are multi-valued dependencies on the key? Explain using an example.

4NF. This addresses situations where multiple independent multi-valued facts are stored in a single table. For example, a table "Model Colors and Styles Available" with columns (Model, Color, Style) where a Model can have multiple Colors and multiple Styles, and these are independent of each other. If you add a new color for a model, you would have to add rows for that new color combined with every existing style. The solution is to split this into two separate tables: "Model Colors Available" (Model, Color) and "Model Styles Available" (Model, Style).

Which normal form requires that it must not be possible to describe the table as being the logical result of joining some other (smaller, more fundamental) tables together without loss of information? Explain using an example.

5NF. This form deals with join dependencies, where a table can be perfectly reconstructed by joining multiple smaller, more fundamental tables. For instance, a table showing which ice cream products (Brand, Flavor) each person is willing to eat might be implicitly defined by three underlying relationships: which brands offer which flavors, which people like which brands, and which people like which flavors. If this combined table is the only place these facts are stored, it can lead to update anomalies. The solution is to store these three fundamental pieces of information in separate tables: "Brand Flavors," "Person Brands," and "Person Flavors," from which the original table can be derived by joining.

What does a DB Admin from the hood say about BreachForum’s DB? (FBI raided them)

That One There Was A Violation Personally I Wouldn't Have It

Known & Predicted Violations:

1. 2NF (Partial Dependencies)

   • Forum tables likely used a composite PK like (user_id, _breacher_rank_id), and yet included user_email or user_ip in that table—depending only on user_id.

   • That would lead to redsundant rows with stale emails, IPs, or aliases duplicated across all breaches that user participated in.

2. 3NF (Transitive Dependencies)

   • Imagine rows storing breach_id, company, and company_domain. Since company_domain depends on company, not on the full key, you’d need to update every record if the naming convention changed—error‑prone and messy.

3. 4NF (Multivalued Dependencies)

   • They likely tried to condense multiple attributes per breach, e.g. (breach_id, stolen_emails, stolen_passwords, IPs, credit_cards), meaning each category could replicate data across many rows—leading to data explosion.

   • We saw enormous leaks: 212,000 members in one dump, then a full database in another—classic signs of unchecked redundancy.

4. 5NF (Join Anomalies)

   • If they had decomposed things into (user, breach), (breach, file_type), (user, privilege_level), but didn’t enforce proper key combinations, FBI analysts could join these to falsely associate users with data they never had access to—or miss connections that did exist.

What is the difference between 2NF and 3NF? Explain using an example and how to recognize.

The difference lies in the type of dependency they address: - **2NF** deals with **partial dependencies**: if a non-key attribute depends on only *part* of a composite primary key, thats 2NF violation. - **How to recognize 2NF violation:** Look for tables with a composite primary key. If a non-key attribute's value can be determined by only one part of that composite key, it's a 2NF violation. - **Example:** In a "Player Inventory" table with a composite primary key of (Player, Item Type), if "Player Rating" is also a column, it's a 2NF violation because "Player Rating" depends solely on "Player" (part of the key), not on the entire (Player, Item Type) key. - **3NF** deals with **transitive dependencies**: a non-key attribute depends on *another non-key attribute*, which in turn depends on the primary key. - **How to recognize 3NF violation:** Look for non-key attributes that determine the value of another non-key attribute. - **Example:** In a "Player" table, if "Player Skill Level" is a non-key attribute that determines "Player Rating" (another non-key attribute), then "Player Rating" has a transitive dependency on "Player Skill Level," violating 3NF.

Picture doesn’t violaTE 2nf BUT DOES VIOLATE 3NF

What is the difference between 3NF and BCNF? Explain using an example and how to recognize.

The difference is subtle and primarily concerns candidate keys:

- **3NF** requires that all non-key attributes are dependent on the key, the whole key, and nothing but the key (no transitive dependencies). It allows for certain situations with overlapping candidate keys.

- **BCNF** is a stronger form. It requires that *every determinant* (an attribute or set of attributes that determines another attribute) must be a candidate key. This means if an attribute (or set of attributes) determines another attribute, and that determinant is *not* a candidate key, then it violates BCNF. BCNF addresses cases where a non-key attribute determines part of a composite candidate key.

- **How to recognize BCNF violation (and difference from 3NF):** Look for tables with multiple overlapping candidate keys. A violation occurs if a non-key attribute (or a subset of a candidate key) determines another attribute, and that determinant is not itself a superkey (a candidate key or a superset of one).

- **Example:** Consider a table `(Student, Course, Instructor)` where:

1. `(Student, Course)` is the primary key.

2. Each `Course` has only one `Instructor`. (`Course` -> `Instructor`)

3. Each `Instructor` teaches only one `Course`. (`Instructor` -> `Course`)

This table is in 3NF because `Instructor` (a non-key attribute) depends on `Course` (part of the primary key), but `Course` is *not* a non-key attribute. However, it violates BCNF because `Instructor` is a determinant for `Course`, but `Instructor` itself is not a candidate key (because `(Instructor, Student)` would be a candidate key, but `Instructor` alone isn't). The solution is to decompose this into `(Student, Course)` and `(Course, Instructor)`. This scenario is less common in typical database designs, which is why 3NF is often considered sufficient for most practical purpos

What single sentence encompasses 2NF and 3NF, Boyce-Codd NF and part of 1NF and should you always keep in mind when designing attributes in a Database?

Every attribute should depend on the key, the whole key (every single attribute if PK is comosite), and nothing but the key (you shouldn’t make it such that an attribute depends on anything else then the whole primary, so you can’t have [player skill rating] and [player skill level])

What is the difference between 4NF and 5NF? Explain using an example and how to recognize.

The difference lies in the types of dependencies they address that are not resolved by previous normal forms: - **4NF** deals with **multi-valued dependencies (MVDs)**. An MVD exists when a single value of one attribute is associated with a *set* of values for another attribute, and this association is independent of other attributes in the table. - **How to recognize 4NF violation:** Look for a table where one attribute (A) has multiple values for another attribute (B), and also multiple values for a third attribute (C), and the set of B values is independent of the set of C values for a given A. This often results in redundant information that cannot be eliminated by previous normal forms without losing data. - **Example:** A table `(Model, Color, Style)` for custom birdhouses. If a "Prairie" model is available in "Brown" and "Beige" colors, *and independently* in "Bungalow" and "Schoolhouse" styles, storing all this in one table creates redundancy. Adding a new color (e.g., "Green") for "Prairie" would require adding rows for both "Green-Bungalow" and "Green-Schoolhouse." This is a 4NF violation. The solution is to decompose into two tables: `(Model, Color)` and `(Model, Style)`. - **5NF** (also known as Project-Join Normal Form - PJNF) deals with **join dependencies**. A table is in 5NF if it cannot be decomposed into smaller tables without loss of information. This means the information in the table cannot be derived by joining two or more smaller tables that represent more fundamental facts without potentially creating spurious tuples (incorrect combinations of data). This is relevant when a relationship inherently depends on the interaction of three or more attributes. - **How to recognize 5NF violation:** This is the most complex normal form to recognize and rarely encountered in practical database design unless there are very complex multi-way relationships. A 5NF violation exists when a table can be losslessly decomposed into three or more smaller tables, but not into two. It signifies that the facts represented in the table are more naturally represented as separate, simpler relationships. - **Example:** A table showing `(Person, Brand, Flavor)` indicating which ice cream products a person is willing to eat. If this table is derived from three independent facts: 1) Which brands offer which flavors (`Brand Flavors`), 2) Which people like which brands (`Person Brands`), and 3) Which people like which flavors (`Person Flavors`), and all three are necessary to define the original relationship without loss. If these three fundamental relationships are not stored separately, the `(Person, Brand, Flavor)` table might be in 4NF but violate 5NF. For example, if Susie likes "Frosty's" brand and "Strawberry" flavor, and "Frosty's" offers "Strawberry," Susie implicitly likes "Frosty's Strawberry." If the original table only stores what she explicitly likes, and she later likes a new brand, you'd have to add many rows. The solution is to store the three fundamental relationships in separate tables: `(Brand, Flavor)`, `(Person, Brand)`, and `(Person, Flavor)`.