Database Normalization Quiz: 1NF & 2NF

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.