清华大学数据库原理课件第三章

数据库原理王建民清华大学软件学院2003年/秋

Chapter3

RelationalModel2/4/2023863项目答辩—清华英泰/武汉天喻Entity-RelationshipModelRequirementforawarehouseWhatwillbestoredinthewarehouse?ADBMSindependentdatabasemodelDatabaseDesignPhasesRequirementsCollection&AnalysisRequirementRequirementCollectionMethodsConceptualDesignE-RModelLogicalDesignPhysicalDesignApplicationDesignRelationalModelRelationalDBSDBMSHPLs,Tools,OSRequirementSpecificationConceptualDatabaseModelLogicalModelsPerformanceRequestsFinalSchemaApplicationRequirementRequirementSpecificationApplicationProgramsEnterpriseSchemaDatabaseSchemaConceptualModeltoLogicalModelReductionofanE-RSchematoTablesAdatabasewhichconformstoanE-RdiagramcanberepresentedbyacollectionoftablesTabularrepresentationisthebasisoftherelationalmodelConvertinganE-RdiagramtoatableformatisthebasisforderivingarelationaldatabasedesignfromanE-RdiagramRelationalModelRelationalDataStructureRelationRelationalOperations(QueryLanguages)RelationalAlgebraRelationalCalculusIntegrityConstraintsEntityIntegrityReferentialIntegrityUser-definedIntegrityMainContentRelationandRelationSchemaQueryLanguageRelationalAlgebraARelationisaTableAttributesTuplesDomainRelationDomain

thesetofpermittedvalueswiththesametypegivensetsD1,D2,….Dn,arelationrisasubsetofD1xD2x…xDnThusarelationisasetofn-tuples(a1,a2,…,an)where

ai

Di

niscalledarity(ordegree)ExampleofRelation

customer-name={Jones,Smith,Curry,Lindsay}

customer-street={Main,North,Park}

customer-city={Harrison,Rye,Pittsfield}

Thenr={(Jones,Main,Harrison),

(Smith,North,Rye),

(Curry,North,Rye),

(Lindsay,Park,Pittsfield)}

isarelationovercustomer-namexcustomer-streetxcustomer-cityTuple&TupleVariableAnelementofrelationrisatuple,representedbyarowinatableAtuplevariabletstandsforatuplet[customer-name]denotesthevalueoftonthecustomer-nameattributet[1]denotesvaluethefirstattributeoft(viz.positionalnotation)ArelationisasetoftupleswiththesametypeTuplesareUnordered

OrderoftuplesisirrelevantTuplesmaybestoredinanarbitraryorderContent-basedlocation

E.g.accountrelationwithunorderedtuplesIsanytablearelation?Attributevaluesare(normally)requiredtobeatomic,thatis,indivisibleE.g.multi-valuedattributevaluesarenotatomicE.positeattributevaluesarenotatomicNullValueThespecialvaluenull(空值)

isamemberofeverydomainmeans‘unknown’or‘doesnotexist’ThenullvaluecausescomplicationsinthedefinitionofmanyoperationsWemustdealwithitconsistentlyRelationSchemaDefinition:R(A,D,dom,F)TableheaderR(A1,A2,…,An)isarelationschema E.g.Customer-schema

(security-no,

customer-name, customer-street, customer-city)r(R)isarelation

instanceofrelationschemaR E.g. customer(Customer-schema)SuperkeyLetKRKisa

superkey

ofRifvaluesforKaresufficienttoidentifyauniquetupleofeachpossiblerelationr(R)

Example:{customer-name,customer-street}and

{customer-name}

arebothsuperkeysofCustomer,ifnotwocustomerscanpossiblyhavethesamenameCandidateKey&PrimaryKeyKisacandidatekeyifKisminimal{customer-name}isacandidatekeyforCustomer,sinceitisasuperkey(assumingnotwocustomerscanpossiblyhavethesamename),andnosubsetofitisasuperkeyAlthoughseveralcandidatekeysmayexist,oneofthecandidatekeysisselectedtobetheprimarykeyTheattributesoftheprimarykeyiscalledprimeattributes(主属性)ExampleofKeysJonesSmithCurryLindsaycustomer-nameMainNorthNorthParkcustomer-streetHarrisonRyeRyePittsfieldcustomer-citycustomertuples11011111111110111111121101111111311011111114Security#PrimaryKeyCandidateKeySuperKeyForeignKeysBorrowerReferencingrelationForeignkeysReferencedrelationCustomerLoanRelationalDatabaseAdatabaseschemaconsistsofasetofrelationschemasAdatabaseinstanceconsistsofasetofrelationsInrelationaldatabase,informationaboutanenterpriseisbrokenupintoparts,witheachrelationstoringonepartoftheinformation

E.g.

account:storesinformationaboutaccounts

depositor:storesinformationaboutwhichcustomer ownswhichaccountcustomer:storesinformationaboutcustomersQualityofRelationSchemaStoringallinformationasasinglerelationsuchas

bank(account-number,balance,customer-name,..)

resultsintoomuchrepetitionofinformationtheneedfortoomanynullvaluesNormalizationtheory(Chapter7)dealswithhowtodesignrelationalschemasE-RDiagramofaBankingEnterpriseSchemaDiagramfortheBankingEnterpriseBasicConceptsDomain (域),Relation(关系),Attribute(属性),

Tuple(元组)Superkey(码),CandidateKey(候选码),PrimaryKey(主码),ForeignKey(外码)RelationSchema(关系模式),

Relationinstance(关系实例),

RelationalDatabase(关系数据库)MainContentRelationandRelationSchemaQueryLanguageRelationalAlgebraQueryLanguageAlanguageinwhichauserspecifytherequestofinformationfromthedatabaseProceduralLanguageAsequenceofoperationsRelationalalgebraNon-ProceduralLanguageDescriptionofthedesiredinformationRelationalcalculusMainContentRelationandRelationSchemaQueryLanguageRelationalAlgebraWhatisAlgebra?MathematicalsystemconsistingofOperands

variablesorvaluesfromwhichnewvaluescanbeconstructedOperators

symbolsdenotingproceduresthatconstructnewvaluesfromgivenvaluesWhatisRelationalAlgebra?AnalgebrawhoseoperandsarerelationsorvariablesthatrepresentrelationsOperatorsaredesignedtodothemostcommonoperationsthatweneedtodowithrelationsinadatabaseTheoperatorstakeoneormorerelationsasinputsandgiveanewrelationasaresultTheresultisanalgebrathatcanbeusedasaquerylanguageforrelationsBasicOperatorsSixbasicoperatorsunionsetdifferenceCartesianproductSelectProjectRenameUnion–ExampleRelationsr,s:

rs:AB121AB23rsAB1213UnionOperationNotation:r

sDefinedas

r

s={t|trorts}Forr

stobevalid r,

smusthavethesamearity

(samenumberofattributes) Theattributedomainsmustbe

compatible(e.g.,2ndcolumnofrdealswiththesametypeofvaluesasdoesthe2ndcolumnofs)SetDifference–ExampleRelationsr,s:r–s:AB121AB23rsAB11SetDifferenceOperationNotationr–sDefinedas:

r–s={t|t

r

andts}

SetdifferencesmustbetakenbetweencompatiblerelationsrandsmusthavethesamearityattributedomainsofrandsmustbecompatibleCartesian-Product-ExampleRelationsr,s:rx

s:AB12AB11112222CD1019201010102010EaabbaabbCD10102010EaabbrsCartesian-ProductOperationNotationrxsDefinedas:

rxs={tq|trandqs}Assumethatattributesofr(R)ands(S)aredisjoint.(Thatis,RS=)Ifattributesofr(R)ands(S)arenotdisjoint,thenrenamingmustbeusedSelect–ExampleRelationrABCDA=B^D>5

(r)ABCD123710SelectOperationNotation:

p(r)piscalledtheselectionpredicate(选择谓词)Definedas:

p(r)={t|tr

andp(t)} Wherepisaformulainpropositionalcalculus(命题逻辑)

consistingoftermsconnectedby

(and),(or),(not)Eachtermisoneof

<attribute>op<attribute>or<constant>whereopisoneof:=,,>,,<,Project–ExampleRelationr:ABC102030401112AC1112=AC112A,C(r)ProjectOperationNotation:

A1,A2,…,

Ak(r) whereA1,A2areattributenamesandrisarelationTheresultisdefinedastherelationofkcolumnsobtainedbyerasingthecolumnsthatarenotlistedDuplicaterowsremovedfromresult,sincerelationsaresetsRenameExampleAllowsustoname,andthereforetoreferto,theresultsofrelational-algebraexpressionsLetEisarelationalalgebraexpressions

X(E)returntheresultrelationofEasXrelationWhentheCartesianproductofarelationwithitselfisdesired,AllowsustorefertoarelationbymorethanonenameLetr1isrelationontherelationschemaR1(son,father)

r1.father,r2.son(

r1.son=r2.father(r1X

r2(r1)))RenameOperationNotation:

x(E) returnstheresultofexpressionEunderthenameX Ifarelational-algebraexpressionEhasarityn,then

x

(A1,A2,…,An)

(E) returnstheresultofexpressionEunderthenameX,andwiththeattributesrenamedtoA1,A2,….,AnCompositionofOperationsCanbuildexpressionsusingmultipleoperationsExample:A=C(rxs)rxsA=C(rxs)AB11112222CD

1019201010102010EaabbaabbABCDE122102020aabFormalDefinitionAbasicexpressionintherelationalalgebraconsistsofeitheroneofthefollowing:ArelationinthedatabaseAconstantrelationFormalDefinition(c1)LetE1andE2berelational-algebraexpressions;thefollowingareallrelational-algebraexpressions:（并集）E1

E2（差集）

E1-E2（笛卡尔积）E1xE2（选择）p(E1),PisapredicateonattributesinE1（投影）s(E1),

SisalistconsistingofsomeoftheattributesinE1（换名）

x

(E1),xisthenewnamefortheresultofE1BankingExampleBranch 营业所

(branch-name,branch-city,assets)Customer 客户

(customer-name,customer-street,customer-only)Account 存款明细 (account-number,branch-name,balance)loan 贷款明细 (loan-number,branch-name,amount)depositor 存款账户 (customer-name,account-number)borrower 贷款账户 (customer-name,loan-number)ExampleQueries1、Findallloansofover$1200(找出所有贷款额超过1200$的贷款)amount>1200(loan)2、Findtheloannumberforeachloanofanamountgreaterthan$1200(找出所有贷款额超过1200$的贷款号)

loan-number(amount>1200(loan))ExampleQueries3、Findthenamesofallcustomerswhohavealoan,anaccount,orboth,fromthebank(找出贷款或存款的客户名)customer-name(borrower)customer-name(depositor)4、Findthenamesofallcustomerswhohavealoanandanaccountatbank(找出既有贷款又有存款的客户名)

customer-name(borrower)customer-name(depositor)customer-name(borrower)—(customer-name(borrower)—customer-name(depositor))ExampleQueries5、FindthenamesofallcustomerswhohavealoanatthePerryridgebranchcustomer-name(branch-name=“Perryridge”(borrower.loan-number=loan.loan-number(borrowerXloan)))6、FindthenamesofallcustomerswhohavealoanatthePerryridgebranchbutdonothaveanaccountatanybranchofthebankcustomer-name(branch-name=“Perryridge”(borrower.loan-number=loan.loan-number(borrowerxloan)))

–customer-name(depositor)ExampleQueries7、FindthenamesofallcustomerswhohavealoanatthePerryridgebranch(找出所有在Perryidge营业所贷款的用户名)Query1

customer-name(branch-name=“Perryridge”(borrower.loan-number=loan.loan-number(borrowerxloan)))Query2customer-name(loan.loan-number=borrower.loan-number ((branch-name=“Perryridge”(loan))xborrower))AdditionalOperationsWedefineadditionaloperationsthatdonotaddanypowertotherelationalalgebra,butthatsimplifycommonqueriesSetintersectionNaturaljoinDivisionAssignmentSet-Intersection-ExampleRelationr,s:rsAB121AB23rsAB2Set-IntersectionOperationNotation:r

sDefinedas:r

s={t|t

r

and

t

s}Assume:r,shavethesamearity

attributesofrandsarecompatibleNote:r

s=r-(r-s)NaturalJoin–ExampleRelationsr,s:AB12412CDaababB13123DaaabbErAB11112CDaaaabEsrsNatural-JoinOperationNotation:rsLetrandsberelationsonschemasRandSrespectively.TheresultisarelationonschemaR

Swhichisobtainedbyconsideringeachpairoftuples

trfromrandtsfroms.IftrandtshavethesamevalueoneachoftheattributesinR

S,atuple

tisaddedtotheresult,wherethasthesamevalueastronrthasthesamevalueastsonsNatural-JoinOperation(c1)Example:

R=(A,B,C,D)

S=(E,B,D)Resultschema=(A,B,C,D,E)r

sisdefinedas: r.A,r.B,r.C,r.D,s.E(r.B=s.Br.D=s.D(rxs))Letr(R)ands(S)havenocommonattributes,thenrs=?Division–ExampleRelationsr,s:r

s:AB12AB123111342rsDivisionOperationNotation:Suitedtoqueriesthatincludethephrase“forall”.LetrandsberelationsonschemasRandSrespectivelywhereR=(A1,…,Am,B1,…,Bn)S=(B1,…,Bn)TheresultofrsisarelationonschemaR–S=(A1,…,Am)

rs={t|tR-S(r)us(tur)}r

s

AnotherDivisionExampleABaaaaaaaaCDaabababbE11113111Relationsr,s:r

s:DabE11ABaaCrsABaaaaaCR-S(r)DefinitionwithBasicOperationsPropertyLetq=r

sThenqisthelargestrelationsatisfyingqxs

rDefinitionintermsofthebasicalgebraoperation

Letr(R)ands(S)berelations,andletS

R

rs=R-S(r)–R-S((R-S

(r)xs)–R-S,S(r))

ToseewhyR-S,S(r)simplyreordersattributesofr

R-S(R-S

(r)xs)–R-S,S(r))givesthosetuplestin

R-S

(r)suchthatforsometupleus,tu

r.InverseOperationofXRelationsr,s:rx

s:AB11112222CD101