Re: Role of functional dependencies in database design

Op donderdag 12 februari 2015 17:02:19 UTC+1 schreef Nicola:
> I hope you won't mind if I start a new thread for the subject. The other
> thread has got quite long and somewhat off-topic.
>
> So, the question is: what is the role of FDs (and possibly other types
> of dependencies) in database design? My personal take:
>
> 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. I do think, however, that when a system is
> understood sufficiently well (e.g., by means of a conceptual design),
> they offer an aid (are my schemas well-designed according to certain
> criteria?) and insight (allowing us to possibly find alternative ways to
> design a database, for example).

The problem is that formally speaking, you must first have that conceptual design (entities+relships) translated into a logical one (relvars). Only after that can you start thinking about FDs and normalization, and it requires that you somehow derived the FDs from, well what else other than the conceptual schema you have, perhaps incremented with a pack of tacit understandings of what it represents.

And what in a conceptual schema is it that you can get the FDs from ? NOT from any key specifications that are already there, because if you already have them, it's pointless deriving FDs from them only to find out that normalization theory gives you exactly the keys you started off with.

The normalization procedure/process as rooted in normalization theory, assumes a bottom-up methodology. First assume the database is a single gigantic table, then start decomposing. The trouble is that most people intuitively prefer top-down, in practice they also do it, and I have certainly known a time when they got things close to 99% right without any application of any kind of normalization theory.

>
> >> Nicola wrote:
> >> FDs do not come out of void, they follow from your requirements. They
> >> are just a formal version of a very special (and relatively simple)
> >> type
> >> of requirement. I don't arbitrarily decide to assume that {desert
> >> island} -> {can opener}, unless you tell me that there cannot be two
> >> can
> >> openers in the same desert island, in the world of desert islands you
> >> are interested in.
>
> > 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" ?
>
> 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.

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.

>
> >> 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.
> >
> >"The projection on {desert island, can opener} of the join of all
> >tables in the database, represents a function from desert island to can
> >opener."
>
> Well no, like this certainly no :) Maybe something along these lines
> (paraphrasing the given-when-then constructs existing in some testing
> tools):
>
> 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

Well, there you have it. If you think that this sentence indicates an FD {L,T} -> {room}, then you are wrong ! This statement per se does not prohibit the same lecturer being in two distinct rooms at the same time. (Physics does, perhaps, but that really is another issue. My issue is correct logical inferences from given requirements as stated.)

The correct conclusion here is the FD {room,T} -> {L}. 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.

(Your example being "temporal" is very tempting to go drifting off into the temporal arena. In fact, the temporal proposals by Darwen/Date/Lorentzos talk about keys almost all the time, but normalization theory isn't mentioned even once. My feeling is the current normalization theory is insufficient to underpin their approach soundly, and although I very much like their approach altogether, I'm amazed I never see any complaint about this.)

(...leaving the ASSERTIONs example for another post)

>
> (this is absolutely off the top of my head: don't take it as a serious
> attempt).
>
> >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.
>
> Could you provide a small example?
>
> Nicola
>
> --- news://freenews.netfront.net/ - complaints: ---
Received on Thu Feb 12 2015 - 19:58:17 CET