SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization

>>Chapter 4 : Index Structures of Files

>>Chapter 5 : Entity-Relationship Model

>>Chapter 6 : Relational Algebra

>>Chapter 7 : SQL

>>Chapter 8 : Functional Dependencies and  Normalization For Relational Databases<You are Here>

Continuing from my previous post on this series.

We are going to Cover the Following Points in this article

• Fourth Normal Form (4NF)
• Normalization using Join Dependencies

Fourth Normal Form (4NF)

Multi valued dependencies are based on the concept of first normal form, which prohibits attributes having a set of values. If we have two or more multi valued independent attributes in the same relation, we get into a situation where we have to repeat every value of one of the attributes, with every value of the other attributes to keep the relation state consistent, and to maintain independence among the attributes involved. This constraint is specified by a Multi valued dependency.

Consider a table employee that has the attribute name, project and hobby.

· An employee can work in more than one project and can have more than one hobby

· The employees’ projects and hobbies are independent of one another.

· A given project or hobby is associated with any number of employees.

To keep the Relation State consistent we must have separate tuples to represent every combination of employee’s project and employee’s hobbies.

The drawback of EMPLOYEE relation is redundant data. This redundant data leads to update anomaly. For example, if we wish to add one more project on Sybase, so that employ B is handling, then we must add two more tuples for each hobby. The values Reading and Movie of hobby are repeated with each value of project. This redundancy is undesirable. One way to remove redundancy is to decompose EMPLOYEE relation into two relations PROJECT AND HOBBY.

NOW, if we wish to insert Sybase in PROJECT relation, then there is only one entry required.

So wherever two independent one_to_many relationships (A:B and A:C) are mixed on the same relation, a multivalued dependency arises. Multivalued dependency can be avoided using the fourth normal form.

Decomposed relation to reduce redundancy

PROJECT

HOBBY

Fourth Normal Form (4NF): The definition of 4NF is violated when a relation has undesirable multivalued dependencies, and hence identify such relations and decompose into 4NF relations.

The Employee relation is not in 4NF because of the non-trivial MVDs (project and hobby attributes of employee relation are independent of each other) and NAME is not a super key of EMPLOYEE. To make this relation into 4NF you have to decompose EMPLOYEE to PROJECT AND HOBBY.

Normalization using Join Dependencies

Join dependency: The 5NF is also called "Project Join Normal form". It is important to note that normalization into 5NF is considered very rarely in practice.

Definition: relation r is in 5NF, if for all join dependencies at least one of the following holds:

· (R1,R2…..Rn) dependency

· Every Ri is a candidate key for R.

For an example of a JD, the relation shown in the figure states that CSE department offers subjects like Data structure and RDBMS, which are taken by Leela. Similarly, the other departments offer different subjects. However, no student takes all the subjects and no subject has all students enrolled in it, and therefore all three fields are needed to represent the information.

DST

The above relation does not suffer any MVD, because Subject and Student are not independent. To make this relation into 5NF we decompose it as:

DJ (Dept. Subject) DS (Dept, Student)

SS (Subject, Student)

The three relations shown above satisfy the rules of 5NF, and also they are lossless. One of the major differences between 4NF and 5NF is that in a given relation R(X,Y,Z), if the attributes Y and Z are independent, then it suffers 4N,F and if they have dependency, then it is in NF. The 4NF gives generally two relations after decomposition, whereas 5NF gives three relations to keep all the information of the original relation.

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |