Synetic College Logo
Unit 4 of 4 · BCA Sem 3

Unit 4: Normalization

Database Management Systems-I notes · PTU syllabus (UGCC2512)

3 min read5 topics10 exam questions
On this page
  1. Unit summary
  2. Functional dependencies
  3. Armstrong's axioms and closure
  4. Anomalies and the need for normalisation
  5. Normal forms
  6. Denormalisation
  7. Key terms
  8. Quick revision
  9. Important questions

Unit summary

Poorly designed tables store the same data repeatedly and cause errors when data is inserted, updated or deleted. Normalisation is the process of organising tables to remove this redundancy. This unit covers functional dependencies, Armstrong's axioms, closure, and the normal forms from 1NF to 5NF, plus denormalisation.

After this unit you can

  • Define functional dependency and apply Armstrong's axioms
  • Compute the closure of a set of attributes
  • Identify insertion, update and deletion anomalies
  • Normalise a table step by step to 1NF, 2NF, 3NF and BCNF, and explain 4NF, 5NF and denormalisation

PTU syllabus topics

  • Functional dependencies and Armstrong's axioms
  • properties (reflexivity, augmentation, transitivity)
  • types of dependencies
  • closure of functional dependencies
  • normal forms (1NF through BCNF, 4NF, 5NF)
  • denormalization
HierarchyNormal forms (each builds on the one below)
  1. BCNF

    Every determinant is a candidate key

  2. 3NF

    No transitive dependency on the key

  3. 2NF

    No partial dependency on part of a key

  4. 1NF

    Atomic values; no repeating groups

1

Topic 1

Functional dependencies

A functional dependency (FD) X → Y means the value of X uniquely determines the value of Y. Example: RollNo → Name.

  • Trivial FD: Y is a subset of X (e.g. {RollNo, Name} → Name).
  • Full FD: Y depends on the whole of X, not on any part of it.
  • Partial FD: Y depends on part of a composite key.
  • Transitive FD: X → Y and Y → Z give X → Z, where Y is not a key.
  • Multivalued dependency (MVD): X →→ Y, one X value has a set of independent Y values.
2

Topic 2

Armstrong's axioms and closure

Key termsArmstrong's axioms
Reflexivity
If Y ⊆ X, then X → Y
Augmentation
If X → Y, then XZ → YZ
Transitivity
If X → Y and Y → Z, then X → Z
Union (derived)
If X → Y and X → Z, then X → YZ
Decomposition (derived)
If X → YZ, then X → Y and X → Z

The closure of an attribute set X⁺ is the set of all attributes functionally determined by X. If X⁺ contains every attribute, X is a super key.

Example

R(A, B, C, D) with A → B, B → C. A⁺ = {A, B, C}. Since D is missing, A is not a key; (AD)⁺ = {A, B, C, D}, so AD is a key.

3

Topic 3

Anomalies and the need for normalisation

In an unnormalised table STUDENT_COURSE(Roll, Name, Course, Faculty):

  • Insertion anomaly: a new course cannot be added until a student enrols.
  • Update anomaly: changing a faculty name requires updating many rows.
  • Deletion anomaly: deleting the last student of a course loses the course's information.
4

Topic 4

Normal forms

ProcessSteps of normalisation
  1. 1

    1NF

    Atomic values; no repeating groups

  2. 2

    2NF

    1NF + no partial dependency on part of a composite key

  3. 3

    3NF

    2NF + no transitive dependency on a non-key attribute

  4. 4

    BCNF

    For every FD X → Y, X is a super key

  5. 5

    4NF

    BCNF + no non-trivial multivalued dependency

  6. 6

    5NF

    4NF + no join dependency (lossless decomposition)

Example

ORDER(OrderID, ProductID, ProductName, Qty) with key (OrderID, ProductID): ProductName depends only on ProductID — a partial dependency. Split into ORDER_ITEM(OrderID, ProductID, Qty) and PRODUCT(ProductID, ProductName) to reach 2NF.

Example

EMP(EmpID, DeptID, DeptName): EmpID → DeptID → DeptName is transitive. Split into EMP(EmpID, DeptID) and DEPT(DeptID, DeptName) for 3NF.

5

Topic 5

Denormalisation

Denormalisation deliberately adds some redundancy back (by combining tables or storing derived values) to speed up frequent read-heavy queries, accepting extra storage and update effort. It is common in reporting systems and data warehouses.

Exam tip

Every decomposition should be lossless (the original table can be rebuilt by joining) and ideally dependency-preserving.

Key terms

Functional dependency
X determines Y uniquely
Closure
All attributes determined by a set of attributes
Anomaly
A problem in insertion, update or deletion caused by redundancy
Normalisation
Organising tables to remove redundancy and anomalies
Denormalisation
Adding controlled redundancy to improve read performance

Quick revision

  • Armstrong: reflexivity, augmentation, transitivity.
  • 1NF atomic; 2NF no partial; 3NF no transitive; BCNF every determinant is a super key.
  • 4NF removes multivalued dependencies; 5NF removes join dependencies.
  • Decompositions must be lossless.

Important exam questions

Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).

Short-answer questions

  1. Q1.Define a functional dependency.
  2. Q2.State Armstrong's axioms.
  3. Q3.What is a partial dependency?
  4. Q4.Differentiate between 3NF and BCNF.
  5. Q5.What are update anomalies?
  6. Q6.What is denormalisation?

Long-answer questions

  1. Q1.Explain insertion, update and deletion anomalies with an example and the need for normalisation.
  2. Q2.Explain 1NF, 2NF and 3NF with a step-by-step example.
  3. Q3.Explain BCNF, 4NF and 5NF with examples.
  4. Q4.Find the closure of attribute sets for a relation with given FDs and determine its candidate keys.

Stuck on this unit?

Message SBS on WhatsApp for help with Database Management Systems-I, or to ask about studying BCA at Synetic.

WhatsApp us