educative.io

Question about answer

Courses
,
#1

Aren’t they all determined by cust id? depending on the id the zip code, state, etc… are determined. I just see all of them as independent attributes

Course: Database Design Fundamentals for Software Engineers - Learn Interactively
Lesson: Solution to Exercise 2 - Database Design Fundamentals for Software Engineers

#2

Hi @Christian_Jaramillo !!
Let’s break down the normalization process and the dependencies in the context of the Customer and Zip Code tables.

In a well-designed database, the goal of normalization is to eliminate redundancy and ensure data integrity by organizing data efficiently. Each normal form addresses specific types of data dependencies.

  1. First Normal Form (1NF):
    This requires that each attribute (or field) in a table must hold a single value, and there should be no repeating groups or arrays. Your initial Customer table satisfies this requirement, as each cell contains a single value.

  2. Second Normal Form (2NF):
    This form addresses the concept of partial dependencies. A table is in 2NF if it’s in 1NF and every non-prime attribute (attributes not part of the primary key) is fully functionally dependent on the entire primary key.

In your initial Customer table, it seems like Cust_Id is the primary key and every other attribute is directly dependent on it. Therefore, the table is in 2NF.

  1. Third Normal Form (3NF):
    This form deals with transitive dependencies. A table is in 3NF if it’s in 2NF and every non-prime attribute is non-transitively dependent on the primary key.

Here’s where the transitive dependency comes into play: In your initial table, the area, city, and state attributes are determined by the zip code. While they are not directly dependent on the primary key (Cust_Id), they are indirectly dependent through the zip code.

The normalization process suggests breaking down the table to remove this transitive dependency. By creating a separate Zip Code table with zip, area, city, and state attributes, and then referencing the zip code as a foreign key in the Customer table, you’re ensuring that the attributes are only dependent on the primary key of the Zip Code table.

So, in summary, while the attributes might seem independent, normalization rules focus on functional dependencies and data integrity. In the context of the relational model, transitive dependencies can lead to data anomalies and redundancy, which is why the normalization process aims to eliminate them.
I hope it helps. Happy Learning :blush: