0Ch04advSQL

Source:DatabaseSystemConcepts,Silberschatzetc.20064-1IntroductiontoDatabaseCHAPTER4AdvancedSQLSQLDataTypesandSchemasIntegrityConstraintsAuthorizationEmbeddedSQLDynamicSQLFunctionsandProceduralConstructs**RecursiveQueries**AdvancedSQLFeatures**1-50Source:DatabaseSystemConcepts,Silberschatzetc.20064-2BasicDataTypes:IntegerChar…Build-inDataTypesinSQLUser-DefinedTypesLarge-ObjectTypes4.1SQLDataTypesandSchemasSource:DatabaseSystemConcepts,Silberschatzetc.20064-34.1.1Build-inDataTypesinSQLBuild-inDataTypesinSQL:date:Dates,containinga(4digit)year,monthanddate,‘2005-7-27’time:Timeofday,inhours,minutesandseconds,‘09:00:30’timestamp:dateplustimeofday,‘2005-7-2709:00:30.75’OperationsforBuild-inDataTypesinSQL:Convertstringtypestodate/time/timestamp,caststringasdateFindinterval:periodoftime,dtae1–date2•Intervalvaluescanbeaddedtodate/time/timestampvaluesExtractvaluesofindividualfieldsfromdate/time/timestamp•Example:extract(yearfromr.starttime)Comparison:ifdate1date2then…Source:DatabaseSystemConcepts,Silberschatzetc.20064-44.1.3Large-ObjectTypesLargeobjects(photos,videos,CADfiles,etc.)arestoredasalargeobject:blob:binarylargeobject–•objectisalargecollectionofuninterpretedbinarydata•whoseinterpretationislefttoanapplicationoutsideofthedatabasesystemclob:characterlargeobject–•objectisalargecollectionofcharacterdataWhenaqueryreturnsalargeobject,apointerisreturnedratherthanthelargeobjectitself.Source:DatabaseSystemConcepts,Silberschatzetc.20064-54.2IntegrityConstraintsIntegrityConstraintsguardagainstaccidentaldamagetothedatabase,byensuringthatauthorizedchangestothedatabasedonotresultinalossofdataconsistency.Example:•E.g.1:Acheckingaccountmusthaveabalancegreaterthan$10,000.00•E.g.2:Asalaryofabankemployeemustbeatleast$4.00anhour•E.g.3:Acustomermusthavea(non-null)phonenumberSource:DatabaseSystemConcepts,Silberschatzetc.20064-64.2.1ConstraintsonaSingleRelationnotnullprimarykeyuniquecheck(P),wherePisapredicatecreatetablebranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger,primarykey(branch-name),check(assets=0))Source:DatabaseSystemConcepts,Silberschatzetc.20064-74.2.2NotNullConstraintE.g.Declarebranch_nameforbranchisnotnullbranch_namechar(15)notnullE.g.DeclarethedomainDollarstobenotnullcreatedomainDollarsnumeric(12,2)notnullcreatetablebranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger,primarykey(branch-name),check(assets=0))Source:DatabaseSystemConcepts,Silberschatzetc.20064-84.2.3UniqueConstraintAstatement:unique(A1,A2,…,Am)TheuniquespecificationstatesthattheattributesA1,A2,…Amformacandidatekey.Source:DatabaseSystemConcepts,Silberschatzetc.20064-94.2.4Thecheckclausecheck(P),wherePisapredicateExample1:Declarebranch_nameastheprimarykeyforbranchandensurethatthevaluesofassetsarenon-negative.createtablebranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger,primarykey(branch-name),check(assets=0))Example2:Usecheckclausetoensurethatanhourly_wagedomainallowsonlyvaluesgreaterthanaspecifiedvalue.createdomainhourly_wagenumeric(5,2)constraintvalue_testcheck(value=4.00)•Theclauseconstraintvalue_testisoptionalattributeDomainSource:DatabaseSystemConcepts,Silberschatzetc.20064-104.2.5ReferentialIntegrityReferentialIntegrity:ensuresthatavaluethatappearsinonerelationforagivensetofattributesalsoappearsforacertainsetofattributesinanotherrelation.Example:If“Perryridge”isabranch-nameappearinginoneofthetuplesintheaccountrelation,thenthereexistsatupleinthebranchrelationforbranch“Perryridge”.InSQLcreatetablestatement:Theprimarykeyclause:listsattributesthatcomprisetheprimarykey.Theuniquekeyclause:listsattributesthatcompriseacandidatekey.Theforeignkeyclause:liststheattributesthatcomprisetheforeignkeyandthenameoftherelationreferencedbytheforeignkey.Source:DatabaseSystemConcepts,Silberschatzetc.20064-11ReferentialIntegrity:Examplecreatetableaccount(account-numberchar(10),branch-namechar(15),balanceinteger,primarykey(account-number),foreignkey(branch-name)referencesbranch)3.account1.branchSource:DatabaseSystemConcepts,Silberschatzetc.20064-12ReferentialIntegrityinSQLForeignkeyscanbespecifiedaspartoftheSQLcreatetablestatement:ByusingtheforeignkeyclauseBydefault,aforeignkeyreferencestheprimarykeyattributesofthereferencedtablecreatetableaccount(account-numberchar(10),branch-namechar(15),balanceinteger,primarykey(account-number),foreignkey(branch-name)referencesbranch)Source:DatabaseSystemConcepts,Silberschatzetc.20064-13Example:BankingDatabaseBankingDatabase:consists6relations:1.branch(branch-name,branch-city,assets)2.customer(customer-name,customer-street,customer-only)3.account(account-number,branch-name,balance)4.loan(loan-number,branch-name,amount)5.depositor(customer-name,account-number)6.borrower(customer-name,loan-number)Source:DatabaseSystemConcepts,Silberschatzetc.20064-14Example:BankingDatabase1.branch2.customer客戶(存款戶,貸款戶)5.account存款帳3.depositor存款戶6.loan貸款帳4.borrower貸款戶分公司Source:DatabaseSystemConcepts,Silberschatzetc.20064-15ReferentialIntegrityinSQL: