Re: Question re: Practical Issues in Database Management

On Mar 23, 9:18 pm, Joe Thurbon <use..._at_thurbon.com> wrote:
> TroyK wrote:
> > On Mar 22, 3:03 pm, Joe Thurbon <use..._at_thurbon.com> wrote:
>
> [...]
>
>
>
>
>
>
>
> >> "A MVD between two columns exists when sets of values in one column are
> >> each associated with values in another column"
>
> >> and 4th normal form is defined as
>
> >> "If no MVDs exist between columns, then a table is in 4th normal form"
>
> >> Even assuming that 'no MVDs' is shorthand for 'no MVDs that are not also
> >> FDs' these definition would mean that the table,
>
> >> EMP# ACTIVITY
> >> ==============
> >> 130 DEBUG
> >> 130 SUPPORT
>
> >> would not be in 4th normal form. (Since, it's clear that there is a set
> >> of values {DEBUG, SUPPORT} that is functionally dependant on EMP#. The
> >> caption of the example on page 138 says that the above table is in 4th
> >> normal form (as do all other definitions I've read).
>
> > But the set of values is not, in fact, functionally determined by emp
> > #.
>
> I disagree (he says, with trepidation).
>
> Although I may be abusing the term functionally dependent - I'd welcome
> correction if I am. Note above that I said there is a _set_ of values,
> and I meant that that set is functionally dependant on EMP#, not that
> ACTIVITY was functionally dependent on EMP#.
>
> I thought that the general notion of dependency was (informally) along
> the lines of:
>
> "B is dependent on A if, every time I am told a value for A, I can
> observe the world and determine the value of B."
>
> For functional dependencies, (A -> B), B is a 'simple value'. For
> multivalues dependencies, (A ->> B), B is a set of values, and for join
> dependencies, I'll have to keep reading and see if my analogy falls over.
>
> As an aside, for a table in 3NF, for exmaple.
>
> TABLE A B
> === ---
> 1 1
> 2 1
> 3 2
>
> Is it right to say that
>
> A -> B
>
> and
>
> B ->> A
>
> And if not, why not?
>

Difficult to say without knowing the business rules that constrain the example relation given.

>
>
> > By taking the projections as detailed in the book, we are able to
> > assert, e.g., that employee "130" is also responsible for activity
> > "DESIGN" on all of the projects on which she works by simply adding
> > that tuple to the EMP-ACT relation.
>
> >
>
> Yes, so there is a (new) set of Activity values which are associated
> with the EMP# value 130. This is, I thought, the definition of
> multivalued dependency.
>
>
>
> > Prior to the normalization, it would have been necessary to record the
> > fact multiple times, once for each of the projects to which employee
> > 130 is assigned. (Try adding the "DESIGN" activity to table A in the
> > diagram, paying attention to the business rules, to see the effect).
>
> Yep, I (think I) understand the point of 4th normal form. I'm merely
> querying the definition given in the book, not the usefulness of 4NF.
>

I think that I can be convinced that the definition in the book is "off", or maybe poorly worded. It seems that the goal is not to eliminate MVDs entirely, but rather to eliminate nontrivial MVDs. The projections result in relations in which the MVDs that hold are trivial.

Check Fagin's paper for more detail and math: http://www.almaden.ibm.com/cs/people/fagin/tods77.pdf

HTH, and thanks for giving me a reason to revisit this! TroyK

>
>
> >> Have I found an error in the book (unlikely). And if not, what am I missing?
>
> > Perhaps unlikely, but not impossible; there is another error not
> > listed in either of the linked errata -- carefully examine the BCNF
> > example on P136.
>
> Will do. Thanks for the pointer.
>
> Cheers,
> Joe- Hide quoted text -
>
> - Show quoted text -
Received on Mon Mar 26 2007 - 19:51:03 CEST