relations/normalization

7 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Normalization overview: 1NF, 2NF, 3NF concepts

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The standard normal forms are: 1NF (First Normal Form) requires atomic values in each column with no repeating groups; 2NF (Second Normal Form) eliminates redundant data dependent on part of a composite primary key; 3NF (Third Normal Form) eliminates data dependent on non-key attributes, removing transitive dependencies. Additional normal forms beyond 3NF exist (4NF, 5NF, 6NF, EKNF, ETNF, DKNF) but are not commonly used in basic database design.

3NF: Third Normal Form - eliminate transitive dependencies

Third Normal Form (3NF) removes data that is dependent on other non-key attributes, eliminating transitive dependencies. For example, in a suppliers table with supplier_id as primary key, if city and state depend on zip_code (a non-key attribute), move city, state, and zip_code to a separate zip_codes table keyed by zip_code, then reference zip_code from the suppliers table as a foreign key.

Normalization fundamental concept

Normalization is the process of organizing data in a database to reduce redundancy (duplication) and improve data integrity (accuracy and consistency). It helps eliminate data anomalies and makes databases easier to understand and maintain.

First Normal Form (1NF) definition

1NF requires that each column should hold a single, indivisible atomic value with no repeating groups of data within a single cell. For example, instead of storing a complete address in one column, break it into separate columns for street address, city, state, and zip code.

Second Normal Form (2NF) definition

2NF applies to tables with composite primary keys (made up of two or more columns). It ensures all non-key attributes are fully dependent on the entire composite primary key, not just part of it. Remove partially dependent attributes and place them in a separate table where they are fully dependent on that table's primary key.

Third Normal Form (3NF) definition

3NF requires removing data that is dependent on other non-key attributes, eliminating transitive dependencies. For example, if city and state are dependent on zip_code (a non-key attribute), move those attributes to a separate table keyed by zip_code.

Benefits of normalization

Normalization reduces data redundancy, improves data integrity, and prevents anomalies: insertion anomalies (difficulty adding new data), update anomalies (updating information in multiple rows), and deletion anomalies (losing valuable information when deleting related data).

Give your agent this brain