Database Concept and Design: Data Redundancies

Database Concept and Design

• A repeating group is a group of multiple entries of the same type that can exist for any single key attribute, which proves the presence of data redundancies; relational tables must not contain repeating groups.
• Identifying primary keys is essential, and they can be an attribute or a combination of attributes.

Conversion to First Normal Form

• Step 1: Eliminate the repeating groups
• Step 2: Identify the primary key
• Use a dependency diagram to depict all dependencies among table attributes, which helps to get an overview of all relationships and makes it less likely that an important dependency will be overlooked.

Normalization

• Normalization is a process for evaluating and correcting table structures to minimize data redundancies, which reduces data anomalies.
• Normalization involves a series of stages called normal forms: First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).
• 2NF is better than 1NF, and 3NF is better than 2NF, but the highest level of normalization is not always desirable.
• De-normalization produces a lower normal form, which increases performance but increases data redundancy.

The Need for Normalization

• Normalization eliminates anomalies, ensures data consistency, and is used while designing and re-designing a database structure.
• It analyzes the relationship among the attributes within each entity and determines if the structure can be improved.

Anomalies

• Update anomaly
• Delete anomaly
• Insert anomaly

Normalization Process

• The objective is to ensure that each table conforms to the concept of well-formed relations.
• Each table represents a single subject, and no data item is unnecessarily stored in more than one table.
• All nonprime attributes in a table are dependent on the primary key, and each table is void of insertion, update, and deletion anomalies.

Functional Dependence Concepts

• Functional dependence: The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B.
• Fully functional dependence: Attribute A determines attribute B if all of the rows in the table that agree in value for attribute A also agree in value for attribute B.

Types of Functional Dependencies

• Partial dependency: A functional dependence in which the determinant is only part of the primary key.
• Transitive dependency: A functional dependence where X → Y, Y → Z, and X is the primary key.

Conversion to Second and Third Normal Forms

• Conversion to Second Normal Form:
  • Step 1: Make new tables to eliminate partial dependencies.
  • Step 2: Reassign corresponding dependent attributes.
• Conversion to Third Normal Form:
  • Step 1: Make new tables to eliminate transitive dependencies.
  • Step 2: Reassign corresponding dependent attributes.