4.10

Concepts of Database NormalizationBasic Terms

• Ent: Refers to the entity, indicating that each B value relates to at most one entity.

• Simple or Composite: Defines if a B value is straightforward or paired with others.

• Functional Dependence: The dependency of one column on another, reflecting a business rule applicable to the database design.

• Normalization: The process organized to reduce data redundancy within a database management system.

Relationships

• Functional Dependence: Illustrated as A depends on B (A | B).

• Business Rules: Establish connectivity and data relationships in the database.

Designing Tables

• Normal Forms

• Rules for Tables: Ensuring minimal redundancy by following normalization principles, leading to more effective database management.

Table Structure Principles

• Primary Key Conditions

  • Primary Key: Unique identifier for each row; every primary key value appears in exactly one row.

  • Non-key Columns: Depend on the primary key; each non-key cell contains exactly one value without duplicates.

• Row Uniqueness

  • No Duplicate Rows: Every row must contain a different primary key value, ensuring unique identification.

• Dependencies in Tables

  • Non-key Columns Dependence on Keys: Non-key columns must depend on the entire primary key; they cannot partially depend on a composite primary key.

Normal Form Implications

• Simple Primary Key: Automatically ensures the table conforms to the Second Normal Form.

• Third Normal Form Compliance Conditions for a Table in Third Normal Form

  • No Columns Dependence: Non-key columns should depend only on the whole key, the entirety of the key, and nothing but the key.

  • Inheritance of Normal Forms: A table in Third Normal Form also complies with First and Second Normal Forms, showcasing a tiered compliance structure.

First Normal Form (1NF)

• A table is in First Normal Form if all the underlying domains contain atomic (indivisible) values, meaning each column should contain unique values without any repeating groups or arrays.

• In 1NF, each entry in a column must be of the same data type. Additionally, rows must be unique from one another based on the primary key.

Second Normal Form (2NF)

• To be in Second Normal Form, a table must first meet all the criteria of the First Normal Form.

• Moreover, all non-key attributes must be fully functionally dependent on the primary key. This means that there should be no partial dependency of any non-key attribute on a composite primary key; each attribute must rely entirely on the whole key.

• Achieving 2NF ensures that there are no redundancies related to specific records, leading to improved data integrity and reduced duplication.