Skip to main content

Chapter 4 · Watch, then practise

Database Constraints & Normalization

Use constraints to reject invalid data and functional dependencies to remove avoidable redundancy without losing relationships.

3 questions · 3 with related videos. Matches are based on playlist titles; broader background matches are labeled.

What to study

  • Primary and foreign keys
  • Functional dependencies
  • Update, insertion and deletion anomalies
  • 2NF, 3NF and BCNF

Chapter playlists

Choose a playlist

Notes

Lec-66: Constraints in SQL in Hindi | DBMS

Gate Smashers · 12:01

The SQL constraints lecture from the relational-languages playlist supports choosing key, uniqueness and check constraints.

1. Constraints that protect data

Which constraints suit Student(id, email, age)?

Use PRIMARY KEY for id, UNIQUE for email when duplicates are forbidden, and CHECK (age >= 0) for nonnegative ages. Add NOT NULL wherever missing values are forbidden: a CHECK alone need not reject NULL. Foreign keys validate references to rows in other tables.

Lec-24: Introduction to Normalization | Insertion, Deletion & Updation Anomaly

Gate Smashers · 12:51

Choose a video · 2 lectures

Anomalies and partial dependencies explain why repeating a course title in enrollment rows is redundant.

2. Find an anomaly

Why is Enrollment(student_id, course_id, course_title) redundant?

If course_id determines course_title, the title repeats for each enrollment. A title change can leave inconsistent copies; deleting the final enrollment can lose the course information. Decompose into Course(course_id, course_title) and Enrollment(student_id, course_id), retaining the relationship through course_id.

Lec-33: All Normal Forms with Real life examples | 1NF 2NF 3NF BCNF 4NF 5NF | All in One

Gate Smashers · 11:15

The normal-forms comparison covers the 2NF, 3NF and BCNF concepts used in this answer.

3. Compare normal forms

How do 2NF, 3NF and BCNF differ?

2NF requires 1NF and no dependency of a nonprime attribute on only part of a candidate key. In 3NF, every nontrivial dependency X → A has X as a superkey or A as a prime attribute. BCNF requires X to be a superkey. Decomposition must also preserve information through a lossless join.

References