SQL Server

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

Multi – Valued Dependencies

The multi valued dependency arises when more than one (1) multi valued attributes or columns are existing. Think through the subsequent relation or table which signifies an object EMPLOYEE which has one (1) multi valued column or attribute named as “Projects”.

EMPLOYEE ( Emp_ID, Department, Projects, Salary)

To this point of time the data or the information which was considered in this article was taken as normalized based on the practical dependencies; dependencies which apply only to single valued particulars. For an instance, Emp_ID → Department denotes only one (1) department data for every single value of employee ID. Not every data in a database is single valued, for an instance; Projects in an EMPLOYEE table or relation might be the list of every single project on which the employee is presently employed to. Although Emp_ID regulates the list of every project which an employee is employed to Emp_ID → Project, is not a practical dependency.

Up to now the article was dealing with multi valued particulars about an item by means of having a discrete relation or table for that multi value column or attribute as well as then introducing a row or tuple for every single data of that particular. This caused in composite keys as the multi valued data should create a segment of the key. In not a single instances as far as now have been dealt with an item which is having more than one (1) multi valued column or attribute in one (1) table or relation.

The fourth (4th) as well as fifth (5th) normal forms deals with multi valued dependencies. The fourth (4th) as well as fifth (5th) normal forms are discussed in the article which deals with normalization methods. Here an instance is discussed in the subsequent instance to exemplify the idea of multi valued dependency.

DOCTOR ( Doc_ID, Qualifications, Specialization )

In the above mentioned table or relation there are two (2) multi valued columns or attributes of the entity DOCTOR, Qualifications, and Specialization. There are no practical dependencies.

The attributes or columns Qualifications, and Specialization are expected to be self – governing of each other. If an individual were to think through Qualifications and Specialization as discrete items, one will have two (2) associations (one (1) between DOCTOR and Qualifications in addition to the additional relation between DOCTOR and Specialization). Both the above mentioned associations are many to many (N – N) that is one doctor can have more than a few qualifications in addition to might have numerous specializations too. Likewise one (1) type of qualification can be gained by more than a few doctors as well as one (1) type of specialization can be gained by numerous doctors.

Assume a doctor have more than a few qualifications like MBBS, FRCS, DCH, CMP in addition to is skilled in more than a few specialization; now in what way should this data be signified? There are more than a few options, like in separate relations or tables.

Additional modifications are possible (note that there is no association among qualifications and specialization). All these modifications have certain difficulties. If the data is repetitive an individual will face the similar type of difficulties like repetitive data in addition to irregularities as and when it is done the second (2nd) or third (3rd) normal form conditions are dishonoured. When there is no duplication, there are still a number of problems with search, insertions as well as deletions. For an instance, the part of NULL data in the above table or relations is unclear. Similarly the candidate key in the above table or relations is (Doc_ID, Qualifications, Specialization) as well as existential integrity needs that no NULLs be stated. These difficulties may be solved by means of decomposing a table or relation.

The origin of the above decomposition is the idea of Multi Valued Dependency (MVD). Practical dependency X → Y relates one (1) data of X to one (1) data of Y however on the other hand the multi valued dependency X → Y describes an association in which a group of data of attribute or column Y are determined by means of a single data of X.

The idea of multi valued dependencies was established to deliver a foundation for decomposition of relations or tables like the one mentioned above. For that reason if a relation or table like Disease ( Disease_ID, Disease_Type ) has an association between Disease_ID as well as Disease_Type in which Disease_ID exclusively regulates the data of Disease_Type, the dependence of Disease_Type on Disease_ID is known as an insignificant Multi Valued Dependency (MVD) as the relation or table Disease cannot be decomposed any more. More properly, a Multi Valued Dependency (MVD) X → Y is known as inconsequential Multi Valued Dependency (MVD) if either Y is a subsection of X or else if X and Y collectively form the table or relation R. The Multi Valued Dependency (MVD) is insignificant as it outcomes in no constrictions being engaged on the table or relation. For that reason a table or relation having non – trivial Multi Valued Dependency (MVD) should have as a minimum three (3) columns or attributes and two (2) of them are multi valued. Non – trivial Multi Valued Dependency (MVD) result in the table or relation having a number of constrictions on it as every possible groupings of the multi valued columns or attributes are then compulsory to be in the table or relation.

In the upcoming part we will be discussing about the Notion of Multi Valued Dependency (MVD) and Relational Database Management System (RDBM).