Re: Role of functional dependencies in database design

Op donderdag 12 februari 2015 17:02:19 UTC+1 schreef Nicola:
> >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?

I overplayed my hand, slightly.

Take an example relvar DBR {A B C} subject to the FD {A} -> {B} (and its key thus being {AC}).

Rewind 40 years.

Enforcing this FD in that design involves extra checks, and in the time when all that theory originated, this meant : extra coding work, precisely in the update process that was exposed to the update "anomaly" involved. As far as I can tell, all that theory was developed so that the update "anomalies" implied by a given design, could be _identified_, so that after that an _informed_ decision could be made regarding how to deal with it : (a) eliminate it through redesign, or (b) live with it and keep the update processes that are exposed to the "anomaly" "guarded","monitored","under supervision",... But in that day and age, (b) was almost never a viable option, simply because the _means_ for doing so (which is "if a program gets it wrong, make sure it won't leave its devastating effects persisted in the database", or iow, "declare a constraint") simply weren't available.

Fast forward 40 years.

This day and age, I can simply do the (equivalent of the) following (hypothetical SQL) :

CREATE VIEW PROJ AS SELECT DISTINCT A,B FROM DBR CONSTRAINT UNIQUE (A) ; That is, I have a system to my avail where I simply declare the view (projection on all the attributes mentioned anywhere in an FD), and declare a constraint to the effect that the LHS portion of the FD constitutes a key to that view. And if a program fails to obey the FD, it won't get any updates through (unless it is accidentally a good one).

Where I "overplayed my hand", is that introducing the constraint to enforce the FD to be obeyed, will not help in simplifying the potentially more complex proceedings a program has to follow to get the database updated. That is, while there is no possibility that the update "anomaly" will lead to incorrect data, the "anomaly" is still there and must still be dealt with by the programs that are exposed to it. Received on Thu Feb 12 2015 - 20:25:41 CET