13.11 Fourrh Normat Form

(4NF)

|

Fourth Normal Form (4NF) AJthough BCNF removes any anomalies due to functional dependencies, furtrrer research

red to the idenriflcarion of another rype of dependen"y

i-,irltiyalued

(M!D)' which can also cause data-redundancv

ffffiT"ffilf#i1$J,*endencv

dependency "urr"J Gagin, 19?D.;il, section, we briefly and the associaltion rr'rrlip" or dependency with

"r

lUlulti-Valued Dependency

13.11.1

The possible existence,of multi-valued dependencies ia a reration is due to first normar form (lNF), which disarows an aftribure in u rupi" rro* tuuirrg u For exarnpre, if we have two multi-varued attributes io u ,"Ltion, each varue of one ' of the a*ributes wirh every varue of the ott urritut", nu; a *j'r-*nr., of the relation are consistent' This type of constraint ", is referred ' to as a *"rii-"J*o dependency results in data redundancy' consider and the er"n"nsturowner relation ,rr*r, in Figure 13.22(a), which disprays rhe names of members of staff owners (oName) at 11*"r"r

," ;;r*.

owner.

'tr;;T:HT""L,ff

:i[*r*l;;iffi vr qrlu urar DLqr

r"i"ilu*r. ,#",

.""Til#

rne :tassumetrratsiannamelsrual'")unjquery owner name (oName) uniquely identifies eactr

BranchStaffOwner

Figure 13,.22(a) The

BranchStaffOwner Carol Farrel Carol Farrel

relation.

Tina Murphy Tina Murphy

In this exampre, members of staff called Ann Beech and David Ford work at branch 8003, and properry owners cated Carol Farreland rina Murphy are registered at branch 3,ff ""t;ru;:,T,'ffJ;:'#:"5:**'::;T,T'u"t*""n*"1ot"i#,,"*randproperrv or starr *a l."*'i"'J,,,*" that

**,,* ,,"Ill!J[iTilJ:atffi:i"?*:ri*

the multi-valued dependency in the Brancha"it*r", relation. r,' because two independent 1:* relarionrhip;;;

,"ras, a MvD

exists "rrr", ;;p."senred in the Branchsraffowner relarion.

+OZ

404

I

Cnapter 13

I

Normalization

We represent a MVD befween atfributes A, B, and notation:

A-->> A-->>

c in a relation using the following

B C

For example, we specify the MVD in the Branchstafiowner relation shown in Figure I3.22(a) as follows: branchNo branchNo

-)) -))

sName oName

A multi-valued dependency can be further defined as being trivial or nontrivial. A MVD A -->> B in relation R is defined as being trivial if B is a subser of A or ia) (b) n u B = R. A MVD is deflned as being nontrivial if neither (a) nor (b) is satisfied. A trivial MVD does not specify a constraint on a relation, while a nontrivial MVD specify a constrailt.

does

The MVD in the Branchstaffowner relation shown in Figure 13.22(a) is nontrivial as neither condition (a) nor (b) is true for this relation. The Branchstatfowner relation is therefore constrained by the nontrivial MVD to repeat fuples to ensure the relation remains consistent in terms of the relationship between the sName and oName attributes. For example, if we wanted to add a new property owner for branch 8003 we would have to create fwo new tuples' one for each member of staff, to ensure that the relation remains consistent. This is an example of an update anomaly caused by the presence of the nontrivial MVD. Even

though the Branchstaffowner relation is in BCNF, the-relation remains poorly strucfured, due to the data redundancy caused by the presence of the nontrivial V1VO. WL clearly require a stronger form of BCNF that prevents relational structures such as the BranchStaffOwner relation.

13,71,2 Deflnition of Fourth Normal Forrn

Fourth normal form (4NF) is a stronger normal form than BCNF as it prevents relations from contaiaing nontrivial MVDs, and hence data redundancy (Fagin, r977).Thenormalization of BCNF relations to 4NF involves the removal of the MVD from the relation by

placing the attribute(s) in a new relation along with a copy of the determinant(s). For example, the Branchstaffowner relation in Figure IZ.ZZ(4) is not in 4NF because of the presence of the nontrivial MVD. We decompose the BranchStaffowner relation into the BranchStaff and Branchowner relafions, as shown in Figure l3.Z\(b).Both new relations are in 4NF because the Branchstaff relation contains the trivial MVD branchNo -) sName, and the Branchowner relation contains the trivial MVD branchNo -)) oName. Note that the 4NF relafions do not display data redundancy and the potential for update anomalies is removed._For example, to add a new properry owner for branch 8003, we simpry create a single fuple in the BranchOwner relation.

13.12Fitrh Normat Form BranchStaff

BranchOwner

(5NF)

j

Figure 13.22(b) The BranchStaff and BranchOwner 4NF

8003

Ann Beech

B003

Carol Farrel

8003

David Ford

8003

Tina Murphy

For a detailed discussion on 4NF the interested reader Elmasri and Navathe (2000), and Hawryszki ewycz (1991).

relations.

is referred to Date (2000),

Fifth Normat Form (SNF) 1\:'

,: :il:

pn3nev-er we decompose a relation into two relations the resulting relations have the lossless-join pr.op"tty. This properry refers to the fact that we the resulting relations to produce the original relation. However, "-'rqoi' there are cases were there is the requirement to decompose a relation into more than rwo relations. Although rare, these cases are managed by join dependency and fifth normal form (5NF). In ihi?section we briefly describe the lossress-join dependency and the association with 5NF.

Lossless-Join Dependency

In splitting relations by projection, we are very expricit about the method of decomposi_ tion. In particular, we careful to use pro3ections

that can be ,*"rr"o by joining the :re resulting relations, so that the original retation is reconstructed. s;J; decomposition is called a lossless'join (also called a nonloss- or nonadditive-jon) decomposition, because it preserves all the data in the original relation and does not result in the creation of additional spurious tuples. For exampie, Figures 13.22(a) and (b) ,rrolv *ruitrr" decomposition of the Branchstaffowner relation

aOS

into ttre Branchstafi and Branchowner

relations has the lossless-join properry. In other words, the original Branchstaffownur r"lutio., can be recontttu"t"-d by performing a nafural join operati,on on the Branchstaff and Branchowner relations. In this exampre, the originai relaiion is decomposed into t o ,"tutror,r. However, there are cases were we require to perform a lossless-join a""o*po* of a relation into more than two relations (Aho el al-, 1979). These cases are the foius of the lossless-join dependency and fifrh normal form (5NF).

'r3"'t2"4

41O I cr,"p,", 13 I

Normaiization

13"12.2 Definition of Fifth Normal Form Fifth

normal rorm {sf),(also caned 5NF relation has no join depende*y

roject-Q1|,normar form(pJNF)) specifies that a 979). To."u*irr. rrr"i dependency trre n.p",iyrtemsupprier relation ";"r" shown in Fi_aure t3.23(a). p

(F"fi;i

+frnu:,::'t:'r:H:r**ole. whichare,,rrd;;; jff ",f :i.T;"J:R;"r"ETiJrT#u;ml

j*:lf"d;g

;?:6ffi1,f#I;;Yri\::":i#i*l9s{;;;i'*r,i."nn1ies,ha,i,"*ii ft *1 ,,pi1y,r*.reqldrcd

n"* r,r !'r "r,"of an item (irembescriptiori description

i" o.'"i'Jil,'?offfifflJ*g|,rTffi:1f"1*

"ttrq"Jry

""I:ii:ilitt"ffJ#

idenrifies each rype of item.

|,fu!"T:T|-' ""r"i'"J"rgItemsupprie,,etatio,' in Figure r3.23(a),

Property PG4 requires Bed 52 supplies properry pG4 fupplier Supplier 52 provides Bed

Then srppti* si ir"rto*

Bed for properry

:ff;Tffi*'::r:,*,^?_?1":1,1T1? ".lf:

pG4

(from dara in tupte 1) (from data :rrrtuple2) (from data in tuple 3)

constrainr on the properryrtemsupprier

:?fr:"?,$:*:,:::',,::il:i:'oi"'"*n"*il"1e-c+;""T,"J,Lfr ,HJ'ff

r;t-.:i::i;

:d:';jlTJ"X;i?::iT ;i3:fl ffi#'il::H:::*::1*xrt''-;*L;i,fr we'"r,rr"t ar, il;ffi#f,J:'j"1fr;:ffi"nfi#,1

rvpe orupdare anomarv and

Figure 13.23 (a) lliegal state for Propertyltemsupplier

(a) Prop€rtyltemsupplier (lllegal state)

relation and

(b) Iegat state for Propertyltemsupplier relation.

(b) Propertyttemsupptier (Legat state)

chapter summary Propertyltem

ItemSupplier

PropertySupplier

| aU

Figure 13.24 Propertyltem,

PG4

Bed

PG4

Chair

Chair

S2

PG16

Bed

Bed

52

ry

,. )),

1)

]I a

As the

Bed

S1

Si

Itemsupplier, and PropertySupplier

PG4

5Z

5NF relations.

PGI5

s2

PG4

relation contains a join dependency, it is therefore not (5NF). To remove *re joia depenaency, we decompose the Propertyltemsupprier relation into three 5NF relations, nam;ly Rropu',tytt", (Rl), ltemsupprier (R2), and Propertysupplier (R:) rerations, as shown in Figure 13'.24. we'ruy trrui trr" Propeftyrtemsupprier relation with the form (A, B, c) satisfies the join dependency JD Propertyltemsupplier

in fifth nonnal form

(R1(A, B), R2(8, C), R3(A, C)). ),

Ir is important to note that performing a nalural join on any fwo relations will produce spurious tuples; however, performing the join on all three will recreate the original PropertyliemSuppl ier relation. For a detailed discussion on 5NF the interested reader is referred to Date (2000), Elmasri and Navathe (2000), and Hawryszkiewycz (1991).