Unitt Database System Arehitecture res with no meaning by ifsell are ented data, Date Data & Informations « Rav fivets and fi | cessed and turned inte information, The result oF dita loos not have any: meaning. wat it is proc processing is infiormation. of meaning: iulormation, A record is a foxival eontain one or more fields, A file is a logical Ve Mey, crete teen ship Mate Data elements (or fields) are the lowest collection of data items, Data records 0 collection af recanty, x Eat UAT, HECAAAT TH Metadata. “data about data” i fa data structure that stores ingla-data, Data dictionary’ Is a table in a database Data dictionary \ field. types. length and other eharaeteristics of the Fields in the database that Stores the na tables. Database is. collection of logically interrelated and organized data, DEMS isa collvetion of interrelated data and a set of programs to ueeess, upelate and manag those dh 2 Nirlines/railways, Universities, Credit card ny af Danthase ster Applicat tions RDBMS (itek.tional DBMS) > DBMS + Referential Int Sehenta Sangin of \ime ay orn, He collection of infozmation stored in the database at a paiticular moment is called an iastines Seyret of the database, The overall logical design of the database is eulled the database schema, aeey ote Weg Detabase Languages (0 yUiate Definition Laan We specily a database sel nisin Tan gnype. DDL sp fe fu ereate a lable . = ANSE Mf structure of the existing table. Q Binp — Me is use to drop the whole tuble, By 1 is finished Ive snoy © pes WS ity « set of definitions expressed by a speciat langue eathed data mds DpL-) defame sdrucdure of db Carute ow mod oblerts Tashewe| «Following & this comment the existence of the hole table OD crete @ Mew @ Dev | ts cae CE Od Column | chen TABLE Crearg TABLE Emmerenst [cre eMl Tomalsbnge | ggeP TREN ecape id ty chyene Geer hor od) FALTER TMMBLE Fmptotess ‘ Now Prac MeryhuyOes)s A y© Twnwe > TRUNCATE TABLE SHipleyce; TAS Reanecating the — a) ® Re-nome OLE No’ omy Yemor erng = a : \ Vloyecs TO Students Yous . Comunging Stowge Ret Aone, Ernyloy Stod ens} dedimarion) ALTER rape Esapeyers NAME TO Student, @ Rename rename a table, | SENRME Students he table is lost. 1 © Trumeate ~ By using this command the existence of all the rows of the table is lost. comparatively faster than delete command as it deletes all the rows fastly. 7 OBL hove TABLE word r @)Data Manipulation Language — DML is a language that enables user to access, modi delete or retrieve data. DML specifies following commands: @ Insert - Inserts new data into a table © Update - Updates or modifies existing data into a table Delete - Deletes records from a table G@ Select - This command is used to retrieves data froma table O Views inn fe from tule TweeLevkofbawAbtacion [views of Awebuse | 3-Hex ‘The system hides certain details of how the di lata are stored and maintained. ‘Thi abstraction. There are three levels of data abstraction: called data al comhia'eoe UB Phy tical Level The lowest level of abstraction describes fgn ine data are actually stored RENN infe(2) Logieal Level: The vent high level oF abstraction desertbes ‘what data are stort in the | Pepegfers 2 Pe database and what ae eeeet among those data. (3) View Level: The highest level of abstraction describes only BRAS te Bury ome user | For example, consider customer table with fields customer_name , customer_id and address, At i Bhysical level. a customer record can be TSSeHtbed as BEE NS ARTE wits hides this | od gl Dmnpiler hides t level of details from programmiers.eAt the losical Ie | definition: L el, each record is specified by type 2© PR Lever & compiler Wide S32) into Programmers uf ve the entire database. | i Qs Pregwormmes qoorks as \his \eve | \ Struct customer “Dale pammistrutoy ose ahi weve) | ! of “absirecdion Ae decide whut char enstomer_vame|20} Be Ae Yee in Auterbute, 1 char customer_address|304. @Q There ove: MIM Aidevenre Views A some quer Es Stodemta, Alon watt vie wey ) = Cxedemaitns ditty and database adh i Tevel of abstractidn, intl customer id istrator work © Finally at the view level, computer users s data types. a set of applicat pro Y) Srasext ° (geet INTO EmPiONeTS CTD, Noseey Jan siawwes ChE, ae’ ) | DELETE F Rom tenprot neg SMERE QE) => @ Pde eenpiotess SELECT 7 FROM Eenperces, , uron’ é Nee! Reet het Present 323) AoSet Cea ctet Ores eral 52_~ = x Perea) nighel View rv} Students eS et TE UOF Vtegne| Re Sac ene nm ect =~ es - & | esd} ce © | ne AA Levey lependence~ _y Node Tndepent Qo User Each higher level of the data architecture is immune to e architecture. wes of the next lower level of the ‘There are three types of data independence: chow iv Independence: isthe ability to modify the physical schema without causing a 3 ae programs {o be rewritten, Modifications at the physical level are occasionally [ofa dey. Hy to iinprove performance, It means we change the physi without affecting the conceptual or external view of the data, pendence: is the ability to modify ms to be rewritten. Modi whenever the logical structure of tlie datal means if we add some new columns view and programs should not change. G) View Level_Dat saan aways depen, because there docsn’t exist any other level above view level, Ta tee st above view le een eG Sen : ‘Difference between, DBMS and mi eae Panwa 7 sStovaye StU hee the fogieal schemia without causing jeations at the a al level are necessary altefed’ ‘data independence remove some Sad "in table‘en thease DBMS DATEL E 2 System Redundandtes and inconsistencies in dala | Redundaneies and inconsistencies ae reduced ait Aire. AVC Data is easily accessed due to standard query procedures, a isolation/retrieval oF required data is ic due to different possible due to common file format. © Forma tobe veral users can access data at the same | ne. Security features can be enabled very ['W/may be difficult to enforce Security features. sj may’ cause problems. Database Admin rator Ale A person who has central conirot 0} ofa DBA include the following: he system is called database administrator. The functions. (1) Schema definition - The DBA creates the ori I database schema by executing a set of DDL statements. = = (2) Schema modification ~The DBA carries oul changes to the schema, venthouyh Tsolariony trangection Shootd be CHecuted sewiary EVO MOLTTE mrowine AMT care cxcrutedi accessing (3) Granting of authorization for data acess ~ DBA can regulate different_users- ng paris of database. eer sesh | (4) Routing maintenance - (1) Periodie backup to prevent loss of data in ease of disasters (2) : ing ou Ensuring that performance of the system is not degraded and (3) Ensuring that enough free isk Space Is available for normal operations. Database System Architecture/ Database Components (Beets (Sapiro ipatawe eee 13 ere ho we © Ge CRpniiaitig RARER TW Quen), Mam ymisined \Rinteeeat orp eaed ] Jon ja) FOU a aby prerds, Le “sect PETRIE S [emma ives =] i Query processor reusien eorrones ey seadegeny ease =| tet pute VP pete Stemese 2 os PN tices sdirtenary 4 shred Melody ow WL, [Badal ‘ Aurghiear dukt < Aedigi & seo The Following are the components of DBMS syste (2) Storage Manager — The stor updating dat in the database. The varios comin: ents ef slong arn Transaetion Manager — It ensnres that diab + Authorization aud In comstvaints and che © Buller Manager = 1 © File Manag nse rertains in consisiont state.—t § ~ tests for satist tion of Vasious intearits¢ dude ce S23 Foto TU a> 2 (2) Query Processor-~It includes the following component a + DDL interpreter— which interprets DDL statements eee easy £ DMI. compiler ~ which translate DML stalenfei ints (SW level languages. “SSNS * Query evaluation engine — which executes low lovel instructions generated by the DML compte. (8) Database Users — There are four different types of d + Naive w: one of the a programs, * Sophis they form Med users — They inter ~ They are unsophisticated users who pplication programs th: + Application programmers — They are computer professionals who write appl query language. applications interact with the system by invoki have been written previously. alabase users: fa ion ract with the system without writing, programs. Instead. iF requests in a database hee + Specialized users ~ They write spec such as knowledge base and expert DEMS which monege emtive Rous al dda = d ROBMS 2 ie other AuPe of Ext insertion ~~ etx Wows the data ow How fast data shovid be veixieved tevatio? dwa iasericd Foto the database DBMS ab Name suggest tH deaiy WAH veletions G Various key comstraints! we huve dobre we have yows - dams Cm a &e Data element Meed +5 access Tmdividvony Seeoris is Less Me Aciartionshi hiw data, Wet support Aistvibuted dudal Stored ater On Hievexcicat ere deais whe STON quand ey 2 hewe ) s ? ab hte RoBMs rv gdeted AIG a8 | Aabolay fowre J meltipre dudes DV TONE Secor} ~) Dug shoved iD Jobve deren cunich arte reluled Yo euch oA. > suppers - aa yet which ws cared Schemq 4+ canted tuples . msot, orucie elements com be accessed C3 Sume xime Provided header ane CoyurNT ynume @ Yow coniey Corresponding Varuey =) dears Lh Verge amour9 Rewskes heheh —foemnisioms iron the vsex — 2g. Jo ano a uses de —exeudeo able gromt— —Popta Devon —lergueae) end ed hse —sbse oo yeule—Jotle bo - Seecle le _cliedtn She dudes “a_ rank _—_pextnissin Ae dorep rier td chumge_sinecloxe of the dik Juble bab eens ££ $$= Double lines — which indicate total participation of an enti Double rectangle = which represents weak em . ina relationship set set. ‘Types of Attributes - (1) Single, and Composite attributes — Single attributes are not divided into subpavis. are a Uribate name “Simple. Composite atiribntes can be divided into subparts. For example. an at can be divided into First names middle aame and lastname. An attribute address ean iT zip eode. (2) Single-valued and mufti-valued attributes ~ The loan_number attribut ‘nitty FeTeRs To crily_one loam number, Such atiributes are said to be employee entity set With attributes phone_no is a multi-valued attribute divided into customer_street_ custome (3) Derived auributes — The vane for 1 Other related outributes. Kor exampie, an aitribute a: and the current date, ae of attribute can be derived! from the values of can be derived uper Key — A super key is 9 sol of one or move atuihutes that. taken collectively allow us to identify uniquely an entity in the entit et. For exampl customer id attribute ofobs igute Unique, Tent; ey a supe in tabe Cramer city. AGC) 5 u watt ut se) nt to distinguish one customer entity from another. Thus, J is a super key. Similarly, combination of customer_name and customer) is ipet hey for the entity set customer. The eustomer name attribute of customer fs not a super key. beeause several people might have the same name, sume [ wner_heyis_calle_eaudidate key, Both feustomer id} and Mani teary customer address) are enndidate keys, {customer 56 F customer_name}does not form a candidate key, ISS FS customer hot form a candidate key. Yer re equally eligible (9 become a primary key. Primary key cxgte Regine ; Rotl Wo C genevesed ) Pusspant to ary key ~ All eandi is unique a WeakEntity Set NWealdEntity § An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a priniaiy key is termed as a strong entity set. For example, children of an employee in any organization are given some benefits like tuition fee, medical expense ete, Existence of child depends on existence of an employee. Employee is identified by primary key, say employee_id. Here, employee is a strong entity set, while child is peste staemerd Rnseseenes) Foutee eck ve . ye REM Bis good 2s gemera\ stokemend Specialization & eralization \ aot se ee 2 ecalzatio PERSIE requnthey veau ced ory /li\defines relationship between. @ highe? IgveLentily set and one or more lower le AY students are goed sets. Spfcialization is the result of taking a subset of a higher level entity: set to form a lower level sulify-set. Generalization is the result of taking the union of wo more lower level entity sels to a 0 osS produce a higher level entity set : SNE masse ve P pesorvec| Vat) x OM} we used. : Rid i Shodem* \Course \ eYeduncies reduced ——— F dont atlecd other table oth Whe chaumging in ome IeeeIn above example, reserved_ticket is a specialization form of ticket. Unreserved_ Hy specialization form of ticket. Ticket is a generalization of both reserved and unreserved ticket, Aggregation vec te age iressy, agen An aggregation’ allows us to model relationship with relationship. tt is like structure within structure. Agere ion is like a nested structure, Suppose we want to model “the set of books wed by a teacher to teach a subject.” ercoress returtons) HP oseony we cern ep te ogee gation FU] |e fore cart sieles) rae work) , He res) one, eat. represents the number of entities to whic ation ships set. The mapping inalities must he one of the follow (1) One-to-one = An Jan entity in Bo Esamplez- A customer with single account at given branch, et et _ Aerosoles re i salve @ mens pe med ~d— mortinic GER oun | perenat loony Oey to 08e 2 NEM PAY Nel EPOUP 180) Pexsomey vou ae Jo nun] 2. MUHIPLE Pavavel PEYSomMAL Loans \ @ one Av one ey MON pauyullel Peroo nc \oant ii it ath mg Wi@) One-to-many ~ An entity in A is associated with any number of entities (zero or more) in B. An entity in B is, however, can be associated with at most one entity in A. Example:- A customer having two accounts at a given branch, sry in A is associated wit ed with’ any’ number (zero, or more),of entitie: - G).Many-to-one = An ° ti however, can be associ iY inh! 02.193. ina,‘ample:- Many employees works for a company. MPM Res | at (4) Many-to-m : (ed with any number (zero or more) of entig in Band iated with any number (zero or more) of entities in A, Example:- Employee - 7 ane Employee works on number of projects and project is handled by number of bprvee} / 4 * speciarizution & teemerullzationL | GENERATION 274 ish process of grovping oust Aevel ermdities Ante hrotk Cugegories Cher led) emriay) bused of Commi adda e i FAs wotlom ve appreck | Common cairiyd tvs of bie) €-13> CGseD C=% ditfeventd emriaies Ro ' Ae basis Quy ccomeralr liz Wo 4 > th SNS Ruy Creteveal specie Xe BRE Prutesl sec eme vusiiation mreauier ial Higher level ems ity) ok AG ANAS based oo | Schemu of Aye ker gy Baro wey Weve uma Compo / nn copecian) castvibuls Pomerds, i er dow eRerUCe Empior £65 Gru meyada,nb, £-ikd Trey ieenas He Schema b Customers (mame, aay ne, ¢ i) UF ErMerging Fhe COMPOMETS, Fewsem C muMe adds)Database Jnvdels & Cok 2 poy g | get of nutes ond slundurds Phat delime hew the aiubase organize duke hs calied dutubare mede wpe of dalubese model ¢ o y Higavchical Pode} yoNelwork model } Relational poder A jeraychicul Pode the hieravehicul Model arvenging Records oon Wi everehy Ae om organizational chard. ' uch Accord AYPT Im this model Ys Gunted 6 Mele ov segment. “A Hede Represents | 3 | Paldtcutaty Enitiy, Furey , The tp mode is cawed “oi AoA J , . Each Mode is) A gubordimare of ‘the a ed is adhe mer highev \evet owney=) mamagey ~) supe ruisey > seen Higher (eve) pode is cutied pavent and lower eve dels cuted child A pavend node cam have ome ov MENY mNwede} A child mole can have ont] one parent ‘node. 7 : Shoes | men’s a Va Qemen's a SS rah =) res | sneg]Ry Nelwork mode} > - A Network model fs gimiluy jo Wiewarehie is : ~ othe difference’ ig shut child node cane oa ay Wen ome Parentsmede . “a ~ he chitd mode ave Aepresend by STO US sg — F_ vequire move compler diagram +o YEP Tes eaf \ - . 7 ! uw Tt is he advance Vewsium of Wieravchicey | Presect ; Sy — = Hl } meet IY. S (Protect J v —— Semriment 8) [Sa B POepantment 8 3> Retational model F VOeEssme Sn Yo Used dade, si model Ne SWC Aclations | (sa Aeem used fox Sable Teprese ms a purticulay Entiny Y or is USed +0 “stare et about the & ~ Relational model is the Mos) comment ~ SF is move pevie thea Nierarchic FRetutional moder comsist of : Y Retutions used Lore VY Revation, Coli nu sons] > : a a tuples > ‘ ' _ C Xx OMeremce SMO da model ) Comslraint so =e Domain entity] Grrlegriny| |Reterential ¥ Coms\ruint 5 dq degy ity COMSHy aris Domein’ “Constraint Domuin Comsicris Cam,’ keLenes, (Os. yee detinition se vad sed of Mawes few on ateribuie. | jhe datuttpe of domain. mciodes shring, char cket, Sm AEGer, Mme , Adte ekC. — . . Naive of the ativibule mush ibe avullabic im the Hot allowed ‘because aye is am Amnicgey AMT ule. . Nek avowed bersuse mame is an a cudy bute doregrity Gnsi\raind 4 Nalwe coms be Woe This is Yccouse We dently Iradividval wows Yn elation and S\uies Whud primer} Wey primer, key Valve is. used +o UW the e RdemtF Primer, Ley has @ MALI Valuer fen oe eugene Vasa] Howse yous. Rabie cam Comat a UI gue okher Phan {NE FM fiend.- Ps ___——srapatiame { Sutuny” ay bf Zmtio | emPAame (Susan - 133 Kame ge5oo a oe nits Bas Woven — 900d © = set} Ai | geese 7 Lame, gsD000 Do agape ot ad Wot Le allowed ab PK cans comsarn a nuit aud res @ Relevential “Galegety Consvurnig | 4 " “As speciticg |) 3 y Fws ie i eh — Jn The Acferemtiad imdegiry Comsixaiensett vg 4 ym juble a velews Yo WHE primany Rey Mey _) = Mem ever} Value of the foreign Key, im, Aaa e yy musi ke Hert ov be avetabie Ano tebre o. : PR Tablet URE ea12b _ i “Not aiyoved os f) Te wok delim, PMT |e ey of dehre 2.0 Soble} eNe 188 Loweignlt teresKey Constraints cia ers ove the emtity set that is used to Wdenttty an end Whi Js EMA ge4 Umniteerr} Aw entity sel can ; : hove mvrtinit ket, pot out of which ome ey WIN be primum key, \ pramay KET corn comlain A Umique Vaue Len the Leabie. ww clude) \eaven Ravi Ram Neevare any Hel atiowed because ary vawe af Primer bey Must be unique,ig. 365 Manptoanyritonsh EXE Representation of Role ‘led ity role, They are ‘The fncion that an ey plays ina ear Bs won be mang ei anes nin For camp the elt rkfor might be coed pas of employees (Gt Manage second is Th the ER dagen, this cn be sawn by labeling the Unes connecting ent (cca) to ltr (Sond) as shown in Fi 367 ==> [a =O ae aD Cea, Fa. 267 EA gram wth ro Inston ‘Sample E Diagrams VER diagram with an atbute attached to a relationship sot 1 0 manip set fas some stibutes associated with it, then we ine tsb fo tat raonsp st For eample in Fig. 68 the acoes-date atibute 2) € diagram with composto multivalued and derived attibute ‘As shown in Fig. 369 ‘Name’ is a composite atsbate with component atbutes ‘nt name, Lastname. Simarly Address i also a composite aftbute with component [tstes Stet, City, State, Fin Pho is a multaledatrbute and Age isa derived seb. eiars> + Cetans> sear pe sue > | Ss ect ‘ects — a = PhP Student to ED Perey mee SAaaresm c= <=>) a> GD Fig. 289 ER dlagram with composi, muttvalued and deve stb 3) Total participation of an entity set in a rolationship sat [As shown in above Fig. 3610, « double line from Loan to Borrvter indicates that ‘each Loan must have Jest one aerocited customer, aco i se) ae a ee mi oe an -@— de Fa.288 ER darn ws nse sacied o9 FONERALOEatyameg = Fig, 36:10 Total partepation of an entity set ina reatonahip setwooas jeostteatioN _ tnd emomay rhark oy 1s a techniyue do etiove from Fable. &simprity ane ' or Yedure edu dune 7) Love Veved dupiic 7 oy ® Columme level \\ Ly sor t sea PHIMUR) Woy -_ dapticany Comic 4 mot muir) SF9/Smemd cis Coriden FI0{ Frond Sat \ “sep _s-meme_ Ase as fae fo | Ty Ram B85 3 es | QR Shue QS 4 : [e Rem___2o7 i ; ; O Tasexion Rromur Sie @ dDeretion D Vpawien 4 1) porter New Skudend a duet | add SAY pYOh atl ares Souk = = as : eV ai tie ue sob ad vari accra a) AH AR aad seve 20 $4 aysyet 2> Belete Faw Studien’ LUhere $102) . SNe pwh = “> Vetere frosa stosent\ culhese sy: 2 Uy at We remave Amer, $2 Ferenc sewn} course Aelall Cegdet) veenuve aay ¥ RECOVIOL WON We S199 0 alEL, B-y FLA sav vpdde Sat gov yo Gok, A wall ere doud MW usr. UPAUse ‘Slodemk Sed Snume Som{! Where s10s3 ees S)D-Symame.) Table ) = aS Po Feo Crome S12 \ Rap Rene Suny Pak GB© aye The Seturion Soin One, tf > OWNY omsite, ane simne Teme should met comauin amy motiveiued fil Role Nevin ne (SRE foe ne? PAWS | ume) conse | coed {fem je pes ee 3 feel 2 P= aawnro ai Pantie ears 1 | Bs is a fe juse tebe we a al Ref tobe Cee FE Ease & Grails Sy - tre lose of keq seniwey tentiht chetheie, woken Penne theeeNa) Ccawecsiom af cat Sheth wae det = minions sot ot super hey anigfia Aevetien in | ane ae 2 a tose ay MME TE te ayy TST UME ad every erums-cicilte sb 88S fy Enter dgsnton en Fasiite tag, How Plime aulivien con site cas ot ony c i" serait §s-m0} een element . de Kconsiter Pottowion . ny Jelatiog | eateeectonll cies FT i ; 4 os 4 3 Boe odode 7 men : cumdlisde Fey Leb coterner fm prime atibeke gga 107 Fe ADS here “atone customer ap aoe ates er es eh SMF Hecate Ret Semoty toca eur _ tition ts leet “fenuionuny depontow enone te owe th as in TNE O Be wen Me went) dependent on other nun: Prime aatvibl ie ts Remuvienanhy_ dependien : ee rots he @D wesitive depenteny ta bahie: > paw de nmol stude| cits ex = enaimey, Fo> BONIS Fone Sos | are “aawies 35 sa prime aad-s Shake cht 3 |rStiestl pean Nom Tilo cab > ate 3 fet fora con) freon] Rk | accenantan gucci 2 fends = noe wre) wwe eves) seate iyart ne Bay guns 1k Ferenc he ot i) wy traning le& suren eeyy) = ; G Super Weds $5 a combination ob aut peutne aaa at Se ny Tae) ew teh tm ene Keates AL Ss Cecemaiduie Key + ary ottey attrttute J siren Patel tee ae Coe Ratt Me, SEP ES oe iA Oe monte oe B GAME (Race code Norman forma) ah dduvied &@ foam Rent -H lee Ah COP eee us hod em ky When al mast ea super Keg. ond in AHF Las F aanriol mame have most ee vetego| age ox. E Rune voles} ast wtese3] (rewrion Te ssid As Be I aN Bt asec fy \ Seat hut Geol mtued dependent ont ws em 4 veritie | debe ) wu, Fraveti | Ne mayer wo murati PLE Qurens TaN | Gat tel maser ved | pe eee eee ta | Sumter [ROE | Butes | Scorer [eae fume! |! ee Given © Selution ® Wh iivibotes AD se, | the morti-weived depeniency A} RD hous: be ae | ema Only Hh the seh ol B Uae depend or 4 ieee oe wot is independ A vutvg | 7 we ES Toe gan Helen fom cuewep | 7 sO a ucnicte | deme; i soe ead? een | Be, vote PLS of cay FO Should be CAL. oF 5. peat ere rena ae Soe | Ree ! (fo Bw YD. Vene— MVD Beh | Rug Genie} PAVE TEL / a rn 9B . ard an 4 : fa Biome. De season is omsioren) eerie ie a ste Se MOTT Lain te Symateny Ate | ee cee fon Wend BE Godereniey { {Qe cs Pie eee ®—e-o 7 ise pene rokel Q-©O-@ es verse nal Sewer . ]Seciews Frere aI. : dept Ive 3 |m4 6 wee o SNE > There ove some Selusions , whith comnot be decomposed Enle Awe! _pwiectigs, but These Terutions¢ AS en i ; i Combe — dewrposed tale Anye'e _Prwiettions ithis is in — ComBidey Ahe yeyusion Deller — Parts _ Custom cope) = Above yeluiioN is im ANE, There are no multi Vulved dy = Decompasition ot Hhe abuve yume d a Ceve-D sanes3 CRS DP Teasien Re svete CD wel\akion customers | Deuter 7 CN. | 0) 1 C3 “®) c) D2 cms camila one Exty TPC ho pa poring TH2O TS WUE cup moh gexies ov orgy ow revadio® ay b) c) bp c) ye Lae we join TS 2 UY ~ we decompose the Fable into ee Sa eels @ priest. hen we Jom 13, B44 we gut org lure i war WHE 3 ee Twe Prjechon it ] BWe buck orgy. weyuth™ | “Tia { WAYS is tamed Join, Ae pendent@ wher is decomposition escpuien aid te oF | | Aecompe sition, f —> Decompositiwn 8 De composition Ys dhe pwrtets ol! VA oH breavin § dowp given de\u rion Saito two OY Move eAutiory, A. Coch News Seiution ss —srepared_b} comidins Subset of the asttibvieg of R 21 de Te gether, Shey ait fmeivdes ay pu ples & cud dv: oe Comsidey Selution flccount! Brunch im Fig. Account. Brunch Co, Shyumnager ef ; ompuns @9 !' shyamnugar AB Rurmnuguy lL, ws deludion can be devigded Ste two teusrey. WD Account CAnv, Batamce, Brame) ] @ Brunch ( Brome Baddvess) Aeris Q®- mane Benume | Bcd Rum Sa AS = fia Rg segura gy Oariddgay | MEF Rérenuge 2 om pure Shyg mepagar| CD: Shyamnag. . ie Shyamnagy Omesva EF, OMPpuTE Ramnagay > mares [oe retell fara O besst Decomposi tio @ Lossless C-mom-les3) Decomposition 1» Loss} Decomposition’ fae! _ the decomposition of Serudlon A fnio RL dD Ra es Lossy ewhen thee Join of BG RA does mot weird game Aerarion od & \- alse caved loss t-jorn decompositionSon formu fion geo Xs ay decompo sixioD Aes} agus eae ane and 3 Joss-less Decompesitie | —— oy ot Actarion, ot Decompos vie \ossiess Whe dhe joi Aguion as i Rk a 4 arso cued —— wn Goee Nan o | Ksaad ef decomops® counmact ger omginul tule _ | mom adative Cnom-loss RS \ost . | gato By oR Rane R\ o- wa Ss a RV & RR produce sume ") decom post Se pis Te at fake LopteS rave gemercied | J ce & | Be) : fecoomnd (et. Sein ed / > ‘Bodh \ Daf Rsant ca j j ee 2 | [Asus s EELIT Slovage Siege ES Storage Svs “28 Gatabase Index? Indexes are g et ed U| data retrieval” lal lookup tables that the database search engine can use to spet iP ove dna tetisa data structure that Improves the speed of data retrieval operations pees 'a database Is very similar to an index in the back of a book. The use are Used to retrieve data from the database very fast. TS Cannot see the indexes, they are just used to speed up searches/queries. Updating a table with Indexes takes more time than updating a table without (because the indexes also need an update), © Syntax: ooee CREATE INDEX Index_name ON table_name (column1, column, .. * Example: CREATE INDEX idx_studentname ON student (studentname); Indexing is a way to optimize the performance of a database by minimizing the number Of disk accesses required when a query is processed. It is a data structure technique which is used to quickly locate and access the data in a database. Explain the structure of Index in database. * Indexes are created using a few database columns. Search-key Pointer The first column is the Search key that contains a copy of the primary key or candidate key of the table. These values are stored in sorted order so that the corresponding data can be accessed quickly. ¢ The second column is the Data Reference or Pointer which contains a set of pointers holding the address of the disk block where that particular key value can be found. Explain different attributes of Indexing. The indexing has various attributes: © Access Types: This refers to the type of access such as value based search, range access, etc. © Access Time: It refers to the time needed to find particular data element or set of elements, « Insertion Time: It refers to the time taken to find the appropriate space and insert a new data.. k sats Time taken to find an item and delete It as well as update the Inde :. Space Overhead: It refers to the additional space required by the index. xplain different Indexing Methods (Types). | Different indexing methods are: * Primary Indexing > Dense Indexing > Parse Indexing Secondary Indexing Clustering indexing Primary Indexing ° If the index is created on the primary key of the table, then it is known as primary index. ‘These primary keys are unique to each record, As primary keys are stored In sorted order, the performance of the searching operation is quite efficient. Student (RollNo, Name, Address, City, MobileNo) [RolINo is primary key] CREATE INDEX idx_StudentRno ON Student (RollNo); The primary index can be classified into two types: > Dense index > Sparse index Dense Index aca lIemoe | Rno Name 101 101 | Raj s 102 302__|Meet_| : gj | 303 —+—>) 103 ‘Suresh S| 104 | a 104 Mira 70s 105 | Nita 106 ——} 106 Jom 107 107 __| Alay 108 a Amit¢ Indense index, th / ere Is an index record for every search key value in the database. This makes s¢ arching faster but requires more space to store Index records. In this, the n main table eet Of Fecords in the Index table Is same as the number of records In the Index records co! Sparse Index 5 ntain search key value and a pointer to the actual record on the disk. Rno Name 101 | Raj 102 Meet 103 | Suresh 104 © In sparse Index, index records are not created for every search key. e The index record appears only for a few items in the data file. « Itrequires less space, less maintenance overhead for insertion, and deletions but is slower compared to the dense index for locating records. Tosearcha record in sparse index we search for a value that is less than or equal to value in index for which we are looking. « After getting the first record, linear search is performed to retrieve the desired record. «In the sparse indexing, as the size of the main table grows, the size of index table also grows.ndary Index In secondary indexing, to reduce the size of mapping, another level of indexing Is introduced. In this method, the huge range for the columns Is selected Initially so that the mapping size of the first level becomes small. Then each range is further divided into smaller ranges. The mapping of the first level Is stored in the primary memory, so that address fetch is faster. The mapping of the second level and actual data are stored In the secondary memory (hard disk). If you want to find the record of roll 112, then it will search the highest entry which Is smaller than or equal to 112 in the first level index. It will get 101 at this level. Then in the second index level, again it does max (112) <= 112 and gets 111. Now using the address 111, it goes to the data block and starts searching each record till It gets 112. This is how a search is performed in this method. Inserting, updating or deleting is also done in the same manner.stering Index | Dent | Name ce Raj ce [Meet ce Ee | Mira EE EE [Nita ec ME [ec [Ajay Ec [Amit Sometimes the index Is created on non-primary key columns which may not be unique for each record. In this case, to identify the record faster, we will group two or more columns to get the unique value and create index out of them. This method is called a clustering index. The records which have similar characteristics are grouped, and indexes are created for. ; these group. ixplain B-tree. ¢ B-tree is a data structure that store data in Its node in sorted order. * We can represent sample B-tree as follows, l---— Root Node Intermediary Node me, Leaf Node s. ss, ¢ B-tree stores data in such a way that each node contains keys In ascending order. © Each of these keys has two references to another two child nodes.The left side chi ° child node keys are less than the current keys and the right side child node keys are greater than the current keys. searching a record in B-tree ; Suppost . ve i" weeny to search 18 in the above B tree structure. , ill fetch for the intermediary node which will direct to the leaf node that can contain a record for 18. So, it : = in the intermediary node, we will find a branch between 16 and 20 nodes. en at the end, we will be redirected to the fifth leaf node. Here DBMS will perform 2 sequential search to find 18. Explain hashing with its types. © For a huge database, it can be almost next to impossible to search all the index values through all its level and then reach the destination data block to retrieve the desired data. © Hashing is a technique to directly search the location of desired data on the disk without using index structure. © Data is stored in the form of data blocks whose address is generated by applying 2 hash function in the memory location where these records are stored known as a data block or data bucket. Hashing uses hash functions with search keys as paramete! data record. Data bucket: Data buckets are the memory locations where the r * Hash Function: Hash function is a mapping function that maps al to actual record address. Generally, hash function uses primary key to g index —address of the data block. Types of hashing methods 1. Static hashing 2. Dynamic hashing Static hashing Inthe static hashing, the resultant data bucket address will always remain the same. « Therefore, if you generate an address for say Student_ID = 10 using hashing function ; mod(3), the resultant bucket address will always be 1. So, you will not see any change in the bucket-address. . ; © Sea ase : © The a remains constant. rs to generate the address ofa ‘ecords are stored. Il the set of search keys enerate the hashmic hashing The drawback of static hashing is that that | size of the database grows or shrinks, ee alae = e Indynamic hashin, ; 18, data bucke ee ts grows or shrinks (added or removed dynamically) as the . ae sare is also known as extended hashing. e. namic j : aa ashing, the hash function is made to produce a large number of values. :f ae there are three data records D1, D2 and D3. : ue ash function generates three addresses 1001, 0101 and 1010 respectively. method of storing considers only part of this address ~ especially only first one bitto store the data. © Solt tries to load three of them at address 0 and 1. ye is that no bucket address is remaining for D3. te D3, and then it updates the existing % © But the problem i: The bucket has to grow dynamically to accommoda' ve 2 bits rather than 1 bit, * So it changes the address ha’ data to have 2 bit address. © Then it tries to accommodate D3.Unit 7 Transaction Processing n = Collecti i _remsacti ion of opera f a eae ; ancien erations that forms a single logical unit of work is called write(A); read(B); B=B+50; write(B ACID properties of transaction — Atomicity — Either all operations should be reflected in the database or none are. Suppose i value of account A = 1000 and B = 2000. The task is to transfer Rs. 50 from account A to account B. After execution of write(A) instruction, suppose power failure occur or system failure due to any hardware or software er Therefore, Rs. 50 is debited from account A, but not credited to account B. Con: ent Before the transaction start, the sum of account A and B is, A + B= 1000 +2000 = 3000. Afier successfully completion of a tr A+ B= 950 + 2050 3000. jency — Afier successfully completion of a transnetion, database remains cons nsaction, the sum of account A and B i Isolation ~ Transaction should be executed serially. Even though multiple transactions executed congyrently, each transaction is not aware about another transaction executing. in the system. is not Suppose, three transactions Ti. ‘Tj and Tk executing in the system concurrently. but 7 aware about Tj and Tk. Similarly, Tj is not aware about Ti and Tk and ‘Tk is not aware about Ti and Tj. f jl {ial completion of a transaction whatever changes it has made to the Durability - After succe: ditabase those changes remain persist even alter power Inikire occurs:fii icons read(Ay |---| [avrite(By Swap read(B) instruction of Th with write(A) instruction of 72. Tf read(Ay f write(A) Clee read(A) a) write(B)_ read(B) write(B) Swap read(B) instruction of 1 with rend(A) insteuction of 2. [7 2 read(AY write(A, dB) read(A) Wwrite(Ay avrite(B: r = Hea —| awrite(B) Swap write(B) instruction of TT with write(A) instruction of T2. ce 1 Tie $—>s5 Seviatinsy write(B) Se et or Nee AON) f won) AB i we (8) ACN w od) BR ca) BA ) (eT eee read(A) write(A) read(B) write(B) * read) write(B) * by a series oF swaps Of non-conig ling Ifa schedule S$ ean be transformed into a schedule instructions, we say that $ and S* are conflict equivalent. 2) View Serializability The schedules S and S” are said to be view equivalent ifthree conditions are met: (1) For each data item Q, if transaction Ti_reads initial: edule § ust, in schedule $" also read th transaction T (2) For each data item ©, iFtransaetion Ti executes read(Q) in sceuleS mdi th ay ‘was produced by a wrte(Q) operation executed by tra Ty. thea the same s must Gecurin schedule al svite() operation () For each data item Q. the transaction that pertorms the £ schedule S. must perform the final write(Q) operation in schedule & (Seeder i [R sof writetAy 30% prea) - ost BeB+S0 | 25°[ write) ead AS | temp=A"0 Tag | : nadie Li banite(B) y 6g far Schedule | is not view equivalent t schedule 2 nee. in sehedule 1. the value of aceount A tule 2 read by tansaetion T2 was produced by TI. shoreas this ease does mot kok in schesiete abi chedule hus abitty to become Sewalizable | Ty cottection of TW™ Lm ~ Or ype (8) i Kor g)-@ \ ae wn Sateen uyriketti tusethe C8) C possi | ) Crossbiniy 2) * gesiavae ox we? ° s «is 1 Sexier ov porane! Bclhedule 2 TT 2 ss ae Rend, iy { ’ Aca) Oo j eo Y Me ool 3 cook) Loop well <- ‘ ees ; severe de? S al A Tpyoul s% cA BABA ef ecioh SANT wy CONVENE GIONS, ore & @)) yD De ; TT . a ™ ee REA) wont) eo Ray) wan) &hedule serial & ¥ “A e[sedaa Ries Us Mor Prive /gacy as] VS RT T-> TH > TR _ Remus TR =>Ty > Ye ae Ta > T 3% Bee A Say, fs * Comfiich of) Wie Savovah, ffipeiean _S gerio\izeblity out this Prd.1 Q) @aficl sexiatizabitity { } SOS => Comlid __£ quivalemt SS Ren) Rta) 4 Non confuct Pury Reay wo) WO RO) | comficl Pai¥ WO) wen) Acad RU) Elen awest wa SING AL to prob, wR) ROD ae ich paiy Rw) @via) Non - contd) Pal wa) WR) Q@ Simd 5 & sl Fp comfVicl equivalent ov not} SEsh 2 [Eran ad) Mor deter cheek gag g h a earere ee Road i g ooh) Mom Comfuck Pare F. Sweep Position Acap Ss! +» Seviatizal Schedvle a connict eet S$) Exist gang en a \ . S>> Sexial sai.However, schedule 1 is view equivalent to seh read by transaction 12 edule 3 because the values of account A a Were produced by Th i n both schedules, Schedules that are not conflict se falizable, Blin serializable schedule th 1 awrit at Recoverable Schedule read(By Tread) —~|—~ co mens - \ ‘Suppose that the system allow. 72 to commit immediately after executing the read(A) instruction. end sem Blo T2 das: Now suppose ta TL is ase fead the value of data item A written! bY TH, We" mist abére 1 2.' However. 12’ as already Committed and can not be, aborted: Thus w Tere: correctly from the fi ¢ have situaifon where failure of TI. This is an example of non recov Hot be allowed it is impossible to recover ‘erable schedule, which should A recoverable schedule is one where for eve data item previously written by 1 operation of Tj, "Y pair of transaction Ti and ‘Tj such th P the commit operation of Ti appears be Tj reads a we the commit Cascading Rollback - TI [re [ready | read(B) \writetAD jsadA) | le \writeCAY Mransaction TI writes a value of A that is real by: transaction ‘T2 12 writes a val read by T3. Suppose that, at this point. 11 fails. ‘TL must be rélled back. Sinee on TI, T2 inust be rolled back. Sinee 3 is dependent on ‘> transaction failure leads to'a seri OF A that is ‘T2 is dependent volled back. sin: aseaudinscadeless schedule is one where, for each pair of transaction Ti and ‘Tj, such that Tj reads a ic 7 operation of Ti appears before the read operation data item previously written by Ti, the com of Th. Problems related to concurrent exeeution of a transaction x (1) Lost_update_problem — The update of one transaction is overwr itten by another ~ transaction, A and 12 debits $50 fom account A. The initial al correct Suppose, TI credits $100 to accou value of account A =500. If eredils and debits are applied correctly. then f vaiue of account should be 450, We run TI and T2 concurrently as follow: Tl (credit) __F2(debit) read(A) {A=500} read(A) {A=500} | Goo = ‘A=AHO0 (A=600} A= A-f@_(A=450) seers write(A)_{A=600) write(A)_{A=450} i) 2 . i ~ Gu egakecny Coupaukeay adl Tue’ gy. ! A Ace wuwr an cowie 50. The credits of Tis missing (lost update) from the account, a2". § est & quecwriten Cccur, Final value of | Uldate of Th gp: 2 Dirty rea_prohtem— Reading OP's non-eXistent value of A by T2 ITI updates A 32 Which is then read by T2. then if T1 abort ‘F2 will have read a value of A which never ~ existed. THeredit) T2(debin a x va ges et cleo goo oF Se TH failed to comm “ ~ f Ti modified A = 600, T2 read A=600, Bur 1 tailed database. Therefore. A is restored to its old value. A: value but read by 12 id its elect is removed (row M1 A =600 is nor-esistence wetion to access a data item only iF it is eu ntly holding a lock en that Two types of lock: