Relational Database Schema Design u0026 Normalization Audio Quiz Quiz

1. Purpose of Normalization

   Why is database normalization important in the design of relational schemas?

   1. It helps eliminate redundant data and inconsistent dependencies.
   2. It increases the number of joins required for all queries.
   3. It allows storing unrelated data in the same table.
   4. It guarantees the fastest performance in all cases.
   5. It makes it easier to store multiple values in a single column.

2. First Normal Form (1NF)

   Which of the following tables violates first normal form?

   1. A table with a single column storing an array of phone numbers per row.
   2. A table where each row stores a single value for each attribute.
   3. A table with a numeric primary key.
   4. A table that contains unique row identifiers.
   5. A table that has columns such as first_name and last_name.

3. Second Normal Form (2NF)

   Suppose you have a table with a composite primary key (OrderID, ProductID) and a column ProductName. What is the normalization issue?

   1. ProductName should depend only on ProductID, not on both OrderID and ProductID.
   2. ProductName should depend only on OrderID.
   3. ProductName should be split into firstName and lastName.
   4. ProductName can depend on any key in the table.
   5. ProductName should not be included in the table.

4. Third Normal Form (3NF)

   A student table includes StudentID, Name, Advisor, and AdvisorRoom columns. What causes it to fail third normal form?

   1. AdvisorRoom is dependent on Advisor, not StudentID.
   2. StudentID is not unique.
   3. AdvisorRoom is a numeric field.
   4. There is no primary key.
   5. Name should be split into first and last names.

5. Naming Conventions Best Practice

   Which of the following is the best practice for naming columns in a relational database?

   1. Use clear, descriptive names like CustomerAddress.
   2. Use abbreviated names such as CAddrss.
   3. Include spaces in the name, like 'Customer Address'.
   4. Add metadata prefixes, such as tblCustomer.
   5. Mix camelCase and snake_case within the same schema.

6. Surrogate vs Natural Keys

   What is an important guideline when using surrogate keys in database design?

   1. Ensure there is also a unique natural key to enforce real-world uniqueness.
   2. Rely only on surrogate keys and ignore natural uniqueness.
   3. Use only string-based surrogate keys.
   4. Never use surrogate keys in any table.
   5. Surrogate keys should be user-facing IDs.

7. Domain Tables

   What is a common problem with using a single 'catch-all' domain table for different kinds of lookup values?

   1. It mixes unrelated data, making joins and queries unnatural.
   2. It improves query performance significantly.
   3. It enforces foreign key integrity perfectly.
   4. It prevents the need for documentation.
   5. It is always preferred for normalization.

8. Documenting Schemas

   Why should you document tables and columns in your database schema?

   1. So other developers and future maintainers can easily understand the data model.
   2. To make the table names longer.
   3. To avoid using any constraints.
   4. So that the schema can be exported as plain text only.
   5. To store business rules only in application code.

9. Constraints and Business Rules

   Which business rules should ideally be enforced at the database level?

   1. Fundamental, non-changing rules such as nullability and valid value ranges.
   2. Temporarily changing rules like maximum monthly discounts.
   3. Visual formatting rules.
   4. Presentation-layer requirements.
   5. All validation should be done only in the application layer.

10. Performance and Normalization

    Which statement best reflects practical advice regarding normalization and performance?

    1. Normalize until it hurts, then denormalize until it works.
    2. Always fully normalize, no matter the cost.
    3. Never normalize if you can avoid it.
    4. The more tables, the slower your database will be, without exception.
    5. Denormalize by default, as normalization is always slow.