考研-數(shù)據(jù)庫復(fù)試44a89ca5-278f4f318a43b3cd86f3927f

1DatabaseFundaments

TheRelationalDataModelCSEEofHunanUniversityJin-MinYang2016.032關(guān)系數(shù)據(jù)模型關(guān)系數(shù)據(jù)庫中的基本概念；數(shù)據(jù)模型，關(guān)系數(shù)據(jù)模型;關(guān)系數(shù)據(jù)模型的數(shù)據(jù)完整性約束;四類約束：主鍵，外鍵，屬性的取值域，業(yè)務(wù)規(guī)則約束；關(guān)系代數(shù)：數(shù)據(jù)的運算；3LearningContentsRelatinoaldatamodel:

relation

,

attribute,

domain,

tuple.relationschema,intension,meta-datarelationinstance,data,relationextension.Degree,cardinality.RelationalIntegrity:Domainconstraint.Entityintegrityconstraint

:primarykey.Referentialintegrityconstraint:foreignkeyEnterpriseconstraints

orsemanticconstraints.RelationalOperation:

Selection,projection,Union,Difference,Intersection,Cartesianproduct,Join,Division.4房產(chǎn)中介公司的數(shù)據(jù)庫

-propertybrokingcompany出租服務(wù)部的業(yè)務(wù)表單:給房東提供服務(wù)。DreamhomePropertyforrentdetailsPropertyNumber:PG21BusinessName:_____________Address:

___________________

TelNo:

____________________

OwnerNo:

_________________

ContactName:

_____________

BusinessType:

______________

Name:CarolFarrelAddress:6AchraySt,Grasgow

G329Dx________

TelNo:0141-357-7419._______

OwnerNo:C087_____________

Address:18DaleRoadCity:gloasgowPostcode:G12Type:HouseRent:600No.ofRooms:5

AllocatedtoBranch:163MainSt,GlasgowBranchNo:B003

ResponsibleStaff:AnnBeech_

Owner’sDetails5TheforminclientdepartmentDreamhomeClientdetailsClientNumber:CR74SeenBy:AnnBeechDate:24-Mar-05BranchNo:B003BranchCity:Glasgow

FirstName:MikeLastName:RichieAddress:16TainSt.TelNo:01475-392178.PA1G1YQ____PreferredProperityType:HouseMaximumMonthlyrent:

750Generalcomments::Currentlylivingathomewithparents

GettingmarriedinAugust

ProperityRequirementDetails客戶部的業(yè)務(wù)表單(Form):給要租房的客戶提供服務(wù)。6TheFormincontractdepartmentDreamhomeLeasedetailsLeaseNumber:10012RentStartDate:1-Jun-05RentFinishDate:30-Jun-06Duration:1Year

MonthlyRent:600PaymentMethod:ChequeDeposit:1200Paid(YorN):YClientNo.:CR74FullName:MikeRitchieAddress(Prevoius):18TainSt.

PA1G1YQTelNo.:01475-392178PropertyNo.:PG21Address:18DaleRd.

GlasgowG12

paymentDetails成交合同部的業(yè)務(wù)表單:與客戶簽訂租房合同7BranchBranchNoNameAddressTelNoManagerNoB01下河街營業(yè)點56HighSt.0207-774-56322004001B02南門口營業(yè)點64FernDr.014103岳麓山營業(yè)點18TainSt.01475-3921781999012B04高新開發(fā)區(qū)5TarbotRd.

1995009TableinDatabase8TableinDatabaseStaffNameStaffNoEmailphoneIdentityIdSalaryBranchMike2004001

420104771202400B01Tom2001017

420104771993600B01Mary1996035

420104771554200B03Jim1999004

420104772505400B01David1995009

420104771773000B029PropertyForRent:PropertyNoStreetCityPostcodeTyperoomsrentOwnerNoPA1416HolheadAberdeenAB75SUHouse6650C046PL946ArgyllStLondonNW2Flat4400C087PG46LawrenceSt.GlasgowG119QXFlat3350C040PG362ManorRdGlasgowG324QxFlat3375C093PG2118DaleRdGlasgowG12House5600C087PG165NovarDr.GlasgowG129AXFlat4450C093PrivateOwner(host):OwnerNofNamelNameAddressTelNoC046JoeKeogh2FergusDr.01224-861212C087CarolFarrel6AchraySt.0141-357-7419C040TinaMurphy63wellSt.0141-943-1728C093TonyShaw12ParkPl.0141-225-7025TableinDatabase10ClientClientNofNameAddressTelNoPrefTypeMaxRentdutyClerkCR76John56HighSt.0207-774-5632Flat425TomCR56Aline64FernDr.0141-848-1825Flat350AndyCR74Jerry18TainSt.01475-392178Hose750AndyCR62Pillip5TarbotRd.

Flat600JimTableinDatabase11TableinDatabaseViewingProperityNoClientNoViewDateCommentsPA14CR621-Jun-01goodsPL94CR741-Aug-01preferencePG21CR621-Jul-01expensive12Lease(契約）LeaseNoPropertyNoClientNorentPaymethodDepositPaidRentStartRentfinishDuration10024PA14CR62650Visa1300Y1-Jun-0131-May-021210075PL94CR76400Cash800N1-Aug-0131-Jan-02610012PG21CR74600cheque1200Y1-Jul-0130-Jun-0212TableinDatabase13LeaseLeaseNoPropertyNoClientNorentPaymentmethodDepositPaidRentStartRentfinishDuration10024PA14CR62650Visa1300Y1-Jun-0131-May-021210075PL94CR76400Cash800N1-Aug-0131-Jan-02610012PG21CR74600cheque1200Y1-Jul-0130-Jun-0212ProperityForRentProperityNoStreetCityPostcoderentPA1416HolheadAberdeenAB75SU650PL946ArgyllStLondonNW2400PG2118DaleRdGlasgowG12600ClientNofNamelNameAddressTelNoCR76JohnKay56HighSt.0207-774-5632CR74MikeRichie18TainSt.01475-392178CR62MaryTregar5TarbotRd.

Client合同部門需要的數(shù)據(jù)14BranchNoNameAddressTelNoManagerNoNameStaffNoEmailphoneIdentityIdSalaryBranchNoProperityNoClientNoViewDateCommentsLeaseNoPropertyNoClientNorentPaymentmethodDepositPaid。。。ProperityForRentProperityNoAddressrentownerNostaffNoClientNofNamelNameAddressTelNoClientBranchstaffLeaseOwnerNofNamelNameAddressTelNohostviewing表之間的關(guān)系15DataModelIntegratedcollectionofconceptsfordescribingdata,relationships

between

data,andconstraints

(約束)

onthedatainanorganization.DataModelcomprises:astructural（結(jié)構(gòu)）part;asetofintegrity(完整性）constraint(約束)rules;amanipulative（操作）part;Purpose:Torepresentdatainanaccurateandunderstandableway;Facilitating(方便)theimplementation(實現(xiàn))ofmanipulation(操作)andcorrectness(正確性)；16關(guān)系中的一些概念PropertyForRentPropertyNoAddressTyperoomsrentOwnerNoPA1416HolheadHouse6650C046PL946ArgyllStFlat4400C087PG46LawrenceSt.Flat3350C040PG362ManorRdFlat3375C093PG2118DaleRdHouse5600C087PG165NovarDr.Flat4450C093TuplesRelationAttributesDegree=6DomainiscurrencyDomainisinteger:1..100Cardinality=75……17AlternativeTerminologies（術(shù)語）FormaltermsAlternative1Alternative2RelationTablefileTupleRowRecordAttributeColumnField18RelationSchemasandInstancesrelationschema

,

relation

intension(內(nèi)涵）,meta-data(元數(shù)據(jù)）；

relationinstance（實例）,

relationextension（外延）,

Data,rows,

records,

tuples;databaseschema

isasetofrelationschemas；Inarow,Avalueof

afieldis

null

representsamissingorunknownvalue.

19ExampleofAttributeDomainAttributeDomainDefintionBranchNoCharacter:size4,rangeB001-B999streetCharacter:size25cityCharacter:size15postcodeCharacter:size8sexCharacter:size2,Value‘男’or‘女’birthdateDate,format:yyyy/mm/ddsalaryNumeric(8,2),range1500.00$~15000.00$20RelationPracticeQuestions1)Whatisthenameoftherelation?2)Whatisthecardinalityoftherelation?3)Whatisthedegreeoftherelation?4)WhatisthedomainofRentStart?WhatisthedomainofPaymentMethod?5)Whatislargerthesizeoftheintensionorextension?

LeaseNoPropertyNoClientNorentPaymentmethodDepositPaidRentStartRentfinishDuration10024PA14CR62650Visa1300Y1-Jun-0131-May-021210075PL94CR76400Cash800N1-Aug-0131-Jan-02610012PG21CR74600cheque1200Y1-Jul-0130-Jun-0212Lease21RelationalModelFormalDefinitionTherelationalmodelmaybevisualizedastablesandfields,butitisformallydefinedintermsofsetsandsetoperations.Arelationschema

R

withattributesA=<A1,A2,…,An>isdenotedR(A1,A2,…,An)whereeachAiisanattributenamethatrangesoveradomainDi

denoteddom(Ai).Example:

Relationschema:PrivateOwner(OwnerNo,FirstName,LastName,Address,TelephoneNo)

R

=PrivateOwnerA={OwnerNo,FirstName,LastName,Address,TelephoneNo}

domain(TelephoneNo);

domain(OwnerNo);22關(guān)系的特性Arelationhasseveralproperties:

1)Eachrelationnameisunique.

Notworelationshavethesamename.

2)Eachattributeofarelationhasadistinct

name.

3)Eachcelloftherelationcontainsexactlyoneatomicvalue

4)Thevaluesofanattributearelimitedindomain.

5)Eachtupleisdistinct.Therearenoduplicatetuples.6)Theorderofattributesisnotreallyimportant.

7)Theorderoftupleshasnosignificance.23關(guān)系的鍵Aprimarykey

(主鍵)isaminimalsetofattributesthatuniquelyidentifiesatupleinarelation.Acandidatekey

(候選鍵)

：一個關(guān)系中能作為主鍵的單個屬性或者是多個屬性；Aforeignkey

(外鍵)

：一個表中充當(dāng)外鍵的一個屬性或者屬性集合，它在另外一個表中必定充當(dāng)主鍵；注意：鍵的概念不是就關(guān)系實例relationalinstance而言，而是關(guān)系模式relationalschema層面的概念。24表中主鍵的本質(zhì)含義PropertyForRent:PropertyNoStreetCityPostcodeTyperoomsrentOwnerNoPA1416HolheadAberdeenAB75SUHouse6650C046PL946ArgyllStLondonNW2Flat4400C087PG46LawrenceSt.GlasgowG119QXFlat3350C040PG362ManorRdGlasgowG324QxFlat3375C093PG2118DaleRdGlasgowG12House5600C087PG165NovarDr.GlasgowG129AXFlat4450C093PrivateOwner(host):OwnerNofNamelNameAddressTelNoC046JoeKeogh2FergusDr.01224-861212C087CarolFarrel6AchraySt.0141-357-7419C040TinaMurphy63wellSt.0141-943-1728C093TonyShaw12ParkPl.0141-225-702525在一個表中，為什么主鍵非常重要？StaffHowmuchshouldthecompanypaysthesalaryeverymonth?Howmanystaffsinthecompany?Avergesalaryinthecompany?WhenMike’shissalaryispromotedto2800,howtoupdatetherelation?NameStaff_NoMobile_phoneIdentity_idSalarydepartmentMikeS01

420104771202400B001TomS07

420104771993600B001MaryS15

420104771554200B003MikeS01

420104771202400B001DavidS23

420104771773000B00226如何來確定一個表的主鍵NameStaff_NoMobile_phoneIdentity_idSalarydepartmentMikeS01

420104771202400B001TomS07

420104771993600B001MaryS15

420104771554200B003MikeS01

420104771202400B001DavidS23

420104771773000B002Staff對于對象實例，選擇既具有唯一標(biāo)識性，又對任何對象實例都肯定存在，而且很少變動的字段。設(shè)計時，必須認(rèn)真考慮。mobile_phone，Identity_id能否分別作為一個主鍵？27在一個表中，為什么外鍵非常重要NamephoneAgesalaryBrachNoMike1370731222302400B01Tom6607318423600B01Mary130073133224200B03David130073155363000B02Staff外鍵是保持關(guān)系（relationship）的手段。無外鍵，聯(lián)系就可能被中斷。例如：其它人無法與你聯(lián)系.在一個表中充當(dāng)外鍵的一個屬性集合，它在另外一個表中必定充當(dāng)主鍵；實施完整性約束；LeaseLeaseNoPropertyNoClientNoStaffphone10024PA14CR62137073122210075PL94CR76130073133310012PG21CR74137073155528在一個表中，為什么外鍵非常重要Therelationashipamongrelationswillbebroken.Associationeimpossible.ProperityForRentProperityNoAddressTypeRoomsPA1416HolheadFlat5PL946ArgyllStHouse4PG2118DaleRdFlat3ViewingClientNoNameAddressCR76John56HighSt.CR74Mike18TainSt.CR62Mary5TarbotRd.ClientProperityNoClientNoViewDateCommentsPA14CR621-Jun-01goodsPL94CR741-Aug-01preferencePG21CR621-Jul-01expensive29QuestionsQuestions:1)IsnameakeyforClient?2)IsviewDateandviewTimeakeyforviewing?3)Listthecandidatekeysforviewing,andforeignkeyProperityForRentProperityNoAddressTypeRoomsPA1416HolheadFlat5PL946ArgyllStHouse4PG2118DaleRdFlat3ViewingClientNoNameAddressCR76John56HighSt.CR74Mike18TainSt.CR62Mary5TarbotRd.ClientProperityNoClientNoViewDateViewTimeCommentsPA14CR621-Jun-0110:00goodsPL94CR741-Aug-0116:30preferencePG21CR621-Jul-019:10expensive30關(guān)系完整性約束IntegrityConstraints(完整性約束):insurethedataisaccurate.Typesofconstraints:1)Domain

（域）constraint

.

null

representsavaluethatiscurrentlyunknownornotapplicable.

null

isnotthesameaszerooranemptystring.2)Entity（實體）integrityconstraint

-primarykey,對任何一行記錄，它的取值不允許為

null.3)Referential(引用）integrityconstraint

-foreignkeyforeignkeyvaluemustmatchaprimarykeyvalueofatupleinthereferencedrelationorbenull.31IntegrityQuestionsQuestion:1)Findallviolationsofintegrityconstraintsinthesethreerelations.ProperityForRentProperityNoAddressTypeRoomsNull25Xishiluhouse4PA1416HolheadFlat5PL946ArgyllStHouseTomPG2118DaleRdFlat3ViewingClientNoNameAddressCR76John56HighSt.CR74Mike1-Oct-02NullTom56HighSt.CR6220045TarbotRd.ClientProperityNoClientNoViewDateCommentsPA14CR621-Jun-01goodsPL94CR741-Aug-01preferencePL54CR764-May-01NullPA14null4-May-01yesPG21CR621-Jul-01Expensive32第四類約束：業(yè)務(wù)規(guī)則約束數(shù)據(jù)庫中的數(shù)據(jù)，要求完整。也就是必須滿足業(yè)務(wù)規(guī)則要求(

enterpriseconstraints

/semanticintegrityconstraints.Examples:

學(xué)生選課一個學(xué)期不能超過30學(xué)分.

學(xué)年平均成績低于75分不能拿一等獎學(xué)金.

掛科24個學(xué)分不能再選課.Howtoperformtheenterpriseconstraints?Usingtriggersandassertions.

第二次作業(yè)就前一個作業(yè)，指出每個表的主鍵，外鍵，每個字段的域；就前一章所舉的一個大學(xué)的教務(wù)數(shù)據(jù)庫，設(shè)它包含有6個表：學(xué)生表，老師表，課程表，老師開課表，學(xué)生選課表，排課表。對這6個表，分別確定其包括哪些字段？并就每個表分別寫出它的4行數(shù)據(jù)；檢查表的設(shè)計是否合理？然后確定每個表的主鍵；分析外鍵，如果有，則標(biāo)識出來，指明它在哪個表中是主鍵。對每個表的每個字段，給出域約束（數(shù)據(jù)類型，取值范圍）；對每個表給出主鍵約束；如有外鍵，給出外鍵約束；舉三個業(yè)務(wù)規(guī)則約束的例子；一旦發(fā)現(xiàn)哪個學(xué)生對此三約束熟視無睹？就請他明年重修這門課！如何理解數(shù)據(jù)的完整性主鍵約束暗示我們，在數(shù)據(jù)庫中，數(shù)據(jù)是分類存儲在不同的表中，一個類對應(yīng)一張表；每個類別都有它的標(biāo)識性字段（主鍵），對于同一個東東（內(nèi)容），確保表中僅只一行數(shù)據(jù)來記錄它，不會出現(xiàn)重復(fù)的行；外鍵約束是保證不同表之間的數(shù)據(jù)，其關(guān)系不中斷的一種措施；例如房子表中的一行數(shù)據(jù)記錄了某個房子的信息，而合同表是記錄房子被租出的信息，因此，如果合同表中的某行數(shù)據(jù)（即某個租房成交合同）中的房子編號的值在房子表中沒有對應(yīng)的行記錄，那么這行數(shù)據(jù)（這個合同）就變得毫無含義。Domain約束，則是描述某個表中，某個字段的含義，數(shù)據(jù)庫類型，取值范圍；確保表中的數(shù)據(jù)有含義，表達想要的意思；例如是手機號碼，就必須1開頭，11個數(shù)字，就可計算屬于哪個公司，哪個地市？如何理解數(shù)據(jù)的完整性完整性約束是屬于數(shù)據(jù)模式（Schema）中的內(nèi)容，因此是屬于原則和邏輯范疇的概念；而不是就當(dāng)前已知的數(shù)據(jù)而言的，要求事先就要設(shè)定；當(dāng)用戶對數(shù)據(jù)庫中某個表，添加一行數(shù)據(jù)，修改某行數(shù)據(jù)，刪除某行數(shù)據(jù)時，就要進行完整性檢查，如果用戶的操作違背了完整性約束，那么用戶的操作就會被拒接，這樣就確保了數(shù)據(jù)庫中數(shù)據(jù)的完整性；例如，當(dāng)用戶給房子表中添加一行記錄時，數(shù)據(jù)庫管理系統(tǒng)就要拿用戶要添加的這個房子的房子編號與數(shù)據(jù)庫中房子表中已有的每行數(shù)據(jù)進行比對，如果發(fā)現(xiàn)相同，那么就說明用戶要添加的這個房子，已經(jīng)在數(shù)據(jù)庫中存在了，用戶的這個添加操作是重復(fù)性行為。關(guān)系代數(shù)數(shù)據(jù)庫的數(shù)據(jù)是所有數(shù)據(jù)，而對于某個具體用戶來說，僅只關(guān)心他職責(zé)范圍內(nèi)的數(shù)據(jù)，例如，對催繳員來說，僅只需要合同表上“客戶姓名，電話，月租金”，好及時電話通知客戶來交租金。對用戶想要的數(shù)據(jù)，如何來滿足？通過關(guān)系運算來解決。LeaseNoPropertyNoClientNorent租金DepositPaidRentStart到期Duration10024PA14CR62650Visa1300Y1-Jun-0131-May-021210075PL94CR76400Cash800N1-Aug-0131-Jan-02610012PG21CR74600cheque1200Y1-Jul-0130-Jun-0212LeaseClientNofNamelNameAddressTelNoCR76JohnKay56HighSt.0207-774-5632CR74MikeRichie18TainSt.01475-392178CR62MaryTregar5TarbotRd.

Client37RelationalAlgebra(代數(shù)）Relationalalgebraconsistsofoperands(操作數(shù)）andasetofoperators（操作符）.Input：

oneormorerelations.Output：

arelation.

Closureproperty-inputisrelations,outputisrelations

Unaryoperations-operateononerelation

Binaryoperations-havetworelationsasinputAsequenceofrelationalalgebraoperatorsiscalledarelationalalgebraexpression.

38RelationalAlgebraOperatorsRelationalOperators:

Selectionσ

ProjectionΠ

Cartesianproduct×

Join?

Union∪

Difference-

Intersection∩

Division÷Notethatrelationalalgebraisthefoundationofallrelationaldatabasesystems.SQLgetstranslatedintorelationalalgebra.

39SelectionOperationTheselectionoperation

isaunaryoperation;

output：asubsetofthetuplesoftheinputrelation.

theoutputrelationhasthesamenumberofcolumnsastheinputrelation,butmayhavelesstuples.σF(R)={t|t∈RandF(t)istrue}Where

Risarelation,tisatuplevariable,

Fisapredicate40SelectionExampleσType＝’House’(PropertyForRent)PropertyForRent:PropertyNoStreetTyperoomsrentOwnerNoPA1416HolheadHouse6650C046PL946ArgyllStFlat4400C087PG46LawrenceSt.Flat3350C040PG362ManorRdFlat3375C093PG2118DaleRdHouse5600C087PG165NovarDr.Flat4450C093PropertyNoStreetTyperoomsrentOwnerNoPA1416HolheadHouse6650C046PG2118DaleRdHouse5600C087PropertyNoStreetTyperoomsrentOwnerNoPG46LawrenceSt.Flat3350C040PG362ManorRdFlat3375C093σType＝’flat‘ANDRent<400(PropertyForRent)41SelectionQuestions

Questions:Writetherelationalalgebraexpressionthat:1)ReturnsallclientsviewingpropertyPA14.2)Returnsallallclientsviewingduring1-May-01and30-Jul-013)ReturnsallpropertythathavebeenviewedbyclientCR74.ViewingProperityNoClientNoViewDateCommentsPA14CR621-Jun-01goodsPL94CR741-Aug-01preferencePL54CR764-May-01NullPA14CR7612-May-01yesPA14CR7422-Jul-01ToosmallPG21CR621-Jul-01Expensive42ProjectionOperationaunaryoperation;outputcontainsasubsetoftheattributesoftheinputrelationandallnon-duplicatetuples.

ΠA1,…,Am(R)={t[A1,…,Am]|t∈R}Where

Risarelation,tisatuplevariable

{A1,…,Am}isasubsetoftheattributesofRoverwhichtheprojectionwillbeperformed.

OrderofA1,…,Am

issignificantintheresult.43ProjectionExample

ΠClientNo,Comments(Viewing)ViewingProperityNoClientNoViewDateCommentsPA14CR621-Jun-01goodsPL94CR741-Aug-01preferencePL54CR764-May-01NullPA14CR7612-May-01yesPA14CR7422-Jul-01ToosmallPG21CR621-Jul-01ExpensiveClientNoCommentsCR62goodsCR74preferenceCR76NullCR76yesCR74ToosmallCR62ExpensiveClientNoCR62CR74CR76Questions:Whichclientshaveatleastaviewingtoproperty?ΠClientNo(Viewing)Whenareweguaranteedtoneverhaveduplicateswhenperformingaprojectionoperation?44Unionoperationabinaryoperation;input:tworelationsRandS;output:alltuplesinRorinSorinbothRandS.Duplicatetuplesareeliminated.R∪S={t|t∈Rort∈S}whereR,Sarerelations,tisatuplevariable.RandSmustbepatible.havethesamenumberofattributeswiththesamedomains.45ΠProperityNo(Viewing)∪ΠProperityNo(PropertyForRent)ViewingProperityNoClientNoViewDateCommentsPA08CR621-Jun-01goodsPL94CR741-Aug-01preferencePA14CR7612-May-01yesPA14CR7422-Jul-01ToosmallPropertyForRentProperityNoPA08PL94PA14PG4PG36PG21PG16PropertyNoStreetTyperoomsrentOwnerNoPA1416HolheadHouse6650C046PL946ArgyllStFlat4400C087PG46LawrenceSt.Flat3350C040PG362ManorRdFlat3375C093PG2118DaleRdHouse5600C087PG165NovarDr.Flat4450C09346Question?ViewingProperityNoClientNoViewDateCommentsPA08CR621-Jun-01goodsPL94CR741-Aug-01preferencePA14CR7612-May-01yesPA14CR7422-Jul-01ToosmallWhichpropertiesareviewedorrentedout?LeaseLeaseNoPropertyNoClientNorentPaymentmethodRentStartRentfinishDuration10024PA14CR62650Visa1-Jun-0131-May-021210075PL94CR76400Cash1-Aug-0131-Jan-02610012PG21CR74600cheque1-Jul-0130-Jun-021247SetDifferenceabinaryoperationoutput:allthetuplesofRthatarenotinS.R–S={t|t∈RandtS}whereRandSarerelations,tisatuplevariable.Notethat:

R-S≠

S-R

RandSmustbeunioncompatible.48ΠProperityNo(Viewing)-ΠProperityNo(PropertyForRent)ViewingProperityNoClientNoViewDateCommentsPA08CR621-Jun-01goodsPL94CR741-Aug-01preferencePA14CR7612-May-01yesPA14CR7422-Jul-01ToosmallPropertyForRentProperityNoPA08PropertyNoStreetTyperoomsrentOwnerNoPA1416HolheadHouse6650C046PL946ArgyllStFlat4400C087PG46LawrenceSt.Flat3350C040PG362ManorRdFlat3375C093PG2118DaleRdHouse5600C087PG165NovarDr.Flat4450C093Question:Whichpropertiesisnotviewedbyanyclient?Whichpropertieshavebeenviewed,butarenotrentedoutinlease?49Intersectionabinaryoperation;output:alltuplesthatareinbothRandS.R∩S={t|t∈Randt∈S}whereR,Sarerelations,tisatuplevariable.RandSmustbepatible.R∩S=R-(R-S)=S-(S-R).50ΠProperityNo(Viewing)∩ΠProperityNo(PropertyForRent)ViewingProperityNoClientNoViewDateCommentsPA08CR621-Jun-01goodsPL94CR741-Aug-01preferencePA14CR7612-May-01yesPA14CR7422-Jul-01ToosmallPropertyForRentProperityNoPL94PA14PropertyNoStreetTyperoomsrentOwnerNoPA1416HolheadHouse6650C046PL946ArgyllStFlat4400C087PG46LawrenceSt.Flat3350C040PG362ManorRdFlat3375C093PG2118DaleRdHouse5600C087PG165NovarDr.Flat4450C093Question:Whichpropertiesarebothviewedandrentedout?51CartesianProductTheCartesianproduct

returnsthesetofallorderedpairs.Example:

D1

={1,2,3}

D2

={A,B}

D1

×

D2

={(1,A),(2,A),(3,A),(1,B),(2,B),(3,B)}PracticeQuestions:

1)ComputeD2

×

D1.

2)ComputeD2

×

D2.

3)howmanyelementsarethereinD1

×

D2

?.4)WhatisthecardinalityofD1

×D2

×D1

×D1?

52Cartesian

ProductAbinaryoperation;Output:aconcatenationofeverytupleofRwitheverytupleofS.R×

S={t|t[A1,…,Ak1]∈Randt[Ak1+1,…,Ak1+k2]∈S}DegreeofR×

Sisdegree(R)+degree(s);CardinalityofR×

Sis|R|*|S|.

TheCartesianproductisalsoknownascrossproduct.53CartesianProductExample

Staff×

PropertyForRentStaffStaffNoNameBrachNoMikeS01B01TomS07B01MaryS15B03PropertyForRentPropertyNoStreetTypePA1416HolheadHousePL946ArgyllStFlatPG46LawrenceSt.FlatNameStaffNoBrachNoPropertyNoStreetTypeMikeS01B01PA1416HolheadHouseMikeS01B01PL946ArgyllStFlatMikeS01B01PG49LawrenceSt.FlatTomS07B01PA1416HolheadHouseTomS07B01PL946ArgyllStFlatTomS07B01PG49LawrenceSt.FlatMaryS15B03PA1416HolheadHouseMaryS15B03PL946ArgyllStFlatMaryS15B03PG49LawrenceSt.Flat54θ-JoinR?FS=σF(R×S).

55θ-JoinExample

Staff?Type=‘Flat’

PropertyForRentStaffStaffNoNameBrachNoMikeS01B01TomS07B01MaryS15B03PropertyForRentPropertyNoStreetTypePA1416HolheadHousePL946ArgyllStFlatPG46LawrenceSt.FlatNameStaffNoBrachNoPropertyNoStreetTypeMikeS01B01PL946ArgyllStFlatMikeS01B01PG49LawrenceSt.FlatTomS07B01PL946ArgyllStFlatTomS07B01PG49LawrenceSt.FlatMaryS15B03PL946ArgyllStFlatMaryS15B03PG49LawrenceSt.FlatMikeS01B01PA1416HolheadHouseTomS07B01PA1416HolheadHouseMaryS15B03PA1416HolheadHouse56TypesofJoinsequijoin

:

Viewing?

Viewing.PropertyNo=PropertyForRent.PropertyNo

PropertyForRentNaturaljoin

:R?S;theequijoinofRandSoverasetofattributescommontobothRandS.

Itremovesthe“extracopies”ofthejoinattributes.

Normallyassumestheattributeshavethesamenameinbothrelations.57EquiJoinExample

Staff?Staff.staffNo=PropertyforRent.StaffNo

PropertyForRentStaffNameStaffNoBranchMikeS01B01TomS07B01MaryS15B03PropertyForRentPropertyNoStaffNoTypePA14S15HousePL94S01FlatPA74S15FlatPA08S07FlatPG36S07HousePG4S15FlatNameS.StaffNoBrachNoP.StaffNoPropertyNoTypeMikeS01B01S01PL94FlatTomS07B01S07PA08FlatTomS07B01S07PG36HouseMaryS15B03S15PA14HouseMaryS15B03S15PA74FlatMaryS15B03S15PG4Flat58NaturalJoinExample

Staff?

PropertyForRentStaffNameStaffNoBranchMikeS01B01TomS07B01MaryS15B03PropertyForRentPropertyNoStaffNoTypePA14S15HousePL94S01FlatPA74S15FlatPA08S07FlatPG36S07HousePG4S15FlatNameStaffNoBrachNoPropertyNoTypeMikeS01B01PL94FlatTomS07B01PA08FlatTomS07B01PG36HouseMaryS15B03PA14HouseMaryS15B03PA74FlatMaryS15B03PG4Flat59NaturalJoinExample

Staff?

PropertyForRentStaffNameStaffNoBranchMikeS01B01TomS07B01MaryS15B03PropertyForRentPropertyNoStaffNoTypePA14S15HousePL94S01FlatPA74S15FlatPA08S07FlatPG36S07HousePG4S15FlatNameStaffNoBrachNoPropertyNoTypeMikeS01B01PL94FlatTomS07B01PA08FlatTomS07B01PG36HouseMaryS15B03PA14HouseMaryS15B03PA74FlatMaryS15B03PG4Flat要有共同的屬性，在一個表中是外鍵，在另一個表中是主鍵；實現(xiàn)了列的擴加功能；沒有關(guān)聯(lián)的行都不在結(jié)果中；60JoinPracticeQuestionsComputethefollowingjoins:Client?Viewing?PropertyForRentProperityNoClientNoViewDateCommentsPA08CR621-Jun-01goodsPL94CR741-Aug-01preferencePA14CR7422-Jul-01ToosmallPropertyForRentPropertyNoAddressTypePA1416HolheadHousePL946ArgyllStFlatPG46LawrenceSt.FlatPG362ManorRdFlatPG2118DaleRdHousePA085NovarDr.FlatViewingClientNoNameAddressCR76John56HighSt.CR74Mike18TainSt.CR78Tom7LondonSt.CR62Mary5TarbotRd.ClientQuestion:Pleasegeneratealistofclientname,propertyaddress,hostnameforthoseclientsinlease.61JoinPracticeQuestionsQuestion:Pleasegeneratealistofclientname,propertyaddress,hostnameforthoseclientsinlease.Lease?Client?privateOwner?PropertyForRentWhy?62典型案例請給出一個清單，列出客戶名，電話，以及所租的房子的地址分析：客戶姓名，電話在Client

表中，所租的房子的地址在PropertyForRent表中；涉及2個表；但是Client表和PropertyForRent表并沒有直接的聯(lián)系，也就是說Client

?

PropertyForRent

沒有意義，其結(jié)果為空，無任何記錄；因此要使Client表和PropertyForRent表關(guān)聯(lián)起來，必須通過lease表來充當(dāng)橋梁：Client?Lease?PropertyForRent63JoinPracticeQuestionsListclientname,propertyaddress,hostnameforthosewhohavesignedupalease:

Lease(clientNo,propertyNo);Client(clientNo,clientName);propertyForRent(propertyno,address,ownerNo);privateOwner(ownerNo,hostName);64OuterJoins1)Leftouterjoin

-R?S-TheoutputcontainsalltuplesofRthatmatchwithtuplesofS.IfthereisatupleinRthatmatcheswithnotupleinS,thetupleisincludedinthefinalresultandispaddedwithnullsfortheattributesofS.

2)Rightouterjoin

-R?S.3)Fullouterjoin

-R?

?S.65LeftouterjoinExampleProperityNoClientNoViewDateCommentsPA08CR621-Jun-01goodsPL94CR741-Aug-01preferencePA08CR7422-Jul-01ToosmallPropertyForRentPropertyNoAddressTypePA1416HolheadHousePL946ArgyllStFlatPG46LawrenceSt.FlatPG362ManorRdFlatPG2118DaleRdHousePA085NovarDr.FlatViewingPropertyForRent?

ViewingPropertyNoAddressTypeClientNoViewDateCommentsPA1416Holhead