数据库系统原理(英文)-8-09

PART2RELATIONALDATABASES第一页，编辑于星期五：十二点三十七分。April20091*Chapter8

ApplicationDesignandDevelopment第二页，编辑于星期五：十二点三十七分。April20092*CasestudyusedinthischapterreturnFig.8.0Schemaofbankingenterprise第三页，编辑于星期五：十二点三十七分。April20093*

§8.6Trigger(触发器)TriggerastatementthatisexecutedautomaticallybyDBMSasasideeffectofamodificationtothedatabaseAsanintegritycontrolmechanism,TriggerisintroducedtoSQL1999standard,butsupportedevenearlierusingnon-standardsyntaxbymostdatabases第四页，编辑于星期五：十二点三十七分。April20094*

8.6Trigger(cont.)Triggerisanevent-condition-actionmodelbasedintegritydefinition,checking,remedymechanismspecifywhateventscausethetriggertobeexecuted(e.g.insert,delete,update),andunderwhichconditionsthetriggerexecutionwillproceedintegrityconstraintscheckingspecifytheactionstobetakenwhenthetriggerexecutesifconstraintsisviolated,remedyactionsaretaken第五页，编辑于星期五：十二点三十七分。April20095*Supposethat,insteadofallowingnegativeaccountbalances,thebankdealswithoverdrafts(透支)byfollowingactionssettingtheaccountbalancetozerocreatingaloanintheamountoftheoverdraftgivingthisloanaloannumberidenticaltotheaccountnumberoftheoverdrawnaccountTriggeroverdraft-triggerthetriggeringeventisupdateonbalancethecondition

forexecutingthetriggerisanmodificationtotheaccountrelationthatresultsinanegative

balancevalueExampleOne第六页，编辑于星期五：十二点三十七分。April20096*triggeractionsareinsertonborrower,loanrelations,andupdateonaccountrelationExampleOne(cont.)第七页，编辑于星期五：十二点三十七分。April20097*createtriggeroverdraft-triggerafter

updateonaccount---event

referencingnewrowasnrowforeachrow

whennrow.balance<0

-------condition

beginatomic

-------action

insertinto

borrower

(selectcustomer-name,account-number

fromdepositor

wherenrow.account-number=

depositor.account-number);

insertinto

loanvalues

(nrow.account-number,nrow.branch-name,

–nrow.balance);

update

accountsetbalance=0

whereaccount.account-number=nrow.account-number

end

Overdraft-trigger第八页，编辑于星期五：十二点三十七分。April20098*GSM网络配置数据库中，利用触发器实现向小区(cell)中新增频点，约束：每个小区最多只有8个频点计算性能指标“BSC切换成功率”，原理：每当发生一次BSC切换时，根据本次切换是否成功重新计算“切换成功率”ExampleTwo第九页，编辑于星期五：十二点三十七分。April20099*createtriggeroverfreonCell_TCHforinsert/*事件event*/asIFexists(selectcellid,count(TCHno)asnum

/*条件condition*/ fromCell_TCH groupbycellid havingcount(TCHno)>8)BegintransactiondeletefromCell_TCHwhereCell_TCH.TCHno=(selectmin(TCHno)asminfreno fromCell_TCH groupbycellid havingcount(TCHno)>8)print'插入TCH频点多于8个，频点号最小的已经被删除！'EndFig.触发器“新增小区频点”动作第十页，编辑于星期五：十二点三十七分。April200910*Theeventsandactionsintriggercantakemanyformsthetriggereventscanalsobeinsertordelete,insteadofupdatetriggersonupdatecanberestrictedtospecificattributese.g.createtriggeroverdraft-triggerafterupdateofbalance

onaccounttriggerscanbeactivatedbeforeorafteranevent,whichcanserveasextraconstraintsvaluesofattributesbeforeandafteranupdatecanbereferencedreferencingoldrowas:fordeletesandupdatesreferencingnewrowas:forinsertsandupdates

8.6Trigger(cont.)第十一页，编辑于星期五：十二点三十七分。April200911*e.g.inFig.8.8,theclausereferencingnewrowasnrowcreateavariablenrowtostorethenewvaluesoftheupdatedtuplesinaccountinsteadofexecutingaseparateactionforeachaffectedrow,asingleactioncanbeexecutedfortheentireSQLstatementthatcausedtheinsert/delete/update,i.e.forallrowsaffectedbyasingletransactionuseforeachstatementinsteadofforeachrowusereferencingoldtableorreferencingnewtabletorefertotemporary/transitiontablescontainingtheaffectedrows

8.6Trigger(cont.)第十二页，编辑于星期五：十二点三十七分。April200912*asinglestatementcanthenbeusedtocarryoutmultipleactionsonthebasisofthetemporary/transitiontables,andthusismoreefficientwhendealingwithSQLstatementsthatupdatealargenumberofrowsAlthoughtriggercanbeusedtomaintainDBSintegrity,itmaybeinefficientinsomecasesifeventsoccursfrequently,actionswillbefrequentlytakentoaccessonDBSomemoreefficientmechanisms,suchasmaterializedviews,insteadoftriggers,areusedtomaintaindatainDBFormoredetails,refertoAppendixAWhenNotToUseTriggers

8.6Trigger(cont.)第十三页，编辑于星期五：十二点三十七分。April200913*§8.7AuthorizationinSQL第十四页，编辑于星期五：十二点三十七分。April200914*UserIdentifier(口令)Authorization(inanarrowsense):privilegedefinition(授权)privilegecheckingencryptiontechniquesUserDBMSNetwork&OperatingsystemDBphysicalstorage第十五页，编辑于星期五：十二点三十七分。April200915*第十六页，编辑于星期五：十二点三十七分。April200916*第十七页，编辑于星期五：十二点三十七分。April200917*DBMSprovidesauthorizationondataaccesstoDBattwolevelsdata(attributes,tuples,tables)levelsschemalevelsAuthorizationondatalevelsread,insert,update,deleteAuthorizationonschemalevelsindex,creation,alteration,dropAuthorizationinDBS第十八页，编辑于星期五：十二点三十七分。April200918*objects

operatingprivilegesattributeSELECT,INSERT,UPDATE,DELETE,ALLPRIVILEGESviewSELECT,INSERT,UPDATE,DELETE,ALLPRIVILEGEStableSELECT,INSERT,UPDATE,DELETE,ALTER,

INDEX,

Create

ALLPRIVILEGESdatabaseCREATETABTable8.0.1PrivilegesinSQL

第十九页，编辑于星期五：十二点三十七分。April200919*GrantstatementinSQLgivesusersprivilegesonDBaccessUsersgrantedsomeformofauthorizationmaybeallowedtopassontheauthorizationtootherusers§8.7.1GrantingofPrivileges

(权限授予)第二十页，编辑于星期五：十二点三十七分。April200920*Thepassageofauthorizationfromoneusertoanotherisrepresentedbyanauthorizationgraphthenodesofthisgrapharetheusers,therootofthegraphisthedatabaseadministrator(DBA)e.g.graphforupdateauthorizationonloanU1U4U2U5U3DBA

anedgeUi

UjindicatesthatuserUihasgranted

updateauthorizationonloantoUj.8.7.1GrantingofPrivilegesinSQL(cont.)第二十一页，编辑于星期五：十二点三十七分。April200921*NotealledgesinanauthorizationgraphmustbethepartofsomepathoriginatingwiththedatabaseadministratorDBAcanrevokeprivilegesusershaveobtained.E.g.InFig.8.11,ifDBArevokesgrantfromU1grantmustberevokedfromU4sinceU1nolongerhasauthorizationgrantmustnotberevokedfromU5sinceU5hasanotherauthorizationpathfromDBAthroughU28.7.1GrantingofPrivileges(cont.)第二十二页，编辑于星期五：十二点三十七分。April200922*Thegrantstatementisusedtoconferauthorization

grant<privilegelist>

on<relationnameorviewname>to<userlist><userlist>isauser-idpublic,whichallowsallvaliduserstheprivilegegrantedarole(moreonthislater)Thegrantoroftheprivilegemustalreadyholdtheprivilegeonthespecifieditem(orbethedatabaseadministrator)8.7.2AuthorizationinSQL第二十三页，编辑于星期五：十二点三十七分。April200923*E.g.grantusersU1,U2,andU3

selectauthorizationonthebranch

relation

grantselectonbranch

toU1,U2,U38.7.2AuthorizationinSQL(cont.)第二十四页，编辑于星期五：十二点三十七分。April200924*Tosimplifytheauthorizationtoeachusersinthesystem,theconceptofroleisintroducedrole:usergroup,usersinarolehavethesamecommonprivilegesonDBPrivilegescanbegrantedtoorrevokedfromroles,justlikeuserausercanbedenotedasarole,andthenbegrantedtheprivilegesoftherolethenumberofrolesinthesystemislessthanthenumberofusers§8.7.3Roles第二十五页，编辑于星期五：十二点三十七分。April200925*SQL:1999supportsrolescreateroleteller

createrolemanagergrantselectonaccounttotellergrantupdate(balance)onaccounttotellerRolescanbeassignedtousers

grant

tellertojohn

grantmanagertomarygranttellertomanager8.7.3Roles(cont.)第二十六页，编辑于星期五：十二点三十七分。April200926*Therevokestatementisusedtorevokeauthorization.revoke<privilegelist>on<relationnameorviewname>from<userlist>[restrict|cascade]8.7.4RevokingofPrivilegesE.g.revokeselectonbranch

fromU1,U2,U3

cascadeRevocationofaprivilegefromausermaycauseotherusersalsotolosethatprivilege;referredtoascascadingoftherevoke第二十七页，编辑于星期五：十二点三十七分。April200927*§8.7.5AuthorizationandViews第二十八页，编辑于星期五：十二点三十七分。April200928*E.g.supposeabankclerkneedstoknowthenamesofthecustomersofeachbranch,butisnotauthorizedtoseespecificloaninformationapproach:denydirectaccesstothe

loan

relation,butgrantaccesstotheviewcust-loan,whichconsistsonlyofthenamesofcustomersandthebranchesatwhichtheyhavealoan createviewcust-loanas

selectbranch-name,customer-name

fromborrower,loan

whereborrower.loan-number=loan.loan-number8.7.5AuthorizationandViews(cont.)第二十九页，编辑于星期五：十二点三十七分。April200929*8.7.5AuthorizationandViews(cont.)第三十页，编辑于星期五：十二点三十七分。April200930*Creationofviewdoesnotrequireresourcesauthorization(creatingnewrelations)sincenorealrelationisbeingcreatedThecreatorofaviewgetsonlythoseprivilegesthatprovidenoadditionalauthorizationbeyondthathealreadyhade.g.ifcreatorofviewcust-loanhadonlyselect,insert,anddeleteauthorizationonborrowerandloan,hegetsonlyselect

,insert,anddeleteauthorizationoncust-loan8.7.5AuthorizationandViews(cont.)

borrower

loan

cust-loan

userprivilege:select,insert,deleteselect,insert,delete第三十一页，编辑于星期五：十二点三十七分。April200931*Audittrail(审计追踪)alog(日志)ofallchanges(inserts/deletes/updates)tothedatabasealongwithinformationsuchaswhichuserperformedthechange,andwhenthechangewasperformedAnotherDefinitionAudittrailofinMicrosoftPressComputingDictionaryinreferencetocomputing,ameansoftracingallactivitiesaffectingapieceofinformation,suchasadatarecord,fromthetimeitisenteredintoasystemtothetimeitisremovedanaudittrailmakeitpossibletodocument,forexample,whomadeachangetoaparticularrecordandwhen§8.7.7AuditTrail第三十二页，编辑于星期五：十二点三十七分。April200932*

T1:begin-transactionread(A)A:=A-50write(A)read(B)B:=B+50write(B)commit

T2:begin-transactionread(A)temp:=A*0.1A:=A-tempwrite(A)read(B)B:=B+tempwrite(B)abortFig.ConcurrentexecutingofT1andT2

log<T1start><T2start><T1,A,1000,950><T2,A,950,855><T1,B,2000,2050><T1,commit><T2,B,2050,2145><T2abort>Initially,A=1000,B=2000第三十三页，编辑于星期五：十二点三十七分。April200933*Audittrailisusedtotrackerroneous/fraudulentupdatesAsshowninChapter17,onthebasisoflog,DBSrecoveryisimplementedAudittrailcanbeimplementedusingtriggers,butmanydatabasesystemsprovidedirectsupport8.7.7AuditTrail(cont.)第三十四页，编辑于星期五：十二点三十七分。April200934*8.8.1EncryptionTechniquesDatamaybeencrypted

whendatabaseauthorizationprovisionsdonotoffersufficientprotection

第三十五页，编辑于星期五：十二点三十七分。April200935*

Digital

signaturesarealsousedtoverifyauthenticityofdata第三十六页，编辑于星期五：十二点三十七分。April200936*Appe