Re: Role of functional dependencies in database design

On Thursday, February 12, 2015 at 1:43:24 PM UTC-8, Nicola wrote:
> In article <b99f007c-a8ab-4a23-9c71-5995e5e2e748_at_googlegroups.com>, Erwin wrote:
>
> > My question, and what I would like to understand is : what exactly is the
> > available input (its semantics and in which possible syntactic forms does it
> > appear, and where) that allows this "discovery" to be made ? I think JKL's
> > post where he used the word "pesky" expressed the very same concern.
>
> Formally, FDs are axioms. As such, *within* the formal system they are
> given facts, but the discovery must happen *outside* the formal system.
> Hence, the available input cannot be described formally (or, if it can,
> it will be in a different formal system that will have its own axioms).

Each base table holds rows that make a corresponding designer-chosen predicate true of the current application situation. A user evaluates the predicate for every row that could fit in a table and puts the ones making true propositions in. They look at the tables and plug the present and absent rows into its predicate to learn how the world is. They query by mapping a predicate about the application situation to a hybrid relation/predicate expression that the DBMS evaluates to the rows making the predicate true.

A designer shows that particular predicates built from these base predicates are true in every application situation that can arise. These are the constraints. A constraint is simultaneously a truth about each database state and its corresponding application situation. So the designer describes the application but this simultaneously describes table values.

> > > >> FDs are a special type of first-order formulas.

From: com...
> F1,... -> T
> FORALL F1,...,Ta,...,Tb,...
> R(F1,...,Ta,...) AND R(F1,...,Tb,...)
> => Ta = Tb

> > > 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

From: com...
> FORALL L1,L2,T,R1,R2
> teaches(L1,T,R1) AND teaches(L2,T,R2) => L1 <> L2

> > Well, there you have it. If you think that this sentence indicates an FD
> > {L,T} -> {room}
>
> Of course not.
>
> > The correct conclusion here is the FD {room,T} -> {L}.
>
> Yes.

No. That FD is:

    FORALL L1,L2,T,R
        teaches(L1,T,R) AND teaches(L2,T,R) => L1 = L2

> > But before spotting
> > that, I first had to twist the requirement into "only one lecturer can be in
> > any given room at any time". Or "if two observations of a lecturer being in
> > a room at time T, both involve the same room and the same time T, then they
> > involve the same lecturer". But (knowing what the FD turned out to be and
> > looking back on the natural language formulation) I find those alternatives
> > only slightly less convoluted than your original.

It's not the same as the original constraint. Which isn't an FD. (Not surprising.)

But the natural language for an FD is not hard:

// {room,T} -> {L}
Given lecturers L1 and L2 and time T and room R,

    if L1 teaches at T in R and L2 teaches at T in R     then L1 = L2

philip Received on Fri Feb 20 2015 - 08:42:40 CET