Why normalized schemes up to 3FN do not have N: N?

Question

Why normalized schemes up to 3FN do not have N: N?

Navigation

#1 by (3 votes)

4

Explanation of concept

I understand that by definition a database schema is in 3FN , when it is in 1FN, 2FN and does not have transitive dependency.

Transitive dependency is when a NOT attribute depends on a / strong> key.

Example:

Inthebookexample,theCpf_gerenteattributeisanon-keyattributethatdependsontheDnumero(non-key)attributeandtheCPF(key)attribute.

Legend

X:KeyattributeCPF.

Y:Non-keyattributeDnumero.

Z:Non-keyattributeCPF_Gerente.

Thenbynotationwehavetheconceptoftransitivityexplainedinthisway

X->YeY->ZeX->Z.

Translatingwouldlooklikethis:

CPFdeterminesonlyDnumeroorDnumerofunctionallydependsonCPF.

DnumerouniquelydeterminesCPF_GERENTEorCPF_GERENTEfunctionallydependsonDnumero.

AfterfindingalltheDFuniquenesses),seethattheFUNC_DEPtableissplitintwoandthendeletestheoldtransitivedependency.

Sowehavetheschemainsidethenormal3FNform.

Okay,butwhatisthedoubt?

EventhoughIknowtheconceptabove,Icannotunderstandwhyitiscorrecttosaythatinarelationalmodelnormalizedto3FNiscorrect,itstatesthat3FNcontainsonly1:1and1:Nrelationships.>

Forthoseinterestedinviewingtheissue,youcanfind here :

Consider that a relational model normalized to

I. 3FN contains only N: M relationships.

II. 3FN contains only 1: 1 and 1: N relationships.

III. 3FN can not contain functional dependencies between non-key attributes.

IV. 1FN or 2FN can not contain functional dependencies between non-key attributes and nor N: M relationships.

It's correct to say ONLY in

A II, III and IV.

B. II and III.

C I and III.

D. II.

E. I.

modelagem

asked by anonymous 02.11.2017 / 15:45

1 answer

Height of the div is greater than the height of the image. Because? How to read a list of serialized objects saved in a file in java? [duplicate]

score 3 · Accepted Answer

Let's assume that we want to relate which dogs have which owners and vice versa. We have that same person can own several dogs and a dog can have more than one owner. There are also no stray puppies and no dogs.

We could implement in the people table, a field with the ids of all the dogs that it has and in the table of dogs a field with the ids of all its owners. This is possible, but it is a violation of 1FN.

Therefore, to reach 1FN, you must delete the multivalued fields. We will have to create a table that says which are the dogs of each person and one that says which are the owners of each dog. It turns out that these tables are actually only one. So we use a middle table where a M: N relationship between the A and B tables is implemented via a new table < strong> C where we have 1: M relationships between A and C and 1: N between B and C . That is, the M: N relationship was broken in two relationships, a 1: M and 1: N . In the C table, there is no field other than the two foreign keys and both are part of the primary key.

So, if M: N relationships must be decomposed (and therefore deleted) to reach 1FN, then there is no way to persist in reaching 2FN, 3FN, or any other normal form beyond.