Scribd
Upload
3 views33 pages

Normalization

Normalisation in DBMS

Uploaded by

shaniyanajeem002
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
3 views33 pages

Normalization

Normalisation in DBMS

Uploaded by

shaniyanajeem002
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% 06.2023 Normalization THuespay * Fumchonal dependency (FD) 4 FD denoted by xX oY, bebueon too set 9 attrrbutes X amd Y that ave subsels of R, specifies a Comsbarnt an che possible tuples. The constraint iw chat, for sony tuo tuples t, amd t, om Y dhat hane t, Exar ty Cx, dheg mast also have €,(7] = 62L71. %: velaton site im R, Pe Ye x “determmes” 4 Yaa X 4,8 CR, Xaml® - Legal velahon states: Relahen extensrons "(RJ dbat Satishy the ED constraints ave called legal oxctersions, Qe. Noymalizahon of data com be comidered 2 process A amaly Zine 2 Gren relahon’ scheme based wm their FD amd prmary keys fo achieve clesivcble properties 4+ Minimizing vedumdan ug, ih Minimizing mserhon, delehon and upelahon amemalia The rormal form indicates. che degree f whih a relahon has been normalized: ; Deneymalizahon ia the process oF Strung “the jon of higher reovmal form velahons 45 & base relation, whi % mm lower normal form: Soper bey: An attabute/ set a atdbute eshose closive cram all attrbutes in dhe relahon, Comdudate bey : Munumal superkey , hese proper subsels (key) ave mot Super Keys. He we have N etlrbates vith (nd CE, the number 2 Posble LoperkeyS gn! FD note: fl Single ll no, covvesponds by one Student only. ( | But tuo students having dhe same name cam have | different ell numbers, Ve ey But, dheve mupht exut dhe some t, 10 Sam ell no. wy tuo cuPRoremt classes” £4) Smith velerving fo two dicberent i ss, Students. Thus d cont be am 2 FD. Pot (xD 2 b 0x1 they £03 = &.079 Types 9 FD ® Trivia) x—4, Yex 85: a? % , Cre, name ) — Nrame OW Non dona X24, xaYeg ‘ : | Nelms is commen im x ond Y, £5: [Link] —> name Aga. Y. &x Semi dowral bs “€5: (Bro, name) — Gene, mark) Armstrong's axvoms 1 onihoried rile: tr Reflexivity ieee ey x SE roe Bmeemecewity Poy amd Ye then x 92 3. Fugmemtahon If x4 hon AX AY 4, Union WX 34 and x2 thn * IZ 5. Decormpasition 18 »_,yz dhen x34 amd XZ . Compasition EF x4 and AB then AX —> BY Banaeterstiwity 1p 5 Sy 2.8 Goh atthe, xz— 4 Attribute cleswre / Close sek Detevowned based on cthe £[Link] applying samstrong's avions. ' 2(A,8,c,p,€) Fo: 198 Be cp, p> EF Clome Q A, At PERE) rebel Reeracccat | Bre facpe} apt-fapece t A,B, c,d, €) 1f{p—8, pel Povme attributes: Attrbuteo that Are part a Ck "RA, BY Cc, BD FD: £4 8,6 40 capa} ABcot = $ n,B, c,D} GE) Ai tttobut er are consid at Past. Acot= f 4,8, ¢,03 ® Ais ay Apt = 1A, 8, c,d} @® ee LA} end {pp areinak, Yess shaft “2 AD uw 2a condelate key, ——O Now check iP eny pp i prernt at RHE 7 y 4 the FOS. yes, veplace kurt Lus 2 dhe FD» dhe Ck. A and D ae PA. Ce,%) cP Au replaced Gy C AD = ch ED is dePmibely 2 Sk, bet Check bel combats EP ths Ck bcd and JD] are rot Ske. y £2 4 2 op 6 Boc ew ie ck [eh BSD} xe mot sks ~ bp we ck—O es es Cks ave AD, BD md CD Geps: + Comer all attributes » Reduce by evaluahns FDs eCheck hoy Cks. «check of PAs are present at PHS 4 FDs : ding + ifyeveplace that attmbule eth LH! 4 the Cores ponding FD and check if the obtamed St ack, 4 RB (a,B,6,0) FD: [48 cD, p> B, CA} - Find all Gb: pecot = §A,B, 6)d3 SE Apt - § 9,B,6,03 fag—cp) £03 amd (B3 are not Sks. : Agu xc ck —O DS ithe tibet DY ByDAC “D> B te ADril-< SE. [A] and {D] are not Sks pp o« ck —®@ an? we cge le vas Sh {oJ amd {BJ ave rot Sts ¢, CDE, Ea FJ .pRu e« Ch —O fas : A, B D—>4 : bB wv 2 SE {D} snd £8} ave rot sks ae ue NDR & « CEO & in eye On ae q ye ace cota cae : . Co x Veen sk a Mize etdnbubes + DAE: GeoB 3 CD a mesh: x {CJ amd {Dp} are | Ope [Link] F . as aed .1 Chs cre AB, BD, Rv GD 4 co ‘ 049. 06.2 1S lormal form, : 023 FRIDAY ol x Contam alomic values . Bown + Disallow mulbveliel en compote attrbubes * Duallow relehons wid velahon or relahont as atorbute vakes in tuples eEeh Column Should contam value From «he same domain . Exch column shold have umgue name. « No ovdevens te vans and columns + Neo dupleate rows, Bere eed cchena carvers ; un Line by delat BP Normal form ole Should be im INF ay * No parhal dependency « (roger subset 9 Ck —> ron- PA ) or PA 3 NPA Full famchonal_dependeniy AFD X34 & said fy be a full flamchonal dependency, if vemoval % ary athrbute A fom xX vil) break the depends Ne, Jxa] doesnt fume honally determmes y Pavha\ funchonal dependenuy ” AFD X —>¥ 9 & parhal depomdency if some sthrbule AEX cam be vemovel amd dhe FD thi} holds, E8/-6L1—> Mi oo A yelahen schema R ws m 2NF ip Nore non- prime attrbute A om R is “fully fuenchonally dependent on dhe Povmary ed 4 R. - R(A,B,c, 0, €,6) £D: JAB, B->e, cp, p_se] RPecDER => AF Ge) fal 14 Le} ave not Ges. Be Arta kh CE fon] Fare not present at RHS 9 FDs. wt There ws only me Ck. Pa: 4,F pon- PA : B,C, a 7) Bo Aw & proper subsel oy Ck and B uv a ron-PA. “1e & @ parhal dependenuy . “Rw notin 2ne. 22 (4,6c,0) FD. SAB — cd, cn, D8} ABCD > AB a) PB ez ck —O PA: A, B,C,D cna a CORT (ae SE ct=ca Bt-B “Buecker —@ 8B Ape « SF Atza G AB,c &D are PAS. ae le, There w no non- Pfr pe tie Weare Rut necliteP Bhieck | re » RC A/8,c,0) FD: faA—4B, Boe, C04 Mie) ce) PF) vy cy Sls A is « Ck. There & no proper subsets avaiable Pov the w No need be check fy Zale cee No need fo check for ONF if: «CE & & Sungle attrbute. + No non-fA ave present oy, cob Ghudent sp tedt Sd \Sname | DOB | Sinte /\ Coun | PIN, | total credits 1 A S He IN 250 | - e \% - |ine IA) 250! = 3 c - : He IN 260! LS 4 0 1a 263 = | Attnbute ez &. Setiple ch ; Move than | compaite ck The sue U comed by FD 9 type; hon-PA —> non-A elt shold be m 2NF * No bramchve depend envy for (Alea —> AlPA) rn: PAs, Tramakwe dependency IP dheve dhe 2 FD X94 im R whith holds xz omg =—>} bora sek a Atnbutes Z dhat nedher & ck nor cs propery subset. oe AaB ee sBqrc. exits i he eS + 4 NPA NPA ys hon-t, Pesinieieivens en NE ier gen ae, FD x sy holds m R , evther OX we Sb oR 4 4 8 pA a2 ' R@Bc,p) FD: (26, B24, cp) ABCD <> # uu 2 Camdidote toy. Frime attribute : A a AB iA io Shae ee Cara —» alPA) (Tramsebve dependency es (wpa alra)d Ctramshue dependency) + Bw rot m nt. 2. B(A,B,C,0,EF) FD. £48 —> coer, BD +f} AecbEr E>? AB f Pome attributes: A sad B ¥_ 48 — coer LHS & oe EHO ‘@ —>F Notun 3nle (N(R Alen) a Bp oF io in ene, 2NF he update amomalies, 8. 2(A,8,6,/D,€,6).chak y 2» vn 2NF or SNF ares ABcOEF > AEF » e ck ——O PAs: A,€,F,D,c,8 D->A : DEF wa SE. pt = baAgC Tae © ee DEFY e ck —@ G—>) +: Cee » 25k Cie GDIAe PeCc Oram aie —®© B-o3c : Ber w @ Sk gt = BCDA Sper wa CE —@ Heve al) che atnbulen im R are prme Atprbutes . 1. Ria im 2NF amd BNE Boyce coded Novenal form (acale) A stock version Q SNF- E.F. Codd : dather g PEMs lb shot be mm 3NF X must be «Sk hy every pon-dowal £D X 4. Avvelahen schema & 1m BONE iff whenever 2 ron-dovvcal funchonal depemden uy KA holds m2, Rive superkey o R &(p,8,c) FD: $A—9B B—9e,c 14] amd the Iagheat revel fr Bc PH be ck -—O CSA 2 ¢ iw Bere Bes eae) CE ee) Pers. Ay Sy C “Riv iy 2NE and 3ne Check hy BenF. AB + Go Rap Bc + Ba @ ck cma ZC by ouen Bhs 2 tlle ct! ere WORT Sua om BONE, EN a a) FO: Ja — BLED BC—> ACE, p+ €] Agcper a? APY «a Ck. Tl capes he: io TE TI NE GILT ANTS ge REE ee ppp: A BC —» ACE => BC —>A Ccb a she at- {3 ete fc} mec 4 ax ck -—(2) Alew, PA : A,B,C f > BLED 2 A—s Bc (Resulls m 4) Check fer cthe bagheot neymal form (BcnF ) h—> bode WAAR on ack Beo—> AcE 2 BC @ Sk o> & Dt-$p€} D0 w not 2 sh. =) 2 w& not Ww BAF . Now check dhe faded =D Por SNF (Others wil be oy af alveady ) Doe D wmta SE and E amt 2PA. <2 w not mw 3nF, Now chee Y fr 2alF: pre aot « proper Subset % IGE 2. B(A,B,C, 0, ED FO: $ ABs coe, DAY ABCDE <> AB wa cl. PAL A,B D51A) Sub, BD Re cance whe 61.8.) AB—> COE <> AB—>DdD_ CAlreacly found J cheek for BNF AB —> cde PB & 2 SE. D—>A4 D ont 2 Sk, Ub hot» BCA check fy Bale D—A fio PA. Ro Mm INF weve NG Alen fy highest nevmal form » Venn deagyaro Ry normal forms: HP all Cles are single Cks are Semple (Sint) Ben — a 2 Se ee Ge ae 3. R (ABCDEFGH) FO: (ABc—> DE, E22 GH, HG, G—>H, ABCD EF ) ABCDEFGH <> ABCFOH << psee 5 neck*= { »,B,c,F,D, €, G,H} ABC Ba Supey key Totheck if Us « Ch, ha} dhe atinbutes mn the Mat- Ta} FS] 4 rps. RHE Bre fa] je, DEGHE RG aD ce fe) ae “bbc vw 2 Ck. athrpules Mist Be prewnt ig CE... ABE 2 4°Cr - knee A,B and C ave rot present Ab FHR D FD: dheve wo mo other CEs. Pa: A,8,¢ NPA DE, F1G,H } Check for BONE : mee OO Pang wsct OS € — GH 2 It is rot m BCNF Bee hy ors — OH wo nt INE for 2NF: Gm H We npcb—» er \~ (th m BcNnF) Welnlen ahena w im 2F- The gwen “.2(A, b,c, DD . Fe:f{ae—ocp,ac+ Bp, Be 0} ABCD <> AB va Ck —O Ac—>B . AC 2.4 SE. ace ch —® PA 4,8,C FD Person '. AB>cp AB wee SE 2. Ac BD Ac w & SE 3. Bc—sD + Bc w not a sk + pv mot 2 PD * BC wo 2 Proper Subsek Ck ks Cor ec é acoee . BCH womidered 2 Proper subset 9 Ck Subset g wren 9 Conclusion BCNF @cNe Not m 8CNE Alot om anF mot in 2A) Cks is consdeved A proper wubsek Q comdidlake key - 2 ow INE 5 Rlascpe ) F: {AB —cDE, D— BE] PBcDE <> AB & dh ck-—O v—8 . AD w @ sk Ap w 4&4 Ck —® mvp: AL B,D Fp Reason Colson 1, APB CDE PQ wv 2 be Bene eae Ce D Yrot « SE Wot Bene BE w not 2 PA Net 3NE Gm® Pa UNPA io tenatdered NPA Due Rrenideer % Ck Not 2NF 2.2 o wm INF 2B A,B, ¢,b,E) F:$ pe—see , ACD, Cp BE, DEF 4 ABCDE <> ADE ee He 8s Bes *—-D cD € 1 ACD wo « Sk, bt nox CE. Do = € 1 AD 2 « Sk AD ao 2 ck —O@ AC—D een On aes Ac wae ck —o ED Bere Conctlad wor, ' AE—> Be Fe vo ack pong erp Ac w 46k acn 3 cp — Be CD in mot 2 gk lot ™ Bene ° BE w nta PA not ™ 3NF e ©CD v @ proper subset Net 2NP 9 ck ~\ The rvelahon Ww vy INF. 7. R (ABCD) JAB—c Abp—>c, ABC —> D, At — 3 48CD <> Abd Ger? <" AB une sk, <—-D FO Reasons Conclusion ' AB 4B 2 Ck 8CNe 2. ABDC ABD va Sk BC NE 3: ABCD ABC Hack BCAF 4.4¢—3D Ac 2 wta sb not by BONE “Dw mta2 PA net m IE 2 AC.» mot 2 proper sibret 2NE % Ck. The velahon © mm 2NF. $F (ABCD) {A bcp, Bc an, D— BY pa iA, BC,D abcp <2 A 2 ch —D Brn 3 Bc < ce —® D>B : pe 4x CH —@ FD Leas mn Combs 04 1, A—> Bcd A b ask BONF 2. BCAD BC bb a Ske BoNF 2 DB DC tb met SE Not m 6CNE Bow 4 Prme attrbule BNF The velahon 4 um 3AF. [Link],B,c) Fi fpA>B, 8 Ac} Me 6s He.e> 4 0 Ac : Bw ack —®O Cae Ay 6 FD Ream eae AB Awa SE Bee 2, Bo Ac Bue Sk BENF ‘e.. Pependeney preserving decompasihon Not chtemed om BONE Consider a velahon 2(A,B,c) Somehmes. Ve eae Posseble EDs : \ ' ( A—bB&A> 6 : Ae - eee BC A ot! a8 ¢ 4 2 Rao Now, SplLt RP mta €,(4,8) amd 2, (Bc) eae FO: fH 8} Pa Eee 1f we fot ED, and) FO, te Unubal FD “Slate be Abumed . [Link] = FAB} hb to not FD. i.e, Qependences on dhe cregin al velabon “umll preserved R(4,8t, 76) Fif AB Bc ,coD DA} eaGhiec). FaGC7o.c) Ne have t find the FDs posible m RB, ond 2. 2, (a,B,c) AT = ABCD (wet the pavent velahen ) = BC (A need hot tr be smwolved & D doant belon b &) Ne, #4 Bo Bf - BCDA (24, @—4e CctscpAB = Ae re, C 9 AB) Apt = ABC ae eo chek for Ane ES ¢ Fi fa Bc, B-+Ae, 6 As] 2, (c,D,&) Ere tonb, A,B} G— > D = $A,8,¢, Di} pee et- fe} ee cory Deeg Tiecks Pees Fy Usifre= & FL,UF2 = $A Bc, BAC, CAB, C7, on} checking fey Fe AB p> BC 9 A278 — (decomposhon) ’ Boc ; BAe Can BO é 2 ea D Cees ROD 0A i arcpatcad ee, aml CAB ae, 0-76 onl eva € ‘ SDA CTramsbuly - 7 +. focnel | a, pAnnt e, EOF, Covers F. h % Sa kovhy , Chek fy FUR, » A> BC [Link] BOC eA, (trarades ty ) A3+B6@k Ac & ABO ey One Bec Coney, Oy 2+: eeoat Bc Ciwen) = c+ AB ame”? ct frecns * cD (Prevent ) » BA Chants) *# Dae pte {paBc} Fo ocwes FUR (Aur cr) be, Thas dlecomposchon i9 dependency prereved CLess ess) FCABCDE) F:$ 4 —+8CD B>AE, BL FED, D> E C4 DE} B,( 4,8) 2 (8,c) 2, (c, DE) it. ABCDE ct. Cpe ot ED Crone p o ABecD : poe: eee ef poy .po»e ete oe] OM be ctependone BA co beE For all canes » fae, BAY $B®—-+c} {D7&/c> DE} Of, = G= $448, BA, BOC DOE, cc 06e} ea Ep closwe (fimo) Canclod tom Sl x A BCD Ate £h,8,¢, p,€3 . True See at Ae Bt. $89, c, De} Tre «BC AED Bct-fBc A, 0, €3 Towe «OE bt-fpe4 Tre +t pe y CINCO ER a Tre - “1G Cove Ee Chisel a Aopen fok toa 0 | £9 . Closuze from F Conclusion 48 ats fABcvE} Ute | 2 | fe BoA Bt {Boe cp} Tm P+ Boc . Tme § s D7E Present Y +cooe Present .F coves QG + $F Already covers G sma bd denved From F. The 1 + G5 les ecomposhon, ep B,C Ph i 3 ‘ 2 ABCD) ? : 2, (a,c) gen? j At = fps) ot-2B} © etejer} pt=fv} Be ctf acon} pot fa,03 Me ur. 2 2 = Dependent iy rot preserved, GEr ppv GCF DPX (6G » 4 proper Suleset 9FI ; PcG Not possible Since G denwed tram F A decomposchen D © toxd te be dependenus preserved with vespect te F if dhe union y f; on cath &. m D & equwaled tw F. ELE Tuo sek 2 Funchenal dependencies ave equesalonk y% eEteet. ne, Every FD m E wnbe mbervred from Fane every FD Fo tombe enberred trom & ESF , if E covers F KF Covers © Léssless jon cle composthon (Won-addidnse) : ee ce eo c) 2 (A;B) Barre) y tet & n Sa = Lb e, VW here in wo Common a Bee G attr bule, ‘ Log ou R PIR = BxR t 4 : 2 code Yous , >. nok less less DD, te, ae ! fe 9 al | Bo decomposchen io sod to be Lossless if & @ viable ‘fo Lonstomet he relahen & Prom the cle competed gubr elahons — usting Join: Pele) og ACR) = ACR) © ACAI A(R) &G AC) + atrbuleQ © Common attrbute 6 @ SE ( bor atleart one sub relahon ) Ae. Al )= B |e we lest RA ,B) (2, (A) ed) ’ : | 1 eal 2 2 i & 8 ' 43 4 es 2 (A,6,.c) 2,(A, 6) Tan 5 jan rz al * 2 326 2 437 aH © p(e,) 0 4(F:2 = ACE) gte Bc Ce) chin 6 Jew oro nelahoo+ e if A t 2 3 u \ ' ES 3 ot Fe 8 ' T Z 3 1 208, € 5 41, 02” A 2 ie I 2 ' = 6 ' ae -" Loss less . A ick get Pe ¢ Ss Ss 4 s Cheek ip che decomposchans on R ave Lossless ov n0& RIA ‘ RAB) 2, (DED < | shoe 2, (pac OR, (DE) =H «Alot Cass less eC Gels ep - DC Fee , ep) “Bc U cD & R(a) Not boss bess. 3 Blpec) &, Cepe) femman atinbuble = € CDE (Coe sk me) oe te loss leas 4.P(Ab) Bade) Corn attrbute =6B Bw not 4 Sk % athe Br lt & mt bm ten. S-B, (abc) 2 CABDE) su Commun attrbute = AB ABwae sk both R £R, wilt w Onl. Beeeth®) * @fep) R, (oe) R, £P, =. RR, «€R, he tommen attrbule « No cornmon attrbutes ~ Not Cows less, Fo& Rs ace checked Rest smdl “Me 7. RCAB,c,D) Fi f Ae, Bc, cA PCABC) 2, (cp) BCA) UR, CA) = RCA) ~ PRCADAR (A) HCH Be 3. Cio oe See = The decomponhon 2 Loss Vers. Camenical cover / Muna cover | Prveducible #t 7 FA fee “ S) Apply armstreng’s axcoms cunbl. every FD has mly « Single alinbute i the RHS- : Gh Remove vedumelamt attabules iy Remove vedundant FDs. Be: foes; 6 Ae, Be Abs ine JAS, Acs} skpl: Atrial taal. Ee ff AB>2C,C7A, COB Be, AbeaA, ABC 16, Ave, Ac—B3 Step 2: Dod O o © @ ® [esa ‘— Bc, Ac} Ct=2 EB CA unt obhens! Caml hircacdD fern, Bac, 4B A>} Saeie) We Ct EA (BE 0 bbe) fora, Ba»c, A645 Dont dad @ A cel g FDS 0 sad by he mininend 4: hh Every FD has « sengle attnbule im RHS. [Link] FD XA tom be replaced Gy YA, whee Ve X 3. Cannot remove avy FD from Fs & The yemnaininy FDs ave equivalent b F. A mimioal cover fa Seba FDS E is 2 munuma S49 dependencies v(m stoned sarborceal ero without vedumd amug ) dhek in equivalent ee 2. E:$ BA, OA ba 03 eae | : § 8-4, D+ 8, 8-03 coer, “£ Bp, pa Ay , 3 © [9 Bee, co+E} {fhe Ac, AFD AE, co LAB, Ac, Aa=DLPE Desa 1 P+ Bed) cos EF Lossless sero decomposiken : (Problem) 1.204,8,¢,0,€.F) ) Mm 246 c , c—+p ps ef, FA, D—- 83 Dp: 5 Abe, coe, EF} R, (Be) ® Cope) R, (er) ACEO AC) DAC) = ate? —< 2-2) 9 Fees es 3. . ct = Jc €FaB} ct wert, =f 9803 Soe oe Sere | "Rk, SAR, tb Gules. 4. ACR2) DAB) = €& Be st = fe] Be 2 nt m oe iy Bitar qth. csubrelifon. Bo PAR io not Lor bess. 2, rt BR ba Re on ne, Gonversion, From INF to 2NF EO OSES ~ &(A,8, 40) F fa8, Bc} Becp <2 AD de ck —O hs :A ,D p—>B ( Froper subse 9 ce —> VPA ) ee 2 Me nck im aN, but im INP. #Decomposchon must be dependenry preserving and bess rs: Hew 4B violates 2NF (A ive subet 4 Ck) Now Rnd at; ate nec ULUwy Grrmped im to one relahon ee wv Cla Be) Spe he ose, |. QF) +A Could be the commen athnrbufe . RB (4D) Ben .a3 . °F lom-check lke result for lessler devomposthion before embiming D. Hishest normal Form ny derompesed relahon vy 2NF. \ 2. RC46,c,05 , Fifn—>8 c40} ‘Pecp <> He —O R is rok im one [ Rw m ine : Pt-9B =——> @, Can) ~ mecD — >» % (cp) But 2 DAR, 2 rol’ Lois lent. <7 Arethuy subrelalion cddeds RB, (A, 0) FR, 8 ae im BONE a — tsi FF ng en ME Ts, Sd ein 2s ar ee a a rr Conversion from 2NF to 3N APRA 1, (A,B, c,d) Fifa se Boe, cp} pBcD <+* Awa ck —O A>B BNF eas 2NE UY Cc7D 2NF Uw Bt- cps 2, (acd) aa cD 2 (cod 2, (AB) RAC 2 chosen, jor beng. Dif{eep, co, ABY Bat fev 2, Bu the Ce Boe BEN —>Dp AWE Y CNot m 3nlF) Ctecp Dac pp) ; RB. (ac) ¢

You might also like