Normalization in Databases: Types and Practical Illustrations

UPI Study › Courses › System Analysis and Design › Free lessons › Lesson 6.5 Free with a free account

Normalization in Databases: Types and Practical Illustrations

System Analysis and Design · Chapter 6: Database Design, Normalization, and Business IntelligenceSection 6.5 · lesson 43 of 6913 min · free with a free account

← The Entity-Relationship Model: Diagrams Made SimpleNext lesson: Turning Data into Decisions: Warehousing and Mining for Business Intelligence →

Earn 3 college credits for System Analysis and DesignRecommended for credit by NCCRS · Computer Science 390 · self-paced · one payment, lifetime access
Enroll in System Analysis and Design · $250 →Download the syllabus (PDF)

In this lesson

Update anomalies, functional dependencies, 1NF to 5NF, and denormalization.

  • Explain the 3 update anomalies normalization eliminates
  • Use functional dependencies to reason about a table's design
  • Apply 1NF, 2NF, 3NF, and BCNF with worked examples
  • Describe 4NF and 5NF, and weigh denormalization's trade-offs

Transcript

Everything said in the video, section by section (1,821 words). Read along, or search it.

Database Design, Normalization, and Business Intelligence

Normalization in Databases

A database can have all the right data, and still be badly designed. This section is about normalization, the set of rules for organizing tables well. We start with the problems it solves, and the tool used to reason about them, functional dependencies. Then we climb the normal forms, one at a time, with a worked example. And finally, why designers sometimes undo normalization, on purpose.

The Problem

3 Update Anomalies

Normalization organizes a database's tables so that each fact is stored in exactly one place. When a table mixes facts that do not belong together, three kinds of update anomaly become possible. Picture one flat table recording student enrollments, where course details are stored only alongside each enrollment. An insertion anomaly means a new fact cannot be added without an unrelated fact it is bundled with. Here, a new course cannot be entered until some student enrolls in it. An update anomaly means a fact stored in several rows must be changed in every one of them. Change an instructor's name, and every row for that course needs updating. Miss one, and the data is inconsistent. A deletion anomaly means deleting one fact erases an unrelated fact stored alongside it. If the only student in a course drops it, the course's own information disappears too.

The Reasoning Tool

Functional Dependencies

To fix these problems, we need a precise way to describe how attributes relate. That is a functional dependency. A functional dependency exists when each value of one attribute is associated with exactly one value of another. It is written with an arrow, and read as, A determines B. Student I D determines student name. Know the I D, and the name is fixed. Course I D determines both the course name and the instructor. And the pair, student I D and course I D together, determines the grade. A grade only makes sense for a specific student, in a specific course.

2 Troublesome Patterns

Partial and Transitive Dependencies

Two patterns of functional dependency drive the normalization rules. A partial dependency exists when a non-key attribute depends on only part of a composite key. If the key is student I D plus course I D, the course name depends on the course I D alone. It does not need the student. A transitive dependency exists when a non-key attribute depends on another non-key attribute, instead of on the key. Course I D determines the instructor. And the instructor determines the instructor's office. So the office depends on the key only indirectly, through the instructor. Second normal form removes partial dependencies. Third normal form removes transitive ones. Spotting these two patterns is most of the work of normalizing.

Figure 6.5

The Normalization Ladder

The normal forms form a ladder. Each one builds on the one before it, adding one more rule that a table must satisfy. First normal form requires atomic values, with no repeating groups. Second normal form adds, no partial dependencies. Third normal form adds, no transitive dependencies. Boyce-Codd normal form, or B C N F, requires every determinant to be a candidate key. Fourth and fifth normal forms handle rarer kinds of redundancy. Climbing is cumulative. A table in third normal form is automatically in second and first normal form as well. And most practical designs stop at the third rung.

First Normal Form (1NF)

One Value Per Cell

Let's normalize a real table, starting with first normal form, or one N F. A table is in one N F when every attribute holds a single, atomic value, and there are no repeating groups. The top table violates it. Ravi Shah's row packs two courses into a single cell. The fix is one row per student-course pair, with every value atomic, shown in the bottom table. This table is now in one N F. But it is still one flat table. Look at the values in bold. Ravi Shah's name appears twice. And the details for course C one oh one, its name and instructor, appear twice as well. That repetition is exactly the redundancy that causes update anomalies. Notice the primary key, too. It is the composite of student I D and course I D. That sets up the next step.

Second Normal Form (2NF)

Depend on the Whole Key

A table is in second normal form, or two N F, when it is in one N F, and every non-key attribute depends on the whole primary key. Check each attribute in the one N F table against its composite key. Student name depends only on student I D. That is a partial dependency. Course name and instructor depend only on course I D. Also partial. Only the grade depends on the full key, student and course together. Neither partial attribute needs the full key to be determined. So reaching two N F means splitting the table, so that each partial dependency becomes a full dependency, in a table of its own.

2NF Result

3 Tables, Each Fact Once

Here is the result. Three tables. Student holds each student's name, keyed on student I D. Course holds each course's name and instructor, keyed on course I D. And enrollment holds the grade, keyed on the pair of student I D and course I D. Every non-key attribute now depends on the whole key of its own table. Ravi Shah's name is stored exactly once, no matter how many courses Ravi takes. And a new course can be added to the course table, before anyone enrolls in it. The insertion anomaly is gone.

Third Normal Form (3NF)

No Non-Key Middlemen

A table is in third normal form, or three N F, when it is in two N F, and has no transitive dependencies. No non-key attribute may depend on another non-key attribute, instead of directly on the key. Suppose the course table is extended with each instructor's office number. The office does not really depend on the course. It depends on the instructor. Course I D determines the instructor, and the instructor determines the office. That is a transitive dependency. The cost shows up the moment something changes. If A. Mehta moves offices, every course A. Mehta teaches needs the same update, repeated.

3NF Result

The Office Lives in One Place

Reaching three N F means moving the transitively dependent attributes into their own table. Course now stores only an instructor I D, pointing to the instructor. And a new instructor table holds each instructor's name and office. An instructor's office now lives in exactly one place. It is updated once, no matter how many courses that instructor teaches. Most practical database designs stop here, at third normal form. The forms that follow handle edge cases that three N F does not fully cover.

Boyce-Codd Normal Form (BCNF)

Every Determinant Must Be a Key

Boyce-Codd normal form is a stricter version of third normal form. Every determinant in the table, meaning every attribute or combination that determines another attribute, must be a candidate key. Consider a table recording which advisor a student has for each major. A student may have more than one major, and each advisor advises only one major. The student and major together determine the advisor. But separately, the advisor determines the major, since each advisor advises just one. This table is already in three N F, because major is part of a candidate key. Three N F's rule against transitive dependencies only applies to non-key attributes. But advisor is a determinant that is not itself a candidate key. And that violates B C N F. Look at the bold cells. The fact that Doctor Kapoor advises computer science is stored twice.

BCNF Result

Splitting the Advisor Table

Splitting the table resolves it. Student advisor records which advisors each student has. And advisor major records the single major each advisor covers. Now the fact that Doctor Kapoor advises computer science is stored exactly once. Every determinant in each table is now a candidate key. This gap between three N F and B C N F is narrow. It only shows up when a table has more than one overlapping candidate key.

Fourth Normal Form (4NF)

Keep Unrelated Facts Apart

Fourth normal form, or four N F, eliminates independent multi-valued dependencies. Two unrelated multi-valued facts about the same entity should never be forced into one table together. Take an employee who has several skills, and speaks several languages. Skills and languages have nothing to do with each other. But in one table, every skill has to be paired with every language. Two skills and two languages already need four rows, a redundant cross-product. Add a third language, and two more rows appear. The fix is to split the table, into one for employee skills and another for employee languages. Two rows each, and no cross-product.

Fifth Normal Form (5NF)

Complex Join Dependencies

Fifth normal form, also called project-join normal form, eliminates redundancy from more complex join dependencies. These arise when a table can only be rebuilt without loss, by joining three or more smaller tables together. Not two. Three or more. It typically matters only for intricate three-way relationships. For example, suppliers, parts, and projects. A supplier might ship a specific part to a specific project, only under a particular combination of conditions. Five N F is rarely something an analyst designs for directly, outside of specialized cases.

Denormalization

Adding Redundancy on Purpose

Denormalization is the deliberate choice to reintroduce some redundancy into an already normalized design. The goal is usually faster reads, at the cost of some of normalization's guarantees. A normalized design stores each fact once. A denormalized one reintroduces copies, in a controlled way. Normalized updates are simple, one place each. Denormalized updates may need to touch several copies. But a normalized design can need many joins to assemble a full picture. Denormalized data is pre-combined, so reads need fewer joins. The integrity risk rises, because redundant copies can drift out of sync if they are not carefully managed. That is why normalized designs suit transactional systems, O L T P, with frequent updates. Denormalized designs suit reporting and analytical systems, O L A P, which are read far more often than updated. Imagine a reporting system that pre-combines customer and order data into one wide table, so dashboards load faster. The customer's name is repeated on every order row. That is not a mistake. Applied deliberately, and documented, it is a trade-off made where it makes sense.

Key Terms

The Vocabulary of This Section

Let's lock in the key terms. An update anomaly is an insertion, update, or deletion problem caused by poor table structure. A functional dependency, A determines B, means each value of A fixes exactly one value of B. A partial dependency depends on only part of a composite key. Second normal form removes it. A transitive dependency depends on another non-key attribute. Third normal form removes it. A determinant is any attribute, or combination of attributes, that determines another. And denormalization reintroduces redundancy on purpose, for faster reads.

Key Takeaways

What to Carry Forward

To pull this together. Poorly structured tables allow three anomalies, insertion, update, and deletion, which normalization is designed to eliminate. First normal form removes repeating groups. Second removes partial dependencies. Third removes transitive dependencies. Boyce-Codd normal form closes a gap three N F leaves open, requiring every determinant to be a candidate key. Fourth normal form separates independent multi-valued facts. Fifth handles complex, multi-way join dependencies. And denormalization deliberately trades some integrity guarantees for faster reads, and is common in reporting and analytical systems.

About System Analysis and Design at UPI Study

System Analysis and Design (Computer Science 390) is a self-paced online course of 69 video lessons in 9 chapters, from the analyst's role and requirements to UML modeling, interface and database design, testing, maintenance and project management. It carries a NCCRS credit recommendation for 3 semester credits.

Every lesson transcript is free on pages like this one, and the videos are free with a free UPI Study account. Enrolling adds a practice quiz after each lesson, the graded work and final exam that earn the credit, lifetime access, and an official transcript on completion. UPI Study has helped over 130,000 students with college credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Enroll now → Course details Download the syllabus All Computer Science courses Every course with PRO →

Compare: Software Engineering vs Systems Analysis

Why take it here

Why students take System Analysis and Design with UPI Study

From business problem to working systemSystem Analysis and Design teaches how analysts study a business need and turn it into a system design: requirements, UML and data-flow models, interface and database design, implementation and project management. It is a core course in information systems and IT degrees.
Credit at a fraction of the priceThe same 3 credits can cost $750 or more at a US college. Here it is one payment, self-paced, with lifetime access.
Finish your degree soonerTransfer credits reduce the courses you still owe, so you can graduate earlier or lighten a heavy semester.
Strengthen an applicationA completed college-level course on an official transcript shows admissions and scholarship committees you can do the work.
Study on your own scheduleNo start dates, no deadlines. Watch the lessons, practice, and take the final when you are ready.
130,000+students helped with college creditsACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Where the credit goes

Universities that work with NCCRS

System Analysis and Design carries a NCCRS credit recommendation. These universities work with NCCRS; some are UPI Study's signed partners. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.

Thomas Edison State UniversityThomas Edison State UniversitySigned partnerUniversity of the PeopleUniversity of the PeopleSigned partnerCharter Oak State CollegeCharter Oak State CollegeSigned partnerStrayer UniversityStrayer UniversitySigned partnerColumbia Southern UniversityColumbia Southern UniversitySigned partnerIndiana TechIndiana TechSigned partnerPenn StatePenn StateWorks with NCCRSSouthern New Hampshire UniversitySouthern New Hampshire UniversityWorks with NCCRSSUNYSUNYWorks with NCCRS +many more cooperating universitiesFind my college →

Find my college → Not listed? Ask Athena in the corner of this page, or book a 15-minute call.

FAQ

Questions students ask us

About Normalization in Databases: Types and Practical Illustrations

What are insertion, update, and deletion anomalies in a database?
Insertion, update, and deletion anomalies are problems caused when one table mixes facts that do not belong together. An insertion anomaly means a new fact cannot be added without an unrelated one, like a course that cannot be entered until a student enrolls. An update anomaly means one fact must be changed in many rows. A deletion anomaly means deleting one fact erases another, such as losing a course when its only student drops.
What is the difference between a partial dependency and a transitive dependency?
A partial dependency exists when a non-key attribute depends on only part of a composite key, while a transitive dependency exists when a non-key attribute depends on another non-key attribute instead of directly on the key. With a key of student ID plus course ID, course name depending on course ID alone is partial. An office number that depends on the course's teacher, who in turn depends on the course ID, is transitive.
What are first, second, and third normal form?
First normal form (1NF) requires every attribute to hold a single atomic value with no repeating groups. Second normal form (2NF) adds that every non-key attribute must depend on the whole primary key, removing partial dependencies. Third normal form (3NF) adds that no non-key attribute may depend on another non-key attribute, removing transitive dependencies. Each form builds on the previous one, and most practical designs stop at 3NF.
What is denormalization and when is it used?
Denormalization is the deliberate reintroduction of some redundancy into a normalized design, usually to make reads faster by needing fewer joins. The trade-off is that updates may need to touch several copies, and redundant copies can drift out of sync if not carefully managed. It is common in reporting and analytical (OLAP) systems that are read far more often than updated, while transactional (OLTP) systems usually stay normalized.

About the System Analysis and Design course

How many college credits is System Analysis and Design?
System Analysis and Design (Computer Science 390) carries a NCCRS credit recommendation for 3 semester credits. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities.
How much does System Analysis and Design cost and what is included?
$250, paid once, with lifetime access: all 69 video lessons, a practice quiz after each, the graded work and final exam, and an official transcript when you finish. Students who want several courses choose UPI Study PRO instead.
Which universities accept UPI Study credits?
UPI Study has signed partner agreements with universities such as Thomas Edison State University, University of the People and Charter Oak State College, and works with universities such as Penn State, SNHU and SUNY. ACE and NCCRS have over 2,100 cooperating colleges that will consider accepting credits; the final decision is with the universities. Use "Find my college" on this page to check yours.
How long does System Analysis and Design take?
It is self-paced with no deadlines. The 69 lessons total about 9 hours of video; most students add reading and practice time and finish in a few weeks to a few months, on their own schedule.
How are the exams done?
Quizzes and assignments are online in the course. The final exam is taken online with proctoring; a person on the UPI Study team checks assignments, usually within two days.
How do my credits reach my college?
When the course shows 100% complete, request the official transcript from your dashboard. It is sent to the college you name, and you can send it to more than one. Check your college first with "Find my college" on this page, or ask Athena.

Ready for the full course? Earn 3 credits in System Analysis and Design.

69 video lessons, a quiz after each one, and the graded work that earns the credit. Recommended for credit by NCCRS.

Enroll in System Analysis and Design →Download the syllabusEvery course with PRO →
System Analysis and Design
3 credits · NCCRSEnroll →