Normalization for Relational Databases - functional dependencies, normalization - Question Bank

1. Which normal form is defined as being in 3NF and having no transitive dependencies of non-prime attributes on any candidate key?
A) 1NF
B) 2NF
C) BCNF
D) 5NF
2. The goal of normalization is to design a database schema that:
A) Minimizes storage space at the expense of data integrity.
B) Maximizes data redundancy to ensure availability.
C) Minimizes data redundancy and avoids anomalies.
D) Simplifies query writing by having fewer tables.
3. Consider a relation R(A, B, C) with primary key A and functional dependencies A -> B, A -> C. This relation is:
A) In 1NF only
B) In 2NF only
C) In 3NF and BCNF
D) In violation of 3NF
4. If a relation is in 1NF, it means:
A) All attributes are atomic, and there are no transitive dependencies.
B) All attributes are atomic, and there are no partial dependencies.
C) All attributes are atomic, and every non-prime attribute is fully functionally dependent on the primary key.
D) All attributes contain only single, indivisible values.
5. What is the main challenge with BCNF decomposition if dependency preservation is desired?
A) BCNF decomposition always preserves dependencies.
B) BCNF decomposition may not preserve all dependencies.
C) BCNF decomposition is only possible if the relation is already in 3NF.
D) BCNF decomposition requires multivalued dependencies.
6. Which of the following is a valid inference rule from Armstrong's axioms?
A) Transitivity Rule
B) Decomposition Rule
C) Union Rule
D) All of the above
7. Consider a relation R(Student_ID, Student_Name, Advisor_ID, Advisor_Name). Dependencies: Student_ID -> Student_Name, Advisor_ID -> Advisor_Name, Student_ID -> Advisor_ID. Primary Key: Student_ID. This relation has a transitive dependency of Student_Name on Student_ID via Advisor_ID. To achieve 3NF, what decomposition is suitable?
A) R1(Student_ID, Student_Name), R2(Advisor_ID, Advisor_Name)
B) R1(Student_ID, Advisor_ID), R2(Advisor_ID, Advisor_Name)
C) R1(Student_ID, Student_Name, Advisor_ID), R2(Advisor_ID, Advisor_Name)
D) R1(Student_ID, Student_Name), R2(Student_ID, Advisor_ID), R3(Advisor_ID, Advisor_Name)
8. What is the purpose of the Armstrong Axioms in relation to functional dependencies?
A) To define the properties of normalization
B) To describe a sound and complete set of inference rules for functional dependencies
C) To determine the primary key of a relation
D) To identify all possible candidate keys
9. A relation R(A, B, C) has primary key A, and the functional dependency B -> C exists. If B is not a superkey, this violates which normal form?
A) 1NF
B) 2NF
C) 3NF
D) BCNF
10. Which of the following is a characteristic of Boyce-Codd Normal Form (BCNF)?
A) It ensures dependency preservation for all functional dependencies.
B) Every determinant is a superkey.
C) It eliminates partial dependencies.
D) It eliminates transitive dependencies of non-prime attributes.
11. In the context of normalization, denormalization is the process of:
A) Increasing the number of tables to reduce redundancy
B) Merging tables or adding redundant data to improve query performance
C) Ensuring all attributes are atomic
D) Eliminating all functional dependencies
12. Consider a relation R(StudentID, StudentName, CourseID, Grade). Dependencies: StudentID -> StudentName, StudentID, CourseID -> Grade. Primary Key: (StudentID, CourseID). Which normal form is this relation in?
A) 1NF
B) 2NF
C) 3NF
D) BCNF
13. What is a non-prime attribute?
A) An attribute that is part of the primary key
B) An attribute that is not part of any candidate key
C) An attribute that is part of at least one candidate key
D) An attribute that is always unique
14. Which normal form is considered the most desirable for most practical applications, balancing normalization benefits with performance considerations?
A) 1NF
B) 2NF
C) 3NF
D) BCNF
15. If a relation R(A, B, C) has primary key A, and dependencies A->B and B->C, which is a transitive dependency?
A) A -> B
B) B -> C
C) A -> C
D) None of the above
16. What is the role of the closure of a set of attributes X, denoted X+?
A) It is the set of all attributes that determine X.
B) It is the set of all attributes that are functionally determined by X.
C) It is the set of all attributes in the relation.
D) It is the set of all prime attributes.
17. Consider a relation R(A, B, C, D) with primary key A, and functional dependencies A -> B, B -> C, C -> D. To achieve BCNF, what decomposition is needed?
A) R1(A, B), R2(B, C), R3(C, D)
B) R1(A, B), R2(C, D)
C) R1(A, D), R2(B, C)
D) R1(A, B, C), R2(C, D)
18. Which normal form is primarily concerned with multivalued dependencies?
A) 2NF
B) 3NF
C) BCNF
D) 4NF
19. When decomposing a relation R into R1 and R2, what is the dependency preservation property?
A) All functional dependencies of R can be enforced by checking dependencies in either R1 or R2.
B) All functional dependencies of R can be inferred from the dependencies in R1 and R2.
C) The decomposition process does not lose any functional dependencies.
D) The decomposition ensures that the primary key of R is preserved in R1 or R2.
20. What is the 'lossless join' property in database decomposition?
A) The decomposition results in relations that can be joined back to form the original relation without any extraneous tuples.
B) The decomposition ensures that no data is lost during the process.
C) The join operation between decomposed relations is always fast.
D) The decomposition prevents update anomalies.
21. Consider the relation R(EmployeeID, EmployeeName, DepartmentID, DepartmentName). Functional dependencies: EmployeeID -> EmployeeName, DepartmentID -> DepartmentName, EmployeeID -> DepartmentID. Primary Key: EmployeeID. Which normal form is this relation in?
A) 1NF
B) 2NF
C) 3NF
D) BCNF
22. Which normal form requires that for every non-trivial functional dependency X -> Y, X must be a superkey, AND for every non-trivial multivalued dependency X ->> Y, X must be a superkey?
A) 3NF
B) BCNF
C) 4NF
D) 5NF
23. If a relation is in 3NF, it means:
A) It is free from partial and transitive dependencies.
B) It is free from partial dependencies and transitive dependencies of non-prime attributes on the primary key.
C) It is free from all types of functional dependencies except those on the primary key.
D) It is free from transitive dependencies and all determinants are superkeys.
24. What is the main purpose of creating candidate keys?
A) To simplify query writing
B) To identify all possible primary keys for a relation
C) To enforce data uniqueness
D) To reduce the number of attributes in a table
25. Which of the following functional dependencies violates BCNF in a relation R(A, B, C) with primary key A?
A) A -> B
B) A -> C
C) B -> C
D) A -> BC
26. A relation is in 2NF if it is in 1NF and:
A) There are no transitive dependencies
B) Every non-prime attribute is fully functionally dependent on every candidate key
C) Every non-prime attribute is fully functionally dependent on the primary key
D) All determinants are superkeys
27. What is a superkey?
A) A key that contains only one attribute
B) A minimal set of attributes that uniquely identifies a tuple
C) A set of attributes that uniquely identifies a tuple (not necessarily minimal)
D) A key that is not a primary key
28. Consider a relation Employee(EmpID, EmpName, DeptID, DeptName, ProjectID, ProjectName). If EmpID -> EmpName, DeptID -> DeptName, ProjectID -> ProjectName, and EmpID, DeptID, ProjectID together form the primary key. To achieve 3NF, what decomposition might be necessary?
A) Decompose into Employee(EmpID, DeptID, ProjectID) and EmployeeDetails(EmpID, EmpName)
B) Decompose into Employee(EmpID, EmpName, ProjectID) and Department(DeptID, DeptName)
C) Decompose into Employee(EmpID, EmpName) and Assignment(EmpID, DeptID, ProjectID, DeptName, ProjectName)
D) Decompose into Employee(EmpID, EmpName, DeptID, ProjectID) and Department(DeptID, DeptName) and Project(ProjectID, ProjectName)
29. If a relation R is in BCNF, does it guarantee that it is also in 3NF?
A) Yes, BCNF is a stricter form of 3NF
B) No, BCNF can sometimes violate 3NF
C) Only if the relation has a single-attribute primary key
D) No, BCNF is a weaker form of 3NF
30. Which of the following is NOT a type of anomaly addressed by normalization?
A) Insertion Anomaly
B) Deletion Anomaly
C) Update Anomaly
D) Concurrency Anomaly
31. What is a trivial functional dependency?
A) A dependency where Y is a subset of X
B) A dependency where X determines Y, and Y is a proper subset of X
C) A dependency where X is an empty set
D) A dependency where Y is the primary key
32. To achieve BCNF, a relation R must satisfy which condition?
A) It must be in 3NF and for every non-trivial functional dependency X -> Y, X must be a superkey.
B) It must be in 2NF and all non-prime attributes must depend on the full primary key.
C) All attributes must be atomic.
D) There should be no transitive dependencies.
33. Consider a relation Student(StudentID, StudentName, CourseID, CourseName, InstructorID, InstructorName). If StudentID determines StudentName, CourseID determines CourseName, and InstructorID determines InstructorName, but also StudentID, CourseID, InstructorID together form the primary key, what kind of dependencies exist?
A) Only functional dependencies
B) Partial dependencies and functional dependencies
C) Transitive dependencies
D) Multivalued dependencies
34. What is the process of decomposing a relation into smaller, well-structured relations called?
A) Aggregation
B) Generalization
C) Denormalization
D) Normalization
35. If a relation has a composite primary key (A, B) and an attribute C is dependent on A only (A -> C), this represents a:
A) Transitive Dependency
B) Full Functional Dependency
C) Partial Dependency
D) Multivalued Dependency
36. A relation is in Third Normal Form (3NF) if it is in 2NF and:
A) All non-prime attributes are fully functionally dependent on the primary key
B) There are no transitive dependencies of non-prime attributes on the primary key
C) Every determinant is a superkey
D) All attributes are atomic
37. Which normal form is considered the 'all-singing, all-dancing' normal form and is a stronger version of 3NF?
A) 1NF
B) 2NF
C) BCNF
D) 4NF
38. A deletion anomaly happens when:
A) A record is added, but its associated information cannot be stored
B) Changing a record unintentionally removes other, unrelated data
C) Updating a record requires modifying it in several locations
D) A non-prime attribute depends on another non-prime attribute
39. An update anomaly occurs when:
A) A record is deleted, and its related information is lost
B) Information needs to be updated in multiple records, and if one is missed, inconsistencies arise
C) New data can only be inserted if some attributes are unknown
D) A non-key attribute determines a part of the primary key
40. Which of the following is an example of an insertion anomaly?
A) Deleting a record unintentionally removes other related data
B) Modifying a piece of data requires changing it in multiple places
C) It is impossible to add new information about an entity without also having information about another unrelated entity
D) Redundant data takes up excessive storage space
41. What is the main problem that normalization aims to solve in databases?
A) Slow query execution
B) Data anomalies like insertion, deletion, and update anomalies
C) Lack of security
D) Difficulty in data retrieval
42. In the context of normalization, what is a prime attribute?
A) An attribute that is not part of any candidate key
B) An attribute that is part of at least one candidate key
C) An attribute that is part of the primary key only
D) An attribute that determines other attributes
43. Consider a relation R(A, B, C) with functional dependencies A -> B and B -> C. If A is the primary key, what type of dependency is A -> C?
A) Partial Dependency
B) Transitive Dependency
C) Functional Dependency
D) Non-trivial Dependency
44. Which normal form addresses partial dependencies?
A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
D) Fourth Normal Form (4NF)
45. What is a partial dependency?
A) A dependency where a non-prime attribute determines a prime attribute
B) A dependency where a non-key attribute depends on another non-key attribute
C) A dependency where a non-prime attribute is functionally dependent on only a part of the composite primary key
D) A dependency where the determinant is not a superkey
46. Which normal form requires that every non-trivial functional dependency X -> Y has X as a superkey?
A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
D) Boyce-Codd Normal Form (BCNF)
47. A relation is in First Normal Form (1NF) if it contains only atomic values. What does 'atomic' mean in this context?
A) Values can be broken down into smaller components
B) Each attribute value is indivisible and contains a single value
C) Values are unique across all rows
D) Values are directly related to the primary key
48. Which normal form aims to eliminate transitive dependencies?
A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
D) Boyce-Codd Normal Form (BCNF)
49. In a functional dependency X -> Y, what is X referred to as?
A) Dependent
B) Determinant
C) Candidate Key
D) Primary Key
50. Which type of dependency states that for any two tuples in a relation, if their values for attribute set X are equal, then their values for attribute set Y must also be equal?
A) Transitive Dependency
B) Partial Dependency
C) Functional Dependency
D) Multivalued Dependency
All Question Banks
E-Book Normalization for Relational Databases - functional dependencies, normalization One Line Questions Normalization for Relational Databases - functional dependencies, normalization Online Test Normalization for Relational Databases - functional dependencies, normalization