2

I am having issues understanding how to determine if relations are in BCNF, 3NF, and in general identifying the candidate keys of a relation.

Consider relation R = (A, B, C, D) with the functional dependencies:

AB -> C
C -> D
D -> A

The questions included:

a. list the candidate keys of R
b. determine if R is in BCNF or 3NF.

The solution resolves

a. 3 candidate keys for R are AB, BC, and BD.
b. R is in 3NF, but not in BCNF.

I've read through What is the difference between 3NF and BCNF? and can understand the difference between 3NF and BCNF when referring to non-arbituary words within a database schema. I am ultimately lost when trying to determine the relationships with a reduced relation, as given in the problem.

Could someone explain how the candidate keys were determined above, and why R is in 3NF but not BCNF?

Community
  • 1
  • 1
sudo_coffee
  • 888
  • 1
  • 12
  • 26
  • 1
    Your question is too broad for SO. In practice you are asking all the important issues on relational theory. Read a good book on databases, see for istance this [aswer](https://www.quora.com/What-are-some-good-books-about-databases). – Renzo Oct 31 '16 at 05:51
  • These steps are in any textbook covering normalization. Dozens are free online. Please don't ask us to write you anther. Name & quote/paraphrase one, show the steps of your work following it & ask a question re the first place you are stuck. Also these are all nevertheless easily found duplicate [so] questions. Although that's a poor source for learning about the relational model. – philipxy Aug 04 '19 at 01:59

1 Answers1

5

You are asking several questions at the same time.

  1. How to compute the keys of a relation. In general, the simplest way is to compute the closure of every single combination of attributes and determine which ones are superkeys. From this set of superkeys, find those that are minimal: these are the candidate keys.

  2. How to determine if a relation is BCNF.

FOr this you don't needs keys. All you need to do is test that every non-trivial functional dependency, the left hand side is a superkey of the relation.

How do you determine this for a FD? Simply compute the closure of the left hand side of the FD. If the result is all the attributes of the relation, then it is a superkey.

  1. How to determine if the relation is 3NF?

FDs must be in Canonical form: one attribute in the right hand side.

For every non-trivial FD, either the left hand is a superkey (as in BCNF) or the right hand side is part of a key (any key).

This is a very good video that explains the differences:

BCNF vs 3NF

André Chalella
  • 13,788
  • 10
  • 54
  • 62
dmg
  • 4,231
  • 1
  • 18
  • 24
  • One step at a time... I am struggling to understand how the closure is determined with the abstract R=(A,B,C,D) and further extracting the superkeys to identify the candidate keys. For the BCNF, how are the A,B,C,D entities of R used to determine non-trivial functional dependency? I edited the original question if that helps. – sudo_coffee Oct 31 '16 at 01:03
  • Further searching to determine if a relation is BCNF I found http://stackoverflow.com/questions/23591784/is-this-relation-in-3nf-as-well-as-in-bcnf?rq=1, which uses candidate keys to determine if the relation is in BCNF – sudo_coffee Oct 31 '16 at 01:36