Lecture Notes Unit1 chapter1 Introduction

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : Introduction to Database Management System
Sub-Topic :
DBMS Definition, Database System Applications, Purpose of database system, View of data (Data Abstraction, Instance and Schema, Data Model, Database Languages- Data Manipulation language and data definition language), Relational databases (DDL, DML , database access from other application programs), Database design (Design process,Database design for banking enterprise, The entity relationship model, Normalization), Object based data model, semi structured data model, data structure and querying, storage manager, The query processor, Transaction management, Database architecture, database users and administrators, History of database system


Target Audience:

Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.

URL for previous slides
Unit V
Chapter 20:
https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter20-transaction/270998760
Chapter 19:
https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter19-cursor-in-pl-sql/270998251
Chapter 18: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter18-packages-pdf/270845026
Chapter 17: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter17-stored-procedures-and-functions/270844763
Chapter 16 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter16-trigger-creation/270631080
Chapter 15 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter-15-pl-sql-programming/270383949

Unit IV

Chapter 14 Synonym : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter14_synonyms-pdf/270327685
Chapter 13 Users, Privileges : https://www.slideshare.net/slideshow/lecture-notes-unit4-chapter13-users-roles-and-privileges/270304806
Chapter 12 View : https://www.slideshare.net/slideshow/rdbms-lecture-notes-unit4-chapter12-view/270199683
Chapter 11 Sequence: https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792
chapter 8,9 and 10 : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter_8_9_10_rdbms-for-the-students-affiliated-by-alagappa-university/270123800

About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educationalpurpose

1. RDBMS -Unit I
Chapter 1
Introduction
Prepared By
Dr.S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernmentArts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
Database System Concepts by Abraham Silberschatz,
Henry F.Korth, S. Sudharshan

2. DBMS -Definition
➢Adatabase-managementsystem(DBMS)isacollectionof
interrelateddataandasetofprogramstoaccessthosedata.
➢Thecollectionofdata,usuallyreferredtoasthedatabase,
containsinformationrelevanttoanenterprise.
➢TheprimarygoalofaDBMSistoprovideawaytostoreand
retrievedatabaseinformationthatisbothconvenientand
efficient.
➢Databasesystemsaredesignedtomanagelargebodiesof
information.Managementofdatainvolvesbothstructuresfor
storageofinformationandprovidingmechanismsforthe
manipulationofinformation.

3. 1.1 Database-System Applications
➢Databasesarewidelyused.Herearesome
representativeapplications:
Banking:Forcustomerinformation,accounts,loans,and
bankingtransactions.
Airlines:Forreservationsandscheduleinformation.
Airlineswereamongthefirsttousedatabasesina
geographicallydistributedmanner.
Universities:Forstudentinformation/course
registrations,andgrades.

4. 1.1 Database-System Applications
Creditcardtransactions:Forpurchasesoncreditcards
andgenerationofmonthlystatements.
Telecommunication:Forrkeepingrecordsofcallsmade,
generatingmonthlybills,maintainingbalancesonprepaid
callingcards,andstoringinformationabout
thecommunicationnetworks.
Finance:Forstoringinformationaboutholdings,sales,
andpurchasesoffinancialinstrumentssuchasstocksand
bonds;
Sales:Forcustomer,product,andpurchaseinformation.

5. 1.1 Database-System Applications
On-lineretailers:Forsalesdataandon-lineorder
tracking/generationofrecommendationlists,and
maintenanceofon-lineproductevaluations.
Manufacturing:Formanagementofthesupplychain
andfortrackingproductionofitemsinfactories,
inventoriesofitemsinwarehousesandstores,andorders
foritems.
Humanresources:Forinformationaboutemployees,
salaries,payrolltaxes,benefits,andforgenerationof
paychecks.

6. 1.1 Database-System Applications
➢interactivevoiceresponsesystemsallowedusersto
dealdirectlywithdatabases-acallercoulddiala
number,andpressphonekeystoenterinformationor
toselectalternativeoptions,tofindflight
arrival/departuretimes,forexample,ortoregisterfor
coursesinauniversity.
➢TheInternetrevolutionofthelate1990s,thedatabases
Organizationsconvertedmanyoftheirphone
interfacestodatabasesintoWebinterfaces,and
madeavarietyofservicesandinformationavailable
online.

7. 1.1 Database-System Applications
➢Forinstance,whenyouaccessanonlinebookstoreand
browseabookormusiccollection,youareaccessing
datastoredinadatabase.
➢WhenyouaccessabankWebsiteandretrieveyour
bankbalanceandtransactioninformation,the
informationisretrievedfromthebank'sdatabase
system.

8. 1.2 Purpose of Database Systems
➢ThepurposeofDatabasesystemisresponsetoearly
methodsofcomputerizedmanagementof
commercialdata.
➢Forexample,inbankingenterprise,Toallowusersto
manipulatetheinformation,thesystemhasanumber
ofapplicationprogramsthatmanipulatesthefiles,
includingprogramsto:
➢Debit or credit an account
➢Add a new account
➢Find the balance of an account
➢Generate monthly statements
➢Systemprogrammerswrotetheseapplication
programstomeettheneedsofthebank.

9. 1.2 Purpose of Database Systems
➢Beforedatabasemanagementsystems(DBMSs)came
along,organizationswereusedthefileprocessing
system.Thedrawbacksoffileprocessingsystemsare:
Dataredundancy:Thesameinformationmaybe
duplicatedinseveralplaces(files).
➢Forexample,theaddressandtelephonenumberofa
particularcustomermayappearinafilethatconsists
ofsavings-accountrecordsandchecking-account
records.
➢Thisredundancyleadstohigherstorageandaccess
cost.

10. 1.2 Purpose of Database Systems
Datainconsistency:Thevariouscopiesofthesamedata
maynolongeragree.
Forexample,achangedcustomeraddressmaybe
reflectedinsavings-accountrecordsbutnotelsewherein
thesystem.

11. 1.2 Purpose of Database Systems
Difficulty in accessing data:Retrieve the information
based on the particular selection criteria.
➢Forexample,thebankofficersneedstofindoutthe
namesofallcustomerswholivewithinaparticular
postal-codearea.
➢Thebankofficerhasnowtwochoices:eitherobtain
thelistofallcustomersandextracttheneeded
informationmanuallyoraskasystemprogrammerto
writethenecessaryapplicationprogram.
➢Thequerymaybedifferentfromtimetotime.So,the
programmerneedtowriteprogramforeveryquery.

12. 1.2 Purpose of Database Systems
Dataisolation:Thedataarescatteredinvariousfiles,
andfilesmaybeindifferentformats,writingnew
applicationprogramstoretrievetheappropriatedatais
difficult.
Integrityproblems:Thedatavaluesstoredinthe
databasemustsatisfycertaintypesofconsistency
constraints.
Forexample,theminimumbalanceofbankaccountsmay
neverfallbelowaprescribedamount(say,Rs.1000)

13. 1.2 Purpose of Database Systems
Dataisolation:Thedataarescatteredinvariousfiles,
andfilesmaybeindifferentformats,writingnew
applicationprogramstoretrievetheappropriatedatais
difficult.
Integrityproblems:Thedatavaluesstoredinthe
databasemustsatisfycertaintypesofconsistency
constraints.
Forexample,theminimumbalanceofbankaccountsmay
neverfallbelowaprescribedamount(say,Rs.1000)

14. 1.2 Purpose of Database Systems
Atomicityproblems:Acomputersystem,Iikeanyother
mechanicalorelectricaldevice,issubjecttofailure.
Ifafailureoccurs,thedatashouldberestoredtothe
consistentstate.
➢ConsideraprogramtotransferRs.1000fromaccount
AtoaccountB.
➢Ifasystemfailureoccursduringtheexecutionofthe
Program,itispossiblethattheRs.1000wasdebitted
fromaccountAbutwasnotcreditedtoaccountB,
resultinginaninconsistentdatabasestate.

15. 1.2 Purpose of Database Systems
Concurrent-accessanomalies:Manysystemsallows
multipleuserstoupdatethedatasimultaneously.
Theinteractionofconcurrentupdatesispossibleandmay
resultininconsistentdata.
➢Forexample,Iftwocustomerswithdrawntheamount
Rs.1000,Rs.500fromAccountA.(Thecurrent
balanceofAccountAmaybe5000.)
➢Ifthetransactionissimultaneousthenthedatamaybe
storedaseither4000or4500insteadof3500.

16. 1.2 Purpose of Database Systems
Securityproblems:Noteveryuserofthedatabase
systemshouldbeabletoaccessaallthedata.
➢Forexample,inabankingsystem,payrollpersonnel
needtoseeonlythepartofthedatabasesystem.

17. 1.3 View of Data
➢Adatabasesystemisacollectionofinter-relateddata
andasetofprogramsthatallowuserstoaccessand
modifythesedata.
➢Amajorpurposeofadatabasesystemistoprovide
userswithanabstractviewofthedata.Thatis,the
systemhidescertaindetailsofhowthedataarestored
andmaintained.

18. 1.3.1 Data Abstraction
➢Amajorpurposeofadatabasesystemistoprovide
userswithanabstractviewofthedata.Thatis,the
systemhidescertaindetailsofhowthedataarestored
andmaintained.
Physicallevel:Thelowestlevelofabstractiondescribes
howthedataareactuallystored.[Blockofconsecutive
storagelocation]
Logicallevel:Thenext-higherlevelofabstraction
describeswhatdataarestoredinthedatabase,andwhat
relationshipsexistamongthosedata.[TypeDefinitionor
Tablecreation]

19. 1.3.1 Data Abstraction
Viewlevel:Thehighestlevelofabstractiondescribes
onlypartoftheentiredatabase.Thesystemmayprovide
manyviewsforthesamedatabase.

20. 1.3.1 Data Abstraction
Viewlevel:Thehighestlevelofabstractiondescribes
onlypartoftheentiredatabase.Thesystemmayprovide
manyviewsforthesamedatabase.

21. 1.3.2 lnstancesand Schemas
Instance:Thecollectionofinformationstoredinthe
databaseataparticularmomentiscalledaninstanceof
thedatabase.
Schema:Theoveralldesignofthedatlbaseiscalledthe
databaseschema.
Databasesystemshaveseveralschemas,partitioned
accordingtothelevelsofabstraction.
Thephysicalschemadescribesthedatabasedesignatthe
physicallevel,whilethelogicalschemadescribesthe
databasedesignatthelogicallevel.Aportionofthe
databaseiscalledsubschema.

22. 1.3.2 lnstancesand Schemas
DataIndependence:Theabilitytomodifyaschema
definitioninonelevelwithoutaffectingaschema
definitioninthenexthigherleveliscalleddata
independence.
PhysicalDataIndependence:Theabilitytomodifythe
physicalschemawithoutcausingapplicationprogramsto
berewritten.
LogicalDataIndependence:Theabilitytomodifythe
logicalschemawithoutcausingapplicationprogramsto
berewritten.

23. 1.3.3 Data Models
Underlyingthestructureofadatabaseisthedatamodel.
Adatamodelprovidesawaytodescribethedesignofa
databaseatthephysical,Iogical,andviewlevel.The
datamodelscanbeclassifiedinfourdifferentcategories:
RelationalModel:Therelationalmodelusesacollection
oftablestorepresentbothdataandtherelationships
amongthosedata.Therelationalmodelisanexampleof
arecord-basedmodel.
TheEntity-RelationshipModel:Theentity-relationship
(E-R)datamodelisbasedonaperceptionofarealworld
thatconsistsofacollectionofbasicobjects,called
entities,andofrelationshipsamongtheseobjects.

24. 1.3.3 Data Models
Theobject-orientedmodel:Itcanbeseenasextending
theE-Rmodelwithnotionsofencapsulation,methods
(functions),andobjectidentity.Theobject-relationaldata
modelcombinesfeaturesoftheobject-orienteddata
modelandrelationaldatamodel.
SemistructuredDataModel:Thesemistructureddata
modelpermitsthespecificationofdatawhereindividual
dataitemsofthesametypemayhavedifferentsetsof
attributes.(Ex:Inrationcard,Afamilythatcontaintwo
personorAfamilythatcontainsthreepersons).The
ExtensibleMarkupLanguage(XML)iswidelyusedto
representsemistructureddata.

25. 1.4 Database Languages
➢Adatabasesystemprovidesadata-definition
languagetospecifythedatabaseschemaandadata-
manipulationlanguagetoexpressdatabasequeries
andupdates.

26. 1.4.1 Data-Manipulation Language
Adata-manipulationlanguage(DML)isalanguage
thatenablesuserstoaccessormanipulatedata.Thetypes
ofaccessare:
➢Retrievalofinformationstoredinthedatabase
➢Insertionofnewinformationintothedatabase
➢Deletionofinformationfromthedatabase
➢Modificationofinformationstoredinthe
database
TherearebasicallytwotypesofDML:
➢ProceduralDMLsrequireausertospecifywhatdata
areneededand.howtogetthosedata.
➢DeclarativeDMLs(alsoreferredtoasnonprocedural
DMLs)requireausertospecifywhatdataareneeded
withoutspecifyinghowtogetthosedata.

27. 1.4.1 Data-Manipulation Language
➢Aqueryisastatementrequestingtheretrievalof
information.
➢TheportionofaDMLthatinvolvesinformation
retrievaliscalledaquerylanguage.

28. 1.4.2 Data-Definition Language
➢Thespecificationofadatabaseschemabyasetof
definitionsexpressedbyaspeciallanguagecalleda
data-definitionlanguage(DDL).
➢Thespecificationofthestoragestructureandaccess
methodsusedbyasetofstatementsinaspecialtype
ofDDLcalledadatastorageanddefinition
language.
➢Thedatavaluesstoredinthedatabasemustsatisfy
certainconsistencyconstraints.

29. 1.4.2 Data-Definition Language
➢ThedifferenttypesofConstraintsaredomain
Constraints,referentialintegrityconstraints,Assertion
andauthorization.
➢DomainConstraints:Adomainofpossiblevalues
mustbeassociatedwitheveryattribute(forexample,
integerWPes,characterWPes,date/timetypes).
➢ReferentialIntegrity:Therearecaseswherewe
wishtoensurethatavaluethatappearsinonerelation
foragivensetofattributesalsoappearsforacertain
setofattributesinanotherrelation(referential
integrity).

30. 1.4.2 Data-Definition Language
➢Assertions:Anassertionisanyconditionthatthe
databasemustalwayssatisfy.Domainconstraintsand
referential-integrityconstraintsarespecialformsof
assertions.
➢Authorization:Authorisedpersononlyallowedto
accessandmanipulatethedatainthedatabase.The
authorizationmaybereadauthorization,insertion
authorization,updateauthorizationanddelete
authorization.
➢MetaData:TheoutputoftheDDLisplacedinthe
dafadictionary,whichcontainsmetadata-thatis,data
aboutdata.

31. 1.5 Relational Databases
A relational database is based on the relational model and
uses a collection of tables to represent both data and the
relationships among those data.
1.5.1 Table
➢Eachtablehasmultiplecolumns,andeachcolumnhas
auniquename.
➢Figure1.2presentsasamplerelationaldatabase
comprisingthreetables:
➢oneshowsdetailsofbankcustomers,thesecond
showsaccounts,andthethirdshowswhichaccounts
belongtowhichcustomers

32. 1.5 Relational Databases

33. 1.5.2 Data-Manipulation Language
➢ThequerylanguageofSQLisnonprocedural.Ittakes
asinputseveraltables(possiblyonlyone)andalways
returnsasingletable.
➢HereisanexampleofanSQLquerythatfindsthe
namesofallcustomerswhoresideinHarrison:

34. 1.5.3 Data-Definition Language
➢SQLprovidesarichDDLthatallowsonetodefine
tables,integrityconstraints,assertions,etc.
➢Forinstance,thefollowingstatementintheSQL
languagedefinestheaccounttable:

35. 1.5.4 Database Access from Application Programs
➢Therearesomecomputationsthatcannotbeobtained
byanySQLquery.
➢Suchcomputationsmustbewritteninahostlanguage,
suchasCobol,C,C++,orlava,withembeddedSQL
queriesthataccessthedatainthedatabase.

36. 1.6 Database Design
➢Databasedesignmainlyinvolvesthedesignofthe
databaseschema.
➢Databasesystemsaredesignedtomanagelargebodies
ofinformation.
➢Theselargebodiesofinformationdonotexistin
isolation.

37. 1.6.1 Design Process
➢Theinitialphaseofdatabasedesignistocharacterize
thedatabasedontheuserneeds.Thedatabase
designerneedstointeractwithdomainexpertsand
userstocarryoutthistask.Theoutcomeofthisphase
isaspecificationofuserrequirements.
➢Thenextphaseisdescribingdataanditsrelationship
byusingnormalizationrules.
➢Finallydesignthelogicalandphysicaldesign.

38. 1.6.1 Design Process
➢LogicalDesign–Decidingonthedatabaseschema.
Databasedesignrequiresthatwefinda“good”
collectionofrelationschemas.
➢Businessdecision–Whatattributesshouldwe
recordinthedatabase?
➢ComputerSciencedecision–Whatrelation
schemasshouldwehaveandhowshouldthe
attributesbedistributedamongthevariousrelation
schemas?
➢PhysicalDesign–Decidingonthephysicallayoutof
thedatabase.

39. 1.6.2 Database Design for Banking Enterprise
➢Toillustratethedesignprocess,letusexaminehowa
databaseforabankingenterprisecouldbedesigned.
Herearethemajorcharacteristicsofthebanking
enterprise.
➢Thebankisorganizedintobranches,whichmonitors
theassetsofeachbranch.
➢Bankcustomersareidentifiedbytheircustomerid
values.Thebankstoreseachcustomer'sname/andthe
streetandcitywherethecustomerlives.Customers
mayhaveaccountsandcantakeoutloans.

40. 1.6.2 Database Design for Banking Enterprise
➢Thebankofferstwotypesofaccounts-savingsand
checkingaccounts.Eachsavingsaccounthasan
interestrate,andoverdraftsarerecordedforeach
checkingaccount.
➢Thebankprovidesitscustomerswithloans.
➢Bankemployeesareidentifiedbytheiremployee-id
values.Alsoitcontainsemployeepersonal
informationanddateofjoiningdetails.

41. 1.6.3 The Entity-Relationship Model
ERModel:Entity-Relationmodelisbasedonthenotion
ofreal-worldentitiesandtherelationshipbetweenthem.
AnERmodelconsistsofacollectionofbasicobjects,
calledentities,andofrelationshipsamongtheseObjects.
Entity:Anentityisa"thing"or"object"intherealworld
thatisdistinguishablefromotherobjects.Forexample,
eachpersonisanentity,andbankaccountscanbe
consideredasentities.

42. 1.6.3 The Entity-Relationship Model
Entities:Entitiesaredescribedinadatabasebyasetof
attributes.Forexample,attributescustomer-name,
customer-street,addressandcustomercitymaydescribea
customerentity.
Relationship:Arelationshipisanassociationamong
severalentities.Forexample,adepositorrelationship
associatesacustomerwithaccountno.
Entityset:Thesetofallentitiesofthesametypeis
termedasanentityset.
Relationshipset:Thesetofallrelationshipsofthesame
typeistermedasrelationshipset.

43. 1.6.3 The Entity-Relationship Model
➢Theoveralllogicalstructure(schema)ofadatabase
canbeexpressedgraphicallybyanE-Rdiagram.It
containsthefollowingcomponents.
➢Rectangles,whichrepresententitysets
➢Ellipses,whichrepresentattributes
➢Diamonds,whichrepresentsetsofrelationships
➢Lines,whichlinkattributestoentitysetsand
entitysetstorelationships.

44. 1.6.3 The Entity-Relationship Model
➢TheE-Rdiagramindicatesthattherearetwoentity
sets,customerandaccountwithasetofattributes.The
diagramalsoshowsarelationshipdepositorbetween
customerandaccount.

45. 1.6.4 Normalization
Amethodfordesigningarelationaldatabaseisknownas
normalization.Thegoalistogenerateasetofrelation
schemaswithoutunnecessaryredundancy.
Abaddatabasedesignmaycontains:
Repetitionofinformation:Thecustomerjohnsonrecord
isrepeatedinFigure1.5.
Inabilitytorepresentcertaininformation:Customer
nameandotherinformationcanbestoredonlythe
knowingofaccountnumberorotherwiseaccountnumber
shouldbenull.

46. 1.6.4 Normalization
Bad Database Design
Good Database Design

47. 1.7.1 Object-Based Data Models
➢Theobject-orienteddatamodelisbasedonthe
object-orientedprogramminglanguageparadigm,
whichisnowinwideuse.
➢Inheritance,object-identity,andencapsulation
(informationhiding),withmethodstoprovidean
interfacetoobjects,areamongthekeyconceptsof
object-orientedprogrammingthathavefound
applicationsindatamodeling.
➢Theobject-relationaldatamodelextendsthe
traditionalrelationalmodelwithavarietyoffeatures
suchasstructuredandcollectiontypes,aswellas
objectorientation.

48. 1.7.2 Semi structured Data Models
➢Semistructureddatamodelspermitthespecification
ofdatawhereindividualdataitemsofthesametype
mayhavedifferentsetsofattributes.
➢XMLprovidesawaytorepresentdatathathave
nestedstructure,anditallowsagreatdealof
flexibilityinstructuringofdata.

49. 1.8 Data Storogeand Querying
➢Thefunctionalcomponentsofadatabasesystemcan
bebroadlydividedintothestoragemanagerand
thequeryProcessorcomponents.
➢Thestoragemanagerisimportantbecausedatabases
typicallyrequirealargeamountofstoragespace.
➢Themainmemoryofcomputerscannotstorelarge
amountofinformation,theinformationisstoredon
disks.
➢Datastorageisresponsibleformovingdatabetween
diskstorageandmainmemory.

50. 1.8 Data Storogeand Querying
➢Thequeryprocessorisimportantbecauseithelps
thedatabasesystemsimplifyandfacilitateaccessto
data.
➢Thejobofthequeryprocessorsystemistotranslate
updatesandquerieswritteninanonprocedural
language,atthelogicallevel,intoanefficient
sequenceofoperationsatthephysicallevel.

51. 1.8.1 Storage Manager
A storage manager is a program module that provides
the interface between the low level data stored in the
database and the application programs and queries
submitted to the system.
The storage manager translates the various DML
statements into low-level file-system commands.
Thus, the storage manager is responsible for storing,
retrieving, and updating data in the database.

52. 1.8.1 Storage Manager
Thestoragemanagercomponentsinclude:
➢Authorizationandintegritymanager:whichtestsfor
thesatisfactionofintegrityconstraintsandchecks
theauthorityofuserstoaccessdata
➢Transactionmanager:whichensuresthatthe
databaseremainsinaconsistent(correct)state.
➢Filemanager:whichmanagestheallocationof
spaceondiskstorage.
➢Buffermanager:whichisresponsibleforfetching
databetweendiskstorageandmainmemory.

53. 1.8.1 Storage Manager
➢Thestoragemanagerimplementsseveraldata
structuresincludes:
➢Datafiles:whichstorethedatabaseitself.
➢Datadictionary:whichstoresmetadataaboutthe
structureofthedatabase
➢Indices:whichcanprovidefastaccesstodata
items.

54. 1.8.2 The Query Processor
Thequeryprocessorcomponentsinclude:
➢DDLinterpreter:whichinterpretsDDLstatements
andrecordsthedefinitionsinthedatadictionary.
➢DMLcompiler,whichtranslatesDMLstatements
inaquerylanguageintolow-levelinstructions[0
and1s]forqueryevaluationengine.Italsoperforms
thequeryoptimization.
➢Queryevaluationengine,whichexecuteslow-level
instructionsgeneratedbytheDMLcompiler.

55. 1.9 Transaction Management
A transaction is a collection of operations that
performs a single logical function in a database
application.

56. Transaction Management
➢Toillustratetheconceptoftransactions,thinkabouta
bankingoperationdatabase.
➢Whenaclienttransfersmoneyfromasavingsaccount
toacheckingaccount,thetransactioncanconsistof
threeseparateoperations:
➢debitingthesavingsaccount,
➢creditingthecheckingaccount,and
➢recordingthetransactioninthetransactionlog.

57. Transaction -Definition
Adatabasetransactionmustbeatomic,consistent,isolatedand
durable.
Atomic:Atransactionisalogicalunitofworkwhichmustbe
eithercompletedwithallofitsdatamodifications,ornoneofthem
isperformed.
Consistent:Attheendofthetransaction,alldatamustbeleftina
consistentstate.
Isolated:Modificationsofdataperformedbyatransactionmust
beindependentofanothertransaction.Unlessthishappens,the
outcomeofatransactionmaybeerroneous.
Durable:Whenthetransactioniscompleted,effectsofthe
modificationsperformedbythetransactionmustbepermanentin
thesystem.
Oftenthesefourpropertiesofatransactionareacronymed
asACID.

58. Transaction -Example
Account AAccount B State
Initial
Balance
5000 10000
Rs. 2000 Transferred from Account A to Account B
5000
3000
10000
12000
Atomicity
(Either Old or new)
3000 12000
Consistency
3000 12000
Durability (Permanent
changes into the
database)

59. 1.1
1.11 Database Architecture
Theoverall
architectureof
database
architectureas
showninFig
1.6.

60. 1.11 Database Architecture
➢TheDatabaseapplicationsareusuallypartitionedinto
twoorthreeparts,asinFigure1.7.
➢Inatwo-tierarchitecture,theapplicationisresidesat
theclientmachine,whichinvokesdatabasesystem
functionalityattheservermachinethroughquery
languagestatements.
➢ApplicationPrograminterfacestandardslikeODBC
andJDBCareusedforinteractionbetweentheclient
andtheServer.
Client Request to Server (Select * from emp)
Server Receives request and response to client.

61. 1.11 Database Architecture
➢Incontrast,inathree-tierarchitecture,theclient
machineactsasmerelyafrontendanddoesnot
containanydirectdatabasecalls.
➢Instead,theclientendcommunicateswithan
applicationserver,usuallythroughaformsinterface.
(WWW)
➢Theapplicationserverinturncommunicateswitha
databasesystemtoaccessdata.

62. 1.11 Database Architecture

63. 1.12 Database Users and Administrators
➢Peoplewhoworkwithadatabasecanbecategorized
asdatabaseusersordatabaseadministrators

64. 1.12.1 Database Users and User Interfaces
Therearefourdifferenttypesofdatabase-systemusers.
➢Naiveusersareunsophisticateduserswhointeract
withthesystembyinvokingoneoftheapplication
programsthathavebeenwrittenpreviously.For
example,abanktellerwhoneedstotransfer$50from
accountAtoAccountBinvokesaprogramcalled
transfer.
➢Applicationprogrammers arecomputer
professionalswhowriteapplicationprograms.

65. 1.12.1 Database Users and User Interfaces
➢Sophisticatedusersinteractwiththesystemwithout
writingprograms.Instead,theyformtheirrequestsin
adatabasequerylanguage.
➢Specializedusersaresophisticateduserswhowrite
specializeddatabaseApplicationssuchascomputer-
aideddesignsystems,knowledgebaseandexpert
systems,systemsthatstoredatawithcomplexdata
types.

66. 1.12.2 Database Administrator
Apersonwhohascentralcontroloverthesystemiscalled
adatabaseadministrator(DBA).ThefunctionsofaDBA
include:
➢Schemadefinition.TheDBAcreatestheoriginal
databaseschemabyexecuting
➢asetofdatadefinitionstatementsintheDDL.
➢Storagestructureandaccess-methoddefinition.
➢Schemaandphysical-organizationmodification.
➢Grantingofauthorizationfordataaccess

67. 1.12.2 Database Administrator
➢Routinemaintenance,Examplesofthedatabase
administrator'sroutinemaintenanceactivitiesare:
➢Periodicallybackingupthedatabase
➢Ensuringthatenoughfreediskspaceis
availablefordatabaseoperations.
➢Monitoringtheperformanceofjobsrunningon
thedatabase.

68. 1.13 History of Database Systems
❑1950s and early 1960s:
➢Data processing using magnetic tapes for storage.Tapes
provided only sequential access.
➢Punched cards for input
❑Late 1960s and 1970s:
➢Hard disks allowed direct access to data
➢Network and hierarchical data models in widespread use
➢Ted Codddefines the relational data model
➢Would win the ACM Turing Award for this work
➢IBM Research begins System R prototype
➢UC Berkeley begins Ingres prototype
➢High-performance (for the era) transaction processing

69. 1.13 History of Database Systems
❑1980s:
➢Research relational prototypes evolve into commercial
systems
➢SQL becomes industrial standard
➢Parallel and distributed database systems
➢Object-oriented database systems
❑1990s:
➢Large decision support and data-mining applications
➢Large multi-terabyte data warehouses
➢Emergence of Web commerce
❑Early 2000s:
➢XML and XQuery standards
➢Automated database administration
❑Later 2000s:
➢Giant data storage systems
➢Google BigTable, Yahoo PNuts, Amazon, ..