Database Concepts Norm L6

Introduction to Normalization

• Definition of Normalization: The process of evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies.

• Role in Design: Normalization assigns attributes to tables based on determination. It is used concurrently with Entity Relationship (ER) modeling to produce a robust database design.

• Key Normal Forms:

  • First Normal Form ($1NF$)

  • Second Normal Form ($2NF$)

  • Third Normal Form ($3NF$)

  • Boyce-Codd Normal Form ($BCNF$)

  • Fourth Normal Form ($4NF$)

• Structural Perspective: Higher normal forms are generally better than lower normal forms. A properly designed $3NF$ structure typically meets the requirements of $4NF$.

• Denormalization: This process produces a lower normal form from a higher one. It results in increased performance for certain tasks but at the cost of greater data redundancy and potential anomalies.

The Need for Normalization

Database designers utilize normalization in two primary scenarios:

1. Designing New Databases: Based on user business requirements, the designer constructs a data model (e.g., using Crow’s Foot notation ERDs), analyzes attribute relationships within entities, and determines if the structure needs improvement.

2. Modifying Existing Structures: When improving existing data structures such as flat files, spreadsheets, or legacy database structures. The designer analyzes attribute relationships to create an appropriate relational design via normalization.

Database Anomalies

Anomalies are flaws occurring from poor planning or storing data in flat structures. These issues must be addressed to ensure data integrity and consistency.

• Update Anomaly: Occurs when the structure invites data inconsistencies.

  • Example: "Elect.Engineer" may be entered as "Elect.Eng.", "El. Eng.", or "EE".

  • Example: The structure might allow Two employees (e.g., John G. News and Alice K. Johnson) with the same job classification to have different billing rates incorrectly.

• Reporting Anomaly: Caused by multivalued attributes.

  • If multiple employees working on a project are stored in a single cell, it is difficult to identify them individually or answer queries like "How many employees are working on the Starlight project?".

  • Simple string variations (e.g., "Database Designer" vs "DB Designer") make filtering and aggregating hours by job class nearly impossible through application programming.

• Adding, Updating, and Deleting Anomalies: Caused by data redundancy.

  • Example: Changing Alice K. Johnson's job classification would require updating multiple rows because employee data is repeated across every project they work on.

Functional Dependence Concepts

• Functional Dependence: Attribute $B$ is fully functionally dependent on attribute $A$ if each value of $A$ determines one and only one value of $B$.

  • Notation: $A \rightarrow B$ (A functionally determines B).

  • $A$ is the determinant attribute; $B$ is the dependent attribute.

• Generalized Functional Dependence: Attribute $A$ determines $B$ if all rows that agree in value for $A$ also agree in value for $B$.

• Fully Functional Dependence (Composite Key): If attribute $B$ is functionally dependent on a composite key $A$ but not on any subset of that key, it is fully functionally dependent on $A$.

• Partial Dependency: A condition where an attribute is dependent on only a portion (subset) of the primary key. This assumes a composite primary key is in use.

• Transitive Dependency: An attribute functionally depends on another non-key attribute (an attribute that is not part of any candidate key).

The Normalization Process

To achieve a good set of normalized tables, the designer must ensure:

1. Each table represents a single subject (e.g., only COURSE data in a COURSE table).

2. Minimum controlled redundancy (data updated in only one place).

3. All nonprime attributes depend on the entire primary key and nothing but the primary key.

4. No insertion, update, or deletion anomalies.

5. Each row/column intersection contains a single value (no groups of values).

Conversion to First Normal Form ($1NF$)

Relational tables must satisfy $1NF$. A table is in $1NF$ if all key attributes are defined, there are no repeating groups, and all attributes are dependent on the primary key.

• Step 1: Eliminate Repeating Groups: Ensure each row defines a single entity instance and every cell has a single value. In the construction company example, the project details (e.g., "Evergreen") previously contained lists of employees; these must be flattened into individual rows.

• Step 2: Identify the Primary Key: In the flattened table, $PROJ\_NUM$ alone is insufficient. A composite key of $(PROJ\_NUM, EMP\_NUM)$ is required to uniquely identify each row.

• Step 3: Identify All Dependencies:

  • Primary Dependency: $(PROJ\_NUM, EMP\_NUM) \rightarrow PROJ\_NAME, EMP\_NAME, JOB\_CLASS, CHG\_HOUR, HOURS$.

  • Partial Dependencies: $PROJ\_NUM \rightarrow PROJ\_NAME$ and $EMP\_NUM \rightarrow EMP\_NAME, JOB\_CLASS, CHG\_HOUR$.

  • Transitive Dependency: $JOB\_CLASS \rightarrow CHG\_HOUR$.

Conversion to Second Normal Form ($2NF$)

A table is in $2NF$ when it is in $1NF$ and includes no partial dependencies.

• Step 1: Make New Tables: Create a separate table for each partial dependency determinant identified in the $1NF$ diagram.

• Step 2: Reassign Attributes:

  • PROJECT: $(PROJ\_NUM, PROJ\_NAME)$

  • EMPLOYEE: $(EMP\_NUM, EMP\_NAME, JOB\_CLASS, CHG\_HOUR)$

  • ASSIGNMENT: $(PROJ\_NUM, EMP\_NUM, ASSIGN\_HOURS)$

Conversion to Third Normal Form ($3NF$)

A table is in $3NF$ when it is in $2NF$ and contains no transitive dependencies.

• Step 1: Identify Transitive Dependencies: In the EMPLOYEE table, $CHG\_HOUR$ is determined by $JOB\_CLASS$, which is not a key.

• Step 2: Make New Tables: Create a table with the transitive determinant as the PK.

  • JOB: $(JOB\_CLASS, CHG\_HOUR)$

• Step 3: Reassign Attributes: Remove the dependent attribute from the original table.

  • Revised EMPLOYEE: $(EMP\_NUM, EMP\_NAME, JOB\_CLASS)$

Advanced Normal Forms

Boyce-Codd Normal Form ($BCNF$)

• Requirement: Every determinant in the table must be a candidate key.

• Relation to $3NF$: $BCNF$ is equivalent to $3NF$ when the table contains only one candidate key. It is violated only when a table has multiple candidate keys and a non-key attribute determines part of a key.

Fourth Normal Form ($4NF$)

• Requirement: A table is in $4NF$ when it is in $3NF$ and has no independent multivalued dependencies.

• Rule: No row may contain two or more multivalued facts about an entity. All attributes must be dependent on the primary key but independent of each other.

Practical Design Considerations

• Surrogate Keys: System-defined, automatically incremented numeric values used when the natural primary key is unsuitable or non-existent.

• Atomicity: Attributes should be atomic (cannot be further subdivided). Example: Separating "Name" into $EMP\_LNAME$, $EMP\_FNAME$, and $EMP\_INITIAL$.

• Granularity: The level of detail represented by values in a row. Design should ensure the PK supports the required granularity.

• Historical Accuracy: Some data (like billing rates) must be frozen in time. In the finalized database, $ASSIGN\_CHG\_HOUR$ is stored in the ASSIGNMENT table to maintain the rate charged when the work was done, even if the rate in the JOB table changes later.

Denormalization Revisited

While normalization reduces anomalies, it increases the number of tables.

• Performance vs. Redundancy: Joining many tables requires more $I/O$ operations and processing logic, reducing system speed.

• Trade-off: Designers may denormalize specific portions of a database to meet end-user demands for fast query performance, though this reintroduces some data redundancy.

Data Modeling Checklist

Business Rules and Naming

• Document and verify all rules with end users. Rules must be precise and used to identify entities and constraints.

• Naming Conventions: Use familiar nouns for entities, entity abbreviations as prefixes for attributes, and specific suffixes ($\_ID$, $\_NUM$, $\_CODE$) for Primary Keys. Relationships should use active or passive verbs.

Entities and Attributes

• Each entity represents a single subject and should be in $3NF$ or higher.

• Attributes must be atomic. Document default values and constraints.

• Avoid redundant attributes unless required for performance or historical accuracy.

Relationships and ER Model

• Clearly define participation, connectivity, and cardinality.

• Validate the model against expected insert, update, and delete processes.

• Minimal Data Rule: "All that is needed is there, and all that is there is needed."

Questions & Discussion

• Question 1: Why is a dependency diagram important?

  • Response: It depicts all dependencies within a table structure, helping to get an overview of relationships and making it less likely that an important dependency (central or undesirable) will be overlooked.

• Question 2: Provide a motivation for and against denormalizing your database.

  • Motivation for: Faster query performance and reduced processing overhead for complex reports needing data from many tables.

  • Motivation against: Increased data redundancy, less efficient updates, cumbersome indexing, and risk of data anomalies (Update, Insertion, Deletion).

• Exercise Instruction: Students are directed to complete the DBC161 Normalization Exercise (Activity 2).