CCMBIT Assignment Tech W Sem R22 MID I1-22ROLADIGS - DATABASE MANAGEMENT SYSTEMS, INIT AR 497 R= (AB ,CA7E) He decompose ‘WY mio Ri = Be) Ra = (PB .b)- The Ams SCL of Funcilormt dependences 49+ A: BC, a> E, BF, EFA: Show Uhad LnIS decomPos Horr IS toss1ess Jom decomPostHort qwen Relation RRB, ChE) Furctoval dependencies (FS) * A> Be co 7k B-A EA Ry = (A, 80) At = 3K, Bc BR KS — ABC gt = 3B, BS -» relviatty funcional dependency ch = Leh > Tevet cunchonat sependeny Ast = LW, Bic RBS AB 6 = Bd = pw Liew AS > BoA cnt = $40, B, RRS 7 cA>B Rect = $A, BA, % BY —> mwially Functional dependency fli A>Be , ABC, BOTA, chow Ra = (AE) At = tigen, ey ~? A-2dE Dt = LKY ~Triviamy euncfonal sopendeny ey = PE PIR BIAS 7 E> AA Apt = LA BMS EY > AB E LPL A Rey - SE A eat = Ym BR OY ERD ABE = LK BL WHY -> tially unchona derendency age 1of 12 - Bach 1V Sem R22 MIO l- DATABASE MANAGEMENT SYSTEMS -25R01A046929) (GURIT Assignment Teen WY Sem R22 MIO N-23RO1AO4G9 - DATABASE MANAGEMENT syste oa Fo tA sak, Era) Nore, BEPA, EAPP CR AR, = DAS » A dotesrines APHE, R7eC1 EPA hus Pr deledeines alt attvioukes of R (RIBS 8,6) rma king Aas a_supes key of Re Since RL ARs = PAS and A $s a SuPerkey of (R)_ the decomposition AS tysS LESS — VOW decomPositio’ + Brass Ww detail about Vabidation vosed. Fuotocols} Valiation - based PhotecolS axe sechniAes Used in COVEN Corm¥Ol WW _dotaoese Systems to |ncude sevialhzaL\litd (i.e, thak ttansackons execuke as if they Hee Sun one afies arover, Without lintevfesing Dib each |_othes) They ave optimistic I viakuse hich means they assume conflicts ate Yate ard ‘stead of locking data dusting the Wansaction they check at the end Whethes o confiict has occumed- TE a confiice 15 found dusing Vatideion, the #0nSACKON may yollback- Phases 6f & vatidason ~Based Péotocol! A_kxanbocion urdes a Yatidasion - based Protocol t¥p(cally Boas khwvouBh Lwéee Phases! I) Read Phase 9) Valgdation Phase 3) pete Phase A} Read Phase: The t¥ansaction lads data vom the database amd SIDSOS a tocad Cory, Tk makes Changes only to (4S local poxksPace (not the actual database). NO atta database modifications a%@ wade at {his Powe + Page 2 of 12 -0.ech 1Y Sem R22 MID I- DATABASE MANAGEMENT SYSTEMS -23RO1A04G3(SMT ssionmemt B.Tech WV Sem R22 MIO I 29ROLAD4G® - DATABASE MANAGEMENT systens VGN 22 Vakidadson Phase once the tansaction Mnishes \s ofevarlon, eMeE the Validaton Phase. Zn 4s Phase the System checks \f the wefeve Uk commits, it Bransaction conhias With any thes syansactions that commiibed ductive Sus ekecurion: Bo cnetict 1S detected the tvan sackion Cor Safely PKoceed to commit TF a confiict ie dereckedy the tbansadin AS Yolled back + 3]_wgue Phaset TR the Validotion succeeds the Wansactlow's changes oxo wri Bo the database: This Phase is usally Wiclc Leaune the charges exe ‘atdeady séiesriined. Vabidation Test 1 The case of Vatldadion- based vtotocel 1s 4he Vall dation 2256» Suppose pe have thee L¥ansackons! Ti Te andT: Bhen Volidoding a tansacton Sag 13 \t must be checked agast PyeVouS\Y committed. Lbansactfous (Tard T2) to ensues TS doesnt Confifce With oPedathons gexhsmed b's Ty eT. sTexe_axe thvee ‘imposter sets + AY Stott (Ti): te time phen Ti Stotled \ts execution. 27 Vabidation (11) ‘The time When Ti Finished \ks sea Phase and stared hs Validation Phase. 37_Finish (Ti) 4 the time phen Ty fivished is Hylte phase. 7 Confiick Rules ¢ 71k TW Hnisnes befote Ti Stavts, no config (safe)" 2 Tk Ti Eirishes afte¥ TS shades but wefose Ty! 18 Validosed then , TS must nok Kead any oloyect that T Wvote- Otheb ise, coneiice and Tj rust yolf backs Page 3 of 12 - B.Tech. IV Sem R22 MIDI DATABASE MANAGEMENT SYSTEMS -23R01A0469397 (MRT - Assignment -8:Tech. V Sem R22 MIO N= 25ROLAD4GD- DATABASE MANAGEMENT SYSTEMS Tyres of Valldakion -Based pYotocols * here axe _alpevent stvaiea{es teased on how sifct the Validation 184 1) Backword Vali dabor 2) Fotpard Validation AY Backwotd Validation ’ check {£ any active tyansacton Beade dedo thed, Pas by iuwet_by the péeviously committed 4¥ansacslon. Focuses on the £ack thet cowdithed data Should Not be ead ncoxectlY- 27 FoxWotd _Validotion 4 4 © 4 \ Phile a e¥ansacion is active, Eo new exansactio +es to tobe srt checks € 1b Would iwtedfeve Dith active tansackonS- useful Hhen Arew axe mse long - KuNming syamsackions. Advantages: DWSh concurrency 2) low~ conflict environments BT hess blocking Dis Ravantagest 437 RoW back ovetheod 27 Vatidodion ComPle xy By Not sultabte fox high - con Pict evita nmerts . Summoize the concepts of ¥emoke backup systems 2 Ferme backuP Ssems a%e dechniqes used to Stoie copies of cviticat, data ota diffesent location (¥ernote sz) to PYotect against local failuses, disasevs px data loss. The main idea is to %ePlicabe dala ftom tre Mimasy system do a semote Sexve¥ of Cloud 50 that ¢ ‘ iS, 1 dhe primaxy system FallS , the backup can be used*vecover the data. Page 4 of 12 -B.]ech.1V Sem R22 MIDI - DATABASE MANAGEMENT SYSTEMS - 23R01A0469{2 sesonnent B41 Sam R22 MO = 201A -ONTABASE MANAGEMENT eres “ee “ UU ll Remo backup sysieras axe designed io ensue high avaijabvity of the | bxvansacton — processing even dusing envitonmental disas texs Nike eyes oF Ploods. Th these Sysierrs, a Péimaty Ske handies Warngackion PIES anual opetatrs ‘intebvention Car assists Page’S of 12 Bach. 1V Sem R22 MID I- DATABASE MANAGEMENT SYSTEMS -23R01A0469hay CCMRET -Assionman- Tech V Sam R22 MID l-23ROLADAG - DATABASE MANAGEMENT SYSTENS HUANG 27 Teanstes of contso) > Backup becomes the pen Pynady- ~ old Primary, onceseavebed, ches becores the backuP of ¥eclaims tne pimnass Foe atiey cadch'ni’ uP UsWJ xedo 1oBS- 3) ‘Time 4o_Recoved! > TP the sedo lof at backup is Wage Kewlery Why be Stow: > Backup site should peviodicallY Check Poll to KeeP 10g size Tana seabel >) Hot-—spabe contigusation! Backup site continuously caPPHs up dotes Feo _1985,, makind takeoved atrrost ‘wmmeds oke + HK) Time to commit : one-sage! | Tan suction commits afke¥. logaing at primaty only. Faxes ) but | ‘ BiSK of data loss 1€ pomabY Falis LEbE Sending Logs to LackUP+ Tio — Ved - Safe _TeanSaction cormm\ts afles 1B ging at both Puimaby and bacluPy Yess Safe, ret availaritis 1S Neb /Loth sHes must ke ofesotional): Tro - safe | TE both ates are actives commit ated both log M¥ites+ TP only Peary “IS achve: commit abies 1o9gin8 al dhe Péimadty . Bararnces abilavifity and dutasinty betes ‘Wustiate the concept of nested and cobvelated sub Wesies Pith examples $ Nested and coprelated sub qweries ave both L4Pes of SOL Subwmedies »but trey differ \n how they ave executed and hod they %elate to the wmdin Wess. Nested subtmeries ave executed once ard +thelY vesute As used tm the main qwerds covselased Sub qMedy on the othey hand age 6of12- B.Tech. IV Sem R22 MID I- DATABASE MANAGEMENT SYSTEMS -23K01AD4G9‘CMT Assignment B.Tech, IV Sem R22 MIDI -23ROLAOGS - DATABASE MANAGEMENT SURIT As 2 MID ca aXe exeuted tof Cath Yar Yelusned by the main Weyy and car Betevence colums fxr the “main Wer + A) Nested suo Welles: A nested sunwess js a Wwesy phn anoles Wéey » usvaltis fa the WINERE of Naving clause * The subsea (Innes qwesy) is executed Psst and ts vesult is Passed tothe outed quesd (main werd). che immed quests does not Aiveclly depend on the wd WeDy for ‘ts execution’ Example Emploses Tarie { Empid | Emename | depid Satoty 4 Alice, io Ssoe 2 Bob aS 6000 3 chase to SSO & Bovih 3e ovo s Eve to 6000 Depatiment Table + COATS der_name ne BR 20 \t 30 Finance quesy bith Nesed submedd! HK SELECT emP.id ,erP-name FROM employees WHERE dep-id = (SELECT dep_iA FROM dePaxtment WHERE dep-rame = SHR')> a7 The trey wes Hill weLutn lo because the depid fo% HR WS4o- > The used wes pill wetuvin alk Employees Hosking ‘ih de Paw ert do (HR) * rage 7of12-b.Tech, IV Sem R22 MID I- DATABASE MANAGEMENT SYSTEMS -29R0LA04G9SUE sme Sem MO I-2ROADICD DRTBASE MAUAGEMENT | II 1M (NIH Il ll ouput, | emp ta ery aMe a Alice. 93 chawile LL 3 Eve 2) cosveladed SWWEVieS | fei es WY hak wetess to coluems of the A covberated Subquesy 1S a. SubMe’ owed query, uniiice a vested submes4 the cxvseloded SuLwesy 1S evatuted Pox each voud PKOeSSEA LY the oUkeY WeHY, he wes on columns Storr the walv wey. qwess con sefeserce ond depend Ques vith covBeloked SuUbqHes'’s | consides the Previous example te Employees and Apartment Tables - SELECT gemp_{d, CxemP-name, e+ Salasy FROM ernployees @ _WMERE €. Sabaty > (SELECT AVG CSala%¥y) FROM employees HHERE deraotrment der \d = er department —\d) } >_Tne Wvret WESY 'S CoBbeleked avd CalcubekeS the avesage Salary fof the dePustment of each employee + > The ouevquesy LAU eu employees Bho se Satavy S_Bs@tes than the avenge Salads ‘m thei depastmert. OUEPUES emPpid | _emp_nome Salo$3 Z, Bob 6000 & Bavid Jooo Ss Eve S000 Page 8 of 12 -a.Tech. IV Sem R22 MID I- DATABASE MANAGEMENT SYSTEMS ~25ROLAOSGDCCMRIT-Asigoment-BTedh WV Sem R22 MID I1-23R01A04GO - DATABASE MANAGEMENT SvsteNs Sq] | alevesentiase between BNE and BCNF vith example % Ans |The both Nn (Tid. Nodal Fos) and BENE (Boyce - Codd Notral For) abe AYPES of database notmatiZaktorn to semove yedundancs and make datarase design betes 4) ANF (Thixd Nosmat, For) | 7A Yelatlon isin BNF S644 15 nl BNE pans eVedy Don -Peime abixtibute 1s fully functonally dependent on evesy candselate KEY. TL Elirinedes asansitive_dependencies + PANS fob ulliPje candidate keds? > Tk PEEVETAS Ihe aMemables WKe WIEN , UPdoke ard deletion. =7 TL 5 \e5S complex than Ben TES widely used in database 4esi gn- 7 Eves non-Péime aus ouske depends ong on the psimasy Keys hot of othes vion-Kkep alt ibubes: ExomPles ware | Rotino | pean | Fee Sai ga cse 30,000 Pooya ad ELE 20,000 | s¥ive, au Azmi _| 45,000 Lax) 26 Ps 47, 000 Ravi = csp 46,000 | ot Tn the above example the £ee atidibuke WS non~ PYime asi nuke PhCH s dependind on the awothes MOn-PElime adtsibide Wanth. To eliminate the tsansMive dependencies divide the abwe exampje. wa to two _d'iffesernt tavles- age 9 of 12 - Tech. IV Sem R22 MIDI» DATABASE MANAGEMENT SYSTEMS - 23ROLAD463(GMEIT-Assigment Tech V Sem R22 MID I1-29ROLADAGD- DATABASE MANAGEMENT SYSTEMS, In IMT Name | Rolino | Byanth Branch Fee sah oN CSE 30,000 Poa | a2 Eee Ece 3.0, 000 |__| shiva, ay AIML AIML AS; 00° Laxmi | a6 as 18,000 Ravi | 28 cs ce 16,000 TE Mas transistve dependency No _tyansistive defenders 27 BONE (BoYle -Codd Nowhral Fown7) t Wh Relavion \s_ in BONE VE evesy determinant 1S a candidate Key Eliwinases al UYyRes of dependencies except eviVial Pumction at dependencies: _Allo2S only one Candidate Key. Dk _P¥EVENAS ok tYPAS of anomalies ‘Mciudlug those PeVened by BNE+ TkNS Mose complex than 3NF Te ‘5 used Wr specific cases Dre claka WwHegyety (5 c¥rtiCas Foe evess Punctionak derencdency (R78) »A Should be a SuPey Key Example ' = = Rollo | Name. Byorchid | Btanch 2 Sai aga cse =o Posy 399. Ece ou Suen 123 ATMEL 26 Laxmt Any BS 28 Ravi das sb Fage 10 of 12 B.Tech. IV Sem R22 MID - DATABASE MANAGEMENT SYSTEMS -23R01A04G9(GMAT Assignmant- Tech V Sem R22 MID Il-23ROLADKGD - DATABASE MANAGEMENT SYSTEMS ANNU AM Rotino | Name _|Byanch Béanth id. Bhanth at | sak csé 394 cs = ao Poca ec doo EcE 2h | sha ATML 493 ATM L 26 Lax 2S 124 BS 28 Ravi cs tas sD | Th is ecwe Te WS Not iw Bae The Javle second {8 not im BCNE because {t only altos derendend -es pheve the left side is a sured key: Page 12 of 12 Bech. W Sem R22 MO DATABASE MANAGEMENT SYSTEMS - 23R01A04G9