Synetic College Logo
Unit 3 of 4 · M.Sc IT Sem 1

Unit 3: Database design and normalization

Relational Database Management System notes · PTU syllabus (PGCA1904)

4 min read12 topics10 exam questions
On this page
  1. Unit summary
  2. The entity–relationship model and ER diagrams
  3. Weak entity sets and extended ER features
  4. Features of good relational design
  5. Anomalies of a bad design
  6. Atomic domains and first normal form
  7. Functional dependency and second normal form
  8. Transitive dependency and third normal form
  9. Boyce–Codd normal form
  10. Armstrong's axioms and attribute closure
  11. Multivalued dependency and fourth normal form
  12. Join dependency and fifth normal form
  13. Domain–key normal form
  14. Key terms
  15. Quick revision
  16. Important questions

Unit summary

Good design avoids redundancy and anomalies. This unit covers the entity–relationship model and ER diagrams, features of good relational design, atomic domains and 1NF, functional dependency and 2NF, transitive dependency and 3NF, BCNF, multivalued dependency and 4NF, join dependency and 5NF, and domain–key normal form.

After this unit you can

  • Draw ER diagrams and reduce them to relations
  • Identify features of good relational design
  • Normalise relations from 1NF to 5NF
  • Explain domain–key normal form

PTU syllabus topics

  • The Entity-Relationship model and ER diagrams
  • features of good relational design
  • atomic domains and 1NF
  • functional dependency and 2NF
  • transitive dependency and 3NF
  • BCNF
  • multivalued dependency and 4NF
  • join dependency and 5NF
  • domain-key normal form
ProcessNormal forms
  1. 1

    1NF

    Atomic values

  2. 2

    2NF

    1NF + no partial dependency

  3. 3

    3NF

    2NF + no transitive dependency

  4. 4

    BCNF

    Every determinant is a candidate key

  5. 5

    4NF

    No multivalued dependency

  6. 6

    5NF

    No join dependency

1

Topic 1

The entity–relationship model and ER diagrams

The ER model (Peter Chen, 1976) describes data as entities, their attributes and the relationships between them.

ER symbolRepresents
RectangleEntity set
Double rectangleWeak entity set
EllipseAttribute
Underlined ellipseKey attribute
Double ellipseMultivalued attribute
Dashed ellipseDerived attribute
DiamondRelationship
Double diamondIdentifying relationship

Types of attributes: simple and composite (name → first, last), single-valued and multivalued (phone numbers), stored and derived (age from date of birth). Cardinality of relationships: one-to-one (1:1), one-to-many (1:N), many-to-one (N:1) and many-to-many (M:N). Participation: total (every entity participates — double line) or partial.

2

Topic 2

Weak entity sets and extended ER features

A weak entity set has no primary key of its own; it depends on a strong (owner) entity and is identified by a partial key plus the owner's key. Example: DEPENDENT of an EMPLOYEE.

ClassificationExtended ER (EER) features
EER

  • Specialisation

    Top-down: EMPLOYEE into MANAGER and CLERK

  • Generalisation

    Bottom-up: CAR and BIKE into VEHICLE

  • Aggregation

    Treating a relationship as an entity

  • Inheritance

    Lower-level entities inherit attributes of higher ones

Example

University ER diagram: STUDENT (ID, name) — TAKES (M:N, attribute grade) — SECTION (course_id, sec_id, semester, year); SECTION is a weak entity of COURSE through SEC_COURSE; INSTRUCTOR — TEACHES (M:N) — SECTION; INSTRUCTOR — ADVISOR (1:N) — STUDENT.

3

Topic 3

Features of good relational design

Key termsGoals of good design
No unnecessary redundancy
Each fact stored once
No anomalies
Insertion, update and deletion anomalies avoided
Lossless decomposition
Original relation recoverable by natural join
Dependency preservation
Constraints checkable without joins
Reasonable performance
Not so many tables that every query needs many joins
4

Topic 4

Anomalies of a bad design

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.
5

Topic 5

Atomic domains and first normal form

  • A domain is atomic if its values are indivisible units. A relation is in 1NF if every attribute holds only atomic values and there are no repeating groups.

Example

STUDENT(roll, name, phones = "98..., 99...") violates 1NF. Fix: STUDENT(roll, name) and STUDENT_PHONE(roll, phone).

6

Topic 6

Functional dependency and second normal form

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.
  • 2NF: in 1NF and every non-prime attribute is fully functionally dependent on every candidate key (no partial dependency).

Example

RESULT(roll, course_code, marks, course_title): course_code → course_title is partial. Decompose into RESULT(roll, course_code, marks) and COURSE(course_code, course_title).

7

Topic 7

Transitive dependency and third normal form

  • 3NF: in 2NF and no non-prime attribute is transitively dependent on a candidate key. Equivalently, for every non-trivial FD X → A, X is a super key or A is a prime attribute.

Example

STUDENT(roll, dept_id, dept_head): roll → dept_id → dept_head. Decompose into STUDENT(roll, dept_id) and DEPT(dept_id, dept_head).

8

Topic 8

Boyce–Codd normal form

  • BCNF: for every non-trivial FD X → Y, X is a super key. Stricter than 3NF; every BCNF relation is in 3NF, but decomposing to BCNF may lose dependency preservation.

Example

TEACH(student, subject, teacher) with teacher → subject and {student, subject} → teacher. teacher is not a super key, so not BCNF. Decompose into (teacher, subject) and (student, teacher).

9

Topic 9

Armstrong's axioms and attribute 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.

10

Topic 10

Multivalued dependency and fourth normal form

  • MVD X →→ Y: each X value is associated with a set of Y values independent of the other attributes.
  • 4NF: in BCNF and for every non-trivial MVD X →→ Y, X is a super key.

Example

FACULTY(name, subject, hobby) — subjects and hobbies are independent sets for each faculty member; decompose into (name, subject) and (name, hobby).

11

Topic 11

Join dependency and fifth normal form

  • A relation has a join dependency JD(R1, …, Rn) if it always equals the natural join of its projections on R1 … Rn.
  • 5NF (project–join normal form): every non-trivial join dependency is implied by the candidate keys — the relation cannot be decomposed losslessly any further.

Example

AGENT_COMPANY_PRODUCT: if agents sell products of companies they represent whenever the company makes that product, the relation decomposes losslessly into (agent, company), (company, product) and (agent, product).

12

Topic 12

Domain–key normal form

  • DKNF (Fagin, 1981): every constraint on the relation is a logical consequence of domain constraints and key constraints only. A relation in DKNF has no insertion or deletion anomalies and is in 5NF, but there is no general algorithm to achieve it, so it is mainly of theoretical value.

Example

ACCOUNT(acc_no, type, min_balance) with the rule "savings accounts need a minimum balance of ₹1,000; current accounts ₹10,000" — the rule ties two attributes. Splitting into SAVINGS and CURRENT tables with domain constraints on balance expresses it through domains and keys.

ProcessNormalisation ladder
  1. 1

    UNF

  2. 2

    1NF

    Atomic values

  3. 3

    2NF

    No partial dependency

  4. 4

    3NF

    No transitive dependency

  5. 5

    BCNF

    Every determinant a super key

  6. 6

    4NF

    No non-trivial MVD

  7. 7

    5NF

    No non-trivial JD

  8. 8

    DKNF

    All constraints from domains and keys

Key terms

Atomic domain
Domain whose values are indivisible
Partial dependency
Dependency on part of a composite key
Transitive dependency
Non-key attribute depending on another non-key attribute
BCNF
Every determinant is a super key
DKNF
All constraints follow from domain and key constraints

Quick revision

  • ER: entities, attributes, relationships, cardinality, weak entities, EER; reduction to tables.
  • Good design: no redundancy, anomalies; lossless, dependency preserving.
  • 1NF atomic; 2NF no partial; 3NF no transitive; BCNF determinant is super key.
  • Armstrong's axioms, closure, keys.
  • 4NF no non-trivial MVD; 5NF no non-trivial JD; DKNF.

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.What is an atomic domain?
  2. Q2.Define 2NF.
  3. Q3.Distinguish 3NF and BCNF.
  4. Q4.What is a multivalued dependency?
  5. Q5.What is a lossless decomposition?
  6. Q6.Define DKNF.

Long-answer questions

  1. Q1.Draw an ER diagram for a university and reduce it to relations.
  2. Q2.Explain functional dependencies and normalisation up to BCNF with examples.
  3. Q3.Explain multivalued and join dependencies with 4NF and 5NF.
  4. Q4.Explain the features of good relational design and DKNF.

Stuck on this unit?

Message SBS on WhatsApp for help with Relational Database Management System, or to ask about studying M.Sc IT at Synetic.

WhatsApp us