database-relations/normalization

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

Second Normal Form (2NF) example with OrderItems and Products

A table OrderItems_Unnormalized with composite key (order_id, product_id) violates 2NF because product_name and product_price depend only on product_id (part of the key), not the full key. To achieve 2NF, create a separate Products table with product_id as primary key, containing product_name and product_price. OrderItems_2NF keeps only order_id, product_id (as composite key), quantity, and order_date, with a foreign key to Products(product_id).

Third Normal Form (3NF) example with suppliers and zip_codes

A suppliers table storing supplier_id, supplier_name, zip_code, city, and state violates 3NF because city and state depend on zip_code, not on supplier_id. To achieve 3NF, create a separate zip_codes table with zip_code as primary key, containing city and state. The suppliers table keeps supplier_id, supplier_name, and zip_code as a foreign key to zip_codes.

Database normalization reduces redundancy and improves data integrity

Normalization is the process of organizing data in a database to reduce redundancy (duplication) and improve data integrity (accuracy and consistency). It reduces data redundancy so information is stored in one place rather than repeated, improving data integrity by minimizing inconsistencies. Normalization prevents insertion anomalies (difficulty adding new data due to missing related information), update anomalies (updating the same information in multiple rows), and deletion anomalies (accidentally losing valuable information when deleting seemingly unrelated data). A normalized database is more logically structured and easier to understand, query, and modify.

First Normal Form (1NF) - Atomic Values

1NF requires that each column hold a single, indivisible value with no repeating groups of data within a single cell. For example, instead of storing a full address like '123 Main St, City, USA' in one column, break it into separate atomic columns: street_address, city, state, and zip_code.

Second Normal Form (2NF) - Eliminate Redundant Data Dependent on Part of the Key

2NF applies to tables with composite primary keys (made up of two or more columns). It ensures that all non-key attributes are fully dependent on the entire composite primary key, not just part of it. Remove partially dependent attributes from the table and place them in a separate table where they are fully dependent on that table's primary key. For example, in an OrderItems table with composite key (order_id, product_id), attributes like product_name and product_price depend only on product_id (part of the key), not the entire key. These should be moved to a separate Products table.

Third Normal Form (3NF) - Eliminate Redundant Data Dependent on Non-Key Attributes

3NF removes data that is dependent on other non-key attributes by eliminating transitive dependencies. For example, in a suppliers table, if city and state are dependent on zip_code (a non-key attribute) rather than the primary key supplier_id, move those dependent attributes into a separate table keyed by zip_code. This creates a relationship where suppliers references the zip_codes table via a foreign key.

Give your agent this brain