数据库原理 第6章 完整性和安全性

1.1DatabaseSystemConceptsChapter6:IntegrityandSecurityDomainConstraintsReferentialIntegrityAssertions断言TriggersSecurityAuthorizationAuthorizationinSQL1.2DatabaseSystemConceptsDomainConstraintsIntegrityconstraintsguardagainstaccidentaldamagetothedatabase,byensuringthatauthorizedchangestothedatabasedonotresultinalossofdataconsistency.Domainconstraintsarethemostelementaryformofintegrityconstraint.Theytestvaluesinsertedinthedatabase,andtestqueriestoensurethatthecomparisonsmakesense.NewdomainscanbecreatedfromexistingdatatypesE.g.createdomainDollarsnumeric(12,2)createdomainPoundsnumeric(12,2)WecannotassignorcompareavalueoftypeDollarstoavalueoftypePounds.把一个域中的值转换到另一个域中。However,wecanconverttypeasbelow(castr.AasPounds)(Shouldalsomultiplybythedollar-to-poundconversion-rate)1.3DatabaseSystemConceptsDomainConstraints(Cont.)ThecheckclauseinSQL-92permitsdomainstoberestricted:Usecheckclausetoensurethatanhourly-wagedomainallowsonlyvaluesgreaterthanaspecifiedvalue.createdomainhourly-wagenumeric(5,2)constraintvalue-test约束名check(value=4.00)Thedomainhasaconstraintthatensuresthatthehourly-wageisgreaterthan4.00Theclauseconstraintvalue-testisoptional;usefultoindicatewhichconstraintanupdateviolated.1.4DatabaseSystemConceptsCanhavecomplexconditionsindomaincheckcreatedomainAccountTypechar(10)constraintaccount-type-testcheck(valuein(‘Checking’,‘Saving’))在deposit关系中指定约束：check(branch-namein(selectbranch-namefrombranch))这个条件必须不仅仅是在deposit中插入或修改一个元组的时候检测，而且在关系branch中的元组被删除或修改时也要检测。1.5DatabaseSystemConceptsdropdomainhourly-wagealterdomainhourly-wagedropconstraintvalue-testalterdomainhourly-wageaddconstraintvalue-testcheck(value=4.00)1.6DatabaseSystemConceptsReferentialIntegrityEnsuresthatavaluethatappearsinonerelationforagivensetofattributesalsoappearsforacertainsetofattributesinanotherrelation.Example:If“Perryridge”isabranchnameappearinginoneofthetuplesintheaccountrelation,thenthereexistsatupleinthebranchrelationforbranch“Perryridge”.Danglingtuples悬挂元组FormalDefinitionLetr1(R1)andr2(R2)berelationswithprimarykeysK1andK2respectively.ThesubsetofR2isaforeignkeyreferencingK1inrelationr1,ifforeveryt2inr2theremustbeatuplet1inr1suchthatt1[K1]=t2[].Referentialintegrityconstraint参照完整性约束alsocalledsubsetdependency子集依赖sinceitscanbewrittenas(r2)K1(r1)1.7DatabaseSystemConcepts参照完整性示例SnoSnameSsexSageSdept99101张三男20CS99103李四男19CS……………98102王五女19ISSdeptSlocSnameCS曹光标大楼计算机科学IS竺可桢大楼信息科学MA华罗庚大楼数学………主码外码，不是Student的主码，但是Department的主码。Student中Sdept中的值都取自Department表中相应Sdept的值。指定外码时，列名不一定要求相同。StudentDepartment1.8DatabaseSystemConceptsReferentialIntegrityintheE-RModelConsiderrelationshipsetRbetweenentitysetsE1andE2.TherelationalschemaforRincludestheprimarykeysK1ofE1andK2ofE2.ThenK1andK2formforeignkeysontherelationalschemasforE1andE2respectively.Weakentitysetsarealsoasourceofreferentialintegrityconstraints.Fortherelationschemaforaweakentitysetmustincludetheprimarykeyoftheentitysetonwhichitdepends.RE1E21.9DatabaseSystemConceptsCheckingReferentialIntegrityonDatabaseModificationThefollowingtestsmustbemadeinordertopreservethefollowingreferentialintegrityconstraint:(r2)K(r1)Insert.Ifatuplet2isinsertedintor2,thesystemmustensurethatthereisatuplet1inr1suchthatt1[K]=t2[].Thatist2[]K(r1)Delete.Ifatuple,t1isdeletedfromr1,thesystemmustcomputethesetoftuplesinr2thatreferencet1:=t1[K](r2)Ifthissetisnotempty,eitherthedeletecommandisrejectedasanerror,orthetuplesthatreferencet1mustthemselvesbedeleted(cascadingdeletionsarepossible).1.10DatabaseSystemConcepts参照完整性示例(r2)SnoSnameSsexSage99101张三男20CS99103李四男19CS98102王五女19ISr2KSlocSnameCS曹光标大楼计算机科学IS竺可桢大楼信息科学MA华罗庚大楼数学r1K(r1)01201郑六男18MA01202任七女18IEt2[]insert=t1[K](r2)delete1.11DatabaseSystemConceptsDatabaseModification(Cont.)Update.Therearetwocases:Ifatuplet2isupdatedinrelationr2andtheupdatemodifiesvaluesforforeignkey,thenatestsimilartotheinsertcaseismade.Lett2’denotethenewvalueoftuplet2.Thesystemmustensurethatt2’[]K(r1)Ifatuplet1isupdatedinr1,andtheupdatemodifiesvaluesfortheprimarykey(K),thenatestsimilartothedeletecaseismade.Thesystemmustcompute=t1[K](r2)usingtheoldvalueoft1(thevaluebeforetheupdateisapplied).Ifthissetisnotempty,theupdatemayberejectedasanerror,ortheupdatemaybecascadedtothetuplesintheset,orthetuplesinthesetmaybedeleted.1.12DatabaseSystemConceptsReferentialIntegrityinSQLPrimary,candidatekeysandforeignkeyscanbespecifiedaspartoftheSQLcreatetablestatement:Theprimarykeyclauseofthecreatetablestatementincludesalistoftheattributesthatcomprisetheprimarykey.Theuniquekeyclauseofthecreatetablestatementincludesalistoftheattributesthatcompriseacandidatekey.Theforeignkeyclauseofthecreatetablestatementincludesbothalistoftheattributesthatcomprisetheforeignkeyandthenameoftherelationreferencedbytheforeignkey.1.13DatabaseSystemConceptsReferentialIntegrityinSQL–Examplecreatetablecustomer(customer-namechar(20),customer-streetchar(30),customer-citychar(30),primarykey(customer-name))createtablebranch(branch-namechar(15),branch-citychar(30),assetsinteger,primarykey(branch-nam