BCNF conversion

Question

I am trying to comprehend BCNF databasing and I can't quite wrap my head around it.

Consider the following relation:

R (A, B, C, D, E, F, G)

The following functional dependences hold:

A -> E, F
A -> G
A, B -> D
B -> C
E, F -> G
A -> D

How would I make it BCNF?

Answer 1

Speaking informally, in homework problems, you get to BCNF by

• assuming you're in at least 1NF,
• removing partial key dependencies to get to 2NF (at least),
• removing transitive dependencies to get to 3NF (at least), and finally
• removing remaining functional dependencies in which the left-hand side isn't a candidate key to get to BCNF (at least).

An example of a partial key dependency is the pair

AB->D
A->D

Since A alone determines D, the functional dependency AB->D has a partial key dependency.

An example of a transitive dependency is the pair

A->EF
EF->G

There's no guarantee that you can normalize a given relation to, say, BCNF and no higher. (This seems to cause a lot of confusion among university students on SO.) Removing partial-key dependencies to get to 2NF might leave all the relations in 5NF.

Answer 2

）We can use the Armstrong Axioms to get the F+

A -> D,E,F,G

B -> C (A,B -> D is ignored because A -> D)

E,F -> G

We can get the primary key(A,B).

According to the definition of BCNF, we have to separate A-> (E,F), B->C, A -> D and (E,F) -> G from the original schema.

(A,B) Primary key(A,B)

(B,C) Primary Key(B)

(A,D) Primary Key(A)

(A,E,F) Primary Key(A)

(E,F,G) Primary Key(E,F)

That's BCNF decomposition.

Answer 3

You can try this: Table 1: A, B, D, E, F with composite primary keys (A, B) and foreign key( B) refer to table 2(B) Table 2: B, C with B is primary key Table 3: A, E, F, G with composite primary keys(A, E, F)