Re: Question re: Practical Issues in Database Management

From: Joe Thurbon <usenet_at_thurbon.com>
Date: Sat, 24 Mar 2007 03:18:27 GMT
Message-ID: <7M0Nh.1087$M.766@news-server.bigpond.net.au>

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?

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

>

>> 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 Received on Fri Mar 23 2007 - 22:18:27 CDT