Avatar of Mike Eghtebas
Mike Eghtebas
Flag for United States of America asked on

Multi Value Dependency, BCNF, 4NF

At http://www.slideshare.net/ramsekhargrandhi/mvd-16251057 in side 4 of 14 shown below...

Question: Why name --> --> phone is a multi valued Dependent?

I understand FD in name --> adress. But why we cannot have name --> phone.
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Jim Horn

Phone is directly related to a person (name), and not an address, e.g. my cell phone doesn't *really* have an address related to it.

So, given a table Name was a table, then
Phone would be a 1:M table with fk name, as a name can have multiple phone numbers.
Address could be in the Name table if you don't care about history, or if a name can have multiple addresses.  If you need history, then Address is a separate 1:M table with fk name and some kind of date column(s) to indicate when the address was valid.

No relationship between phone and address.
Mike Eghtebas

Hi Jim,

Dirinkers  <--- Table name with 3 fields

The discussion, as I understand it, to see if this table could be further reduced (broken down to two tables) and why. So, name and phone (if it is cell phone) is candidate key. The combination of  name-cell phone - and address possibly is another candidate key if all names are living alone but we never know. As the result

name --> cell -phone have functional dependency (FD) but
name --> address have not and definately
name - -> beer type have not FD because Mike --> Coors   cannot be unique because we can easily have Robert --> Coors.

Now, lets forget the content of this post and shift to the original question:

Question: Why name --> --> phone is a multi valued Dependent?

The purpose of this post was just to not assume name, phone, address are each separate tables.


Scott Pletcher

>> Address could be in the Name table if you don't care about history, or if a name can have multiple addresses. <<

I believe you meant to say "or if name can have only a single address".  If a name can multiple addresses, then addresses need to be in a separate table, of course.

To me:
(name, phone, address) [beers]
is not even a valid setup of 4nf issues, as the table is not yet in 3nf.  

I believe the only true candidate key here is "name", since phone numbers change all the time.

Thus, phone and address are both non-key attributes, and, if so, we're dealing with potential 3nf issues, not 4nf issues.

I personally don't see any true transitive dependencies among phone, address and beers.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mike Eghtebas

HI Scott,

Could you possibly, in a table like:

field1          field2                field3
---------         -----------            -------------
x                    x                          x

give me an examples for FD and MVD?

Basically, I want to understand FD and MVD.

Also, please add a comment to: https://www.experts-exchange.com/questions/28584856/3rd-Normal-Form.html?anchorAnswerId=40512198#a40512198


Scott Pletcher

Sorry, I need to come back to this.  I've had only about 2 hours' sleep in the last two days so now is the time for me to tackling normalization :-).  Even what I have above is not really clear or direct.
Vitor Montalvão

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.