1. 1NF (First Normal Form)

Rule:

👉 A table is in 1NF if:

All values are atomic (single value per cell, no multiple values).
No repeating groups or arrays in a column.

Example (Not in 1NF ❌):

StudentID	Name	Subjects
1	John	Math, Science
2	Alice	Physics, Chem

Problem: Column Subjects has multiple values in one cell.

Fixed Table (In 1NF ✅):

StudentID	Name	Subject
1	John	Math
1	John	Science
2	Alice	Physics
2	Alice	Chem

Now → each cell has only one value.

✅ Super Simple Definition:

👉 1NF = No repeating groups + Atomic values (one value per cell).

2. 2NF (Second Normal Form)

Step 1: Recall 1NF (First Normal Form)

Table must have atomic values (no repeating groups, no multiple values in one cell).
✔️ Example (Good 1NF):

StudentID	Subject	Marks
1	Math	90
1	Science	85
2	Math	88

Step 2: What is 2NF?

A table is in 2NF if:

It is already in 1NF ✅
Every non-key column depends on the whole primary key, not just part of it.

👉 This problem occurs only when you have a composite key (primary key made of 2+ columns).

Step 3: Example (Not in 2NF)

StudentID	Subject	StudentName	Marks
1	Math	John	90
1	Science	John	85
2	Math	Alice	88

Here, Primary Key = (StudentID, Subject)
Problem ❌: StudentName depends only on StudentID, not on both (StudentID + Subject).
This is called partial dependency.

Step 4: Fix it → Convert to 2NF

👉 Split the table into two:

Table 1: Student

StudentID	StudentName
1	John
2	Alice

Table 2: Marks

StudentID	Subject	Marks
1	Math	90
1	Science	85
2	Math	88

Now ✅ every column depends on the whole primary key.

✅ Super Simple Definition:

👉 2NF = 1NF + No partial dependency
(Every non-key column must depend on the whole primary key, not just a part of it).

3. 3NF (Third Normal Form)

Step 1: Recall 2NF

Table is in 2NF if there are no partial dependencies (non-key columns depend on the whole primary key).

Step 2: What is 3NF?

A table is in 3NF if:

It is already in 2NF ✅
No transitive dependency exists.

👉 Transitive dependency means:
Non-key column depends on another non-key column (indirectly depending on the key).

Step 3: Example (Not in 3NF)

StudentID	StudentName	DepartmentID	DepartmentName
1	John	D1	Computer Sci
2	Alice	D2	Physics

Primary Key = StudentID
Problem ❌: DepartmentName depends on DepartmentID, which itself depends on StudentID.
So DepartmentName is indirectly dependent on StudentID.
This is a transitive dependency.

Step 4: Fix it → Convert to 3NF

👉 Break into two tables:

Table 1: Students

StudentID	StudentName	DepartmentID
1	John	D1
2	Alice	D2

Table 2: Departments

DepartmentID	DepartmentName
D1	Computer Sci
D2	Physics

Now ✅ all non-key columns depend directly on the primary key only.

✅ Super Simple Definition:

👉 3NF = 2NF + No transitive dependency
(Every non-key column must depend only on the primary key, not on other non-key columns).

4. BCNF (Boyce-Codd Normal Form)

Step 1: Recall 3NF

A table is in 3NF if it’s in 2NF and has no transitive dependency.
But sometimes 3NF still allows problems when there are multiple candidate keys.

Step 2: What is BCNF?

A table is in BCNF if:
👉 For every functional dependency (X → Y), X must be a super key.

In simple words:
✅ Every determinant (the thing on the left side) must be a key.

Step 3: Example (Not in BCNF)

StudentID	Course	Instructor
1	DBMS	Prof. A
2	Networks	Prof. B
3	DBMS	Prof. A

Candidate Keys: (StudentID, Course) and (Course, Instructor)
Functional Dependencies:
- StudentID + Course → Instructor ✅ (OK, left side is key)
- Course → Instructor ❌ (Here, Course is not a key, but it determines Instructor).

So table violates BCNF.

Step 4: Fix it → Convert to BCNF

Split into two tables:

Table 1: Course_Instructor

Course	Instructor
DBMS	Prof. A
Networks	Prof. B

Table 2: Student_Course

StudentID	Course
1	DBMS
2	Networks
3	DBMS

Now ✅ every dependency has a key on the left side.

✅ Super Simple Definition:

👉 BCNF = Stronger 3NF

3NF removes transitive dependency.
BCNF goes further: Every determinant must be a candidate key.

Key Differences:

Normal Form	Goal	Key Points
1NF	Eliminate duplicate data, single values per column	Unique rows, atomic values
2NF	Eliminate partial dependency	No partial dependency on primary key
3NF	Eliminate transitive dependency	No non-key attribute depends on another non-key attribute
BCNF	Eliminate all functional dependency issues	Left side of dependency must be a superkey

Quick Tips to Remember:

1NF: Atomic values (no lists).
2NF: No partial dependency (whole primary key).
3NF: No transitive dependency (non-key attributes don’t depend on each other).
BCNF: Every dependency must have a superkey on the left side.

MCQ

1. Which of the following is the primary goal of First Normal Form (1NF)?
A) Remove partial dependencies
B) Eliminate duplicate data and ensure atomic values
C) Remove transitive dependencies
D) Ensure all non-key attributes depend on the primary key

Answer: B) Eliminate duplicate data and ensure atomic values

2. In Second Normal Form (2NF), which of the following must be true?
A) The table must be in 1NF
B) There should be no partial dependency of attributes on the primary key
C) All non-key attributes must depend on the whole primary key
D) Both A and B

Answer: D) Both A and B

3. Which of the following is a characteristic of Third Normal Form (3NF)?
A) Eliminate transitive dependency
B) All non-key attributes should depend on the primary key only
C) The table must be in 2NF
D) All of the above

Answer: D) All of the above

4. What does BCNF (Boyce-Codd Normal Form) eliminate?
A) Partial dependency
B) Transitive dependency
C) All functional dependencies where the left side is not a superkey
D) Both A and B

Answer: C) All functional dependencies where the left side is not a superkey

5. Which of the following conditions must a table satisfy to be in 1NF?
A) No partial dependency
B) No transitive dependency
C) Each column must contain only atomic values
D) Each table must have a composite key

Answer: C) Each column must contain only atomic values

6. If a table has columns StudentID, CourseID, InstructorName, and InstructorPhone where StudentID and CourseID form the primary key, which form of normalization is violated if InstructorPhone depends only on InstructorName?
A) 1NF
B) 2NF
C) 3NF
D) BCNF

Answer: C) 3NF

7. In 2NF, if a table has a composite key, what must be ensured about the non-key attributes?
A) Non-key attributes must depend on only one part of the composite key
B) Non-key attributes must depend on the whole composite key
C) Non-key attributes can be completely independent of the key
D) There should be no non-key attributes in the table

Answer: B) Non-key attributes must depend on the whole composite key

8. A table is in 2NF but not in 3NF if:
A) It contains transitive dependencies
B) It contains partial dependencies
C) It has atomic values in all columns
D) It does not contain any duplicate rows

Answer: A) It contains transitive dependencies

9. Which of the following is true for a table in BCNF?
A) Every non-key attribute depends on the primary key
B) Every determinant is a superkey
C) It must be in 1NF, 2NF, and 3NF
D) Both B and C

Answer: D) Both B and C

10. Which of the following describes a transitive dependency in a database table?
A) A non-key attribute depends on the primary key
B) A non-key attribute depends on another non-key attribute
C) A non-key attribute depends on part of the primary key
D) None of the above

Answer: B) A non-key attribute depends on another non-key attribute

11. Which of the following is the correct order of normal forms?
A) 1NF → 3NF → 2NF → BCNF
B) 1NF → 2NF → 3NF → BCNF
C) BCNF → 2NF → 1NF → 3NF
D) 3NF → 1NF → 2NF → BCNF

Answer: B) 1NF → 2NF → 3NF → BCNF

12. A relation is in 1NF, 2NF, and 3NF but not in BCNF. What could be the possible reason?
A) A non-superkey determines a non-prime attribute
B) A non-prime attribute is dependent on another non-prime attribute
C) A primary key is split across multiple columns
D) All columns are atomic

Answer: A) A non-superkey determines a non-prime attribute

13. What is the key problem with a table that violates 2NF?
A) There are partial dependencies where non-key attributes depend on part of the primary key
B) There are transitive dependencies where non-key attributes depend on other non-key attributes
C) The primary key is too large
D) The table has duplicate rows

Answer: A) There are partial dependencies where non-key attributes depend on part of the primary key

14. Which of the following is a requirement for a table to be in 2NF?
A) The table must be in 1NF
B) All non-key attributes must depend on the whole primary key
C) There must be no transitive dependencies
D) Both A and B

Answer: D) Both A and B

15. Which statement about BCNF is true?
A) BCNF is a stricter version of 3NF
B) BCNF allows transitive dependencies
C) A table in BCNF will always be in 3NF, but not always in 2NF
D) BCNF allows partial dependency

Answer: A) BCNF is a stricter version of 3NF

Normal Forms

1. 1NF (First Normal Form)

Rule:

Example (Not in 1NF ❌):

Fixed Table (In 1NF ✅):

✅ Super Simple Definition:

2. 2NF (Second Normal Form)

Step 1: Recall 1NF (First Normal Form)

Step 2: What is 2NF?

Step 3: Example (Not in 2NF)

Step 4: Fix it → Convert to 2NF

✅ Super Simple Definition:

3. 3NF (Third Normal Form)

Step 1: Recall 2NF

Step 2: What is 3NF?

Step 3: Example (Not in 3NF)

Step 4: Fix it → Convert to 3NF

✅ Super Simple Definition:

4. BCNF (Boyce-Codd Normal Form)

Step 1: Recall 3NF

Step 2: What is BCNF?

Step 3: Example (Not in BCNF)

Step 4: Fix it → Convert to BCNF

✅ Super Simple Definition:

Key Differences:

Quick Tips to Remember:

MCQ