1

How do you tell if a relation R is in BCNF and 3NF?

I'm reading a textbook, and it's telling me that there are 3 main attributes you're looking at, but I'm having trouble understanding what they're saying, or at least applying what they're saying when given a relation and FD's.

The 3 attributes: Given a relation R with the attribute A, and X a subset of attributes of R, for every FD X⟶A in F, one of the following statements is true:

  • A ∈ X; that is, it is a trivial FD (∈ meaning "is found in X")
  • X is a superkey
  • A is part of some key for R

The top two correspond to BCNF, and 3NF's include the third.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
dxu
  • 459
  • 2
  • 5
  • 13
  • Start by listing the attributes in the post -- I suspect they are from a standard theorem, and listing them will help focus responses. –  Oct 16 '11 at 01:04
  • The third statement - "A is part of some key for R" - looks more like 2NF than 3NF to me - see http://en.wikipedia.org/wiki/Second_normal_form . –  Oct 19 '11 at 16:31

1 Answers1

4

The book SQL Antipatterns by Bill Karwin has a nice example about BCNF and 3NF on page 303 that is a little complicated but I believe points out the difference more succinctly than any description of the difference I've read so far.

For example, suppose we have three tag types: tags that describe the impact of the bug, tags for the subsystem the bug affects, and tags that describe the fix for the bug. We decide that each bug must have at most one tag of a specific type. Our candidate key could be bug_id plus tag, but it could also be bug_id plus tag_type. Either pair of columns would be specific enough to address every row individually.

bug_id tag      tag_type
------------------------
1234   crash    impact
3456   printing subsystem
3456   crash    impact
5678   report   subsystem
5678   crash    impact
5678   data     fix

The book then changes this single table (which satisfies 3NF) into two tables that satisfy BCNF:

bug_id tag
----------
1234   crash
3456   printing
3456   crash
5678   report
5678   crash
5678   data

tag       tag_type
------------------
crash     impact
printing  subsystem
report    subsystem
data      fix
Andrew Feather
  • 173
  • 2
  • 14
sarnold
  • 102,305
  • 22
  • 181
  • 238
  • 2
    It feels like a slight of hand has been applied here. You started with {id,type -> tag; tag -> type} and decomposed from the 3NF relation (id, tag, type) to BCNF relations (id, tag) and (tag, type). That decomposition would be lossless join, but you haven't preserved the FD (id, type -> tag): it would now be possible to insert into (bug_id, tag) values (3456, report), whereas previously that would have broken the key constraint. Sometimes that's acceptable, but you should be aware that you've relaxed the constraint. – beldaz Sep 11 '13 at 04:35