What is 5nf in DBMS.
Database Management System
Computer Science Engineering
582
Qayanat
The Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), is a level of database normalization that goes beyond the Third Normal Form (3NF) and the Boyce-Codd Normal Form (BCNF). It addresses the problem of join dependencies in a relational database schema.
To understand 5NF, let's first discuss the concept of a join dependency. A join dependency arises when a relation can be decomposed into two or more smaller relations, and the original relation can be obtained by joining these smaller relations based on certain attributes.
5NF aims to eliminate join dependencies by decomposing a relation into smaller, non-redundant relations, such that every join dependency is represented explicitly. It ensures that a database schema is free from any redundancy or anomalies caused by join operations.
The conditions for a relation to be in 5NF are as follows:
A nontrivial join dependency occurs when a relation can be expressed as a join of two or more smaller relations, and this join is not implied by any of the candidate keys or superkeys of the original relation.
To achieve 5NF, the relation is decomposed into smaller relations based on the nontrivial join dependencies. Each smaller relation will represent a subset of the attributes and tuples from the original relation, ensuring that no redundancy exists.
It's important to note that achieving 5NF may lead to an increase in the number of relations in the database schema, as the decomposition process creates new relations to represent the join dependencies explicitly. This can have implications on query performance and maintainability, so careful consideration is required when deciding to decompose a relation into 5NF.
Overall, the Fifth Normal Form (5NF) addresses the elimination of join dependencies in a relational database schema, ensuring that the schema is free from redundancy and anomalies caused by join operations. It represents the highest level of normalization and is typically used in complex database designs where preserving data integrity and minimizing anomalies is crucial.
Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), is a level of normalization in database design that goes beyond the Boyce-Codd Normal Form (BCNF). It aims to eliminate redundancy and anomalies caused by multi-valued dependencies and join dependencies. 5NF is considered the highest level of normalization and ensures the absence of all kinds of redundancy in a database schema.
To understand 5NF, let's first discuss the concept of multi-valued dependencies (MVDs) and join dependencies.
Multi-valued dependencies occur when a relation has attributes that depend on only part of a candidate key rather than the entire key. In other words, if changing the value of one attribute within a multi-valued dependency can result in multiple possible values for another attribute, then a multi-valued dependency exists.
Join dependencies occur when a relation can be decomposed into multiple smaller relations, and the original relation can be reconstructed by joining these smaller relations together.
Now, let's define 5NF. A relation is said to be in 5NF if and only if every join dependency in the relation is implied by the candidate keys of the relation.
Here's an example to illustrate 5NF:
Consider a database schema with three relations: "Teachers," "Subjects," and "Assignments.". Teachers (TeacherID, TeacherName, SubjectIDs) Subjects (SubjectID, SubjectName) Assignments (AssignmentID, TeacherID, SubjectID, AssignmentName)
In this example, the TeacherID attribute is the primary key in the Teachers relation, and the SubjectID attribute is the primary key in the Subjects relation.
Now, let's examine the multi-valued dependencies and join dependencies in this schema:
Multi-valued Dependencies:
Join Dependencies:
To achieve 5NF, we need to decompose the schema into smaller relations, eliminating the multi-valued dependencies and join dependencies.
Decomposed Relations: Teachers (TeacherID, TeacherName) TeacherSubjects (TeacherID, SubjectID) Subjects (SubjectID, SubjectName) Assignments (AssignmentID, TeacherID, SubjectID, AssignmentName)
By decomposing the schema, we have eliminated the multi-valued dependencies and ensured that every join dependency is implied by the candidate keys of the relations. Each relation now represents a separate entity with its own attributes, reducing redundancy and improving data integrity.
In summary, 5NF eliminates redundancy and anomalies caused by multi-valued dependencies and join dependencies. It ensures that all join dependencies are implied by the candidate keys, resulting in a highly normalized and well-structured database design.
Lets take the example of 5NF.
SUBJECT | LECTURER | SEMESTER |
---|---|---|
Computer | Anshika | Semester 1 |
Computer | John | Semester 1 |
Math | John | Semester 1 |
Math | Akash | Semester 2 |
Chemistry | Praveen | Semester 1 |
In the above table, John takes both Computer and Math class for Semester 1 but he doesn't take Math class for Semester 2. In this case, combination of all these fields required to identify a valid data.
Suppose we add a new Semester as Semester 3 but do not know about the subject and who will be taking that subject so we leave Lecturer and Subject as NULL. But all three columns together acts as a primary key, so we can't leave other two columns blank.
So to make the above table into 5NF,
We can decompose it into three relations Relation1, Relation2 & Relation3:
Relation1
SEMESTER | SUBJECT |
---|---|
Semester 1 | Computer |
Semester 1 | Math |
Semester 2 | Math |
Semester 1 | Chemistry |
Relation2
SUBJECT | LECTURER |
---|---|
Computer | Anshika |
Computer | John |
Math | John |
Math | Praveen |
Relation3
SEMESTER | LECTURER |
---|---|
Semester 1 | Anshika |
Semester 1 | John |
Semester 2 | John |
Semester 1 | Praveen |