Re: Functional Dependencies > Uniqueness Constraints

Marshall wrote:
> Okay, fine. I propose that a good relational DBMS should
> not have any special feature for enforcing uniqueness constraints.
> Instead, it should be able to record and enforce functional
> dependencies.

I'd rather say that an RDBMS should be able to reason about FDs. Uniqueness constraints are convenient, and conceptually simpler than FDs, and half the point of normalisation is to enable all FDs to be specified as uniqueness constraints / keys.

But what exactly do you mean? That the DDL of the DBMS shouldn't include the concept of uniqueness constraints? And/or that the DBMS shouldn't bother optimising the enforcement of FDs that correspond to keys (i.e. where the closure is the entire relvar heading)?

> (And of course there must be a rule that
> says every base table must have at least one functional
> dependency in which the union of the determinant set
> and the dependent set equals the set of attributes. (This
> restriction is sufficient to ensure every base table is a
> relation; is it necessary?))

No more (or less) necessary than a rule that any relvar must have at least one key specified.

> Why? Because uniqueness constraints by themselves
> don't capture enough meaning.
>
> Example:
>
> Relation R1(a,b) with FD {a} -> {b}
> Relation R2(c,d) with FD {c} -> {d}
>
> Derived relation J = R1 join R2
> (this join is a cross product)
>
> FDs in J:
> {a} -> {b}
> {c} -> {d}
>
> What if we had just recorded uniqueness constraints in R1
> and R2 (on a and c respectively?) We can't express anything
> derived from those uniqueness constraints in J, at least not
> as uniqueness constaints anyway.

Yes, we can. Dataphor, for example, derives that the/a key for J is { a, c }. In any case, from the posited uniqueness constraints on R1 and R2, we can infer the FDs {a} -> {b} and {c} -> {d}. Now, I agree that the DBMS should be able to infer that these FD still hold in J. (Dataphor does not do this, afaik, with the result that its key inference is incomplete/wrong/weak for more complex expressions.) But to discard the concept of uniqueness constraints is to throw the baby out with the bathwater---it seems detrimental to both usability and performance. The ability to specify constraints in the form of FDs might be useful, but marginally so---in most cases, keys are both sufficient (due to normalisation) and simpler; and in the cases where they aren't, FDs can be expressed as database constraints without special syntax.

To sum up: A good RDBMS should be able to infer FDs from keys / uniqueness constraints (and inclusion dependencies), and reason about them; in particular, to infer FDs (and thus keys) for arbitrary relation expressions, e.g. in order to perform semantic optimisation.

-- 
Jon