Functional Dependency:
Functional dependency is a concept in the normalization process of database management
systems (DBMS).
It defines the relationship between attributes in a table, indicating how the value of one
attribute determines the value of another.
Types of Functional Dependency:
1. Full Functional Dependency:
Definition: The value of one attribute uniquely determines the value of another
attribute, and this dependency doesn't exist for any proper subset of the
determinant.
Example:
In a table with columns StudentID and StudentName, StudentID →
StudentName is a full functional dependency because knowing the StudentID
uniquely determines the corresponding StudentName.
2. Partial Dependency:
Definition: The value of one attribute uniquely determines the value of another, but
this dependency exists for only a part (not the whole) of the determinant.
Example:
Consider a table with columns CourseID (composite key of DepartmentCode
and CourseNumber) and CourseTitle. If DepartmentCode → CourseTitle, it is a
partial dependency because it depends on only part of the composite key.
3. Transitive Dependency:
Definition: A depends on B, and B depends on C, so A indirectly depends on C.
Example:
In a table with columns StudentID, CourseID, and Professor, if StudentID →
CourseID and CourseID → Professor, then StudentID → Professor is a
transitive dependency.
In this table:
StudentID is the primary key.
There is a full functional dependency between StudentID and StudentName
because StudentID uniquely determines the corresponding StudentName.
There is a transitive dependency between StudentID and AdvisorID through
Department. If StudentID → Department and Department → AdvisorID, then
StudentID → AdvisorID is a transitive dependency.
Normalization in the context of Database Management Systems (DBMS) is a systematic
process of organizing and designing a relational database to minimize data redundancy and
improve data integrity.
The primary goal of normalization is to structure the database in a way that avoids anomalies
and ensures efficient storage and retrieval of data.
This process involves breaking down large tables into smaller, related tables and
establishing relationships between them.
the example of student and teacher tables
1. First Normal Form (1NF):
Objective:
Ensure that each table has a primary key.
Eliminate duplicate columns.
Ensure that each column contains atomic (indivisible) values.
Steps:
Identify the primary key for each table.
Eliminate duplicate columns.
Ensure that each column contains only atomic values (no repeating groups).
2. Second Normal Form (2NF):
Objective:
Meet the requirements of 1NF.
Remove partial dependencies.
Steps:
Identify and remove partial dependencies by moving columns that are not fully dependent
on the primary key to a new table.
Create relationships between the new table and the original table.
3. Third Normal Form (3NF):
Objective:
Meet the requirements of 2NF.
Remove transitive dependencies.
Steps:
Identify and remove transitive dependencies by moving columns that depend on non-key
columns to a new table.
Create relationships between the new table and the original table.
Additional Normal Forms:
4. Boyce-Codd Normal Form (BCNF):
Objective:
Meet the requirements of 3NF.
Ensure that there are no non-trivial functional dependencies on non-superkey attributes.
Steps:
Decompose tables further to remove any remaining anomalies.
