classmate. © Date Pepe | = +r a Unit = 1 et Lotreduction TS Database Management eS System T By @ curious Programmer ¥ || Lotroduction= + le In today's World as the information techno- | legy has changed Fapidiy, many computing applications deal With large amount of information reguiarly. le As the end user applications has changed significantly in last Few decades, +here is a chottenge to store the Large amount of information » retrive and manage this Tefomation in timely manner. |e This ¢29 be achieving Feoday By making i; use OF services of Database Management system COBMS). eA is Database Management Systeny aly =m) is A software the database,classmate, le OBNS Provides an interface +o perfor J Various Operations like database creation, storing data in it, Updating data, creatine ea tobie in +he database and a tot of users there are highly chances that the data might not remain consistent because +oo mony Users might +ry to access ita+ same e and mossy try to chonge the vatue, Curiotts—. pregrammer The DBMS mus+ ensure that the chances 7 — getting anomotous results when theHistory oF Bo atabase Systeny:- ry The fellowing are the historical perspectve oF OBMs system: 0 On Early 1960s ,+he First generad purpose DBMS was clesigned by Charles Bachman at fenerar Electric, Which Was Later, catted as LOG (Entegratec| Data store). e This TOS formed groundwork for introdu- ction of NetWork Data Mode|, which was later, standardized by CopasyL (conference on Oata systems Aanquages), e@ In late 1960s ,IBM Developed the Fms (Information Management System) Which was widely used. le This DMs Formed groundaork For Trtro- oluction of Hiewrchical Data mode. @ Curious —. programmer By the joint venture of of Fam and American Airlines , the SABREE Was Jaunched Which help the people +o reserve +the +ickets. “The New Data representation Frame- Worle Was Tnitioty Launched by Edgar Codd) called the Relational Data r eH, an arePurpose of +he Oatabase System 04 database system provides a data | defination Language +o specify the database schema_and a_ data manipulation lonquage ; [+e express cdatabase queries 4 Updates. ¢ One of the matn reagons for using DBMSs is to have central contro! oF both the data and the programs +hat access those data. le fo see Why database management system is Necessary ,4Let us look ata typical “file precessing system” supported b conventional operating system. e-9- ! The application is @ saving bank | @ Saving account 4 customer records are | leept 19 Permanent system Files. Ty Curious.. prggtamm er [ © Applicaton programs are written +o | manipulate Files +o _perform following task. t AQ account, a Te add a ned account eurtr - © Find the account balance Tee Fi rs he monthiy Stacteme!e System 5 Programm ers “wrote +hese application programs +o meet +he needs of the bank. 7 ft e File System has several disadvantages and +he Following problems are associated [oien file system: - 1. Oota redundancy and in consistency: e The _ major problem With file processing system is that it macéntains several versions oF same file i-e; duplication oF dataiiis possible at mettiple Places, changed sthe different versions of same File may Not be updated which leads to in consistency of data. OCnicus= Srey amer 2. Diffilty in accessing the dato « Consider the Qérline reservation systeny . IF the senior management of company Wants to access the information of akits customers Who are living in the sameMWwformation. | eso in the above case, there are +wo | options . Either the application program-_ [mer has +o Drite a new application | Program +o satisfy +he usucg request lor coulda get this information Balen says\0| y TF a query changes , a New application | Program should be Written +0 get the { | needed information. | One of +he major problems with the File system js that +he data is and in different formats.concurrently, e Suppose +00 users Jocated a+ different Jocations wants +o book the tickets, there might be situation that both of +he people will be given the same seat because +the data is stored in multiple focattions and both of them Will be given a seat From individual copy of the data. 4 ¢ Therefore there should be some protec-| tion mechanism +o avoid this concurre-_ nt updates, 5. Security problems- Every user in this system should be able to cess the data which he is access and not aut the he salesperson in an J Lowed to access mM and should not6. LTategrity problems- e Data stored iq +he database should’ be alowed +6 sotisFy certain constraint checking. «Fer €9. before adding a new employee 19 +he Employee table, if we check the age -F the employee and iF we apply constraint such that whose age is be otlowed +o enter in the table Which means +hat before +he new data is should be calculated. J. Atomicity problems = Every Application System is assumed +o fail at some point ign near Bins many applications , pelene syste) Fails ,+the tt back +o the state before +he Failure Oecure. gee ia pane inserted the age of +he employee Future, | only these employee grater +han 19 years should | T 4 4 + data Should be rolled 1 5 - customer is Withdrawing some ATM Machine Fr een not A ©a | — i account but +he customer is Dot ge HiINg any cash from +he machine. eMnother disadvantage with File Seing System is that it becomes Foal iat? Healt +o ensure atomicity: (Advantages And Disadvantages of Aotabase Systems - erred ever +he The O8Ms is eonven tional proces sing sys tery due +o +he FolloWing advantages + 41. Centrolling Data Redundance — @& the conventional file processing syste , every user group matntancns its own files a may Lead +0: For handaling 1#s data Files. — Duplication ef same data io different I Files. hJastage. oF storage space since duplica— ped dara is stored. elas in entering data agatn and is wasted. a ~OGurious —. Programmer¢ —thewiee ' —— |. Elimination a | — |e der Us consider +he Following example oF student: at oF Onconsistency: = Mnagine thot a particular student has opted Fort Embedded system as one of the elective subsect in sem-v for ITYBSc FT sem v examination while filling up +he examination Fromm. a | ah -TF, after getting hottticlket that +he rather than expecting Embedded system 25 +he choice of elective subj in the hawt ticket, iF some other sukyect it means that the data _— On centralizing the database +he [sep iecttiod will be controiiect and onsistency Will be removed.manner because our existing sy: a —— 1 Het capable +o produce +he © Centralizing +he data in the database a1s0 means that user can obtacny neo and combined jn formation easily that wowd have been impossible +o obtatn otherwise. e Also use of OBS should attowW users tat don't know programming to Onteract Dith the data more easily, unlike File processing systery, @Dhere the programmer may need +o Write new programs to meet every neO every neO demand. 4, Flexibitity oF +he Syster) is Ompreved : ince changes are Often necessary +o the oF the data stored jn an contents |_ system + +hese changes are made more in a Centralized database5. Integrity can be improved: @ Since data of the organization CH clatabase approach is Centratized and Would be used by a number oF us Tes ata +ime ,it is s tick +o enforce iotegrity - Constracnts, For Exompie= “The £xample of Hatl Tick Generation systery that we have ctready discussed s since multiple Files are +o matnteined ,so sometimes You May enter a value For subject moy oot exist. Suppose Ele ctve Subjects CaM have values but we enter a value ‘Mathematics -' For it, it ™ lead +o datcbase inconsistency, e Even iF We centraliced +he database i+ may stil] contaty incorrect data For example — Salary oF Full time cleric may be entered as 1500 rather +han Rs 4500 | by eFining +he validation procedures The above problems can be avoidedSag -_ i 6 —i —|e Standards are easier to enforce | in database syste because all the — classmate (er ») 6. Standards can be enforced data in database is access Fhrough ‘2 centralized DBMS. T je Here We can rete +o the Naming of data, Structure of data, Format oF the data etc. e Standardizing stored data Formats is usu desirable For +he purpose of data interchange or migration between systems. 7 $b. Security can be improved:—T: Fer example ,a cleric May be given the authority +0 know only +he names oF ott they customer Whe have a 10AN in -bonk but Not the detot}s of each | oan the custemer may have. le This can be accomplished by giving +he [privileges te each employee. + 8. Organization's requirement can be easily identified > 0 All organization have sections and departm- Snis andveach of these units often consia serine work of ther unit as the most | cel ° od contro), it Will be necessary ||te. ident organizations requirement and +e balance +he needs oF +heof se ting up of database “systern is or’ the requirement +hat an overatt data model For an organization be burid. GJ conventional system, jt is more lilkely +hat files ill be desined as per Need of particular applications demand. e@ The overott vied is often N0+ considered . uilding #9 everott view ef AQ organization's data is usucd ‘= 4 cost effective i9 +he l0ng9 ers. 40. Provides backup and Repvergi ————tt e Centralizing a database provides the Schemes such as recovery and backups from +he Farlures inctuding disk crash, power fatiures, software errors Which moy help +he catabase +o recover from +he inconsistent ~state +o +he Stoyenthoat existeds priory to +he occurrence OF the Ffallure » +hough Methods are very complex. @Curious_. Programmerfoe ( A = = = te ~ Disadvantages of Database] systems = rien! “The following are +he disadvantages oF Database Systems 1. Database Complexity “The oclesign of the database system is complex , oiFFIicult and is very time consuming task +o perform, %.9ubstantial hardware and software stort -up costs. Huge amount ef investment is needed, +o setup +he required hardware and “Fhe software needed to run +hose applications. 18 Damege to database affects virtuatty ok applications pregrams | TE one part of the database is corrupted or damaged becatse of +he ware or : Faliure , Since we t aS = = 4: Extensive conversion cost in moving From =. fletAs - i a File- - based system +o a Jotabase * system —_ =. - a , © correnty Worleing ©9 ELF you Hi Fire based system and need +o upgrade I+ +o database system , then large amount SF cost is incurred iF purccha- ing different +ools, adopting different [techniques as per the reduirem-ent. §.Taitial -+traning reduired For OW progra mmers and user. ae Aarge amount oF humans effort 4+the time and cost is needed +o +raiq the end users and application programmers in order +o get used +o the database systems.“Lntreduction To Relationo! Database Management Systems By: Curious—. programmer Loatroduction To Relational Database Management System: le A relationak BEMS is speciok software that is used +o manage the organization, storage ,access , security and integrity of data. | | | This specialized software allows application system +o Focous of +he interface , data | validation and screen navigation. | kihen there is a need +0 insert, modify, delete display cata, +he application system | Simply mnakes a “cat” +o the RoOBMSclassmate. InfFoTMation in each of whi hasa UMIQUe identifier oF "primary key” L BRdbms are Widely used in real life appis Such as: 1. Firlines: ttcan be used +o keep the Flights and schedules and For anc canceilation of +ickets. 2. Banking: Ltis usefu\ tin storing the customer information , account detalls , loan details and banking +ransactions. 3% | Universities: Ttis useful in storing +he student I information, coarse reqistrations, grades etc. The Relational Model le The relational Model is a collections of relationsclassmate. G e Th relational model , each row in the table consists of set of related data values. e In this model, each row in the +able shares some reality Which corresponds +o the real world entity oF relationship. . Every 4+able and +he columns present in the table is given a unique +uble name end column names Which can be Used +o extract the relevant values From +he +ables. Consider +he TDOLSVIT table given below: A Relation Table name! TDoUsy = _ Attributes S-NAME (string) Contact no (gn) Email(s+ing) 5253461512 s 1913 9246je A trow feF recovde insbhe giver helble is eal as tuple. In +he above example , the [individual records For stucients starting » 4oith [1000 +0 1005 a e In above example ;the individual columns are called as attributes of the system ss = _ _ JeThe table itseif is catledsas +he relation. e The data +ype describing the types oF values +hat san appear in each column is called a domain. . | je GM the above example, iF We define the Teich OM> scherna it Would look lilee +his k IDOLS YIT (S-FD integer + S-Name: String, contact not integer, ematl: String) Se atotats iRevery rodin a se “Fabie —s7 - peo C | A ae _Tatreduction | To ~S6Ql _pkero? wyetivieianetaseiepel Sarimeens oo enor e Programming language and its popular nes Popularity has explored since past 4.90 decades. je Ot tes porte Dleminnoudgen 4 SHS 4 P poris Tight From mounframe system to Personal computers and even +o hand held | devices. oe HE | eTSday Most of company’s Software products _ lie of s@r For its data management and | s@n is the nucleus of database procucts | From Microsoft and Oracle ,+Wo of +he _ bargest software companies in the World. le The journey OF S@L is a tremendous right From +he beginning AS 2S EBM research vegny¢[Carntin] moe _| i Dato [owe e The figure below shows how actuolly SQL i Works With databases: SQL Request Computer System The lAlorking of S@L eln the above system, the computer system +h st +h om pute y st hos a datbase Which stores att +he needed database is For a compan 2 +he information of manufac , human resource inventole There are various roles which are played by S@L-Some of +hem are cdiscused below. 1, S@L is an interactive query Aanguage. S@l provides a very user Friendly , easy +> use too] Which atloWs the user to write +he +ypicial s@i commands iq order to retrive the cata frem the database. 2. SQL is a datebase Programming fanguage _Through +he use of databose uti ity programs, programmers Orite SQL commands in their 0©n application +e retrive the data _| Stored io database Tt allows +he administrators to define database structures and also contro) the Jeactess +0 +he stored data. 3.) Sais a database administration language. 4. S@Lis client/server Janguage. Fhe server architecture, the 5s uses SQL to communicate tuner. FO access +he sharedcommunicate with othey systems by sending request For data access. G. Sais & database “gatewoy lang uage- Sqtis most of the time used asa gateway Which allows one brad of Dams [te communicate with the other brands =e _|Aerking With Relations oF ROBMS This section highlights how +o create, ryocli or delete relations Which roy exist in relational rmycde). This can understood by the Following SQL statements ; 4. Creating Relations (create Tabe| Statement) 2. ModiPying Relations (aiter table statement) fentasgrity constraints over +he relation - aaacste tabie _ “Too.ry rr Cs- =o integer nob tle char C25) [20° ol Not null) le Wdhen Hhe user is creating +he above ptoble the User noW became +he owner of the newly created table, Which is give +he name Specified in the above statement. 2. Modifying & relation (Aiter table statement) ae Alter the table is ready , a+ Himes user feels the need to store additionad informaton about -+he entries in the table. eThe alter table allows +he user +o change er modi , the relation (schema) oF +hea. Date L 4. Add a drop primary eva Pera eee 1G. Add _or drop the Foreign key For a tuble. s €. Add or crop the uniqueness constraint For a t+able. F. Add or drop check constraints Fer a +eble. Some of the examples are cdliscussed below 1.Alter Table Toorsytr Add Subject char C'5) 2% the above example, an existing table ef TDOLYsTT is modified Ditha new umn is added as subject Which wasdotabase kihen +he contents of a databace le The term data integrity refers +o +he correct ness and completeness oF the data ina arelimeditied with the TNSERT, DELETE, or a | eliffFerent Ways. UPDATE statements , the integrity of the stored data can be fost in many e lo preserve +he consistency and correctness oF jt5 stored cata, a relational CBMs +ypicaby imposes ene or more data integrity _| constraints. © These constraints restrict the data values that can be inserted into the database or created by a database Update.[Camilin| Page Date a 2. Validity checking: Every column jn a database has a clomain, ja set of data values +hat are legal For that column. The OGBMS can be asked +o prevent __sther data values iq these columns. 3. Entity integrity: “The primary Key of a table must contain a unique Value in each row, which is different “From the values in all other rows. Duplicate values are illegal, becottse they wouldnt allow the database +o distinguish / one entity From another.The O8MS can be enferced this unique values[Camnlin| Pase pate / §. Other data retationships : 5 |e che real-world situation modelled by _ clatabase often have adiclitional constraints that qoverg +he leqal data values +hat may appear in +he database. ) The OBMS can be asked +o check modif- ie jeation to the +ables +o malee sure that their values are constrained in +his way. ee 7 . Business rules e Updates +o a database moy be constrained business rules governing +he retal-world tions that are represented by the[Carlin] Pose. ye Advantages and Disadvantages oF RoBMsS Advantages + 2 Simple clata Structures: le By storing the data in the table Format, it becomes ecasiex for the user +o underst- and the structure of database and use reek le RDBMS provides data access using & Natural structure and erqanization eF the data. Kklhen +he user are writing a qHeries, data- Ss cam search any columns For matching ent ries. allows the multiple database users database simultaneausly.GB. let cleFined privileges a —- Authorization and privilege control Features 19 & ROBMS Allow +he clatabase adrninistrater fo restrict access +o authorized Users and grant privileges +o individual Users based —+— — | #o perform. _ en the +ypes of database tasks they need — 4. Network Access: ROBMSs provide access +o the clatcbase trough @ server daemon, & specializec| software —— 3 Program that listens for request OF anetwork, and allows darabase clients +o connect +o and use the datnbase.ANTROOUCTION TO OATABASE STRUCTURE SS Se i gee INTRODUCTION : eI traditional system ,each collection oF _ application programs hat i+ own indepencient master File .The duplication of data over | master files could lead +o inconsistent clata. early clays ,efforts Were discovered +o use “& common master file For a number of leppication programs resulted in problems of a integrity and security. eo Asa First step +towaTds a OBMs , packages + tines Were introduced +o reduce effort in metntatning +these[Garnlin]| Pace ) pate t = e The schema js the structure of the database and the data is the facts of the database. Levels OF AbstractionIn OBMS: _2 Database management can be clefined in the way in Which they use their data dicti- =f ae @ Data dictionaries are helpful for atl human Users ,especiawy the database administrator wellas invaluable +o the application wrograms and report) generators +hat might ecess the database,Wale Gr eda age qrehirecure e The +hree are. 7 MeBERernal |Lavel: Ob |= ~concaanedecoan the Bae was) Cee abeerre the data, 2. Conceptual level! T+ can be regarded See OOM UNY User view a Formal tes cription of data of intrest +o the EtMsaton | independent sot any sterage eonsiclerations. ». —- al meerternal Jevel! Te is a concerned with Fhe woy in Which the Gata is actually My program can access End Userq. fre Rohits customer ofermechion at erent levels of tecture[Rot oe lear vhaiahe 4 = Je They are unsophisticated users who in inte- ract With +he system by invoking one of the permanent opplication programs “hack have been | Written previously. - . 7 Example? r Suppose the banlk teller Wants +o b ransFer +he money aPhter machurity of the - fixed deposit amount of & particular customer , Needs +o jnvoke a PTOgram called +ransfer. Q | Application programmers? @ They are the computer professional Who | interact With +he syste through pm calls, Which are em bedded in a program _ el 2 in a host Programening language. Pre = Processor + catted the ome ler, converts the OML statement[Camlin| rave pote - Peres ie . . 3. Sephisticated users: ‘ _ uae a le These users interact with the database a Al using database query language Hf i: o They submit +helr query to query processor $$ bE Then Data Manipulation Language COmt) | Functions are performed on the datab- ase +o retrieve the data. | aia. e Teels used by +hese users are OLAP € Online Analytical Processing ) ancl data mining Fools. @ Caneus= programmer 4. Speci avized users Whese users write speciatized database application +o retrive data.T ‘ data is cated OBA. ie the functions of +he OBA: e@ Schema defination: DBA creates databa- se schema by executing Data DeFinition Language (Dot) statements. @ Storage structure and access method defination. eGranting of authorization for data access: DBA can decide which ports ef data can be accessed by wohich Users. Before any user access +he data, OBMS chects, udhich _vights= are granted +o the user by the* Rae mo Although a transaction | is an event which eccurs of +he database - | Generally o __|transaction neads a value From the clatabase of worites a value +o +he | database ra A read operations does Net change the 5 mage of these database iN any way. TRe four Properties of Transactions: — 5 4 3 . . Ese, trenst donqieeaeOhccbeuv pecialized solutions | for this purpore- @ In 1964 +he First commercia] database management system Was born; IDs - Fntegrated Data Store, developed at General Electric, based upon 29 early networle data mode! developed by Ck Bachman (Bachman 1965) ein (970 Fdgav F. Codd published 25 article which offered O Fundamentally ape ee eo sea S75) © Curious... prOgTAM Mer“Tyres oF Oata Model: _—There_are_four different types oF dara " medets: “| a: Hierarchicate-dababase | ea ab ae 2. Network database 3. Pelationg) clatabase 4. Object eriented database Hierarchical Ppatabase:[Camiin] rose tare “e Hierarchical Database is most commonly “used With mainframe system, — ¥ a Gt is one of oldest methods of organiaing and storing data and jt is still by some organizations For making +ravel reservations. ~ e@ ® hierarchical database js organised 19 pyramid Fashion , lice the branches ofa +ree Smecoone downwords. N etwork. Database Marleet Reserch Social es security Human Pesourses[Camiin| rece | Date L t 4— « Metworiknetabeesadnemcimiian fo mene oh ao by also having a hierarchical structure here are a few leey eilarerances, however. —, Instead of looking lilkean upside -down tree ,a network database looks more jike a cobWeb OF interconnected NetWork of records .§ JA Network database , children are called members and parents are called ovuoners, RelationeQ clatabase e Pre -velational medels depended upon being | ables to determine explicity Phere and how individueg records were stored. e Early faiakioned proponents argued +hat the z relational data medel viewed informaton than physically » but this“Object Oriented Database: 0 data Medel is a logic organization of “the real World Obj ects (entities), constraints on them, and the relationships among obyects =A BB language is a concrete syntax — or a data model. (ae DB system 2 implements a data model. mM eA core object - oriented data model consists of the Following basic ckject =“ N \ oriented concepts. 1) Object and Object identifier 2) attributes and methods 34) elass. 4 class hierarchy ane) inheritance.ship berween the various layers is jst Advantages And Disadvantages oF Data Medels: Hierarchial Database: Since the database is based on _ the hiewrchical structure » the reio- logically simple [amin|ace —_] E _ | _ 4. Simplicity: a 2. Data Security! Hierarchica} model was +he First Clatabase model thar offered the data security that is provided by +he DBMS. Disadvant ages: BE Implementation complexity: | [though iFis simple and easy to | clesign MAS quite comple * +0 implement, | ‘i ~DParabase Management Problem, ehomqes in the #hen you need to entire application Fre database. TF you roalke any database structure} make ehanges 10 sbi ’ rogreand that access ze Sy Curious-- pregsTan™er’ ‘ 4 § Networlk Model: i ; . 2 . Qadvantages “J - - e a Conceptual) Simplicity: _ Z UST like hierarchical, model it L also si le or) =Ssy to implement, : . E 2. oa. | Capability a handel n More @ relatship Hpes: - i the Network medel . can handel one +o ene 42 end many te many HEN reitship . S §6|g, Fase telBectessdaral the» Garda access - = - 5 : is easiet than +he hierarchial model. Disadvantages: 1. System Complexity: @Il_ +he records hove +o maintaty using pointers +hus the database structure becomes More complex. 2. Operation Mnomaties: As discussed earlier in network model large number Of pointers is jnsertion , deletion and required so upd acting phere compiles:Eee Mecey pdvantages: i 3. Conceptual So pve le have S€29 +hat both +he hierarchial and network 4 Medels are con ceptually simple « but } relational model is simpler than beth (of these boon 2. Structural) “Ondependence: “Fn the Relationg] model, » Changes in the stra&cture “do net affect the Aata access. Disadvantages: 1. Hardware overheades’ The celationa) database systems hide the implergpentatiod complexities and the Physical cata storage detetis From the user. For J 0ing. this, the velationa] database system need more Powerki) hardware computers and clata storage devices. T 12. Ease t© design can lead to bad design. The relational database is easy +o design and use. The user needs nor Fo l mew fe caeiplexities oF the aara Storage. - Curious--“Business Rules: + Business Rules helps employees focous en and implement the actions within +he organization environmen. Some things +o think about hen Beare business rules +o keep +herny simple, easy 4p understand + leeep therm broad 20 +hat Leveryone s89 Have a similoy interpretation “To be considered +rue business rules must be in Writing and tept up +o date. Seme examples Of business rules: = wiMent aS Srelopere == Lacaurae: a Se - -— - - - - generates — —--class Prokessore= = = — Feaches —--~ -class“patabase Design : he clotabase design process aa oF a number of steps listed , below i “Step ht Requirements | Collections 4 Anoly sis: Prospective users ave interviewed +o understand and document AaAata requirements — This step results 19 a concise set of User requirements , Which shoul) be cletatled and complete. « Functiong] requirements can be documented using diagrams such as sequence diagrams » data Flow diagrams ,s¢enarics, Cre. Stee: Conceptual Design: © Once the requirements are collected and analyzed the designers go about creating the concteptuqh schema. * Conceptual schema: constsd description 4 OF "efare: requirements OF the users, and include a detatied description of the entity types, relationships and constraints. —t e Curious = programm er a