Re: Role of functional dependencies in database design

On Thursday, February 12, 2015 at 8:02:19 AM UTC-8, Nicola wrote:

> So, the question is: what is the role of FDs (and possibly other types of dependencies) in database design?

We determine predicates that describe application situations. Basic predicates correspond to base tables. A FD holds when a certain constraint predicate built from a table predicate holds. (A JD corresponds to being able to write a table predicate as a conjunction of others. A FD corresponds to a certain JD.)

> In general, I don't think that it is possible for them to inform the design activity since the early stages or that they should be used as the main design tool.

As soon as you have a predicate you can determine FDs.

> > Erwin wrote:
> >There you have it. When people tell you "there can only be one can
> >opener on any given desert island", then what do you see ?
> >
> >A table {desert island, can opener} with (a.o.) a key "desert island",
> >or
> >A relation schema of whatever set of attributes in which you must add
> >the FD desert island -> can opener ? Where this then tells you "the
> >identity of a desert island is a determinant factor to the identity of
> >the can opener that is on it" or "the relation over {desert island, can
> >opener} is a function from desert island to can opener" ?

You don't need keys or entities, you just need predicates and determinants.

"If desert island I has opener Oa and desert island I has opener Ob then Oa = Ob."

Ergo I -> O in "desert island I has opener O".

> At an early stage, you probably cannot say: it might also be that desert island and can opener should be represented as two relation schemas (for which we have identified no attributes yet) related through a one-one or one-many relationship. My point here is simply that FDs are not invented, they are discovered.

First *say something* then you can find the FDs. There is no "one-one or one-many relationship" until then. First comes *a particular* relationship ie predicate then come parameters/attributes and then arities and other constraints.

> >> FDs are a special type of first-order formulas. I can imagine defining an English-like syntax for them.
>
> (Not that it would necessarily be a good idea, I should add.)
>
> >Hmmmmmmmm.

FD F1,... -> T holds in predicate R(F1,...,T,...) when (for all situations):

    FORALL F1,...,Ta,...,Tb,...

            R(F1,...,Ta,...) AND R(F1,...,Tb,...)
        => Ta = Tb

Notice that the determinant variables are unchanged between atoms, that there are two determined variables for one value, and that the other variables are don't-cares.

We can describe this pattern. Eg an FD holds "when each satisfying determinant subtuple has just one determined value". But all we have to do is *use* it with each predicate.

> given two lecturers L1 and L2 and a time T
> when L1 is teaching at time T and L2 is teaching at time T
> then L1 and L2 are in different rooms

FORALL L1,L2,T,R1,R2
    teaches(L1,T,R1) AND teaches(L2,T,R2) => R1 <> R2

> (this is absolutely off the top of my head: don't take it as a serious attempt).

No FD.

For "then it was the same room", T -> R. For "then it was the same lecturer", T -> L.

> >And besides, avoiding redundancy was only a relevant topic in database
> >design as long as there were no feasible ways to control the
> >redundancies, e.g. via ASSERTIONs. Those days are gone.

But predicates, queries and updates are simpler in 5NF, ie when the only JDs are implied by the CKs.

philip Received on Fri Feb 20 2015 - 07:16:12 CET