Converting 3NF to BCNF

Question

I am trying to change my table from Third Normal Form to BCNF but not sure I understand the concept entirely.

I managed to work through 1NF, 2NF and 3NF (I think), but need help getting it to BCNF.

I have

**Students Table**

ID -- First Name -- Last Name -- Age
1  --   Joe      --   Jugg    -- 22
2  --   Ben      --   March   -- 23
3  --   Sally    -- Rainbow   -- 19



**StudentCourse Table**

ID -- Course Title -- Grade
1  -- Math         -- A
1  -- Physics      -- B
2  -- Math         -- C
3  -- Music        -- A


**Courses Table**

Course Title -- Course Fee -- Qualification -- Lecturer
Math         -- £1900      -- Advanced Level -- 2
Physics      -- £2300      -- Diploma        -- 1
Music        -- £1200      -- Certificate    -- 3



**Lecturers Table** 


Lecturer ID -- Lecturer Name
1           --  James Thomas
2           -- Harry Todd
3           -- Rachel Adam

I appriciate ANY help and would appriciate if you could explain the concept to me so I can understand, thanks.

Answer 1

I don't think there's enough information there to determine how to reach BCNF from 3NF. We need to know something about the functional dependencies between columns within tables in order to extrapolate candidate keys and super keys. It doesn't help that the tables all have information that doesn't appear in multiple rows, with the exception of StudentCourse which already meets BCNF.

Looking at the data, we can postulate on the functional dependencies for the purposes of a working example. This doesn't mean the answer I'm providing is correct, but will give some insight given presumptions.

Let us presume that:

• Course Titles taught by a particular Lecturer are always taught at the same Qualification level
• All Certificates have a £1200 fee
• All Advanced Level Qualifications have a £1900 fee
• All Diplomas have a £2300 fee

Let us also presume that the Courses table has the following rows (note the additional fourth row):

Course Title -- Course Fee -- Qualification -- Lecturer
Math         -- £1900      -- Advanced Level -- 2
Physics      -- £2300      -- Diploma        -- 1
Music        -- £1200      -- Certificate    -- 3
Music        -- £1900      -- Advanced Level -- 1

Here we have a case of two functional dependencies in the table:

• Candidate Key #1 = (Course Title, Lecturer) -> (Course Fee, Qualification)
• Candidate Key #2 = (Qualification) -> (Course Fee)

If we want to change the Course Fee for Advanced Level Qualification to £2000, then because we have to update two rows in the Courses table, we encounter an update anomoly - this is a violation of BCNF. We should only ever have to update one row in some table.

In order to meet BCNF, we need to create a new Qualification table, like so:

Qualification  -- Course Fee
Certificate    -- £1200
Advanced Level -- £1900
Diploma        -- £2300

...and in conjunction with that new table, we change the Courses table to the following:

Course Title -- Qualification -- Lecturer
Math         -- Advanced Level -- 2
Physics      -- Diploma        -- 1
Music        -- Certificate    -- 3
Music        -- Advanced Level -- 4

We now meet BCNF, as we no longer have a second candidate key (Qualification -> CourseFee) in the Courses table, and are able to perform our update on a single row.

---

You may find further useful information in the following links:

• Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old (StackOverflow question with high votes)
• External link that has a working example of data transforming from 1NF > 2NF > 3NF > BCNF > 4NF > 5NF