Normalization

First Normal Form

A relation table is in first normal form, if each cell has SINGLE atomic value. A relation should not contain any multivalued or composite attributes.

• Every row should be unique
• One primary key
• Every column should have a unique name
• Order of row and column is irrelevant

Second Normal Form

Prime and Non Prime attribute yaad hai naa ? Candidate key ke attributes are prime.

Partial and Total Dependency

• Partial Dependency : When a non prime attribute is dependent partially on the candidate key then it is a partial dependency. If AB is the candidate key, and A→ C, then C is only dependent on A, not the full AB, so partial partial.
• Total Dependency : AB→ C hota to total dependency.

A relation R is in 2NF if

• R should be in 1NF and
• R should not contain any partial dependency. (that is every non prime attribute should be total dependent on the candidate key)

Transitive Dependency

A functional dependency from non prime

A→ B
B→ C
C→ D

Here A is the key. here B→C, A non prime attribute is dependent on another non prime attribute. This is called transitive dependency.

The first line above (“A functional dependency from non prime”) is left as written in the original notes; it isn't a finished sentence there.

Third Normal Form

A relation R is 3NF if

• R should be in 2NF and
• It must not contain any transitive dependency.

In this relation both AB and AC are the candidate keys so A, B, C are prime attributes. Since there are no non prime attribute, there are no partial dependency or transitive dependency → THIRD NORMAL FORM

BCNF (Boyce Codd Normal Form)

A relation R is said to be BCNF if every functional dependency from A→ B, A must be a super key. Super key bhul gya kya ?

• A relation R does not contain any non trivial dependency, this is already in BCNF
• Two attributes relation is always in BCNF
• If only prime attributes then R is always 3NF, but may or may not BCNF

… tough to link, solve some questions (Chapter 4.2)

Multi Valued Dependency

Represented by … A→→ B, for every value of A, there may exist more than one value of B.

In left and right table there is a multivalued dependency. Problem arises when they are merged and we have more than one multivalued dependency, in that case … values get repeated.

Non Trivial and Trivial Dependency

Example se hee smjh aayega,

X = Student
Y = Hobby
R = {Student, Hobby, Language}

Now for Student →→ Hobby

First Condition is Hobby is subset of Student ?

Or

Student U Hobby = Relation but in this case Language is missing

Therefore in this, it is a non trivial multi valued dependency. If either one of the condition is true then TRIVIAL.

Fourth Normal Form

A relation is in 4NF if

• It is in BCNF
• There must not exist any non trivial multivalued dependency.

Each MVD is decomposed jisse duplicate duplicate redundancy naa ho.