# Re: BCNF

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 01 Aug 2008 08:49:01 -0700
Message-ID: <1217605733.949337@bubbleator.drizzle.com>

aarklon_at_gmail.com wrote:
> Hi all,
>
> BCNF
>
> the following is the definition is the definition of BCNF , which i
> saw in a schaum series book
>
> 1) The relation is 1 N.F
>
> 2) for every functional dependency of the form X -> A , we have
> either A C X or X is a super key of r. in other words,
> every functional dependency is either a trivial dependency or in
> the case that the functional dependency is not trivial then X must
> be a super key.
>
> now my questions are as follows
>
> 1)
>
> we know that 2-ND normal form is all about separating partial
> dependencies and full dependencies.third normal form is all about
> removing transitive dependencies, in these lines can any one give
> simple/ easy to understand method/explanation for converting a
> relation in 3rd normal form to BCNF
>
>
> 2) how correct is the following definition of transitive
> dependencies
>
>
> transitive dependencies
>
> assume that A,B, and C are the set of attributes of a relation(R).
> further assume that the following
> functional dependencies are satisfied simultaneously : A -> B , B -/-

```>> A, B -> C , and C -/-> A and A -> C
```

> observe that C -> B is neither prohibited nor required. if all these
> conditions are true, we will say that attribute C is transitively
> dependent on attribute on A

Personally I prefer the following definition:

Boyce Codd Normal Form (BCNF) is a further refinement of 3NF. In his later writings Codd refers to BCNF as 3NF. A row is in Boyce Codd normal form if, and only if, every determinant is a candidate key. Most entities in 3NF are already in BCNF.

BCNF covers very specific situations where 3NF misses inter-dependencies between non-key (but candidate key) attributes. Typically, any relation that is in 3NF is also in BCNF. However, a 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys.

```--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
```
Received on Fri Aug 01 2008 - 10:49:01 CDT

Original text of this message