Data Normalization Examples: Implementing 1NF and 2NF in Real Databases

📚 Quick Study Guide

🔍 First Normal Form (1NF): Eliminate repeating groups. Ensure each column contains only atomic (indivisible) values. 💡 Second Normal Form (2NF): Must be in 1NF and eliminate partial dependencies. A non-key attribute should be fully functionally dependent on the primary key. 📝 Partial Dependency: When a non-key attribute depends only on part of the composite primary key, not the entire key. 🧪 Atomic Value Example: Instead of storing 'John, Doe' in a single name field, split it into 'FirstName' and 'LastName' fields. 🌍 Functional Dependency Notation: $X \rightarrow Y$ (Attribute X determines attribute Y). 🔢 Prime Attribute: An attribute that is part of any candidate key. 🧠 Non-Prime Attribute: An attribute that is not part of any candidate key.

Practice Quiz

1. Which of the following best describes First Normal Form (1NF)?
   1. A) Eliminate all redundancy.
   2. B) Eliminate repeating groups and ensure atomic values.
   3. C) Eliminate partial dependencies.
   4. D) Eliminate transitive dependencies.
2. Consider a table with attributes StudentID, CourseID, and CourseName. If StudentID + CourseID is the primary key, and CourseName depends only on CourseID, which normal form is violated?
   1. A) 1NF
   2. B) 2NF
   3. C) 3NF
   4. D) BCNF
3. What is an 'atomic value' in the context of database normalization?
   1. A) A value that can be further subdivided.
   2. B) A value that cannot be further subdivided.
   3. C) A primary key value.
   4. D) A foreign key value.
4. In a table with a composite primary key (A, B), attribute C is dependent only on A. What does this violate?
   1. A) Full functional dependency
   2. B) Transitive dependency
   3. C) 1NF
   4. D) Atomicity
5. Which normal form addresses partial dependencies?
   1. A) 1NF
   2. B) 2NF
   3. C) 3NF
   4. D) 4NF
6. A table has attributes: EmployeeID (PK), ProjectID (PK), EmployeeName, ProjectName. If ProjectName only depends on ProjectID, what normalization issue exists?
   1. A) Repeating groups
   2. B) Partial dependency
   3. C) Transitive dependency
   4. D) Full functional dependency
7. What is the primary goal of normalization in databases?
   1. A) To increase data redundancy
   2. B) To decrease data redundancy and improve data integrity
   3. C) To complicate database design
   4. D) To slow down query performance