SQL Normalization - 1NF 2NF 3NF
1 1NF
1.1 1NF - CounterExamples
1.1.1 Repeating groups
| StudentID | Subjects |
|-----------|------------------------|
| 1 | Math, Physics, Chemistry|
| 2 | Biology, History |Issue: The “Subjects” column violates 1NF because it contains multiple values, creating a repeating group. To bring it to 1NF, you’d split the subjects into separate rows.
1.1.2 Composite Attributes
| EmployeeID | Contact |
|------------|------------------------|
| 101 | (555) 123-4567, email@example.com |
| 102 | (555) 987-6543, another@email.com |Issue: The “Contact” column contains a composite attribute with phone number and email. To adhere to 1NF, you’d separate these into distinct columns.
1.1.3 Multivalued Attributes:
| MovieID | Genres |
|---------|------------------------|
| 1 | Action, Comedy |
| 2 | Drama, Romance |Issue: The “Genres” column violates 1NF by holding multiple values. To conform to 1NF, you’d have a separate row for each genre and movie combination.
1.1.4 Arrays or lists
| OrderID | Products |
|---------|------------------------|
| 1 | [Item1, Item2, Item3] |
| 2 | [Item2, Item4] |Issue: The “Products” column is an array or list, violating 1NF. You’d split these into separate rows with one product per row.
2 2NF
2.1 2NF - CounterExamples
2.1.1 Partial Dependencies
| OrderID | Product | Category |
|---------|----------|-------------|
| 1 | Laptop | Electronics |
| 1 | Printer | Electronics |
| 2 | Pen | Stationery |
| 2 | Pencil | Stationery |Issue: The “Category” depends only on part of the primary key (OrderID), not the whole key. To satisfy 2NF, you’d separate this into two tables: one for orders and products, and another for products and categories.
2.1.2 Composite Primary Key with Dependencies
| EmployeeID | ProjectID | ProjectName |
|------------|-----------|-------------|
| 101 | 1 | ProjectA |
| 101 | 2 | ProjectB |
| 102 | 1 | ProjectA |Issue: Both “EmployeeID” and “ProjectID” form the composite primary key, and “ProjectName” depends on only “ProjectID,” violating 2NF. To satisfy 2NF, you’d separate this into two tables: one for employees and projects, and another for projects and project names.
2.1.3 Non-atomic attributes
| OrderID | Product |
|---------|-------------------|
| 1 | Laptop, Printer |
| 2 | Pen, Pencil |Issue: The “Product” column violates 2NF because it is not atomic. To conform to 2NF, you’d separate this into two tables: one for orders and products, and another for products.
3 3NF
3.1 3NF - CounterExamples
3.1.1 Transitive Dependency
| EmployeeID | Department | Location |
|------------|-------------|--------------|
| 101 | HR | BuildingA |
| 102 | IT | BuildingB |Issue: “Location” depends on “Department,” and “Department” depends on “EmployeeID.” This is a transitive dependency. To satisfy 3NF, you’d separate this into three tables: one for employees, one for departments, and one for locations.
3.1.2 Derived Attribute
| StudentID | Course | Credits | GPA |
|-----------|---------|---------|------|
| 1 | Math | 3 | 3.5 |
| 2 | Physics | 4 | 3.2 |Issue: “GPA” is a derived attribute, as it can be calculated from “Credits” and other information. To satisfy 3NF, you’d store only the raw data in one table (student and course information) and calculate the GPA when needed.
3.1.3 Non-atomic Attributes in a Composite Key
| OrderID | Product | SupplierName |
|---------|-------------------|----------------|
| 1 | Laptop | ABC Electronics|
| 1 | Printer | XYZ Suppliers |Issue: “SupplierName” depends on part of the composite key (“Product”), violating 3NF. To satisfy 3NF, you’d separate this into two tables: one for orders and products, and another for products and suppliers.
