-1

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
shanlikestrains
  • 7
  • 1
  • 5
  • 1
    Right now you are just asking for us to rewrite your textbook with a bespoke tutorial & do your (home)work & you have shown no research or other effort. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following your textbook with justification & ask 1 specific question re the first place you are stuck. PS All the parts are SO duplicates. "Any help" is not a valid SO question. You don't give anything needed to solve this so it seems you don't know very basics. Textbook. – philipxy Apr 08 '20 at 01:22

1 Answers1

-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:

e_i_pi
  • 4,590
  • 4
  • 27
  • 45