Gyanrush
Start freeOpen Gyanrush
Start freeOpen Gyanrush
  1. LPU MCA Semester 1
  2. CAP570
  3. Unit 3

CAP570 · Unit 3: Database normalisation

Normal forms 1NF, 2NF and 3NF

10 practice questions · in the MTE and ETE syllabus

Practise this topicPractise this topic

Key points

  • 1NF means atomic values
  • 2NF removes partial dependencies
  • 3NF removes transitive dependencies
  • 3NF allows a prime attribute on the right side

Normalisation removes redundancy through successive normal forms.

  • 1NF: every attribute holds an atomic value and there are no repeating groups.
  • 2NF: in 1NF and no non-prime attribute depends on only part of a candidate key (no partial dependency).
  • 3NF: in 2NF and no non-prime attribute depends on the key through another non-prime attribute (no transitive dependency). Formally, for every non-trivial FD X → A, X is a superkey or A is a prime attribute.

A prime attribute belongs to some candidate key.

Example: R(StudentID, CourseID, StudentName, Grade) with StudentID → StudentName is only in 1NF because StudentName depends on part of the key. R(EmpID, DeptID, DeptName) with EmpID → DeptID and DeptID → DeptName is in 2NF but not 3NF. Splitting into (EmpID, DeptID) and (DeptID, DeptName) fixes it without losing information or dependencies.

Exam trap: partial dependencies need a composite key, so a single-attribute key is always in 2NF.

Try two questions

Question 1 · Tier 3, Apply

R(EmpID, DeptID, DeptName) has FDs EmpID → DeptID and DeptID → DeptName. What is its highest normal form?

  1. 1NF but not 2NF
  2. 3NF but not BCNF
  3. BCNF
  4. 2NF but not 3NF
Show the answer

D. EmpID is the key, and DeptName depends on it through DeptID, which is a transitive dependency.

Why not A: The key EmpID is a single attribute, so no partial dependency exists.

Why not B: The transitive dependency breaks 3NF.

Why not C: The transitive dependency means it is not even in 3NF.

Question 2 · Tier 2, Understand

Which dependency does 3NF remove that 2NF still allows?

  1. Partial dependency of a non-prime attribute on part of a key
  2. Transitive dependency of a non-prime attribute on the key
  3. Multivalued attributes stored in a single column
  4. Repeating groups of columns in each row
Show the answer

B. 3NF removes dependencies of non-prime attributes on the key through other non-prime attributes.

Why not A: That is already removed by 2NF.

Why not C: That is removed by 1NF.

Why not D: That is removed by 1NF.