Database Management System - Dependency Preserving Decomposition

This article will concentrate on a piece of software known as a database management system (DBMS) that enables users to effectively manage, store, and retrieve data. A DBMS's main job is to make sure that the data in a dataset is accurate, reliable, and doesn't have duplicates. Information bases are habitually coordinated as an assortment of tables, with conditions characterizing the connections between these tables. In addition, examples of dependencies will be provided within the framework of DBMS dependency-preserving decomposition.

DBMS dependencies

A dependence is a connection between two or more database table characteristics. Dependencies come in two types: multivalued dependencies and functional dependencies.

Functional Dependencies

Connections between at least two qualities in a table known as "useful conditions" (FDs) happen when the worth of one trait freely influences the worth of the other. In a database with employee data, for instance, the field "employee_name" is uniquely identified by the attribute "employee_id." Thus, assuming we know the worth of "employee_id," we can likewise sort out the worth of "employee_name."

Multivalued Dependencies

A link between two or more sets of attributes in a table is known as a multivalued dependence (MVD). In an MVD, the value of one feature set affects the value of another feature set, but the second action is independent. first layer you wear. Using the attribute set "employee_id, project_id" as an example, the attribute "employee_name, project_name" may be specified, however the value of "employee_name" in the table holding the employees and their information is independent of the value of "project_name"..

Dependency Preserving Decomposition

Decomposing a single table into numerous tables is known as decomposition in database management systems (DBMS). Typically, this is done to decrease data redundancy and increase query efficiency. The interdependence between the characteristics must be retained when breaking down a table, though. Dependency-preserving decomposition is the term used for this.

Dependency-preserving decomposition comes in two types: dependency-preserving decomposition and lossless decomposition.

Lossless Decomposition

A form of decomposition called lossless decomposition ensures that no data is lost during the breakdown of a table. This indicates that a join operation may be used to reconstruct the original table from the fragmented tables. Lossless decomposition, however, does not ensure that the relationships between the qualities are maintained.

Dependency Preserving Decomposition

A form of decomposition known as dependency-preserving decomposition preserves the relationships between the characteristics. This implies that the original table's dependencies will remain in the deconstructed tables as well. Data loss might occur as a result of dependency-preserving decomposition, though.

Examples of Dependencies

To demonstrate the operation of a database table's dependencies, consider a straightforward example. Let's say we have the attributes listed below in an "employees" table −

Employee_id, Employee_Name, Department, and Salary are a few examples of dependencies in this table −

• employee_id → employee_name − This is a functional dependency because the value of "employee_id" uniquely determines the value of "employee_name."

• department → salary − This is also a functional dependency because the value of "department" determines the value of "salary."

• {employee_id, department} → salary − This is a multivalued dependency because the values of {employee_id, department} determine the value of "salary," but the values of "employee_name" are independent of the value of "salary."

Let's say we wish to decompose the "employees" database into two separate tables, one for employee data and the other for salary data. By utilizing the following schema, we can do this −

Employees (employee_id, employee_name, department) Salaries (employee_id, salary)

This decomposition is lossless because the original table can be reconstructed by combining the "Employees" and "Salaries" tables with the "employee_id" attribute. Since the "Salaries" database no longer contains the "department" property, this decomposition no longer relies on the "department" wage. This has resulted in the loss of some data.

We may include the "department" attribute in the "Salaries" table to accomplish a dependency-preserving decomposition −

Employees (employee_id, employee_name, department)

Salaries (employee_id, department, salary)

The fact that we have added a new attribute (department) to the "Salaries" table makes this decomposition not lossless even if it keeps all the relationships in the original table. But in order to maintain the dependencies, we must make this trade-off.

Conclusion

Because it guarantees that the links between attributes in a database table are preserved when the table is deconstructed, dependency-preserving decomposition is a crucial idea in DBMS. Dependency-preserving decomposition comes in two types: dependency-preserving decomposition and lossless decomposition. While dependency-preserving decomposition retains dependencies but may result in data loss, lossless decomposition preserves all data in a table but may not preserve dependencies. We can build databases that are more effective and consistent if we understand dependencies and how to maintain them.