Database Normalization Quiz: 1NF & 2NF

Questions and Answers

What is a key characteristic of a table in First Normal Form (1NF)?

It can have non-unique rows.

Attributes can depend on other non-key attributes.

It can contain repeating groups of values.

Each entry must be a single, atomic value. (correct)

Which condition must be met for a table to be in Second Normal Form (2NF)?

It can have partial dependencies on non-key attributes.

All non-key attributes must be fully functionally dependent on the primary key. (correct)

All attributes must depend solely on the primary key.

It must be in Third Normal Form.

What exemplifies a violation of Third Normal Form (3NF)?

All attributes being functionally dependent on the primary key.

An attribute depending solely on a primary key.

A composite key with all attributes in separate tables.

A non-key attribute depending on another non-key attribute. (correct)

What is the primary purpose of database normalization?

To improve data integrity and reduce redundancy. Signup and view all the answers

Which of the following describes a composite primary key?

A primary key made up of multiple fields. Signup and view all the answers

What should not be present in a table that is in Second Normal Form (2NF)?

Attributes that depend only on part of a composite key. Signup and view all the answers

Which example illustrates a valid First Normal Form (1NF) setup?

A table with separate rows for each customer and their individual phone number. Signup and view all the answers

What is a characteristic of a table in Third Normal Form (3NF)?

No transitive dependencies among non-key attributes. Signup and view all the answers

Study Notes

DTS Normalization Tables

First Normal Form (1NF)

Definition: A table is in 1NF if it contains only atomic (indivisible) values and each record is unique.
Key Characteristics:
- Each column contains unique values.
- Each entry in the table is a single value (no repeating groups or arrays).
- Each row can be uniquely identified by a primary key.
Example:
- Invalid: A column containing multiple phone numbers for a single customer.
- Valid: Separate rows for each phone number associated with the customer.

Second Normal Form (2NF)

Definition: A table is in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.
Key Characteristics:
- Eliminates partial dependency; non-key attributes should depend on the entire primary key, not just part of it (relevant for composite keys).
- If a table is in 2NF, it should not have any attributes that depend only on part of a multi-column key.
Example:
- A composite primary key of (StudentID, CourseID) with an attribute (InstructorName) should not depend solely on CourseID.

Third Normal Form (3NF)

Definition: A table is in 3NF if it is in 2NF and all the attributes are functionally dependent only on the primary key.
Key Characteristics:
- Eliminates transitive dependency; non-key attributes should not depend on other non-key attributes.
- Ensures that each piece of data is stored in one place only.
Example:
- If a table has attributes (EmployeeID, DepartmentID, DepartmentName), DepartmentName should not be in the same table as EmployeeID and DepartmentID, since it depends on DepartmentID, not EmployeeID.

Summary of Normalization

Purpose: To reduce data redundancy and improve data integrity in relational databases.
Process: Progressively apply normalization forms from 1NF to 3NF to achieve a well-structured database design.

First Normal Form (1NF)

A table is in 1NF if it contains only atomic values, meaning each entry can’t be divided further.
Unique values are required in every column to ensure proper identification.
Each table row must be identifiable through a primary key.
Example of violation: A column including multiple phone numbers for one customer.
Example of compliance: Listing each phone number in separate rows linked to the same customer.

Second Normal Form (2NF)

A table meets 2NF criteria if it's in 1NF and all non-key attributes depend wholly on the primary key.
It eliminates partial dependency; all attributes must relate to the entire primary key, especially in composite keys.
A composite key example: (StudentID, CourseID) where an attribute (InstructorName) must depend on both parts, not just one.

Third Normal Form (3NF)

A table is in 3NF when it is in 2NF and non-key attributes solely depend on the primary key, not on other non-key attributes.
This normalization step removes transitive dependencies, ensuring that data is stored singularly to prevent redundancy.
Example: In a table with (EmployeeID, DepartmentID, DepartmentName), DepartmentName must be separated since it relies on DepartmentID alone, not on EmployeeID.

Summary of Normalization

Normalization aims to minimize data duplication and enhance data integrity within relational databases.
The process involves sequentially applying normalization forms from 1NF through 3NF to establish a structured database layout.

Description

Test your knowledge on database normalization by exploring the concepts of First Normal Form (1NF) and Second Normal Form (2NF). This quiz covers definitions, key characteristics, and examples that illustrate proper normalization practices in database design. Perfect for students or professionals looking to reinforce their understanding of database structures.