Skip to content
SQLDatabase Designintermediate45 min read

Database Normalization

Learn how normalization reduces unnecessary duplication, prevents insertion, update, and deletion anomalies, and improves data integrity through 1NF, 2NF, and 3NF.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Define database normalization and explain its goals.
  • ✓Identify insertion, update, and deletion anomalies in poorly designed tables.
  • ✓Explain atomic values, candidate keys, partial dependencies, and transitive dependencies.
  • ✓Transform a table into First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).
  • ✓Use primary keys and foreign keys to represent normalized relationships.
  • ✓Recognize when denormalization may be useful for read-heavy analytical workloads.
  • ✓Evaluate normalization decisions based on data integrity, query requirements, and maintenance costs.

1. What Is Database Normalization?

Database normalization is the process of organizing relational tables to reduce unnecessary data duplication and avoid problems when inserting, updating, or deleting records.

Imagine storing student names, course names, and instructor names in one large table.

| Student | Course | Instructor |
|---|---|---|
| Asha | SQL | Neha |
| Ravi | SQL | Neha |
| Meera | Python | Amit |

The instructor Neha appears repeatedly because several students take SQL.

If Neha's name changes, multiple rows may need updating. If one row is missed, the database can contain inconsistent information.

Normalization asks:
- Which facts belong together?
- What uniquely identifies each record?
- Which attributes depend on which keys?
- Should a fact be stored once and referenced by other records?

A normalized design might use separate students, courses, instructors, and enrollments tables, connected through keys.

Normalization does not mean that no value can ever repeat. Foreign-key values are expected to repeat in one-to-many relationships. The goal is to avoid unnecessary duplication of facts and the anomalies it can create.

Normalization is a design technique, not a SQL command. You implement it through table structures, keys, constraints, and relationships.

2. Understand Data Anomalies

Poorly designed tables can produce three common anomalies.

1. Update anomaly

Suppose instructor Neha teaches SQL to 100 students. Her name is stored in all 100 enrollment rows.

If her name changes, all 100 rows must be updated. If only 99 rows are updated, the database contains conflicting information.

2. Insertion anomaly

Suppose you want to register a new course before any students enroll.

If course details can only be stored in an enrollment table, you may be unable to add the course without inventing a student or inserting incomplete data.

3. Deletion anomaly

Suppose only one student is enrolled in a course, and the course details are stored in the same row.

Deleting that student's enrollment may accidentally remove the only stored record of the course and its instructor.

A better design stores course information independently from enrollment information.

The key idea: each fact should be stored in a place where it can be maintained independently, while relationships connect the relevant records.

Example of duplicated course information

Example of duplicated course information
sql
CREATE TABLE enrollment_records (
    student_id INTEGER NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    course_id INTEGER NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    instructor_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (student_id, course_id)
);
 
INSERT INTO enrollment_records
    (student_id, student_name, course_id, course_name, instructor_name)
VALUES
    (1, 'Asha', 101, 'SQL', 'Neha'),
    (2, 'Ravi', 101, 'SQL', 'Neha'),
    (3, 'Meera', 102, 'Python', 'Amit');
 
SELECT course_id, course_name, instructor_name
FROM enrollment_records
ORDER BY course_id, student_id;
Output
course_id | course_name | instructor_name ----------+-------------+---------------- 101 | SQL | Neha 101 | SQL | Neha 102 | Python | Amit

The composite primary key prevents duplicate student-course pairs, but course and instructor facts are still repeated across enrollment rows. A key alone does not guarantee a well-normalized design.

3. First Normal Form (1NF)

First Normal Form (1NF) requires each attribute to contain a single value appropriate to that column's data model, rather than a repeating group or a list of independent values.

Consider this design:

| student_id | student_name | courses |
|---|---|---|
| 1 | Asha | SQL, Python |
| 2 | Ravi | SQL |

The courses column contains a list of courses. Finding every student taking Python becomes awkward, and the database cannot enforce an ordinary foreign key for each course in that comma-separated list.

A relational design stores one student-course association per row.

| student_id | student_name | course |
|---|---|---|
| 1 | Asha | SQL |
| 1 | Asha | Python |
| 2 | Ravi | SQL |

This removes the repeating list, but it still repeats student names. 1NF alone does not guarantee that a table is free from all redundancy.

Important: "Atomic" depends on the intended domain. A full name may be treated as one value in a simple application, while an application that needs to query first and last names separately may model them as separate attributes. The central issue is whether a column is being used to hide a repeating collection of independently meaningful values.

Represent course enrollments as rows

Represent course enrollments as rows
sql
CREATE TABLE student_course_values (
    student_id INTEGER NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (student_id, course_name)
);
 
INSERT INTO student_course_values
    (student_id, student_name, course_name)
VALUES
    (1, 'Asha', 'SQL'),
    (1, 'Asha', 'Python'),
    (2, 'Ravi', 'SQL');
 
SELECT student_id, student_name, course_name
FROM student_course_values
ORDER BY student_id, course_name;
Output
student_id | student_name | course_name -----------+--------------+------------ 1 | Asha | Python 1 | Asha | SQL 2 | Ravi | SQL

Each row represents one student-course association. This illustrates a 1NF-style representation; additional normalization is needed to avoid repeating student facts and to reference course records consistently.

4. Keys and Functional Dependencies

To understand 2NF and 3NF, learn two important concepts.

Candidate key

A candidate key is a minimal set of attributes that uniquely identifies a row. A table can have more than one candidate key. One candidate key is chosen as the primary key.

Functional dependency

A functional dependency X -> Y means that, in the intended data model, whenever two rows agree on X, they must also agree on Y.

For example:

student_id -> student_name

This means a student ID determines the student's name, assuming student_id uniquely identifies a student.

Likewise:

course_id -> course_name

If a course ID uniquely identifies a course, the course name depends on that ID.

For a table containing student-course enrollments, a candidate key may be:

(student_id, course_id)

This pair identifies one enrollment, assuming a student can enroll in a particular course only once.

A partial dependency occurs when a non-prime attribute depends on only part of a composite candidate key.

A transitive dependency occurs when a non-key attribute depends on another non-key attribute that is determined by a key.

These dependency patterns help explain why tables are decomposed during normalization.

5. Second Normal Form (2NF)

Second Normal Form (2NF) requires:
1. The table is in 1NF.
2. Every non-prime attribute is fully functionally dependent on every candidate key; in particular, there must be no partial dependency on a proper subset of a candidate key.

A non-prime attribute is an attribute that is not part of any candidate key.

Consider:

| student_id | course_id | student_name | course_name | grade |
|---|---|---|---|---|
| 1 | 101 | Asha | SQL | A |
| 1 | 102 | Asha | Python | B |
| 2 | 101 | Ravi | SQL | A |

Assume the candidate key is (student_id, course_id).

Dependencies:
- student_id -> student_name
- course_id -> course_name
- (student_id, course_id) -> grade

The student name depends only on student_id, not on the entire composite key. The course name depends only on course_id.

These are partial dependencies, so the design does not satisfy 2NF under these assumptions.

Separate the facts into tables:

students(student_id, student_name)

courses(course_id, course_name)

enrollments(student_id, course_id, grade)

Now student_name depends on the student key, course_name depends on the course key, and grade depends on the enrollment key.

A useful shortcut for many beginner examples: if a table has a single-column candidate key, it cannot have a partial dependency on a proper subset of that key. However, it can still violate 3NF or higher normal forms.

Decompose a table to remove partial dependencies

Decompose a table to remove partial dependencies
sql
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);
Output
Three tables are created. Student names are stored in students, course names in courses, and enrollment-specific grades in enrollments.

This decomposition removes the partial dependencies described in the example. The grade depends on the student-course enrollment, rather than on only one side of the composite key.

6. Third Normal Form (3NF)

Third Normal Form (3NF) requires the table to be in 2NF and to satisfy the formal 3NF dependency condition.

For every non-trivial functional dependency X -> A, at least one of these must hold:
- X is a superkey, or
- A is a prime attribute (an attribute that belongs to at least one candidate key).

For many introductory examples, the goal is to remove transitive dependencies of non-key attributes on a key.

Consider:

| employee_id | employee_name | department_id | department_name |
|---|---|---|---|
| 1 | Asha | 10 | Engineering |
| 2 | Ravi | 10 | Engineering |
| 3 | Meera | 20 | Finance |

Assume:
- employee_id -> employee_name, department_id
- department_id -> department_name

Therefore, department_name depends on employee_id through department_id.

The department name is a fact about the department, not an individual employee. Repeating it in every employee row creates an update anomaly.

Separate the tables:

employees(employee_id, employee_name, department_id)

departments(department_id, department_name)

Now department_name is stored once per department.

This example illustrates the common 3NF decomposition. Formal normal-form assessment depends on all candidate keys and functional dependencies, not merely on whether a column appears to be "non-key."

Remove a transitive dependency

Remove a transitive dependency
sql
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);
 
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER NOT NULL
        REFERENCES departments(department_id)
);
 
INSERT INTO departments
    (department_id, department_name)
VALUES
    (10, 'Engineering'),
    (20, 'Finance');
 
INSERT INTO employees
    (employee_id, employee_name, department_id)
VALUES
    (1, 'Asha', 10),
    (2, 'Ravi', 10),
    (3, 'Meera', 20);
 
SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
JOIN departments AS d
    ON d.department_id = e.department_id
ORDER BY e.employee_id;
Output
employee_name | department_name --------------+---------------- Asha | Engineering Ravi | Engineering Meera | Finance

The department name is stored in departments and retrieved through a JOIN. The UNIQUE constraint is suitable here only if department names must be unique in this application's data model.

7. Full Normalization Example: From One Table to Three

Let's apply the ideas together.

Suppose an enrollment report stores these attributes in one table:

student_id, student_name, course_id, course_name, instructor_name, grade

Assume:
- student_id identifies a student and determines student_name.
- course_id identifies a course and determines course_name and instructor_name.
- (student_id, course_id) identifies one enrollment and determines grade.

The original table has several types of redundancy:
- Student details repeat for each course.
- Course details repeat for each student.
- Enrollment-specific grades belong to the student-course association.

A suitable decomposition is:

students(student_id, student_name)

courses(course_id, course_name, instructor_name)

enrollments(student_id, course_id, grade)

The students table stores student facts.
The courses table stores course facts.
The enrollments table stores facts about the relationship.

This decomposition is based on the stated assumptions. If instructors teach multiple courses or a course can have multiple instructors, instructor information may need its own table or another relationship. The correct design depends on the real business rules.

Normalization is not simply splitting a large table into many small tables. Each split should be justified by dependencies and constraints, and the design should preserve the intended information and relationships.

Normalized enrollment schema

Normalized enrollment schema
sql
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    instructor_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);
 
SELECT
    s.student_name,
    c.course_name,
    c.instructor_name,
    e.grade
FROM enrollments AS e
JOIN students AS s
    ON s.student_id = e.student_id
JOIN courses AS c
    ON c.course_id = e.course_id
ORDER BY s.student_id, c.course_id;
Output
The query returns one row per enrollment, combining student details, course details, and the grade stored for that enrollment. Exact rows depend on the data inserted.

The schema avoids repeating student and course descriptions in every enrollment. If instructor data has its own dependencies, normalize it further based on the domain.

8. Normalization Does Not Mean Eliminating All Repetition

Some repetition is necessary in relational designs.

For example, several employees may reference the same department_id. This is expected in a one-to-many relationship.

The department name is stored once in departments, while each employee stores the foreign key that identifies their department.

That foreign-key repetition is not the same as repeating the department's descriptive facts in every employee row.

Likewise, a junction table repeats student IDs and course IDs across different associations because those keys represent relationships.

The aim is to store each fact in a place consistent with its dependencies and use keys to connect related facts.

Over-normalizing is not the usual concern in formal relational design; the practical concern is creating a schema that is difficult to use or that splits facts without a clear dependency-based reason. Good designs balance integrity, clarity, and the application's access patterns.

9. Normalization vs. Denormalization

Normalization reduces redundant facts and makes updates easier to maintain consistently. However, retrieving a complete report may require joining multiple tables.

Denormalization deliberately duplicates or precomputes data to improve selected read workloads or simplify access patterns.

Example: an analytics dashboard may need total sales per day. Calculating totals from millions of order-line rows for every dashboard request may be expensive. A summary table or materialized view can store precomputed totals.

Benefits of denormalization:
- May reduce repeated aggregation work.
- Can improve performance for specific read-heavy workloads.
- May simplify some reporting queries.

Costs and risks:
- Duplicated values can become inconsistent.
- Additional write logic may be needed.
- Cached or summarized values can become stale.
- The design requires monitoring and reconciliation.

Use evidence from realistic queries and data volumes before denormalizing. Measure the bottleneck first, and decide how derived data will be updated and corrected.

Denormalization is not permission to duplicate data arbitrarily. It is a deliberate trade-off with explicit consistency rules.

A simple reporting summary table

A simple reporting summary table
sql
CREATE TABLE daily_sales_summary (
    sales_date DATE PRIMARY KEY,
    total_orders INTEGER NOT NULL,
    total_amount NUMERIC(14, 2) NOT NULL
);
 
-- Example summary row maintained by a reporting process.
INSERT INTO daily_sales_summary
    (sales_date, total_orders, total_amount)
VALUES
    ('2026-10-20', 125, 42500.00);
 
SELECT sales_date, total_orders, total_amount
FROM daily_sales_summary
ORDER BY sales_date;
Output
sales_date | total_orders | total_amount -----------+--------------+------------- 2026-10-20 | 125 | 42500.00

The row is illustrative, not a calculated result. A real summary table needs a reliable process to refresh or maintain the totals and reconcile them against source transactions.

10. Practical Rules for Good Normalized Design

Use these checks when designing tables:

1. Identify the entities.
Examples include students, courses, employees, departments, customers, and orders.

2. Define the candidate keys.
Decide which attributes uniquely identify each type of record.

3. Identify functional dependencies.
Determine which attributes depend on each key.

4. Represent relationships explicitly.
Use foreign keys and junction tables where appropriate.

5. Avoid repeating groups.
Store individual associations as rows rather than comma-separated collections.

6. Check partial dependencies.
For tables with composite candidate keys, make sure non-prime attributes depend on the whole key.

7. Check transitive dependencies.
Make sure descriptive facts are stored with the entities they describe.

8. Add constraints.
Use PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK where they express genuine business rules.

9. Consider actual queries.
Normalization provides a foundation for integrity, but indexes and query plans still matter.

10. Document exceptions.
If a summary, cache, or duplicated field is intentionally maintained for performance, document its source of truth and update strategy.

A schema is not normalized merely because it contains several tables. Correctness depends on the keys, dependencies, constraints, and business rules.

11. Common Mistakes

Mistake 1: Assuming 1NF, 2NF, and 3NF all mean the same thing.
Fix: Understand the separate requirements of each normal form.

Mistake 2: Thinking every repeated value is bad.
Fix: Distinguish necessary foreign-key repetition from redundant descriptive facts.

Mistake 3: Assuming a primary key automatically guarantees 3NF.
Fix: Examine the functional dependencies of all candidate keys.

Mistake 4: Splitting tables without preserving relationships.
Fix: Keep the necessary keys and constraints, and verify that joins reconstruct the intended information.

Mistake 5: Denormalizing before measuring performance.
Fix: Identify and measure a real query bottleneck before adding redundant or precomputed data.

Mistake 6: Assuming a normalized schema is automatically fast.
Fix: Evaluate indexes, query plans, data volume, and workload.

Mistake 7: Storing several independent values in one column.
Fix: Represent repeating collections with related rows and appropriate keys.

12. Practice Exercises

Exercise 1:
A table stores student_id, student_name, and a comma-separated list of course names. Explain the 1NF problem and propose a relational design.

Exercise 2:
A table has the composite key (student_id, course_id), along with student_name and grade. Assume student_id determines student_name, while the key determines grade. Explain why student_name is a partial dependency.

Exercise 3:
An employee table stores employee_id, department_id, and department_name. Assume department_id determines department_name. Explain the redundancy and propose a 3NF-style design.

Exercise 4:
Explain one insertion anomaly, one update anomaly, and one deletion anomaly.

Exercise 5:
Why does a normalized enrollment schema still contain repeated student_id and course_id values?

Exercise 6:
Give an example of an analytical workload where a summary table or materialized view might be useful. Describe how you would keep it consistent with the source data.

Exercise 7:
A table has a single-column primary key. Does that automatically mean the table is in 3NF? Explain.

Exercise 8:
Explain why normalizing a table requires understanding its functional dependencies and candidate keys.

Practice answer: separate department facts

Practice answer: separate department facts
sql
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id)
);
Output
The design stores each department name once and references its department from employee rows. The nullable foreign key allows an employee to have no assigned department.

This is one suitable decomposition under the stated dependency. Add NOT NULL or additional constraints only if the business rules require them.

13. Knowledge Check

Choose the best answer for each question. Focus on the formal purpose of normalization and the assumptions behind the examples.

14. Interview Questions

Q1. What is database normalization?
Answer: It is the process of organizing relational tables around keys and dependencies to reduce unnecessary redundancy and modification anomalies.

Q2. What are insertion, update, and deletion anomalies?
Answer: They are design problems in which inserting a fact is difficult, updating a duplicated fact creates inconsistent values, or deleting one record unintentionally removes another important fact.

Q3. What is 1NF?
Answer: In introductory relational design, 1NF requires each attribute to contain an atomic value and avoids repeating groups. Atomicity depends on the intended data model.

Q4. What is 2NF?
Answer: A table is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. It rules out partial dependencies of non-prime attributes.

Q5. What is 3NF?
Answer: Formally, for every non-trivial functional dependency X -> A, X must be a superkey or A must be a prime attribute. Introductory examples often explain this through eliminating transitive dependencies of non-key attributes.

Q6. What is a functional dependency?
Answer: X -> Y means that the value of X determines the value of Y under the database's intended rules.

Q7. What is a partial dependency?
Answer: It occurs when an attribute depends on only a proper subset of a composite candidate key.

Q8. What is a transitive dependency?
Answer: In a common example, a key determines one attribute, which in turn determines another non-key attribute. Such dependencies can create redundancy.

Q9. Does a primary key alone guarantee 3NF?
Answer: No. The table's other candidate keys and functional dependencies must also satisfy the normal-form requirements.

Q10. What is denormalization?
Answer: It is a deliberate design choice to duplicate or precompute data for a workload-specific benefit, with additional responsibility for keeping the data consistent.

Q11. Is a normalized database always faster?
Answer: No. Normalization helps integrity and maintenance, but performance depends on queries, indexes, data volume, and workload.

Q12. Why do normalized tables still use JOINs?
Answer: JOINs reconstruct related information from tables that store different facts separately, using their keys and relationships.

15. Lesson Summary

Database normalization organizes data so facts are stored according to their keys and dependencies.

Remember:

- Normalization reduces unnecessary duplication and insertion, update, and deletion anomalies.
- 1NF addresses repeating groups and requires values to be atomic within the intended data model.
- 2NF requires 1NF and removes partial dependencies of non-prime attributes on candidate keys.
- 3NF uses functional dependencies to prevent certain forms of redundancy, including the transitive-dependency patterns in common examples.
- Primary keys, candidate keys, foreign keys, and constraints support a reliable design.
- Foreign-key values may repeat when they represent legitimate relationships.
- Denormalization may improve specific analytical workloads, but it introduces maintenance and consistency responsibilities.
- A good schema follows the real business rules and is evaluated against actual queries.

Before finalizing a schema, identify its candidate keys, determine its functional dependencies, choose suitable constraints, and verify that the design preserves the information the application needs.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming normalization means every value must be unique

Why it happens: The goal of reducing redundancy is confused with a rule that forbids repeated values.

Incorrect Approach
sql
sql
-- Repeated department_id values are not inherently wrong.
-- Many employees may belong to the same department.
Correct Solution
sql
sql
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(department_id)
);

Key takeaway: Repeated foreign-key values are expected in a one-to-many relationship. Normalization targets unnecessary duplication of facts, not valid references.

#2 Believing a primary key automatically makes a table 3NF

Why it happens: Uniqueness is mistaken for complete normalization.

Incorrect Approach
sql
sql
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER NOT NULL,
    department_name VARCHAR(100) NOT NULL
);
Correct Solution
sql
sql
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
 
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER NOT NULL REFERENCES departments(department_id)
);

Key takeaway: The primary key uniquely identifies an employee, but department_name may still depend on department_id and be unnecessarily repeated. Normalization requires examining functional dependencies, not only the primary key.

#3 Treating 2NF as a rule that only applies to composite primary keys without considering candidate keys

Why it happens: Introductory examples focus on composite primary keys, which can hide the formal role of all candidate keys.

Incorrect Approach
sql
sql
-- Assuming a single-column primary key alone
-- proves that every normal form is satisfied.
Correct Solution
sql
sql
-- Identify all candidate keys and functional dependencies.
-- Check 1NF first, then evaluate 2NF and 3NF
-- using the complete set of relevant dependencies.

Key takeaway: A single-column candidate key cannot have a partial dependency on a proper subset of that key, but the table can still violate higher normal forms.

#4 Denormalizing without a plan to maintain derived data

Why it happens: A summary table seems faster to query, but the process that keeps it current is not designed.

Incorrect Approach
sql
sql
-- Store a copied total without defining
-- when or how it is recalculated.
Correct Solution
sql
sql
-- Define a refresh or maintenance process.
-- Reconcile derived totals against source records.
-- Monitor freshness and correction failures.

Key takeaway: Denormalization can improve selected workloads, but it must include a deliberate consistency and recovery strategy.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Database Normalization Knowledge Check

Test your understanding with immediate feedback

Question 1 of 6

What is the main goal of normalization?

Question 2 of 6

Which issue does 1NF primarily address in beginner examples?

Question 3 of 6

A table has key (student_id, course_id), and student_id determines student_name. What kind of dependency is this?

Question 4 of 6

In a common 3NF example, department_id determines department_name, while employee_id determines department_id. What is the issue if both department attributes are stored in employees?

Question 5 of 6

Does normalization remove all repeated values?

Question 6 of 6

When might denormalization be appropriate?

Key takeaways

  • Normalization organizes data to reduce unnecessary redundancy and modification anomalies.
  • First Normal Form requires each column value to be atomic according to the chosen data model.
  • Second Normal Form requires 1NF and no partial dependency of a non-prime attribute on a candidate key.
  • Third Normal Form requires 2NF and eliminates transitive dependencies of non-key attributes on candidate keys, subject to the formal definition.
  • Primary keys, candidate keys, and functional dependencies help determine a correct table design.
  • Normalization does not eliminate every form of duplication or guarantee faster queries.
  • Denormalization can be appropriate when its performance benefits justify the additional consistency and maintenance responsibilities.
Was this lesson helpful?
Found an error? Report an issue