SQL Server

Relational Database Management System (RDBMS) and Normalization Forms — Part 10

Lossless – Join Decomposition

In this article so far normalization of a number of relations or tables has been done by means of decomposing them. Table or relation has been decomposed instinctively. At this point of time a better understanding for determining decompositions as instinct might not always be precise. Over here, an illustration is given as an instance for a careless decomposition and which shows that it can lead to difficulties counting loss of information.

Think through the subsequent table or relation structure:

Enrolment (Student_ID, Class_No, Date_Enrolled, Room_No, Instructor_Name)

Now, supposing an individual decomposes the above mentioned relation or table into two (2) tables or relations Enrolment1 and Enrolment1 as follows:

Enrolment1 (Student_ID, Class_No, Date_Enrolled)

Enrolment2 (Date_Enrolled, Room_No, Instructor_Name)

There are difficulties with this decomposition however over here the focus is on one (1) feature. Let an instance of the relation or table be Enrolment as well as let the decomposed relations or tables be Enrolment1 and Enrolment2. Now, suppose the data which was in the relation or table Enrolment appears to be still existing in Enrolment1 as well as Enrolment2 however this is not so. Supposing, an individual desires to recover the student ID of all the students taking a course from a particular instructor say “Instructor-1”, then the individual will need to use join operation Enrolment1 and Enrolment2.

The join has a number of false tuples or rows which are not in the original relation or table Enrolment. For these extra rows or tuples, an individual have missed the data about which students have taken courses from Instructor-1. At this particular point of time it can be understood that an individual will have more rows or tuples however less information as an individual are incapable of saying with confidence that who is taking courses from that “Instructor-1”. This type of decompositions is known as loss decompositions. A non-loss or loss-less decomposition is that which confirms that the join operation will result out in accurately the identical relation or table as was decomposed. An individual may think that there may be additional methods of recuperating the original relation or table from the decomposed relations or table, but sad, no additional operators can recuperate the original table or relation if the join operation does not.

An individual must analyse why a number of decompositions are lost. The common column or attribute in above mentioned decompositions was Date_Enrolled. The common column or attribute is the adhesive which gives us the capability to find the relationships among various relations or tables by means of linking the relations or tables together. If the common attribute or column is not exclusive, the relationship data is not well-preserved. If every single tuple or row had an exclusive data of Date_Enrolled, then the difficulties of losing data would not have occurred. The difficulty arises as a number of enrolments may happen on the similar date.

A decomposition of a table or relation R into table or relations R1, R2, R3, …. Rn is called a loss less join decomposition with respect to practical dependencies, when the table or relation R is at all times is natural join of the relations R1, R2, R3, …. Rn. It must be noted that natural join is the sole method to recuperate the relation or table from the decomposed relations or table. There is no additional group of operators which can recuperate the table or relation if the join cannot. In addition, it must be noted that when the decomposed relations or tables R1, R2, R3, …. Rn are obtained by means of projection on the table or relation R, for an example R1 by projection π1 (R), the table or relation R1 might not always be exactly identical to the projection as the table or relation R1 might have extra tuples or rows which is known as the dangling tuples.

It is not problematic to check whether a given decomposition is loss less join given a group of practical dependencies P. An individual can consider the simple case of a table or relation R being decomposed into R1 and R2. When the decomposition is loss less join, then one of the subsequent two (2) conditions must hold true:

· R1 ∩ R2 → R1 – R2

· R1 ∩ R2 → R2 – R1

Dependency Protection Decomposition

By this point of time it is clear that decomposition must be loss less so that an individual do not lose any data from the relation or table which is decomposed. Dependency protection is a different significant requirement as a dependency is a constriction on the database and when X → Y holds than it is known that the two (2) groups of columns or attributes are carefully connected as well as it will be beneficial if both attributes or columns appeared in the identical relation or table so that the dependency can be examined effortlessly.

Let’s think through a table or relation R (Q, R, S, T) which has the dependencies P that include the subsequent:

Q → R

Q → S

If an individual decompose the above mentioned table or relation into R1 (Q, R) and R2 (R, S, T) the dependency Q → S cannot be examined or preserved by means of looking at only one (1) table or relation. It is anticipated that decompositions be such that every single dependency in P may be examined by means of looking at only one (1) table or relation as well as no joins are needed to be computed for examining dependencies. In a number of cases, it might not be probable to preserve every single dependency in P however as long as the dependencies which are preserved are equal to P, it must be adequate.

Let P be the dependencies on a table or relation R which is decomposed in tables or relations R1, R2, R3, …. Rn.

An individual can divide the dependencies given by P such that P1, P2, P3, …. Pn. Pn are dependencies which only include columns or attributes from tables or relations R1, R2, R3, …. Rn respectively. If the combination of dependencies Fi suggests every dependency in Fi then it can be said that the decomposition has conserved dependencies, or else not.

When the decomposition does not conserved the dependencies F, then the decomposed tables or relations might not cover tables or relations which do not fulfils F or the updates to the decomposed tables or relations might need a join to examine which restrictions got implied by means of the dependencies which still hold.

Think through the subsequent table or relation:

Subject (Subject_ID, Instructor_Name, Agency)

Now, an individual might wish to decompose the above mentioned table or relation to eliminate the transitive dependency of agency on Subject_ID. A probable decomposition is

Sub1 (Subject_ID, Instructor_Name)

Sub2 (Subject_ID, Agency)

The table or relations are now in third normal form (3NF) however the dependency Instructor → Office cannot be confirmed by means of looking at one (1) table or relation; a join of Sub1 and Sub2 is desirable. In the above mentioned decomposition, it is quite possible to have more than one (1) agency number for one (1) instructor though the practical dependency Instructor → Agency does not permit it.

This is the last part of the article series “Relational Database Management System (RDBMS) and Normalization Forms”, hope the readers had find it helpful in some respect.

Thanks

Facebooktwittergoogle_plusredditpinterestlinkedinmail