---

Sixth Normal Form (6NF) in DBMS

---

The Sixth Normal Form (6NF) is a level of database normalization that specifically deals with temporal data. While other normal forms focus primarily on eliminating redundancy and ensuring logical consistency, 6NF aims to efficiently handle the historical data in temporal databases.

A relation is said to be in 6NF if:

1. It is already in 5NF.

2. All temporal data (data that has time-bound attributes) is segregated into its own separate table, such that it allows for efficient insertion, deletion, and modification of temporally bounded data without the need to update non-temporal data.

Temporal databases require special attention to represent and query data spanning across different time frames or versions. There could be valid-time (the time for which a fact is valid in the real world) and transaction-time (the time at which a fact is stored in the database).

Example for Sixth Normal Form (6NF)

Consider a table with employee salaries over time. Employees may receive raises, and we wish to keep a history of all their past salaries.

Initial Table (EmployeeSalaries):

| EmployeeID | Salary  | ValidFrom   | ValidTo     |
|------------|---------|-------------|-------------|
| E1         | ₹50,000 | 2021-01-01  | 2022-01-01  |
| E1         | ₹55,000 | 2022-01-01  | 2023-01-01  |
| E2         | ₹60,000 | 2021-06-01  | 2022-06-01  |

In the above table, each row specifies the salary of an employee for a specific time interval. As you can imagine, updates (like giving a raise) could become complicated and might require adjustments in the `ValidTo` and `ValidFrom` columns, especially if you have multiple date ranges.

To bring this into 6NF, you could decompose the table into separate relations, one capturing the essence of the entity (e.g., the employee and some constant attributes) and others capturing the temporal aspects.

Employee:

| EmployeeID | OtherConstantAttributes |
|------------|-------------------------|
| E1         | ...                     |
| E2         | ...                     |

EmployeeSalaryHistory:

| EmployeeID | Salary  | ValidFrom   | ValidTo     |
|------------|---------|-------------|-------------|
| E1         | ₹50,000 | 2021-01-01  | 2022-01-01  |
| E1         | ₹55,000 | 2022-01-01  | 2023-01-01  |
| E2         | ₹60,000 | 2021-06-01  | 2022-06-01  |

By segregating the time-variant data in its own table, operations related to time-bound attributes become more efficient and clearer. This structure makes it easier to handle and query temporal data.

In practice, 6NF is specialized, and its application is restricted to systems that demand intricate temporal data management. Also, while 6NF facilitates the handling of temporal data, it can introduce complexity in the form of multiple tables, which might require complex joins during querying.

---

Next Topic :Transaction Concept in DBMS