DatabaseManagementSystems Chapter1 Instructor: RaghuRamakrishnan - - PDF document

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 1

DatabaseManagementSystems Chapter1

Instructor: RaghuRamakrishnan raghu@cs.wisc.edu

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 2

WhatIsaDBMS?

• Averylarge,integratedcollectionofdata.
• Modelsreal-worldenterprise.

Entities(e.g.,students,courses)

Relationships(e.g.,MadonnaistakingCS564)

• ADatabaseManagementSystem(DBMS) isa

softwarepackagedesignedtostoreand managedatabases.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 3

Filesvs.DBMS

• Applicationmuststagelargedatasets

betweenmainmemoryandsecondary storage(e.g.,buffering,page-orientedaccess, 32-bitaddressing,etc.)

• Specialcodefordifferentqueries
• Mustprotectdatafrominconsistencydueto

multipleconcurrentusers

• Crashrecovery
• Securityandaccesscontrol

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 4

WhyUseaDBMS?

• Dataindependenceandefficientaccess.
• Reducedapplicationdevelopmenttime.
• Dataintegrityandsecurity.
• Uniformdataadministration.
• Concurrentaccess,recoveryfromcrashes.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 5

WhyStudyDatabases??

• Shiftfromcomputation toinformation

atthe“lowend”:scrambleto webspace (amess!)

atthe“highend”:scientificapplications

• Datasetsincreasingindiversityandvolume.

Digitallibraries,interactivevideo,Human Genomeproject,EOSproject

...needforDBMSexploding

• DBMSencompassesmostofCS

OS,languages,theory,“A”I,multimedia,logic

?

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 6

DataModels

• Adatamodel isacollectionofconceptsfor

describingdata.

• A schema isadescriptionofaparticular

collectionofdata,usingtheagivendata model.

• Therelationalmodelofdata isthemostwidely

usedmodeltoday.

Mainconcept:relation,basicallyatablewithrows andcolumns.

Everyrelationhasaschema,whichdescribesthe columns,orfields.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 7

LevelsofAbstraction

• Manyviews,single

conceptual(logical)schema andphysicalschema.

Viewsdescribehowusers seethedata.

Conceptualschemadefines logicalstructure

Physicalschemadescribes thefilesandindexesused. * SchemasaredefinedusingDDL;dataismodified/queriedusingDML.

PhysicalSchema ConceptualSchema View1 View2 View3

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 8

Example:UniversityDatabase

• Conceptualschema:

Students(sid:string,name:string,login:string, age:integer, gpa:real)

Courses(cid:string, cname:string,credits:integer)

Enrolled(sid:string,cid:string,grade:string)

• Physicalschema:

Relationsstoredasunorderedfiles.

IndexonfirstcolumnofStudents.

• ExternalSchema(View):

Course_info(cid:string,enrollment:integer)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 9

DataIndependence*

• Applicationsinsulatedfromhowdatais

structuredandstored.

• Logicaldataindependence:Protectionfrom

changesinlogicalstructureofdata.

• Physicaldataindependence:Protectionfrom

changesinphysical structureofdata.

* OneofthemostimportantbenefitsofusingaDBMS!

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 10

ConcurrencyControl

• Concurrentexecutionofuserprograms

isessentialforgoodDBMSperformance.

Becausediskaccessesarefrequent,andrelatively slow,itisimportanttokeepthe cpu hummingby workingonseveraluserprogramsconcurrently.

• Interleavingactionsofdifferentuserprograms

canleadtoinconsistency:e.g.,checkiscleared whileaccountbalanceisbeingcomputed.

• DBMSensuressuchproblemsdon’tarise:users

canpretendtheyareusingasingle-usersystem.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 11

Transaction:AnExecutionofaDBProgram

• Keyconceptistransaction,whichisanatomic

sequenceofdatabaseactions(reads/writes).

• Eachtransaction,executedcompletely,must

leavetheDBinaconsistentstate ifDBis consistentwhenthetransactionbegins.

Userscanspecifysomesimpleintegrityconstraints on thedata,andtheDBMSwillenforcetheseconstraints.

Beyondthis,theDBMSdoesnotreallyunderstandthe semanticsofthedata.(e.g.,itdoesnotunderstand howtheinterestonabankaccountiscomputed).

Thus,ensuringthatatransaction(runalone)preserves consistencyisultimatelytheuser’s responsibility!

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 12

SchedulingConcurrentTransactions

• DBMSensuresthatexecutionof{T1,..., Tn}is

equivalenttosomeserial executionT1’... Tn’.

Beforereading/writinganobject,atransactionrequests alockontheobject,andwaitstilltheDBMSgivesitthe lock.Alllocksarereleasedattheendofthetransaction. (Strict2PL lockingprotocol.)

Idea:IfanactionofTi(say,writingX)affects Tj (which perhapsreadsX),oneofthem,sayTi,willobtainthe lockonXfirstand Tj isforcedtowaituntilTicompletes; thiseffectivelyordersthetransactions.

Whatif Tj alreadyhasalockonYandTilaterrequestsa lockonY?(Deadlock!)Tior Tj isaborted andrestarted!

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 13

EnsuringAtomicity

• DBMSensuresatomicity (all-or-nothingproperty)

evenifsystemcrashesinthemiddleofa Xact.

• Idea:Keepalog (history)ofallactionscarriedout

bytheDBMSwhileexecutingasetof Xacts:

Before achangeismadetothedatabase,the correspondinglogentryisforcedtoasafelocation. (WALprotocol;OSsupportforthisisofteninadequate.)

Afteracrash,theeffectsofpartiallyexecuted transactionsareundone usingthelog.(ThankstoWAL,if logentrywasn’tsavedbeforethecrash,corresponding changewasnotappliedtodatabase!)

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 14

TheLog

• Thefollowingactionsarerecordedinthelog:

Tiwritesanobject:theoldvalueandthenewvalue.

• Logrecordmustgotodiskbefore thechangedpage!

Ticommits/aborts:alogrecordindicatingthisaction.

• Logrecordschainedtogetherby Xact id,soit’seasyto

undoaspecific Xact (e.g.,toresolveadeadlock).

• Logisoften duplexed andarchived on“stable”storage.
• Alllogrelatedactivities(andinfact,allCCrelated

activitiessuchaslock/unlock,dealingwithdeadlocks etc.)arehandledtransparentlybytheDBMS.

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 15

Databasesmakethesefolkshappy...

• EndusersandDBMSvendors
• DBapplicationprogrammers

E.g.smartwebmasters

• Databaseadministrator(DBA)

Designslogical/physicalschemas

Handlessecurityandauthorization

Dataavailability,crashrecovery

Databasetuningasneedsevolve MustunderstandhowaDBMSworks!

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 16

StructureofaDBMS

• AtypicalDBMShasa

layeredarchitecture.

• Thefiguredoesnot

showtheconcurrency controlandrecovery components.

• Thisisoneofseveral

possiblearchitectures; eachsystemhasitsown variations.

QueryOptimization andExecution RelationalOperators FilesandAccessMethods BufferManagement DiskSpaceManagement DB Theselayers mustconsider concurrency controland recovery

DatabaseManagementSystems3ed,R. Ramakrishnan andJ.Gehrke 17

Summary

• DBMSusedtomaintain,querylargedatasets.
• Benefitsincluderecoveryfromsystemcrashes,

concurrentaccess,quickapplication development,dataintegrityandsecurity.

• Levelsofabstractiongivedataindependence.
• ADBMStypicallyhasalayeredarchitecture.
• DBAs holdresponsiblejobs

andarewell-paid!

• DBMSR&Disoneofthebroadest,

mostexcitingareasinCS.