Re: BCNF: superkey or candidate key ?

"David Cressey" <dcressey_at_verizon.net> wrote in message news:u49Sg.6942$Iq5.6063_at_trndny02...
>
> "Jan Hidders" <hidders_at_gmail.com> wrote in message
> news:1159257366.662041.197990_at_h48g2000cwc.googlegroups.com...
>
>> I assume you are talking about the case where R has only two columns.
>> Indeed, then it has only trivial FDs and is in BCNF. All the
>> definitions agree on that, of course, because they are equivalent.
>
> I'm interested in the case where R has n columns, and the primary key has
> n-1 columns.
> Is there anything in particular that applies to that case, and not to
> cases
> with more than one non-key
> column?
>
> Here's what's behind my question. relational tables with at most 1
> non-key
> column is where the great debate about NULLs becomes moot. If you want to
> leave the non-key column NULL, then just omit the row. Of course, you
> need
> another table with just the primary key to keep track of which of the
> possible primary keys are in existence.
>

I don't think you can dismiss the NULL debate by transforming a database schema into one that has relation schemata that have only one key and one non-prime attribute. There are issues that must be addressed from both a practical and theoretical standpoint.

One issue involves "lossless" decomposition. Even though a relation that conforms to the schema {A, B, C} and satisfies the functional dependencies A --> B and A --> C is equal to the join of its projections over {A, B} and {A, C}, that doesn't necessarily mean that a database schema F consisting of R {A, B, C} is equivalent to a database schema G consisting of S {A, B} and T {A, C}. The closure of R includes the nontrivial functional dependencies A --> B, A --> C, and A --> {B, C}, but A --> {B, C} is not preserved when the relation schema R {A, B, C} is decomposed into S {A, B} and T {A, C}. In order for the two database schemata to be equivalent, every instance of G must also satisfy a circular inclusion dependency between S[A] and T[A]. The point I'm trying to make is that if {B, C} is significant, then decomposing R into S and T may not be indicated; therefore, you may need relation schemata with more than one non-prime attribute.

The number of attributes that allow NULLs can also complicate things. If both B and C in R can be NULL, then a database schema equivalent to F with no NULLs would require three relation schemata, S {A, B}, T {A, C} and U {A}, and two inclusion dependencies, S[A] <= U[A] and T[A] <= U[A] . If only C in R can be NULL, then an equivalent database schema would require two relation schemata, S {A, B} and V {A, B, C}, and one inclusion dependency, V[A, B] <= S[A, B].

Another issue is that relations can have more than one candidate key. If a BCNF relation schema R {A, B, C} satisfies the FDs, A --> C and B --> C, then the closure of R includes the nontrivial FDs,

A --> B, A --> C, A --> {B, C},
B --> A, B --> C, B --> {A, C},
A --> B --> C, B --> A --> C, and

Because C has multiple determinants, it may be the case that C could be NULL only some of the time. For example, a particular combination of values for A and B may require a value for C, while another may not. Transforming a database schema that allows NULLs into one that doesn't may require constraints that were specified for a single relation schema to be specified in terms of multiple relation schemata.

I think that while it is always possible to transform a database schema with nullable attributes into one without, there are consequences to doing so that should be taken into account. 6NF (Date et al) isn't a panacea. Received on Wed Sep 27 2006 - 10:07:13 CEST