ARTICLE: Normalization in Database Management System

Functional Dependency 

The attributes which are about each other have a set of constraints, and those constraints are called functional dependency. In the concept of FD, functional dependency, if two tuples share the same values of attributes A1, A2. Moreover, so on, then it is fixed that those two tuples have the same values for the other set of attributes, let’s say, B1, B2, and so on. If want to show the functional dependency between two values, then we use an arrow to represent it. For example, we are here to denote a value say, M which functionally determines the value N, then represent it by M-> N. In the general case, learn that the value on the left-hand side of the denotation determines the right-hand side’s value. Hashing in Managing the Database System is a significant topic to read under the discipline. 

Armstrong’s Axioms 

The closure of the set of the functional dependencies which is denoted by asterisk F contains the set of all those functionalities and their dependencies which F denotes logically. In Armstrong’s Axiom, we contain the rules in a set. When those rules are applied in a repeated manner, then we get a functional dependencies closure. There are three rules under the axiom. Let us discuss them in brief. 

  1. Augmentation Rule-

If we have a functional dependency between two attributes say a and b, or we can say that if a->b holds, and from that y is the attribute set, then according to this rule, ay-> by also holds. This rule makes it clear that if we add something in the functional dependency, then the basics of the concept do not change. 

  1. Reflexive Rule-

As per the guidelines of the rule, if we have a set of attributes, say alpha, and along with this we have a subset of the same set, say beta, then alpha holds beta. 

  1. Transitivity Rule-

It is the same rule in Armstrong’s Axioms as we have in algebra. Here, if we have two attributes which share a functional dependency, like a-> b, and similarly, we have b-> c, then according to this rule, we have a->c. Here a is the functional dependency which determines c. 

Trivial Functional Dependency 

  1. Trivial-

If we talk about X-> Y, a functional dependency, where the subset of X is Y, then this type of functional dependency is called trivial functional dependency. The best thing about this type of functional dependency always hold. 

  1. Non-Trivial-

It is the opposite of the trivial functional dependency. If a functional dependency where the right hand side of the dependency is not a subset of the attribute of the left hand side attribute, then it is called a non-trivial functional dependency. For example, X-> Y. Here Y is not a subset of X. 

  1. Completely non-trivial-

In functional dependency, if the value intersects the attribute on the right hand side by the left attribute, then this comes under a completely non-trivial functional dependency. 

Normalization 

There are several anomalies in the function when the design is not perfect. The introduction in the database and the function is not less than a nightmare for the DBA. It is not possible to manage the database with some or any of the anomalies. There are queries which we can make in order to update, delete, or insert an anomaly in the system. 

  • Update Anomalies- The system experiences strange situation when the data items are scattered. It also happens when they are improperly linked to each other. If we look at an example, we have a data item which has got several copies which are scattered over the system, and we have to update it, we face issues. These come in the form of the improper function of those systems. Some of the data items get copied properly, and the rest are left with the same and the old values. It is the inconsistent state which the instances create.
  • Deletion of anomalies- When we try to delete the records, but due to many reasons or unawareness, it does not happen properly. That data which we wanted to delete, is still somewhere in the system. It has not deleted properly due to the anomaly.
  • Insert Anomalies -We have inserted some data items in the record which does not have any existence. 

Here come the technique and the concept of normalization. It is a method through which we can remove all the anomalies. It also helps in bringing the database to state which is consistent. 

There are several forms in the concept of normalization. Let us talk about them in brief. 

First Normal Form 

The definition of the tables in the system relates to its definition. It makes sure that the relation in the attributes has the active domains. The main feature of the elements of the table is that these are indivisible units. The active domain contains these values in the first normal form. From the set of its predefined domain, there must be only one single value for that attribute in the domain. 

Second Normal Form 

There are pre-requisites to get the concept of the second normal form. Let us first discuss them. 

  1. Prime Attribute-

The element or the attribute which make the part of the prime-key are called the prime attributes. 

  1. Non-prime attribute-

It is an attribute opposite to the prime-attribute. These contain the attributes which are not a part of the prime-key, unlike the prime attribute. The condition for using this type of attribute is that this attribute must be functionally dependent on the prime key attribute. When there are no proper subsets in the system if the functional dependency between the two attributes, X and A holds. Still, the functional dependency in the two attributes, i.e. Y-> A holds true. 

Partial Dependency 

It applies that the non-prime attribute is dependent functionally on a candidate key’s part rather than being on the entire key. Here, the candidate key has no partition contributed by the non-prime attributes. 

Third Normal Form

The relation which has to reach the level of being a part of the third normal form, then it has to go through the second normal form initially. There are other points which are to be satisfied. 

The prime key attribute has no transitive dependency by the non-prime attribute.

If we have any of the non-trivial functional dependency, say we are talking about A-> B, then it implies that A is a super key and B is a prime attribute. 

Transitive Dependency 

The name of the functional dependency in the concept of database management system which holds true by virtue of transitivity is the transitive dependency. For a transitive dependency to occur, we need a relation which consists of three or more than three attributes. 

Boyce-Codd Normal Form 

On strict and stringent terms, we have BCNF, i.e. Boyce-Codd Normal Form as an extension of the third normal form. It states that for the BCNF to be true, the non-trivial functional dependency must have a super key.