Options A, B, and D are the tables that are in 3NF.
A: 2NF should comprise 1NF, and 3NF should be of both 1NF and 2NF. Since one of the normal form is here in the option, this qualifies for the right option.
D: This is right because normalization aims to reduce redundancy and inconsistency, etc.
For example if there is table where , emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is dependent on emp_id that makes non-prime attributes (emp_state, emp_city & emp_district) transitively dependent on super key (emp_id). This violates the rule of 3NF.
To make this table complies with 3NF we have to break the table into two tables to remove the transitive dependency.
B: A table design is said to be in 3NF if both the following conditions hold:
- Table must be in 2NF
- Transitive functional dependency of non-prime attribute on any super key should be removed.
As 3NF has the attribute that of 2NF too, take a table with teacher_id and teacher_age.
It is not in 2NF because non - prime attribute teacher_age is dependent on teacher_id alone, which is a proper subset of the candidate key. This violates the rule for 2NF as a rule says, “no non-prime attribute is dependent on the proper subset of any candidate key of the table.” To make the table complies with 2NF, we can break it into two tables.
Q1 Normalization
Which of the following conditions must be true for a table to be in 3NF?
Note: More than one option may be correct.
A. The table should be in 1NF
B. No non-prime attribute should be dependent on the partial candidate key
C. All non-prime attributes should be dependent on the partial candidate key
D. No non-prime attribute should have transitive dependency on the super key