Data Normalization

A table is in First Normal Form (1NF) when it meets the following atomicity and uniqueness criteria:

• Atomic Values: Each column contains only indivisible, single-valued data (no repeating groups or arrays).
• Unique Rows: Each row is uniquely identifiable, typically by a primary key.
• Column Consistency: All values in a column are of the same data type.

Example: A CustomerOrders table storing multiple phone numbers in a single comma-separated cell violates 1NF. To comply, you would create a separate CustomerPhones table linked by a foreign key.

A table is in Second Normal Form (2NF) if it is in 1NF and every non-prime attribute is fully functionally dependent on the entire primary key. This eliminates partial dependencies, where an attribute depends on only part of a composite key.

Process:

• Identify composite primary keys.
• For any attribute that depends on only a subset of the key columns, move it to a new table.

Example: In an OrderDetails(OrderID, ProductID, ProductName, Quantity) table, ProductName depends only on ProductID, not the full (OrderID, ProductID) key. To achieve 2NF, move ProductName to a Products table.

A table is in Third Normal Form (3NF) if it is in 2NF and contains no transitive dependencies. This means no non-prime attribute is dependent on another non-prime attribute; all depend directly on the primary key.

Rule (Boyce-Codd Simplification): Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key.

Example: In a Students(StudentID, AdvisorID, AdvisorDepartment) table, AdvisorDepartment depends on AdvisorID, which is not the primary key. This is a transitive dependency. To achieve 3NF, create separate Advisors and Students tables.

Boyce-Codd Normal Form (BCNF) is a stronger version of 3NF. A table is in BCNF if, for every one of its non-trivial functional dependencies X → Y, X is a superkey (a set of attributes that uniquely identifies a row).

BCNF addresses residual anomalies not caught by 3NF, particularly in tables with:

• Overlapping composite candidate keys.
• Non-key determinants (an attribute that determines another but is not a candidate key).

Violation Example: In a table Enrollments(Student, Course, Instructor) where each course has one instructor, but a student can take multiple courses, the dependency Course → Instructor exists where Course is not a superkey. BCNF requires decomposing this table.

A table is in Fourth Normal Form (4NF) if it is in BCNF and has no non-trivial multi-valued dependencies (MVDs) except those implied by its candidate keys. An MVD X ↠ Y exists when for a single value of X, there is a set of associated values for Y, independent of other attributes.

4NF eliminates situations where unrelated multi-valued facts are stored in the same table, requiring redundant row combinations.

Example: A Physicians(Doctor, Specialty, Language) table where a doctor has multiple specialties and speaks multiple languages independently. Storing all combinations creates spurious tuples. 4NF requires splitting into DoctorSpecialties and DoctorLanguages tables.

Fifth Normal Form (5NF), or Project-Join Normal Form (PJNF), addresses join dependencies. A table is in 5NF if every join dependency in the table is implied by its candidate keys.

A join dependency means the table can be reconstructed without loss of information by joining its projections onto certain subsets of attributes. 5NF decomposition ensures that facts are not represented by the coincidence of row entries across multiple columns.

Practical Use: 5NF is primarily of theoretical interest. It deals with complex, cyclic multi-way relationships that are rare in typical business schemas. Decomposing to 5NF can eliminate the final, subtle forms of redundancy but may increase join complexity for queries.

The foundational rule requiring each table cell to contain a single, atomic value and each record to be uniquely identifiable. This eliminates repeating groups.

Key Actions:

• Ensure each column contains only scalar values (no lists or sets).
• Define a primary key for the table.
• Remove any columns that store multiple values in a single field.

Example: A CustomerOrders table storing multiple product IDs in a single comma-separated Products column violates 1NF. To comply, you would create a separate OrderItems table with one row per product ordered.

Builds on 1NF by requiring that all non-key attributes be fully dependent on the entire primary key. This eliminates partial dependencies, which occur in tables with composite primary keys.

Key Actions:

• Identify attributes that depend on only part of a composite key.
• Move those attributes to a new table where they depend on the whole key.

Example: In an OrderDetails table with a composite key of (OrderID, ProductID), the ProductName depends only on ProductID, not the full key. To achieve 2NF, move ProductName to a separate Products table linked by ProductID.

Builds on 2NF by requiring that all attributes depend only on the primary key, not on other non-key attributes. This eliminates transitive dependencies.

Key Actions:

• Identify non-key columns that are dependent on another non-key column.
• Move those attributes to a new table.

Example: In a Customers table with columns CustomerID, CustomerName, ZipCode, City, and State, the City and State depend on ZipCode, not directly on CustomerID. To achieve 3NF, create a separate Ziplookup table for ZipCode, City, State.

A stricter version of 3NF that addresses anomalies not resolved by 3NF, particularly in tables where:

• There are multiple candidate keys.
• Those candidate keys are composite.
• They overlap.

Rule: For every functional dependency X → Y, X must be a superkey.

Example: In a table tracking which Professor teaches a Course in a given Semester, with a rule that each professor can only teach one course per semester, but a course can be taught by multiple professors. If dependencies create a scenario where a non-superkey determines part of a key, BCNF requires decomposing the table to resolve it.

The intentional introduction of redundancy into a normalized database design to optimize read performance for specific query patterns, often at the expense of increased storage and update complexity.

Common Techniques:

• Pre-joined Tables: Storing frequently joined data in a single table to avoid costly JOIN operations at query time.
• Aggregate Tables: Storing pre-calculated sums, counts, or averages (e.g., daily sales totals).
• Repeated Columns: Duplicating a frequently accessed column from a parent table into a child table.

Use Case: In a data warehouse or analytical database supporting complex, read-heavy reporting, denormalization is standard practice to reduce query latency.

In machine learning and vector search, normalization refers to scaling numerical vectors to a standard magnitude, most commonly unit length (L2 norm). This is critical for distance-based algorithms.

Process:

1. Calculate the vector's L2 norm: sqrt(x₁² + x₂² + ... + xₙ²).
2. Divide each vector component by this norm.

Result: The transformed vector has a magnitude of 1. This ensures that similarity measures like cosine similarity depend only on the angle between vectors, not their raw magnitude, which is often irrelevant for semantic comparison.

Application: Essential for creating consistent embeddings for Retrieval-Augmented Generation (RAG) and semantic search in vector databases.

Data cleansing (or data cleaning) is the process of detecting and correcting (or removing) corrupt, inaccurate, or irrelevant records from a dataset. While normalization restructures data to eliminate redundancy, cleansing directly fixes errors within the data values themselves.

Key activities include:

• Correcting syntactical errors (e.g., misspellings, inconsistent formatting).
• Removing duplicate records.
• Filling or flagging missing values.
• Validating and correcting data against known business rules or reference datasets.

Cleansing is often a prerequisite step before normalization, ensuring the raw data is accurate enough to be structured properly.

Data transformation is the broader process of converting data from one format, structure, or value into another. Normalization is a specific type of structural transformation applied to relational data.

Transformation encompasses:

• Format conversion: Changing data from CSV to Parquet, or XML to JSON.
• Value mapping: Standardizing values (e.g., 'USA' to 'United States').
• Structural changes: Flattening nested JSON, pivoting tables, or applying normal forms.
• Derivation: Creating new calculated columns from existing data.

These operations are core to ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) pipelines, preparing data for analysis or machine learning.

Schema validation is the process of verifying that a data structure conforms to a predefined formal specification, or schema. This ensures data matches expected formats, types, and constraints before processing.

Key aspects include:

• Data type checking: Verifying a field contains an integer, string, or date.
• Constraint enforcement: Checking for required fields (nullability), value ranges, and unique keys.
• Structural validation: Ensuring JSON or XML documents adhere to a defined hierarchical structure.

Tools like JSON Schema, Avro Schema, and XML Schema (XSD) are used to define these rules. Validation acts as a gatekeeper, while normalization restructures validated data for optimal storage.

Data integrity refers to the overall accuracy, consistency, and reliability of data throughout its lifecycle. It is the overarching goal that techniques like normalization and validation aim to achieve.

Integrity is maintained through:

• Entity integrity: Enforcing primary keys to ensure each table row is unique.
• Referential integrity: Ensuring foreign keys correctly reference existing primary keys in related tables.
• Domain integrity: Validating that data falls within defined permissible values (e.g., a status field can only be 'active' or 'inactive').
• User-defined integrity: Enforcing custom business rules via CHECK constraints or application logic.

A normalized database design is a foundational method for promoting and enforcing data integrity.

Schema evolution is the practice of managing changes to a data schema over time while maintaining compatibility with existing data and applications. In systems using normalized data, schema changes must be carefully managed.

Core compatibility modes:

• Backward compatibility: New schema can read data written with the old schema. (Adding an optional column).
• Forward compatibility: Old schema can read data written with the new schema. (Removing a column the old schema didn't require).

Evolution strategies include:

• Using schema registries (e.g., for Avro, Protobuf) to manage versions.
• Writing database migration scripts to alter table structures.
• Implementing soft deletes or versioned tables instead of dropping columns. This prevents schema drift and breaks in downstream pipelines.

Data profiling is the automated process of examining existing data to collect statistics and metadata. It is a critical discovery phase that informs both cleansing and normalization efforts.

Profiling analyzes:

• Structure: Data types, lengths, and nullability.
• Content: Value patterns, frequency distributions, and statistical summaries (min, max, mean).
• Relationships: Primary/foreign key candidates, inferred dependencies between columns.
• Quality: Initial assessment of completeness, uniqueness, and potential anomalies.

Results help answer:

• What normal form violations exist (e.g., repeating groups, transitive dependencies)?
• What data requires cleansing before normalization?
• What validation rules or constraints should be defined in the final schema?