Complete Worked Normalisation: Unnormalised to BCNF

The starting table

A training institute records course enrolments in one spreadsheet exported as a single table.

  UNNORMALISED

  student_id | student_name | student_phones        | course_codes  | course_names        | trainer  | trainer_email   | centre  | centre_city | fee   | marks
  -----------+--------------+-----------------------+---------------+---------------------+----------+-----------------+---------+-------------+-------+------
     S1      | Meera        | 98400111, 98400222    | C1, C2        | Databases, Networks | T1, T2   | t1@x, t2@x      | CTR1    | Chennai     | 5000  | 87, 74
     S2      | Ravi         | 98400333               | C1            | Databases           | T1       | t1@x            | CTR1    | Chennai     | 5000  | 91
     S3      | Anitha       | 98400444, 98400555    | C3            | Circuits            | T3       | t3@x            | CTR2    | Madurai     | 4500  | 65

  Multiple values in single cells, parallel lists that must
  be read positionally, and one row per student rather than
  one row per fact. This is not a relation at all.

The business rules

1. A student id identifies one student, who has one name and may have several phone numbers.
2. A course code identifies one course, which has one name.
3. Each course is taught by exactly one trainer.
4. Each trainer has one email address and teaches at exactly one centre.
5. Each centre is in exactly one city and charges one fee.
6. Marks are recorded per student per course.

Step 1 — to 1NF

Remove repeating groups and make every value atomic. One row per student per course; phone numbers move to their own relation.

  ENROLMENT ( student_id, student_name, course_code,
              course_name, trainer, trainer_email,
              centre, centre_city, fee, marks )

  student | name   | course | c_name    | trainer | t_email | centre | city    | fee  | marks
  --------+--------+--------+-----------+---------+---------+--------+---------+------+------
  S1      | Meera  | C1     | Databases | T1      | t1@x    | CTR1   | Chennai | 5000 |  87
  S1      | Meera  | C2     | Networks  | T2      | t2@x    | CTR1   | Chennai | 5000 |  74
  S2      | Ravi   | C1     | Databases | T1      | t1@x    | CTR1   | Chennai | 5000 |  91
  S3      | Anitha | C3     | Circuits  | T3      | t3@x    | CTR2   | Madurai | 4500 |  65

  STUDENT_PHONE ( student_id, phone )
     S1 98400111
     S1 98400222
     S2 98400333
     S3 98400444
     S3 98400555
     primary key ( student_id, phone )

  IN 1NF. Every value atomic, no repeating groups.

Step 2 — dependencies and keys

  From the business rules:

    F1  student_id -> student_name
    F2  course_code -> course_name, trainer
    F3  trainer -> trainer_email, centre
    F4  centre -> centre_city, fee
    F5  { student_id, course_code } -> marks

  CANDIDATE KEY, by the Phase 7 method:

    left only  : student_id, course_code   -> ESSENTIAL
    right only : student_name, course_name, trainer_email,
                 centre_city, fee, marks   -> never in a key
    both       : trainer, centre           -> middle

    CORE = { student_id, course_code }
    closure:
      start                        { student_id, course_code }
      F1  add student_name         + student_name
      F2  add course_name, trainer + course_name, trainer
      F3  add trainer_email,centre + trainer_email, centre
      F4  add centre_city, fee     + centre_city, fee
      F5  add marks                + marks
      = every attribute

    CORE+ = R, so { student_id, course_code } is the ONLY
    candidate key.

    prime     : student_id, course_code
    non prime : everything else

Step 3 — to 2NF

  Test each dependency against the composite key.

    F1  student_id -> student_name
          left side is PART of the key    -> PARTIAL
    F2  course_code -> course_name, trainer
          left side is PART of the key    -> PARTIAL
    F5  { student_id, course_code } -> marks
          full key                        -> FULL

  Two partial dependencies. Move each to its own relation
  keyed by its real determinant.

  STUDENT ( student_id, student_name )
     S1 Meera / S2 Ravi / S3 Anitha

  COURSE ( course_code, course_name, trainer,
           trainer_email, centre, centre_city, fee )
     C1 Databases T1 t1@x CTR1 Chennai 5000
     C2 Networks  T2 t2@x CTR1 Chennai 5000
     C3 Circuits  T3 t3@x CTR2 Madurai 4500

  ENROLMENT ( student_id, course_code, marks )
     S1 C1 87 / S1 C2 74 / S2 C1 91 / S3 C3 65

  STUDENT_PHONE unchanged.

  All four are now in 2NF.

Step 4 — to 3NF

  STUDENT      key student_id, single attribute, no other
               dependency                        -> 3NF
  ENROLMENT    key { student_id, course_code },
               marks depends on the whole key     -> 3NF
  STUDENT_PHONE  both attributes prime            -> 3NF

  COURSE ( course_code, course_name, trainer,
           trainer_email, centre, centre_city, fee )
    key = course_code

    course_code -> trainer          direct
    trainer     -> trainer_email, centre
                                    trainer is NOT a key
    centre      -> centre_city, fee
                                    centre is NOT a key

    TWO transitive chains:
      course_code -> trainer -> trainer_email
      course_code -> trainer -> centre -> centre_city, fee

    NOT in 3NF.

  DECOMPOSE, innermost dependency first:

  CENTRE ( centre, centre_city, fee )
     CTR1 Chennai 5000
     CTR2 Madurai 4500

  TRAINER ( trainer, trainer_email, centre )
     T1 t1@x CTR1
     T2 t2@x CTR1
     T3 t3@x CTR2
       centre -> CENTRE

  COURSE ( course_code, course_name, trainer )
     C1 Databases T1
     C2 Networks  T2
     C3 Circuits  T3
       trainer -> TRAINER

  Each relation now has only its key as a determinant.
  ALL IN 3NF.

Step 5 — to BCNF

  Test every relation: is every determinant a super key?

  STUDENT        student_id -> student_name
                 student_id IS the key                 -> BCNF
  STUDENT_PHONE  no non trivial dependency             -> BCNF
  ENROLMENT      key -> marks, determinant is the key  -> BCNF
  COURSE         course_code -> course_name, trainer
                 course_code IS the key                -> BCNF
  TRAINER        trainer -> trainer_email, centre
                 trainer IS the key                    -> BCNF
  CENTRE         centre -> centre_city, fee
                 centre IS the key                     -> BCNF

  ALREADY IN BCNF. Nothing further to do.

  This is the usual outcome: a schema carefully taken to
  3NF is very often already in BCNF, because BCNF and 3NF
  differ only when candidate keys overlap.

The final schema

  STUDENT       ( student_id PK, student_name )
  STUDENT_PHONE ( student_id FK, phone, PK both )
  CENTRE        ( centre PK, centre_city, fee )
  TRAINER       ( trainer PK, trainer_email, centre FK )
  COURSE        ( course_code PK, course_name, trainer FK )
  ENROLMENT     ( student_id FK, course_code FK, marks,
                  PK ( student_id, course_code ) )

  6 relations, all in BCNF.

Verifying the result

  LOSSLESS?
    Each split was on a determinant that became the key of
    the new relation, and the determinant stayed behind as
    a foreign key. Every join is on a key.  -> LOSSLESS

  DEPENDENCY PRESERVING?
    F1 student_id -> student_name       in STUDENT      ok
    F2 course_code -> c_name, trainer   in COURSE       ok
    F3 trainer -> t_email, centre       in TRAINER      ok
    F4 centre -> city, fee              in CENTRE       ok
    F5 key -> marks                     in ENROLMENT    ok
    -> ALL PRESERVED

  ANOMALIES RESOLVED
    insert  a centre with no trainers, a course with no
            students - all now storable
    update  a fee changes in ONE row of CENTRE
    delete  removing an enrolment loses only that enrolment

Common mistakes

• Jumping straight to the final answer. Marks are awarded per stage; show 1NF, 2NF and 3NF separately.
• Not finding the candidate key before testing 2NF. Every test depends on it.
• Decomposing the outer transitive dependency first. Work from the innermost determinant outward, as done in step 4.
• Dropping the determinant from the parent relation. It must remain as a foreign key.
• Forgetting to verify losslessness and preservation. The last step is part of the answer.

Exam and interview questions

1. Normalise a given unnormalised table through 1NF, 2NF, 3NF and BCNF, showing each stage.
2. How do you find the candidate key of a large relation before testing 2NF?
3. Why should transitive dependencies be removed from the innermost determinant outward?
4. After decomposing, how do you prove the result is lossless and dependency preserving?
5. Which stage of this worked example removed each of the three anomalies?

Practice

1. Normalise LIBRARY ( member_id, member_name, book_isbn, book_title, author, publisher, publisher_city, issue_date, due_date ) from UNF to BCNF, showing every stage.
2. Normalise HOSPITAL ( patient_id, patient_name, doctor_id, doctor_name, speciality, dept, dept_head, visit_date, diagnosis ).
3. For your answer to question 1, verify losslessness and dependency preservation.
4. State which stage removed which anomaly, for all three anomalies.

Conclusion

Atomic values first, then remove partial dependencies, then transitive ones, then check every determinant is a super key — and finish by verifying losslessness and dependency preservation. Follow the stages in order and even a large table reduces cleanly.