Functional Dependencies and 1NF Normalization

The Problem: Why a Single Large Table is a Bad Idea

Imagine we're building a database for a university and decide to store all student and course information in a single table. It might look something like this:

At first glance, this seems simple. But this design suffers from serious issues:

• Data Redundancy: Notice how the information for "C101, Intro to Python, Dr. Sharma" is repeated for every student enrolled in that course. This wastes space and, more importantly, creates opportunities for errors.
• Anomalies: This redundancy leads to problems when you try to insert, update, or delete data. These problems are called anomalies.

To understand these anomalies better, let's watch a short video that explains them with a clear example.

As you can see, these anomalies make our database unreliable. The solution is normalization: a process of breaking down large, problematic tables into smaller, well-structured ones. The first step in normalization is to understand the relationships between the data, which we do using functional dependencies.

Functional Dependencies: The Rules of Your Data

A functional dependency is a constraint or a rule that describes the relationship between attributes (columns) in a table.

We say that an attribute B is functionally dependent on an attribute A if, for any given value of A, we can uniquely determine the value of B. We write this as:

A → B ("A determines B")

The formal rule is:

In a table, for any two rows, if the values in column A are the same, then the values in column B must also be the same.

Let's look at our example table again:

Here are some functional dependencies:

• student_id → student_name: For any student_id (e.g., 'S2'), the student_name ('Ben') is always the same. This is a valid FD.
• course_id → course_name: For any course_id (e.g., 'C101'), the course_name ('Intro to Python') is always the same. This is a valid FD.
• student_name → student_id: Is this valid? What if two students are named 'Ben'? We couldn't uniquely determine their ID. So, student_name does not determine student_id. This is an invalid FD.

The following video provides a great introduction to this concept, using an analogy to mathematical functions that should be familiar to you.

Functional dependencies are the building blocks of database normalization. By identifying all the FDs in a table, we can understand its structure and figure out how to break it down correctly.

For a more formal and structured definition, which is useful for exam preparation, you can refer to the following resource.

First Normal Form (1NF): Ensuring Atomicity

Now that we understand anomalies and functional dependencies, we can begin normalizing. The first step is achieving the First Normal Form (1NF).

A table is in 1NF if it satisfies a few basic rules, but the most important one is:
Every attribute (column) must hold only atomic (indivisible) values.

This means you cannot have multiple values stuffed into a single cell. For example, a phone_numbers column cannot contain '12345, 67890'.

Let's look at a table that is not in 1NF:

The emp_skills column violates 1NF because it contains multiple, comma-separated values. To fix this and bring the table into 1NF, we have two main options.

The following reading explains these rules and conversion methods very clearly.

As the article shows, the two ways to achieve 1NF are:

1. Expanding the Table: Repeat the employee information for each skill. This makes the table 1NF but creates a lot of redundancy (e.g., John's emp_id and emp_name are repeated).

   emp_id	emp_name	emp_skill
   1	John	Python
   1	John	JavaScript
   2	Darth	HTML
   2	Darth	CSS
   2	Darth	JavaScript

2. Decomposition: Break the original table into two separate tables: one for employees and one for skills, linked by emp_id. This is the preferred approach.

   Employee Table:

   emp_id	emp_name
   1	John
   2	Darth

   Employee_Skill Table:

   emp_id	emp_skill
   1	Python
   1	JavaScript
   2	HTML
   2	CSS
   2	JavaScript

Decomposition is the essence of normalization. By achieving 1NF, we have solved the problem of multi-valued attributes and have taken the first step toward a clean, robust database design.

Conclusion

In this lesson, we've laid the groundwork for good database design. You learned why stuffing all your data into one large table is a bad idea and how it leads to data redundancy and update anomalies.

Key Takeaways:

• Poorly designed tables suffer from insertion, deletion, and updation anomalies, making them unreliable.
• Functional Dependencies (FDs) are rules (A → B) that formally describe how attributes in a table relate to each other. Identifying them is key to normalization.
• Normalization is the process of decomposing tables to reduce redundancy and eliminate anomalies.
• First Normal Form (1NF) is the first step, requiring that all table columns contain only atomic (single) values.

Preview of the Next Lesson:
Achieving 1NF is essential, but it doesn't solve all our problems. As you saw in the "expanding the table" method, a 1NF table can still have significant data redundancy. In our next lesson, "Normalize a database schema to Second (2NF) and Third Normal Form (3NF)," we will learn how to use functional dependencies to identify and eliminate these more subtle forms of redundancy, bringing us much closer to a truly well-designed database.