3NF and BCNF Compared

Third normal form and boyce-codd normal form a comparison

Difference between 3NF and BCNF

Assume the following things;

A and B are set of attributes.

A is non-key attribute and B is the primary key.

FD – { A → B. }

The above Functional Dependency is about the dependency of primary key on a non-key attribute. This functional dependency is permitted in Third Normal Form (3NF). 3NF tries to identify and eliminate Non-key → Non-key dependency.

This Functional Dependency is not permitted in Boyce-Codd Normal Form (BCNF), because BCNF expects the determiner should be a candidate key. In our example, A is not a candidate key. This is why BCNF is termed as strict 3NF.

3NF is always achievable. BCNF is not. BCNF may result in Loss-less Join Decomposition and lead to loss of Dependency Preserving Decompositions.

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

Table 1 – 3NF – BCNF Comparisons

*********

Related Links

• Go to Normal Form - Home page

• Go to Normalization - Home page

• Go to Normalization Solved Exercises - Home page

• Go to Comparison of All Normal Forms page

Compare third normal form 3nf and boyce codd normal form bcnf

Exam Questions - Normalization, File Organization, Indexing and Hashing

Exam Questions - Normalization, File Organization, Indexing and Hashing

1. List some advantages of DBMS over File Processing System.
2. Define the different levels of data abstraction
3. What do you mean by degree of relationship set?
4. What does DDL interpreter do?
5. What is Multi-valued dependency?
6. Define Loss-less join decomposition.
7. What do you mean by Full Functional Dependency?
8. Define Domain Key Normal Form (DKNF)
9. Define Query Optimization
10. What do you mean by Data transfer rate? How does it helpful in query processing?
11. Define average latency time, mean time to failure, and average seek time.
12. What are the responsibilities of Buffer manager?
13. Discuss the Toss-immediate buffer management strategy with an example.
14. Why the Most Recently Used (MRU) block replacement is considered the optimal one in database?
15. Define heap file organization, sequential file organization, and hash file organization.
16. How do we handle insertion of new records in Sequential file organization?
17. List down various information stored in the Data dictionary.
18. Does the allocation of records to blocks affect database-system performance? Why?
19. Define Dangling pointer.
20. List and define the types of indices.
21. Are primary index and clustering index meaning the same thing?
22. List and define the types of ordered indices.
23. List some advantages of dense index.
24. List some advantages of sparse index.
25. Why the sequential scan in primary index is efficient?
26. List some disadvantages of sequential file organization.
27. Why do we need a hash function which can both distribute uniformly and randomly?
28. Define skew.
29. What are the various issues one should consider while choosing File organization and Indexing techniques?
30. For executing a query which specifies a range of values in the WHERE clause, among Ordered indexing and Hash indexing, which is preferable? Why?
31. What would be the result of the SQL statement CREATE UNIQUE INDEX idxA ON TabA(A), if the column A is not a candidate key?
32. Differentiate Primary and Secondary index.
33. How could we reduce the occurrence of bucket overflows in hash file organization?
34. How does a database index work?
35. What is hash table?
36. What are the problems with static hashing?
37. What does an index record contain?
38. For a query like ‘SELECT * FROM Emp WHERE EmpNo = ‘E1’’, hashing is the best choice. Why?
39. How does Denormalization help in improving performance of the system?
40. How does Dynamic hashing manage file expansion?