Define BCNF. How does it differ from 3NF? Why is it considered stronger than 3NF?
Boyce-Codd normal form (BCNF)
A relation is in BCNF, if and only if, the relation is in 3NF and every determinant is a candidate key.
The difference between 3NF and BCNF is that for a functional dependency A → B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key, whereas BCNF insists that for this dependency to remain in a relation, A must be a candidate key.
|
Properties |
3NF |
BCNF |
|
Achievability |
Always achievable |
Not always achievable |
|
Quality of the tables |
Less |
More |
|
Non-key Determinants |
Can have non-key attributes as determinants |
Cannot have. |
|
Proposed by |
Edgar F. Codd |
Raymond F.Boyce and Edgar F.Codd jointly proposed |
|
Decomposition |
Loss-less join decomposition can be achieved |
Sometimes Loss-less join decomposition cannot be achieved |
BCNF is stronger than 3NF:
A relation R is in 3NF if and only if every dependency A → B satisfied by R meets at least ONE of the following criteria:
1. A → B is trivial (i.e. B is a subset of A)
2. A is a super-key
3. B is a subset of a candidate key
BCNF doesn't permit the third of these options.
Therefore BCNF is said to be stronger than 3NF because 3NF permits some dependencies which BCNF does not.
So, if a relation is in BCNF, it is always true that it is in 3NF.
R (A, B, C, D)
AB → CD
The candidate key is only one that is AB. Fortunately that AB is also our left hand side FD (AB → CD), so the relation is in BCNF. Nothing like C → D or D → C not possible, as the determinants are not candidate key. So, in BCNF, there is no possibility for a non-key attribute to transitively dependent on a key attribute. So, automatically the Relation is in 3NF.
Define a Foreign key. Why is the concept needed? How does it play a role in the join operation?
What are the differences between Primary Index, Secondary Index, and Clustering Index?
Write a C program to find minimum among three numbers.
What is Resource management?
