Module ee Logical Database Design ped dor good Aatahase disign - Funchinal Aspendancies and ays - closure % — functional, dapendanctes sek — closuia attibukes — Dependenay presewation - Deusmposttion using functional | Aspendenctos — canontcal. coves ~ Novmalization : | iuse Normal form — Seumd, Normal Form — Third Novmal form — Boyce Codd Normal Fosm — Fourth [Normal form - Figth Normal Form — Join dapendancies. — Blueprints 4 hoo the dota te gong fo Shue - - Dehaon | ory application. = Mee alt reguleemerts uset | = Rduced conten 4 dusignig database . Roquinemart Arobysis —> Dammbore _ vg aa A cis) = Manning, ee = Logical mod convention & [i eee toading “dayton = Payal Modis rangFunctional _flepencancies The relationship | xoy ast y= Dependent | Ager | [caw clan} wt AAR as | oa 268 an | 103 cet 2a 1034 DDD ay The junctions dapendanctos ate, ap > Name zp > Age | Nome > Age 2p ,name > Ageeys_in DBMS: Huy One fundamental elena 4 a pelational database mod ‘ak ensue oniquenes, data akegrity and ebttctent data accom. /ypee_oy_ tye: D Super key D Candidate kay 2, Pome i 4) Foveign kay D Alternate ty Composite tay - A Sip Ky ba grup mR sigle oF rrlbie kip, reat) Satgullye. - 'eateley, ste ba table De Supports NoLL value» ib us = Moaimum number 4 Super Ky 5 Se whe on - No @ althbubes Eo a Retation. ~ Example 86918, Wo. | Supe ky = 2Exange: = Super Keay - whose Proper Subset % Super key wh rot upd key, — Hinimad Set 4 super kay. A 8B ce I t 1 dol dn , 2 4 a a ray Senseys oe 403 faey tach | Apecy fees | | Condicat Kay» cut ta: | LAS — Sk - Ne proper subset fey — Fah — Sk [Super ty] feey. = tag otelpliny | Ahecy — {A} Super kay YBc$ — wo piper subset Z super KeyThe candidat Key gay 9 fees | Pelaasy tay t = only one Key 26 prmaty vig = Some as Randidals key eT coins hE! mal bh TE ip enene ® r emm condidab ky > fa) i primaty at = Dey te Salatiou hi = with other table. - a va ty ees gitd os colleen ts oa tabetha | Agee 10 tte piaty kay fo another table.poe: ¢ | fesont | Wane age | para 4 hha 80 “ 2 eee $2 . cee as one ordertd | duder-no_| Person—iol | -> Rreign 1 raat aces yy a s3blp 8 | 8 rey | a ‘8786 t | Types oy functional Dependlancy : | The types functional dependency O80 | D Trivtal Functional dupendancy D Non-Teivial Pusstional apendany 3D Multivalued functional dapendaney 1) Transitive functional dipendancy 5) Full Qusctional lapendeny | ) Pastial functional dependency bee Functional _Beperclency » = Dependent always a subset q The dokerminant . ‘ Aye Bb functional Apedank HX & 4 subst HA. sstd | nome | ae | Std, Nome > Std Non -rrivtal Functional , rot a Subsot O — Dependent ShvicHy the daterminant hye B funettonat Apendat , Ye Bre Subset A Emorple » | Std, Nome —> Age “Matti yolued functional Deperdtondy + = when one abbibute, datos mines | adopendint Values dps arctier athibute . a weg A-rec & furctional eperdont thon 6 and c Should not be Aapenclont: pivalviand Gye dk me danse dapondtant . Gaampe: Std > Slama, Ageay Name > Age ond ge > Name att nok Punctonol, ore “transitive Fusekidnal dapenden — oceuss whan a nion_kiy aaltYhake dapends on Onoties non- Kuy abhibuke. which thon daperds on the prima kay. - Te AsB & Funetionag dapendeny ard Bsc th functonat spendoncy than Asc & alse {unctional — dupenctencite abso sid > Olle - FD College > place — FD thon eid > plate FD Fabl_ functional ey: A guncttonal dupendanuy X>Y ie a fully functions dapendanay YH furetionally daperdant 08 x ard Y f& not Functionally duperdont 09 O”4 PRCA subs 4X.| Bal: je AB +c G furttonas “dyendent . Buk | Arc A Bre & mot {usctional uperdant: |e eciall areiey: | A funcional dependency XY as a partial on X | dupendaney HY functionally dependent asd 1 canbe Aokermined by 4 Pre subse aaa ‘Earp kore i. functional dapendint - And Ase ant B+c doth or any one funcKonal —claperdant _Rropeston 9 cfurchinad _dperdoney + | os Aamstrong 's_ Aaioma Be furetional depending : Aztoms —e Primaty Rules seurndaty Rules Ro lea Hity |— orton Rude |— Devomposttion Rule Prugmentation | Trans Hil | Pseudo Trans sny ‘— composition Rutets [eyessig Ty a dbyia tA subsak Ay tan A hols & al\bukes ard & | ‘ my BLA than, Ase meta Hon * Ape Be gunctional’ dapaclonk’ than ‘oct a ahbatas added to bath datereteee cs } [and daprcunt than | Ac FCB ih functional dipadont | “reamitivily * ' ty AD ee ja also functional —lapendot - FD & Bac & FD » than Ate | ‘vat (Onion: | pe Avett Ae lost ndunctoral | | dipendant fen A-bae functional deportont-. | ¥ Composi Hon + Ty dapendont — thon Acd Bp & Amd Coe functionod | eee dperdot - f | |is junctional dependant than basi: esa. Te Ade te Funekionat diperdont and | fo a RD. ear | Ac > Dd 2 functional dependent ate also functional dapondast. peeene Functional Deperdancies set | The «closure Of, functional dependensy (F) dated as Ft i the det q all tegulay furctlonst — dapendention that can be levived ton F. | qh & used fo dincover Some the “adden tunctional —daperdanclr so as ty dusign aq betes database. | Fo: Ave # Be diveck visible FD | ct: Azc — Hdden functional dapondaney Ly rmarrong Aatoms |R= EA BC DIED ard set o functional | daspendancioa Fs Fade, CDSE/ADE, BHD .EPAY wah? Sabavetre tustemabe; Palen » Compute ee D using Twonsltivity Ade » BOD, then AyD 2D eD>e , EFA thon cpaA 3) Using psuedo Tronitivily G3D , CDSE thin Bc > E i» Arc , CDSE ten ADOE eing Union Rule APB, Adc than Ase = [ ASD, CD>A, CAE, ADDE bse}closure _ _ Attributen: depsnas one ygitialt,,, x. Ye ES a alibutes that ate functional Atperdercios On X with wespect to F. De ik danced by xt which «means hak % Can Antermniag peep RCA,B.6,DiE FD Fr EDA, EOD, ASC, AOD, RESP, AG HK | me doom a © to et | LEA Fee = [Link] ae = fe nde} Me Heine) Mk spt ane added 4 EA DCPS nea A@>k cant possible add kK Gy mak o> tae Beerasres es) Leder}& Candidate Da tr Comert a altvihuke closuy , 9 abibutes whose Closust a tehatlon , bile a Super apr kay & ony SE A all attiuke facade cansdoi ty minimal Sap key meaning no. pmpey suet g, tes obloienlbhorpe, Ky. Example = 4 RCAB.C, DIE) FD Ave, CHD, DoE | pd clesue 4 ahibutes, CAsedey” = LAr, ¢,Die} — Super Ky | Paenent alt abbibubes Ae & Ralation (acdey = 1.A,¢.D:E,84 — Super kay C+D (aces = 4 A.cr6,8.D4 — Supe boy > | Chey = fc. 8.D8} — Super kay (cet 2 Fee Dy LL Nok” & supe ky. Not have alt abhibute Bo De® - CRED = FAO BY — poe a supe, kay,(ay = 98.85 om Not supe Ray (edt = Perdie} — Nok super ky | Ce) = fey = Noe supe Koy. hart Sepa east AgcDde » ACDE, ACE. Ac Tha _condidats Key OF Minimal Sek oy Super ley = Ac Thon, wo have 10 Chick Quy other ¢ Jay proet fn fh Balaton nd shied ne ae cabhribubes aac Prom’ (AC =e, Pina i) Chwen ten Se Inet OA ern Ver pea abhibukes ote On RS % ony functional Aspen donoy - 2) TE nok — Tae 4 only one Condidate ey. b) DE Yor - Replate pome attibutos by Candidate ray with corresponding Ls @ fuacHonel spendin. TH) Rapest finding super kay on candidat ky,| The prime alhiute A AC AH Moe proct de the Rus L Functional —dipandoncy. So, | A decomposition 9% 4 relation R sat Jeter Re t dependony ..prderying i me rion a — functional —dependancita on fhe acompaced relations equivalent’ to tea original [Set a functional —Laperdoncter | comides Relation & , F wtih come Pucctional, | Aapendsnes CFD) | Te ®t decompond tw R with FD Ray [ea Ra with @D¢ea) . tn than of Thue Case , D fr Ug. =e — Dependony preewing 2) Ufa CF NOE Depertony Procavig D fv. DF — Mor possible. | | Rolls oF paepeakive = | 9 Dependancy —-procrving —propety D_ Loss eas:Example» : Q (A,B,C 1D,8) FL Ade, Boe, CD, DAY Ry Ac6.c) Rc, D,8) | ate f8,c.03 Aree Bolick eS [Link] bdr Cap Ore FD ABY cane fpmac) Dec | Bhs 965 | pgetesec, a Fa fayse, Boca, cone} es teen bs ey FrUPa = { Abc, B>cH EAB (CHD, D rc} rum BE 2 Dependaney prserving Devomposl tion cicinbidcheihbas gait ea Eee 91 eens MeN ee ENR eh ci ame “Canontcal cover: Canonical cover i Called minimal cover Which %& called the minimum Se Q FDS. | A Set % FD Fe called comovical Covey (FR cath FD tm Fe fs a simple FD, fut veduced FD 8 Non —vedundant FDEnonple end tee Canonical (Over oo, FD = f A+ec, BoAc, c>ABy Stept crea a Stagleton sigh tend Side doperdancy Ave 2 Ade, Axe Fi f Aye, ADC, BHA, BHC, CSA/CPBY step _ Remove extraneous athibukes te any eateli Sq tH no eabonsout athibuta go, Fs fare, AC. BrP .C>Cs can eS By Steps: = Ramove the vedundank FD O Rome >A @ Remove coe Because Bae A, 08 3 8A ae coe coe E24 pre, Are, BHC, CHA, C>BY Bs fp POE 2c 9A) @ Pomove Pc . Became PSB, Be psc Tht final Canonical Cover | FD: f pve,Bsc,crAy a‘ffok tn datakate disign "5 eipicteney » consistency tha primaty objedtive fos normalizing the below anomalies . relations «-& fo obiminake = tho 1) | Bawetion anomalies : = occur whan te & not patibve fo Tnset database became ta Asqulzad ta damm fe Enwoaplete . (data tet a Sidd, ate missing 08 ° Deletion anomalies: = oun when dateting a Tewrd frm o database Gf Can vet dm tha unintentional sss data 3) Updation anomalies » = occas une moatding “tate fA % “database and can esult fn Froonsistancies oF enrossFeatures % Database Nosmalization + > BttminaHon oy Data Redundancy . 2) Enturing Data Coutstancy 3) Stmpligtcation Data Managemant 1) Baproved Database Hegn 3) Avolding Updati Anomalies & Standordization. Tire_a_Nevnalization Cov Type _vemaly Fowns? lhschigQy Eitan oe (ne) | a) Seok Noxmal Form Cane) s) Thtsd Namal Form C3Ne) 4) Boyce ~codd Novmat Form (BCNE) 5) Fourth Nowmal form Une) ) €tsth Novmal Form (SNF) Fixst Normal Fown = A. velatton ts Tn | eviipmetiitako.; t,. Mgt, relation ts single - valued prst normal form UF Jattinta on Te dae mot contate any composite Fox mull valued abiyibute .Example > a A aedatton 15 (SAA te se lob is NF Bt 9 All tha abtriates conta only atomic volutes . 1» Bach column contains values aq single tyre. 3) Fath reard is unique » meaning Gk can be tdantified >| & Primany Koy tp thaw ata fo vepéating! groeps) oF amma & ay 0. le (Se [vaes aenal to | nome | couse te a cen ele we 1 oy 4 | exes To make the table in INE , Ramove mubtivalueel jee table.Sewnd _Novmal fosm (ANF) : ie eae ee 4 toy Atdbuke ly functionally dapendaat patie: ena, Wis thas Te allaton Sib ‘second Norma own CANP)- Rules ox Conditions fpr ANF * 1) Ralation should be in INF. 2) Relation Showd not have paral functional ae = The huschioral, [Link]] ota | Fees dapendonsy {Bhi | Faia} | ARP cid > Fee toa | pytton | rsp00 lave tceee angie Studd, cot -> Feed fest cl tomo 3 Cid» Fees > pantiat was | Tova | to000 functiveal to, | as | 20000 crea 2D dot ane studid | cid cad | Feet tol | rma Tava | to«00 toa | python alt sae Python | 16300 ee cH | 2000 > noo Table wos | Tova ¢ |rean NV aKE ton, | To creefom fun A A “Fae following Goaditiowt holds tn etton Rule @0 9 Relation Should be D Relation Third Normal Form (aNF) relation % im the thind Nowmal form, TH tte no teanaittve dependency for non - prima altlintes on well as i & Ww the Setond normal rotation is ih ane te ak feast ore o, every on - trivial dupeadeny X+Y x Bg super toy. y & a pame altibuke / port a condidale key. condition fiw ANP > in ane. Should not have trantitive Lepandancior Jpx non ‘prime attributes _Exanple + a Std | Course Fees dependency 49, | [oer Towa 20000 |. | aoa c t0000 Suis One | | tos ctt ts000 Se ae toy | Python 25000 Sod’ eet los Tava 20000 S3d couse > Feat 10b | Pytion 25000 Sid , Feet > counse | | @t | tava 20000 Sid > Primary ay | [we cH 15000 Pina ottthuta> Course > Non Pine athibute Sid 3 couse Counse > Fath 5 cTenniitive diyendancy « Sd > Foor No Ne. = Remove Transitive dapendenry Said oumaciod | a ma bis ae Tava e000 a : ec tooo | (os | re Het 15200 ‘4 Python 25000 = eva Python | ie Python ot Java 1S cH [Boyee -Codd Nowa) Form (3.5NE) : For BcNF, the elation Should Satisfy the below conditions , D The elation Should be to the BNF. D xX shod be a Sopa ky for evely functional, apendaney (FD) x-r¥ te @ Qlvenwetatn. Soi tee ee studi Tutorqhe closuin attribute, sett 4s,c.7y — Super key a ee veens, = are Mace candidate ky = {sc} s {st} Non prime attibute => not in Putetion v G0 Relation in NF Tc Not Candidate kay Student |” Tutor teh oan Towa Sam tor aan Pytion | Sava toa | Sam toa, | Santosh Fousth Nowmal Fowm (ANF) a A velation @ i io ANF i and only ty tho following —conditiom age Satisfied , A Relation must be in BNF 2) A quen ‘elation sry tot contale more fRan One muttivaluiad athibakes . ar’ = isitich - Super lay Nee ie ty 2 geyre. cAetel., 17, ¢3 > Noe Super kayane eliminates Tadapendent — many ~ to - One yelattenship between columns Stutd | Subject | Activity | | 100 | pusic | autmantg | too | Account | suirmming | too | tuic | Tennis too] Account | Teanis 150 Matta | Tagging Prima Kay = A gtutd , Subject , Activity) Ri {suatd , subject } Ra fF stunt , Activity | stutd | subject stutd | Activity | fod Music 09 100 Aceount (oo ee 16D | Tagging Fisth Nowmad Form CONE) :@0 Pasjeck Toin Normal Pom CPsNE) A velation R & ty SNF Te ond only te TE Saisie the following conditions: D R Ghoul be already tn ANE 3) DE cannot be faster non soss dacomposad Chota. dependency)Losslexs A@compositton : Th ensures that whan a velaton RO doxomposed | breakad tato tuo oy moe relation, no data 1 bect, & tha ovighat elation R can {ba again vecombructed — by Sotning these dovompored, aulation’ . A B | ty leva ales 6 ‘Detomposed RCAB) £ KCAL) rear fale 1 ry 1/3 Natural Jon 4 a 4 Rim Ra ¢| tems [Psi] | 4+ [s]e | DR aga ge St b bosstem.Totn__Sependancter | ote dapenden: [te wate eH opiate Jee ota present Th the atte « yy con be ‘qiustrated 4 whan tha Sub -Yelahion Veropene ef | > basshow din dapendanay + = Join oceurs betwarn tables, TO Should be fost. Yagpemnation | | D Lossy Tom dapendanos — Jom deperdenny , data loss - Erample + Alation & | Company | Pmduck | Aqeat a ~ Aman a Ac Bman | Ca | Ragyigesat | Mohan @ w RB Gompary | __ Prduct ey i Cy, Ac C2 | pagrigenatos co. wRy Ra 5 com PAM} | Prduck Agent Cr Ww Aman a w Fiona cr Ac Pervan ce Ratsigeratos | Mohan en nN @man “ 7 Mowe Additional tuples Cr, TV, Mohaw Co, TV, Aman We cseati RS comet Sie Cr Prean C Monae ca | Monit teow (By PO Ra) PORE Gampasy | Prduct | Agent rf w Pman a be man ee on ca, | Ratrgerame | Mohan catalan & 1 Monit