Hello Everyone, Welcome to Rajasthan Polytechnic Blogs.
Subject - Introduction to DBMS CS 4002(Same as CI/IT 4002)
Branch - Computer Science Engineering
Semester - 4th Semester
UNIT 5: Functional Dependencies and Normalization for Relational Databases
This unit covers functional dependencies and normalization, which are important concepts in relational database design. We'll also explore various normal forms (1NF, 2NF, 3NF, BCNF), as well as advanced dependencies such as multi-valued dependencies, join dependencies, and further normal forms (4NF and 5NF). These concepts help in designing efficient, reliable, and well-structured relational databases.
5.1 Functional Dependencies and Normalization for Relational Databases 🧠
5.1.1 Normalization Concepts 🔍
Normalization is the process of organizing the attributes and tables in a relational database to reduce redundancy and dependency. The goal is to eliminate undesirable characteristics like data anomalies, which can occur during operations such as INSERT, UPDATE, and DELETE.
Normalization helps to improve the efficiency of the database and ensures that it is free from undesirable dependencies.
Functional Dependency (FD): A functional dependency occurs when one attribute (or a set of attributes) uniquely determines another attribute within a relation.
For example, if A → B, it means that B is functionally dependent on A, i.e., the value of attribute A determines the value of attribute B.
- Example:
If we have a relationEmployee(EmpID, Name, Department), and the EmpID uniquely determines the Name and Department, we can say EmpID → Name and EmpID → Department.
5.1.2 Normal Forms (1NF, 2NF, 3NF, BCNF) 🏗️
Normalization involves transforming a database schema into various normal forms (NF) to ensure that the data is structured in a way that minimizes redundancy and dependency.
First Normal Form (1NF) ✅
- A relation is in 1NF if it has no repeating groups and all its attributes contain atomic (indivisible) values.
- Example:
If an attribute contains a set or list of values (e.g., "Phone Numbers" attribute containing multiple numbers), the database is not in 1NF. It must be split into individual rows.
1NF Requirement:
- Every column must contain atomic values.
- Each record (row) must be unique.
Example of a non-1NF table:
EmpID Name Phone Numbers 101 John 123-456, 789-012 102 Alice 234-567 To make it 1NF, we must split the phone numbers into separate rows:
EmpID Name Phone Number 101 John 123-456 101 John 789-012 102 Alice 234-567 Second Normal Form (2NF) ✅
A relation is in 2NF if it is in 1NF and if all non-key attributes are fully functionally dependent on the primary key.
- Example:
In a table with a composite primary key (a key made up of two or more columns), any non-key attribute must depend on the entire composite key.
2NF Requirement:
- Must be in 1NF.
- Every non-prime attribute must depend on the whole primary key (eliminate partial dependencies).
Example of non-2NF:
EmpID DeptID Name Department Name 101 D01 John HR 102 D02 Alice IT In this case, Department Name depends only on DeptID, not on the whole composite key (EmpID, DeptID). To convert it to 2NF, we separate it into two tables:
EmpID DeptID Name 101 D01 John 102 D02 Alice DeptID Department Name D01 HR D02 IT - Example:
Third Normal Form (3NF) ✅
A relation is in 3NF if it is in 2NF and if there is no transitive dependency between non-key attributes.
- Example:
A transitive dependency occurs when one non-key attribute depends on another non-key attribute.
3NF Requirement:
- Must be in 2NF.
- No transitive dependencies (non-key attributes must not depend on other non-key attributes).
Example of non-3NF:
EmpID Name DeptID DeptHead 101 John D01 Smith 102 Alice D02 Brown Here, DeptHead depends on DeptID, which in turn depends on EmpID. This is a transitive dependency. To bring it into 3NF, we separate the DeptHead into a separate table:
EmpID Name DeptID 101 John D01 102 Alice D02 DeptID DeptHead D01 Smith D02 Brown - Example:
Boyce-Codd Normal Form (BCNF) ✅
A relation is in BCNF if it is in 3NF and if, for every functional dependency, the left-hand side is a superkey (a set of attributes that uniquely identifies each record).
BCNF Requirement:
- Must be in 3NF.
- Every functional dependency must involve a superkey.
Example of non-BCNF:
EmpID DeptID DeptHead 101 D01 Smith 102 D02 Brown In this case, DeptID → DeptHead is a functional dependency, but DeptID is not a superkey. To make it BCNF, we separate DeptHead into a new table:
EmpID DeptID 101 D01 102 D02 DeptID DeptHead D01 Smith D02 Brown
5.2 Relational Database Design Algorithms and Further Dependencies ⚙️
5.2.1 Multi-Valued Dependency and 4NF 🔀
A multi-valued dependency occurs when one attribute in a relation determines multiple independent values of another attribute. To eliminate this dependency, the relation must be in 4NF.
- 4NF: A relation is in 4NF if it is in BCNF and has no multi-valued dependencies.
Example of 4NF violation:
| EmpID | Skill | Language |
|---|---|---|
| 101 | Programming | English |
| 101 | Design | Spanish |
Here, EmpID determines both Skill and Language, and they are independent of each other. To convert this to 4NF, we split the relation:
| EmpID | Skill |
|---|---|
| 101 | Programming |
| 101 | Design |
| EmpID | Language |
|---|---|
| 101 | English |
| 101 | Spanish |
5.2.2 Join Dependency and 5NF ➗
A join dependency occurs when a relation can be decomposed into several relations, but the original relation can still be reconstructed by performing a join on them.
- 5NF: A relation is in 5NF (also known as Project-Join Normal Form (PJNF)) if it is in 4NF and cannot be decomposed into smaller relations without losing information.
5NF Requirement:
- Must be in 4NF.
- No join dependencies that can result in information loss.
Conclusion 🎯
- Normalization is an important process in database design to reduce redundancy and dependency.
- The normal forms (1NF, 2NF, 3NF, BCNF) guide the process of transforming a database to eliminate anomalies.
- Advanced dependencies like multi-valued dependencies and join dependencies lead to 4NF and 5NF, which ensure a well-structured and efficient database design.
0 Comments