Database Normalization: 1NF to 5NF Essentials Quiz Quiz

Explore key principles of database normalization, from First Normal Form (1NF) through Fifth Normal Form (5NF). Assess your understanding of data redundancy, functional dependencies, and how each normal form improves database organization and consistency.

1. Identifying 1NF Violations

   Which of the following scenarios violates First Normal Form (1NF) in a relational table?

   1. Each column holds only integers.
   2. A column contains multiple phone numbers in a single field, separated by commas.
   3. All rows are uniquely identified by a primary key.
   4. A row has a null value in an optional column.

   Explanation: 1NF requires that each field in a table contains only atomic, indivisible values. Storing multiple phone numbers in one field violates this rule because the data is not atomic. Having null values in optional columns or ensuring unique primary keys do not violate 1NF. Data type uniformity in columns is good practice, but 1NF specifically addresses atomicity rather than types.

2. Understanding 2NF Requirements

   If a table is in 1NF but has partial dependencies on a composite primary key, which normal form is not satisfied?

   1. Boyce-Codd Normal Form (BCNF)
   2. Fourth Normal Form (4NF)
   3. Third Normal Form (3NF)
   4. Second Normal Form (2NF)

   Explanation: 2NF requires that there are no partial dependencies of non-prime attributes on any candidate key, which addresses issues with composite keys. If partial dependencies exist, the table is not in 2NF. 3NF and BCNF build on 2NF but address different types of dependencies. 4NF concerns multi-valued dependencies, which is a different concept.

3. Exploring 3NF

   Which situation prevents a table from being in Third Normal Form (3NF)?

   1. All non-key attributes depend only on the primary key.
   2. A non-key attribute depends on another non-key attribute.
   3. The table has a single-column primary key.
   4. Every non-key attribute is atomic.

   Explanation: 3NF requires that all non-key attributes depend only on the primary key and not on other non-key attributes (i.e., no transitive dependency). If a non-key attribute depends on another non-key attribute, it breaks 3NF. Atomicity is a 1NF requirement, and having a single-column primary key does not inherently cause 3NF violations. The correct dependency structure is essential for 3NF.

4. Recognizing BCNF Violations

   Which scenario is a violation of Boyce-Codd Normal Form (BCNF) but may still be in 3NF?

   1. A non-trivial functional dependency has a determinant that is not a candidate key.
   2. All attributes are atomic.
   3. There are multi-valued dependencies in a table.
   4. All non-key attributes rely on the full primary key.

   Explanation: BCNF strengthens 3NF by requiring that every determinant must be a candidate key. If a non-candidate key determinant exists, BCNF is violated even if the table is in 3NF. Attributes being atomic addresses 1NF, and full key dependency is a concern of 2NF. Multi-valued dependencies are addressed by 4NF, not BCNF.

5. Understanding 4NF and Multi-Valued Dependencies

   In the context of Fourth Normal Form (4NF), which issue does 4NF specifically address that previous normal forms do not?

   1. Transitive dependencies among non-key attributes
   2. Partial dependencies on a composite primary key
   3. Denormalized data with repeated groups
   4. Multi-valued dependencies that are independent of each other

   Explanation: 4NF eliminates non-trivial multi-valued dependencies by ensuring that independent multi-valued facts are stored in separate tables. Transitive dependencies are handled by 3NF, and partial dependencies by 2NF. Repeated groups and denormalized data are mostly covered in 1NF.

6. Spotting 5NF Needs

   When would Fifth Normal Form (5NF) decompositions be required for a table?

   1. When information can only be reconstructed through joining several decomposed tables based solely on candidate keys
   2. When there are transitive dependencies among non-key fields
   3. When attributes are not atomic
   4. When there are simple partial dependencies

   Explanation: 5NF is necessary if a table contains join dependencies that cannot be represented by decomposing on candidate keys alone. This often happens with complex relationships requiring further decomposition. Transitive dependencies are relevant for 3NF, and atomicity relates to 1NF. Partial dependencies are resolved by 2NF.

7. Recognizing a 1NF Table

   Which description best fits a table that is correctly in First Normal Form (1NF)?

   1. There are non-key attributes that depend on another non-key attribute.
   2. Attributes are grouped, sometimes having multiple values in one field.
   3. All fields contain only a single, indivisible value per cell.
   4. There are no nulls in any field.

   Explanation: 1NF requires that every cell contains a single atomic value and that there are no repeating groups. Having no nulls is not a requirement for 1NF. Non-key attributes depending on other non-keys is a 3NF issue, and fields with multiple values break the atomicity required by 1NF.

8. Identifying 2NF Compliance

   Given a table with a composite primary key (OrderID, ProductID), which situation would violate Second Normal Form (2NF)?

   1. ProductPrice depends on both OrderID and ProductID together.
   2. There is a unique identifier for every row.
   3. The field ProductName depends only on ProductID, not on the full composite key.
   4. Each attribute is strictly atomic.

   Explanation: 2NF requires that non-prime attributes are fully functionally dependent on the entire composite primary key, not just part of it. If ProductName depends only on ProductID, it creates a partial dependency, which is not allowed in 2NF. Atomicity, unique identifiers, and dependencies on the whole key do not violate 2NF.

9. Recognizing a Table in 3NF

   Which scenario best shows that a table is in Third Normal Form (3NF)?

   1. Every non-key attribute depends only on the primary key and not on other non-key attributes.
   2. Several columns contain repeating groups.
   3. All candidate keys are comprised of more than one column.
   4. At least one non-key attribute is dependent on only part of a composite key.

   Explanation: 3NF ensures that all non-key attributes are fully and only dependent on the primary key with no transitive dependencies. Repeating groups violate 1NF, and partial dependencies indicate violation of 2NF. The number or composition of candidate keys is not the main concern for 3NF.

10. Multi-valued Dependency Example

    Which of the following is an example of a multi-valued dependency in a database table?

    1. An attribute only depends on a part of a candidate key.
    2. A table contains a column for employee's alternative names.
    3. A non-key attribute determines another non-key attribute.
    4. Each employee can have multiple skills and multiple projects assigned, independently.

    Explanation: A multi-valued dependency arises when two or more attributes are independent but both depend on the same attribute, such as employees having multiple skills and multiple projects unconnected to each other. Dependencies involving only part of a candidate key are partial, not multi-valued. Non-key attributes determining others is a transitive dependency. Alternative names in one column does not illustrate independent multi-valued dependencies.