r mal'zafi0n

Mos ma/,raton o Dala basae

Datnsa&e, Nornali 2ahon u a fechnigue 7 eganiz9

thedala n the dalabade Alotmalrcahon e a

Kysennatrc approach s7 decompok /pp tat Les

unolyak
eliminala olata edun olony a
auod deletren
luKe Inteltton, updale
chatatteiet'e pDCesThot pul
It u a mulAsep
anams/es. luptr calrcs
catid
e mo
emevyy dypli
date ula takuda em by
bles
data wm the e l a m

mainly tiso puup E

ueeod
m al=attor
Ludeleos) a t a t a
Enminatn
uedunotan t
H
k nd
en di le
l
ke
mab e
oleper olenua
ma

Ensuin/Aata
leea
ata ta epea
Normaliaatrom
rblem o hoct
l
Momalizato t becomas dlluult
Ohoutt
olalalaze ,
orheu
hande anol updale the
loas Ynston, Vpdatton and Deletro
ala la
a c
alabad k
hnamd oe vey epunbt
o t Mrmaliz&4

7
 SAAdest
Sol SAVame
Alorola
yo Adam

o lex Janpal Matha

o3 Stualt Jamm Maths

yoy dam Morda hy&i

tuden los le

% updo-l 2ddrcs 5/ a
Upclattern namoy
Sudent i o ceche twice o r mre h a r wia
Sncltes4
a-to bG. e have updol
e/ee
olala L
tottlI
l l2ece
e ceme
Csumn ll Lora ,
in Concittent

Insehón nans y suppoLe pranes

admiss ton oeRave a uotlent 1c/CS IA),
0 ame ancdk adlrt tuoden, but
Sucent ha nst pted or any éulyets get
then Ase Kane n tu AIULL fert, headippts
Tnselthm AnamTy

nam1y Sl yo fat my
(3)Zelet
ana tahp o ragily Ae diops , (
00e ubfelt
Ahen l e l e t e 7hatko0/ etiie tuolent
erd &otl be deleted aloot ort t .
 oal'z at o ule
Noxmalizat o tnlle aue. ne lode1nls Monp
mal om

Y s / r maf m
) e Com d Womal 1erm
Thsod Nomal A>xm

Cy) CNA

frst NoTm al stm (INP)

no u o o
As per isst Narmal a m
in tmatto
alata must Cota in epeatp p 7
CsTumn mast i a e a unig u e
te each ket S
Cann&t s e
value, suth AAal mu/tipte Cumnx
used & JetehKe Same f o
Ca ch tse tabe Shrdd ocganiz ecd InbAAR,
be

an d tah lao Shauld haue a plimaly hey h a t

oluhp nishu
a i g ue

d e r ebe

tu duntt
Age kubett
clam
Aex Bsto/y Matk
Math
Sugst 17
Mats
 ahore
Theola0n os nat m
D
, Vany m u o t nSt Lae zhumn
In LhrcA thore han one CslumnCan be
mino
- Ceoate a
Sil plu maly key.
Ke
tuden Age Susyet
Adam
Prete/y
Adex Matl
Maths
alE
dam Maths

VLm NE data Aeldun olany LAleaßey a/ KD

toU be many cetumnt ni+A/ Same data in multrpl
Oo b u each R o as a LeAote wtll be nigue.

S e con d X/ormal torm (2NF)

ume aribule An allhibul which a pamtf
he plme -k ey Cprurnas y ke
Nen-Kumt atlibulí An altlu b l o lsh1 h io
nst a
pat h e p9maiy key
erely nMPIme attiisula shnutd
be tully dpindentsm mme key attukalb
UFunckonally
 Ie X er he euUol Le (8
&uset -
an
aoAetale nd

Shlden stet laslo

Stu SHulamsP Aoume

h a t the P
n sudent-Psy7elt
I, Tektto
AcLrd 1np
key attubulis aië Shud-PD and fo.
-key atmbulo Stu-Nami
h e Hule, nsw

hurt se dependlenupm
and rMome
arbul
n t On 5 e p7me ey
bot% and any

individua lly
t he Uind tAa Su-lame can be lolentrfred
anod -Name Can be iolented
SudD
Th alled
by 77-ID alepen odent
nst allened 1
whic w
Patial olepe
olpen l n y ,

Sudon
Shhd SD ShulName kfo
Pye Pap) Alame
 So Thel e col na paltlal leptndeny
7hrrdA loY mal em (3P
er
er relattom be rinThin l Aldrmal m , |
L mul be 2/A anala Satsy h e t-llcosin

Alon eprme ah but uuansitively

dependent 8)
pmmay key tlhrLtulE
() 7 ày nsn toval Junthonal lepenolny
X-A hen ellke X ua kupy hel
C A Pme at) bule

SucdentDetacl
StudID Stu-/a me

Stu d-D Z2ip

tsant h cpenaleny
Crty SoMele
So e e ey utr
ey átt

udent-Deal
Sud ID ShSuMome Zip
zipCoels
Zip
 yCe Codd Norma/ . t

BCN i an e MJene1 3NA OnSt teqms

CA Jalus hat
t any non. Aoval Junctrenal clepen deny YA
Ymust(/6e a Supen Rey
1n Ue C e l a t i n Shudin
Stuc/ID u e [Link] Key tae elattm

R / Supy key 1 thA earr

Detat. and zip s
i p Cole So

SudID uN/ame zp

an
2ip üey
n 2enp
hch Confanp Sat Reht on
Ler

tees n o t hoaive mulnple

3NAf abl hch
Oandrdali key u Sais o 6 e BcNA
Oneylapp194

Conoler th-e follsorp e/atton3 hip.

RCo,B,c.D)
and 7ol/on alepen olen Ces

bc AD
D B
abrre Lelatrnship u d aliw ad in Bvt
 key a AAC
ten ce he unchonal olepenaleny ,A-sD

s T e corl e étronL
e luypeA Ry
B D s o a key

but h ,D B,D ie net a key

e/attons hiyp A /nls

hence<
hence e btexk r

KL and K2.
uod e latum-sh1p

Rla, B,C,D)

RA,C, D) RD, 8
 Boyce-Codd Neomal tom (BCNP)
Boyee-Coold rvmal fvm CBC NE) basrd on untroral:
take n t o aceount all Canol'cdale keye
Xpentencras hat
in a Aehhion. a Ae lahon t a b ) wirt only ene

Candiolate key, Thrd lar mal form C3e) anot

BCNS aie egu/valent

dtizm1nant
evey
i in BCNF a nd only
Aselathon

te a can di'dake Key ure toden tity

all
1he

BCNF ,
}s in claoti'cdaL
e l a l i on ase
Tonhe7he a t h a t hey
une
and make
delimnant

Keys. s Z ubulzs e
ellu1bulzs on
n

6utsS s r a gtoup
attir6
defeumin a n t 2/ an
u/yaodinally
alyfuo
oleper dent
dionally olperdnt
attiusule
AShich Some 07hu
ane/ BcN
Normal o
belieen thuad
TAe chosou dtpendney AS9
7or fn chonal
cdpendeney nn
a
sKat m ependenuy
allonat 7hes
thie
nomal Frm /and n' s
the atiitul
pmay ky,
ulatron B',i a / n s idá
That r
BtNf
mta Canodidale key , eeal4 muat be a
elatrn, A
cehna/u ln a
hu depenoleny b
Candidat Key. fom the
Thra

B c is a tonge in BCNA
Thessfere h a t v y guelateoin
monmalGom
,Suh
Momaljemm
h a thrrod Dot reeesailu
r eersaily
a ko n ii in
n N
i not

felatron
a
7henefre
 tine
tie
h i ch Con rtnt
Contint
C Shch
NTERVIEn)
Cons'dt a l a trov inteyeiof 7
for
7 aand
n da l t
he aMa ements
delnilev/
compa"y
he nheese lechnital expe a
by
/AIT EEVIEI 7able

INT71ME |TNTVA-ID|KoOMNO
CANDID TE 1/P TNLDAT6
a 1? 19| lo:90
OO ROO
4-Ma-1199
EO01 ROo1
Co0 GO02 Ro0z
Coo3

4-Mey-1999 lo!3
RO02
CO0y A6 May1999| EOO3
1230

has aee compon t&

candidalë key
The bove table
a
The Crmmon auisale INtp9TE
Shch Oemlap by Shouny

CANDIDATE-1D anel LNT DI7E

d INTVR-TD, INTDATE and LANT-77ME
3KooM-NO , TNT-DATE anol I/7T7ME
w make Thi Cmpea tt Key (CANDIo ATE-ID, INT DA7E)
as the pmmay key yAs Ke Melalron has tlhe ollron
tunctienal dihndeneus
DATEZ RINT-TIME, TNTVK_ID
LI A NDIDATE ID, INZ
Ro0Ato Pnmary
7 I T VR- PD INT DATE, INTTINE3
,
CANDIDATE -TD
Candi'olala key
a R00MLAto, NT DATE,
INT_Ime ZNTIVeTD
CANDIDATE-TD 5
YTNJVR-PD, TNT DATES carstr ola tt key
KooNNO
 he/»c} hre e olpendenc ree ae all candiolati
re T naS
thi ke laleim anl oillm6 E Ca uot
any pmblome 7or 70
Sela lion.7he cmly fne tromal dpen oden ty TNTVR-KD
INT-DATE 3-> KooM_NO
ven thoufh tKe
INTVR-ID,TIT. DATEts n87 a tand1ela
Key ferh Se/atron, this functtonal dpendny u altoved
SN, hecauae KocONNO i a pomay koy atabeeli br
e part 7-the candidat kof)- kooNAlo, INTDATE
and N7 77ME. h e t e ae ne patil er tsantitue
ependentuus C)the mmay koy CAND IDATE -1D anad
JNT DATE ) and Snie Al unctromal lipenden ty
ZNT Vk-ID , INTDATE Z kooNAlo allaed he
selalten 3 NE he

nD In BCNP (a Ibrnge,. F
7ha Selation it
e
presence &the lTNTVRI D,
(TNTVRID
hnel Nomal fwm),
du s
is s nbta cardidali ke
, h
D A 7 6 2 eletmin
an

Fo thefelatien ants i h a oclatten

diliymin
T l a t a l l Ke
BCNE Keg u n a y
muwt be
Canlrlate key r K e Kelaltom
a

TERVIEO AL/dton may Kufper

u
he Censepen tu fhe

m upelatz a na mbliea, BCNFE n

Ltelalcon
INTERVNEN
So bancfmo he
lepcndin Cu
m u t Aemere th 's/a7i uncTronal
a nak
neuv Ke
laTtons INTERVEI
Auuo
By Aaaf
ooM
 INTERV/etiTABLE

CANDIDATE-ID|-INT 1E /NZTIME
INTVR_JD
AU-May-/999| l0:30
CO02 I1:30 E oD
du-Nay1977|
Coo2 /:30 O02
u-Nay-1999
Cooy 30 E 003
&-May-/99?|

Roo asle

IA/TVR-ID TDATE RooMA/o

E O01 24-Ma-1999
ROo2
EO0 y-May-1999|
R O02
E003
26-May-1199
 ORmntiT A1On
coNIINUE7
Definrtronof Decompexi ton
latron chumas
dtla fr ón Ceheoa , h set t/
be a
let
RI, R2 hol 2 a oleconmpoaihon bKY
R-Rt RI (O R3 n
Kn

ecch ha a susset R Cfu i'= 1, n)

Exampk o De conmpotron

Le duseil
o Kx,y,z) thu Can
elation
RLCy) and Raly)
union Ri ond K
ne Re, wejet
R- ave
4oals Deconpodth en
ole comp oup a tealion h o
Eho1n alc kedun olane by

Sevasal Aelahone inlthghy

a
mermal m
T t t m p o a n t tS check luata de compeein
olot& nö Jead t bad ddpn.

Prvb lem o i Decompailtron

e latroN, Lema
h t may

Gen notan ce o6he checompadtd

the Corespendmp
mtbe a s t kreb Jjeconstu i
Aelatren latomalionb i
InoTan ce the i a l
 loblom with Decom1podton

E Xanmpe

ModelMavne uice Catcgo

lov Can
Nkon
S20
Canno

Motel NWame
Cab cah
Cangn Canon
20 Nikm 2o9 Nikm
Canun Canom

nm Ri&R2 Inetead5 setro nal

Le la
on fre pet
RI UR

p odel ame
CaA
Cans
Can)
S20 MROn
Can om
ISD Can
 Decompetion
A unctronal drtmpevttren u 4he p rocen b 7
d o n At punctns a n faniZetren n u brupemnd

dilall
Jealt ine and ne ) Vltvels f
Cmeinctro letcubed h g e
n deconposhon, Junctrbmt.
uppcahop
Set &7 thw
detail by a
RConoat
K lonovab
kefation dchinma
h e econpesre on a tw
o mrt
ke 1477o
&chema by R
7he eaTron taltiibuli
epla cio1 a
cu4sez
7hatenth Conta
Schena vh K
ateisuts
include all
and togethuu
im eliminatih
Serme sf the pribme
De compostt on helps inCongielintiet
Sueh as rediinflh ney ,

a d clelipn
anol andslie4.

D e coMpeut on

Deconpeit on.
7ypes&7
hal ate two

D e c o n p o a t r om

LosslessJ0in Decompoit
-16n
ess Detmpoirhon

Lasy DecmpoSHren

Decompoiihon,
L8) Lae leltjonn
 (A) DCeY LDe conmpositt¢)
Ki and f z
2e /atrom n
he cecerm) prekfrom yreld
7he lon,

d o c nit
P2
e the joim R and

ada ntape adecommpeort in b

0
lawo of mm

One dsadrvo otage ef decmpbsrtron

he
lome 1frma7on
tnslu) Hat
Alatrinad,Aehemae ( tabd
iginalefatrén
or

lest
chuuif peieal 67
L L t t hoee
Comtidethat hwehave l a s l
STUD ENT
attubulis sallno sn ame and depatmenE

STUDENT

Rotl-Alo Snam Dept

paumatE CoMPUTER
paAumal ELECTRI CAL

i n i wofelaltom
Thes gel/ati en it deconp eNLd no name

and namedept.
Na name Name dpE
RstLNo Sname Dept

Patima
patiml ConnpUTER|
payma ECTRcol
22 Pasimal
lossy leconp osrion, Spui'o us lup lo ale ptnatatid
a u a l om A ) i applhed h e Retate
tn he cle
comfo H on

STU-TInlED

KolLato Cname Dept

paLImal CoMPUTER

Pauuma
ELECTRCnL

CoMPUTER
P a L p m a

22
pauimal EECTRicaL

222
r

a bad ehe conpeertren

esitn 2!
decom1p
7he above
Lossy elecempDihon

OSIT70
2DECoHP
Lss JoIN
LOss 1n& Ri a n d
Re
Tioin K
»e Hr en sAela he Same
s ame

ca The ce cernap and A2 r e l d

7KI
when the_join
loss esc
rmort

latrén s in R". n s two

conpoeLd
t a p l y de
Can
Clieypne
A elaton al 7 h a tthi
uch a cay kosl
tosls, jn h e or)gnal
Amallu
Cnkhe loR
7hi ppecwe Callucl
Cpt u
aeconnpoled
pe{[Link]
thi
21400
Tejon d t conpedHron
detompelutron.
nn-adolctnne
af
Thu is alto seletrel
feted
 The lots lezc, a l e ae of
/o ole con1p pdr.
At
Aeupe ct lo Alpenden ct e

STUDENT
Rs/LNo Sname Dept

Pauma CoMPUTER
2 2 Pcima
ElECTKICAL

Thic (elatren 'a

e cenapored mbvo eatron &tu
and tudep

Stunome SHde2
RstLato Sname Dept
RotLAto
PatumalV CoMP OTER

222 Patmall 222 CLECTRICAL

Ahen tivor wv tobles Che atrons ) aefomed enhe

ommn Column A6lLno's the Acaubtant elathon w l

Lspk lke Sujo ined,

Stu Joinsd
RILAto Snam Dept
Pauma CompUTER
222
priumal ELECTRICAL
 Dependeny Phes etvins Decomp oihon (4)
he depende ny p(eouvatie de tonnpoarti in "
anb/huPPy
7Tmmpolid Ae la tron al databaae sohema D n hlh
each
inchnal pendentyXY specihidm
eithu
ppe atead diecy t one o7 7he elatron Aehamas
The
h the de contperled/D or cs ld Le mlenedpn
epenolen cies that aPpeat m Some k

Detemp es/tm D» k,Rz.R,, n3 R*Aa10 2

be de penclenty Ledetvrn w i t s e e p e c t 6 E 7 h e
untem tKe pisjcetl 8 f en ea h ket Ki, im Dis
uvalent i f.
n otheu werd,, Kejoin f R oe X. The
Mewd becauai Each alpendenty
olependencres au
Cmstant on
+he elaTaban , 2L
4pptosn a

ependen pKeOLLVI12 Kom

ole cmp oditron nat
lnt 7 h deconypouh in
olependeny 'io

Xample and Bet FR -LA > 8

a Aelatron RA,B,c,D)
Le
C

elelron R i cdecompeesd n
A-LA»B, Asc? a n e
wits PB
Ki = (A,B, )

2 Cc,D)
f'-U Sn1B, 9->C,C-D?

So -E
Ard SO
 Thuu, 7 hu clecommpostron 2s Aepenolenty eduv
CeComp e0 |-A orn.

*Inclusron depentlen Ciet

cAusr ckep enden cy hott callld as ND
au The oepenctercit(drh ch ekilh uhen dome

columns Jalcèn ae Con tained ei othet eslumnilutual

8a setond chupa )
Selaton

The exampl of tnclusien clependerey ua r r

C E
Comliat er
xeleertal ke Tnteptiy comiiamt
tate that tie AeJenn cslumnly h one elatren
must be Con tarud i/ 7he prmay koy csurnn)

theeferencnd zelatron
7asla)
ENo Name Age Dn -fol7
Ank Dro y
as

2 Sncht allatd

Mat
as t
Som v 22 n t
dyhned
2
tast
Souabh| 19 piimay
kuy Dno
u
tLe1,
and
a rer k
Ke latrens haup
Dlocati on
Dno
hrtek
Bhio ant
im
12
Han st
 7
66bjectine sincfuson lependentils (

70 rmaalite uwo fypct f ineue attonale onliain

Cohich Cannd PD MVD
e expsned ud or

Kepertal mtepuity Con&hanls

Clata/suscla se \ke latronthipe

te'nsbic
nclus/m depenclentits au mosty ky baotd
tolve only keyc. kefuantil Irtlif or Fry
hchut on
C onoZiats de geod eranpl f t-baaLd

dpenden ciuo
1b vofve 7sA hierathie
dstao )
hat
An EkD CER nolentte.
h c l u s / e n ope
key bases
alco load o
Lassd thao
e n cies a Key
depenol
atlu bul guy
mdulon
all
I abrt Spli
t Kasely hae b10ory Ant
decempoachont
patieipalb
tn hclua
hat

usualy do n s t Splet thu pumay ky.

plittap
& BCNP abways vote
thvrotre Aplithnp

Yeng formn 3Nt enei hR

nst the oum ay ky
&cne key hepepully X-JA
Jom
,

endensywlap
ht pat
tp
hee i þaui
 O n DepenclenyCTD
Zbe abitkaty
letR be a stetrin, Vet A B
Ksthifes 7he ID
Sussetu sR& attibutr.

*(D,B 7) 7 prpjéetronc

and onlyi Ruefual b he/on

n A ,8. Z ,
ko)pefhid o
Bin dlependony D (RR2.
.

AelatronSchmd , 2 a ival VD, one e7h

latron Schmas i n T CAI, A2
n) fuat

teRN Nokmnl
FiPTH NoRMAL foRM (PRoJECT TIN
A ulaTion Klatiahu Tht / nermal prm( sNE)
ty
fnpectjoin Nermal t m tPI NE) Y preny
nen-

ivialon depeneliny "[R,. K.. kn7 » e Ke *a

ypetky 7 i oginl aulaticin

8 ' a a /eel dalakase Nlemalizaltin dapned &

cdue e dun Nanty dn &hhional aA7a bates jiccordn
mula lud Gh ed dy ws/atny Kemarticaly ulst
muthple lahonshj
7abl Aard b be n SNE Yand enly if/ twy
Jen dapenoleny n t t implies5y 7he bandielati

(a
( Cannst Kane a l0&sles de conpasyho
 Inlo ary numbet rna tles, tatlas 6

The 7oste poeditat u:produ et f s

he ype
7he b t a n d
deifnalid ty hodut ye made by

eipnalid Ay and a aatloslh rem thu

a n b l o p kalemao,

Zavdlr aleman dojnaTed

Tavello
alum&
Band
A cme Vatcu clea nel
Jack Breadbo*
ack Acme
Resusto hunnosheal
wily Vacun hart
Rebusto5
weldy Rsbusto Buadbe
wlly Kobae
mbiella

loll VaCu ckare

loud Rob
Rsbusth 7edesea7oe
Loulh Vaccum clron
A ch
Louis
Acm lava la np
Lous

Louu's m b u 7 e hac

hu
lZn his
n his ease ta Lle pht n

T falman , hodue gpe

1) h a e l i j
Band
Bsand
)) MavelNng LalomaO

Ban o
oodcttt y
 PAodatt
TeduipBnd
ald md
Saleshan
Jack Home
Ja ck aree
Clean e

Jack BcadBx twillykobusts

wnlly enipShea Lo'uisSLuala

Vaceumcltantu
N, Lours
Acme
oiley Lout Nmbus
Umbenlla
wil

Lous
Teledcope
Loua
Vaceu
Cleane

Lous lava lampP

T Rack
Loun

B4anol hy hawla
hoduct 7ype by Taauelay balesm4

alman
Branol hoduct pe
Vaccum cle~ne
Acme BusdBex

Acme
Lava lop
Acme
fuunyn? sheau
esus to Vacum cleanu
Rsbrutts
bendbox
Asbusts
Unbentla
Robuols

Rohasts Teles ccpe

The Kack foductee

mbl and
Let us finally summarize the normalization
steps we have discussed so far.

Input Transformation
Relation Output
All Relations Eliminate Relation
variable length record. Remove
lines in table. multi-attribute 1NF
INF Remove dependency of
Relation attribute key. non-key attributes on part of a multi-2NF
2NF Remove dependency of non-key attributes on other non-key3NNF
attributes.
3NF Remove dependency of an attribute of a multi attribute BCNF
attribute of another (overlapping) multi-attribute key
on an
BCNF key.
Remove more than one independent multi-valued 4NF
dependency from relation by splitting relation.
4NF Add one relation
relating attributes with multi-valued5NF
dependency.