WorksheetsDatabase Normal Forms and Decomposition Worksheet
Total questions: 20
Worksheet time: 10mins
Given R(A, B, C, D) with FDs: A → B, B → C, C → D. Which is the highest normal form of R?
1NF
2NF
3NF
BCNF
If a decomposition of R into R1 and R2 is lossless, which condition must be true?
R1 ∩ R2 → R1
R1 ∩ R2 → R2
(R1 ∩ R2) → R1 or R2
Both R1 and R2 must be in BCNF
Consider R(A, B, C) with MVD A →→ B. Which decomposition is correct for 4NF?
R1(A, B), R2(A, C)
R1(B, C), R2(A, C)
R1(A, B, C)
R1(A), R2(B, C)
Which situation indicates that the relation is not in BCNF even though it is in 3NF?
A non-key attribute determines another non-key attribute
A composite key has a partial dependency
A non-prime attribute determines a key attribute
A determinant is not a candidate key
You decompose a table into 3NF, but some FDs are not preserved. What is the consequence?
Join becomes lossy
Query optimization becomes harder
You cannot enforce all constraints without joins
MVDs will vanish
Relation R(A, B, C, D) has FD: AB → C and C → D. What is the candidate key?
AB
ABC
ABD
A
Which of the following indicates the need for 4NF?
Partial dependency
Transitive dependency
Attribute depends on many independent multi-valued facts
Candidate keys are not unique
In a schema R(A, B, C, D), if A → B and C → D, which decomposition ensures BCNF without losing FDs?
R1(A, B), R2(C, D)
R1(A, C), R2(B, D)
R1(A, B, C), R2(C, D)
R1(A, C, D), R2(B)
Which design choice is preferable when FD preservation is more important than BCNF compliance?
Use 4NF decomposition
Use 3NF decomposition
Use 5NF decomposition
Use unnormalized design
If a decomposition is dependency-preserving but lossy, what does it imply?
Some FDs are lost
Some original tuples cannot be recovered by join
Extra tuples appear after join
The decomposed tables are not in 1NF
Which of the following is a multi-valued dependency?
A →→ B
A → B
A ↔ B
A ⊆ B
A decomposition is lossless if and only if:
No redundancy is removed
The join of decomposed relations gives original relation
All FDs remain preserved
Data duplication occurs
Which condition ensures a lossless-join decomposition for R(A,B,C)?
A → B
A → C or C → A
AB → C
C → B only
Which of the following indicates redundancy in a database?
Functional dependency
Partial dependency
Atomic values
Primary key
Normalization is a process used to:
Increase redundancy
Reduce anomalies
Increase file size
Reduce access speed
Which of the following anomalies can normalization reduce?
Insertion, deletion, update
Deadlock
Indexing
Encryption
In alternative design approaches, ER modeling is used for:
Network administration
Conceptual schema design
If a relation has attributes that depend on non-key attributes, it violates:
1NF
2NF
3NF
BCNF
A dependency A → B holds if:
Two tuples with same A have same B
Two tuples with same B have same A
A and B are always equal
A is a key
Normalization using MVDs helps remove:
Full dependencies
Partial dependencies
Transitive dependencies
Repeating groups in multiple rows
