Database Normalization
Subject: Database Systems
Forms 1NF, 2NF, 3NF, BCNF rules.
Sample Practice Questions
Question 1: In database design, which normal form requires the table to be in 1NF and have all non-prime attributes fully functionally dependent on the primary key (no partial dependency)?
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- Fourth Normal Form (4NF)
Explanation: 2NF is violated when a non-key attribute depends on only a part of a composite primary key. Removing partial dependencies puts the table in 2NF.
Question 2: Which ACID property ensures that all operations in a database transaction are completed successfully, or none of them are applied at all?
- Consistency
- Isolation
- Durability
- Atomicity
Explanation: Atomicity is the 'all-or-nothing' property, ensuring that if any operation inside a transaction fails, the entire transaction is rolled back.
Question 3: What type of key constraint ensures that values in a column must match existing values in a primary key column of another table?
- Primary Key
- Foreign Key
- Unique Key
- Super Key
Explanation: A Foreign Key constraint enforces referential integrity by linking a column in a child table to a primary or unique key column in a parent table.
Question 4: Which SQL clause is used to filter group results after a GROUP BY operation has been performed?
- WHERE
- HAVING
- ORDER BY
- SELECT
Explanation: The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions; it filters groups rather than individual rows.
Question 5: In database normalization, what type of functional dependency is characterized by a non-key attribute depending on another non-key attribute, violating 3NF?
- Partial Dependency
- Transitive Dependency
- Trivial Dependency
- Multivalued Dependency
Explanation: A transitive dependency exists when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key. Removing transitive dependencies puts the table in 3NF.
Question 6: A domain is ______ if elements of the domain are considered to be indivisible units.
- Atomic
- Subatomic
- Substructure
- Subset
Explanation: A set of names is an example of a nonatomic value.
Question 7: Identify the composite attributes
- Salary
- Credits
- Section_id
- None of the mentioned
Explanation: Composite attributes, such as an attribute address with component attributes street, city, state, and zip have nonatomic domains.
Question 8: Consider the relation given below and ind the maximum normal form applicable to them i . R ( A , B ) WITH productions { A --> B } ii . R ( A , B ) WITH productions { B --> A } iii . R ( A , B ) WITH productions { A — > B , B --> A } iv . R ( A , B , C ) WITH productions { A -->B, B --> A, AB --> C }
- i, ii and iii are in 3NF and iv is in BCNF
- i and ii are in BCNF and iii and iv are in 3NF
- All are in 3NF
- All are in BCNF
Explanation: One of the more desirable normal forms that we can obtain is Boyce–Codd normal form (BCNF). It eliminates all redundancy that can be discovered based on functional dependencies.
Question 9: Which one is based on multi-valued dependency:
- First
- Second
- Third
- Fourth
Explanation: One of the more desirable normal forms that we can obtain is Boyce–Codd normal form (BCNF). It eliminates all redundancy that can be discovered based on functional dependencies.
Question 10: If a relation is in BCNF, then it is also in
- 1 NF
- 2 NF
- 3 NF
- All of the mentioned
Explanation: Third normal form (3NF) relaxes this constraint slightly by allowing certain nontrivial functionaldependencieswhose left side is not a superkey.