Can any one explain me BCNF, 3rd form , 4th form , 5th form. Is there any form above that also ?
For this,wikipedia articles on normal forms are great,it has examples of relations in 3 NF and not in BCNF.Similarly examples of relations which are in 2NF but not in 3NF.The description is to the point and very useful .After that you can try solving previous year questions on normal forms.
The problem is there can be n number of ways of describing same normal form but you have to first understand what thing was creating problem in that particular form as in update,deletion anamolies and after adding a higher normal form ,how that problem is solved.
But it confuses a lot !! can you give some better approach
Normalization of Database
Database Normalisation is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
Normalization is used for mainly two purpose,
Eliminating reduntant(useless) data.
Ensuring data dependencies make sense i.e data is logically stored.
Third Normal Form (3NF)
Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form.
Fourth Normal Form (4NF)
Fourth normal form (4NF) is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key.
It builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF). It states that, in addition to a database meeting the requirements of BCNF, it must not contain more than one multivalued dependency.A multivalued dependency is best illustrated using an example. In a table containing a list of three things - college courses, the lecturer in charge of each course and the recommended book for each course - these three elements (course, lecturer and book) are independent of one another. Changing the course’s recommended book, for instance, has no effect on the course itself. This is an example of multivalued dependency: An item depends on more than one value. In this example, the course depends on both lecturer and book.
Thus, 4NF states that a table should not have more than one of these dependencies. 4NF is rarely used outside of academic circles.
Fifth Normal Form:
Fifth normal form deals with cases where information can be reconstructed from smaller pieces of information that can be maintained with less redundancy. Second, third, and fourth normal forms also serve this purpose, but fifth normal form generalizes to cases not covered by the others.
We will not attempt a comprehensive exposition of fifth normal form, but illustrate the central concept with a commonly used example, namely one involving agents, companies, and products. If agents represent companies, companies make products, and agents sell products, then we might want to keep a record of which agent sells which product for which company.
This form is necessary in the general case. For example, although agent Smith sells cars made by Ford and trucks made by GM, he does not sell Ford trucks or GM cars. Thus we need the combination of three fields to know which combinations are valid and which are not.
1 NF: No multivalued attributes.
2 NF: No partial dependency
3 NF: No trivial dependency.
BCNF: Either all a->b is a trivial dependency
Or a is the super key for every a->b.
4NF: No multivalued dependency.
5NF: No join dependency.