復(fù)試-877學(xué)長數(shù)據(jù)庫002課件10版database

2TheRelationalModelofDataAnOverviewofDataModelsBasicsoftheRelationalModelDefiningaRelationSchemainSQLAnAlgebraicQueryLanguageConstraintsonRelations

2.1AnOverviewofDataModelsAdatamodelisanotationfordescribingdataorinformation.Thedescriptiongenerallyconsistsofthreeparts:Structureofthedatarelationalmodel=tables;networkandhierarchicalmodels=graphs/trees.OperationsonthedataConstraintsonthedata2.1AnOverviewofDataModelsImportantDataModels:TheRelationalModel,includingobject-relationalextensions;TheSemistructured-dataModel,includingXML(擴(kuò)展標(biāo)記語言);NetworkandHierarchicaldataModelsHigh-LevelDatabaseModelTheEntity/RelationshipModelObjectDefinitionLanguagetitleyearlengthgenreGoneWiththeWind1939231dramaStarWars1977124sciFiWayne’sWorld199295comedy2.2BasicsoftheRelationalModelRelation:Therelationalmodelgivesusasinglewaytorepresentdata.Atwo-dimensionaltablecalledarelation.Eachrelationhasaname.Movies2.2BasicsoftheRelationalModelWhyRelations?Verysimplemodel.Oftenmatcheshowwethinkaboutdata.AbstractmodelthatunderliesSQL,themostimportantdatabaselanguagetoday.2.2.1AttributesAttribute(屬性):Thecolumnsofarelationarenamedbyattributes.Eachattributeofarelationhasaname,describesthemeaningofentriesinthecolumnbelow.Anytwoattributesofarelationcan’thavesamename.titleyearlengthgenreGoneWiththeWind1939231dramaStarWars1977124sciFiWayne’sWorld199295comedyMoviesAttributes(columnheaders)2.2.2Schemas(模式)Relationschema=relationnameandthesetofattributes.Example:

Movies(title,year,length,genre)

or

Movies(title:string,year:integer,length:integer,genre:string)

Database=collectionofrelations.Databaseschema=setofallrelationschemasinthedatabase.titleyearlengthgenreGoneWiththeWind1939231dramaStarWars1977124sciFiWayne’sWorld199295comedy2.2.3TuplesTuples(元組):Therowsofarelation,otherthantheheaderrowcontainingtheattributenames,arecalledtuples.Theremaybenotupleinarelation.Atuplehasonecomponent(分量)foreachattributeoftherelation.

Tuples(rows)Movies2.2.3TuplesHowtodescribeatuple?Usecommastoseparatecomponents,anduseparentheses(圓括號)tosurroundthetuple.Example:(‘StarWars’,1977,124,‘sciFi’)Weshouldalwaysusetheorderinwhichtheattributeswerelistedintherelationschema.2.2.4DomainsDomains:Thesetofallowedvaluesforeachattributeiscalledthedomainoftheattribute.Attributevaluesare(normally)requiredtobeatomic,thatis,indivisible.Adomainmustbeanelementarytype,suchasinteger,char(n),date,time.Wecanrepresentaschemaas:Movies(title:string,year:integer,length:integer,genre:string)

2.2.5EquivalentRepresentationsofaRelationRelationsaresetsoftuples,notlistsoftuples.Orderoftuplesisirrelevant(無關(guān)的).Anytuplecannotappearmorethanonceinarelation.Wecanreorderthetuplesofarelation,withoutchangingtherelation.

Wecanreordertheattributesofarelation,withoutchangingtherelation.2.2.6RelationInstancesTheschemaofarelationisrelativelystatic,whilethetupleschangeovertime.Relationinstances(實例):Asetoftuplesforagivenrelationisaninstanceofthatrelation.Theinstanceofarelationchangesovertime.Thesetoftuplesthatareintherelation“now”is“currentinstance(當(dāng)前實例)”.2.2.7KeysofRelationsKey:Asetofattributesformsakeyforarelationifwedonotallowtwotuplesinarelationinstancetohavethesamevaluesinalltheattributeofthekey.Movies(title,year,length,genre)employee-ID,Social-Securitynumber,student-ID,drivers’licensenumbersandautomobileregistrationnumber2.2.7KeysofRelationsKeyoftherelationMovies(title,year,length,genre,studioName,starName):{title,year}?{title,year,starName}?2.2.8AnExampleDatabaseSchemaThedatabaseschemainthisbook:Movies(title,year,length,genre,studioName,producerC#)MovieStar(name,address,gender,birthdate)StarsIn(movieTitle,movieYear,starName)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)cert#--CertificatenumberMovieExec–Movieexecutant2.2.8AnExampleDatabaseSchemaWe'llbuildamarketingdatabasesystemforasalecompany(supermarket).Itwillmanageallthefollowinginformation:1.

Managealldepartmentinformationinthecompany(suchas"Shanghaisaledepartment","JiangSusaledepartment").Alsomanageeverysalesmaninformationinthosedepartmentsincludingexclusiveemployeenumber,IDcardnumber,andsomeprivateinformation(suchasname,gender,birthdayandphonenumber).Bythewayoneofsalesmenwillactasthedepartmentmanagerinhisdepartment.2.2.8AnExampleDatabaseSchema

2.

Manageagroupofcustomers:name,province,city,companyname,phonenumber.3.Managealltheproductinformation:manufacturers(e.g.Chunlan,Hailer),types(e.g.motorcycle,airconditioner),specifications(e.g."MT125","RE1500"),prices,descriptions.

4.

Managesalesorderwhichrecordeachdealhasbeendone.

Notes:everysalesordercontainsanuniqueorderNo.,signdate,acorrespondingcustomer,asalesman,andatleastonekindofproducts.Anyproductintheordershouldhaveitsquantityandunitpricewhichwillbeusedtocalculatethetotalprices.2.2.8AnExampleDatabaseSchemaRelationalDataModel：Customer(custid,name,prov,city,phone,unit)Product(prodid,factory,type,spec,price,desc)Salesman(empid,idno,name,gender,phone,deptid)Department(deptid,name,headerid)Salesorder(orderno,signdate,empid,custid)Salesitem(orderno,lineno,prodid,unitprice,quantity)2.2BasicsoftheRelationalModelExercise:p282.2.1(thinkabout)

2.3DefiningaRelationSchemainSQLSQL：StructuredQueryLanguage

MaincontentsofSQL:DQL(select)：SQLisprimarilyaquerylanguage,forgettinginformationfromadatabase.DML(insert,delete,update)DDL(create,drop,alter,...)：SQLalsoincludesadata-definitioncomponentfordescribingdatabaseschemas.DCL(grant,revoke,...)2.3.1RelationsinSQL

SQLmakesadistinctionbetweenthreekindsofrelations:

Storedrelations,whicharecalledtables,existinthedatabaseandcanbemodifiedbychangingtheirtuples,aswellasqueried.Views,whicharerelationsdefinedbyacomputation.Theserelationarenotstored,butareconstructedwhenneeded.Temporarytables,whichareconstructedbytheSQLlanguageprocessorwheninperformsitsjobofexecutingqueriesanddatamodifications.

Theserelationsarenotstored.2.3.2DataTypes

Allattributesmusthaveadatatype. 1.Characterstringsoffixedorvaryinglength.

Char(n),Varchar(n) 2.Bitstringsoffixedorvaryinglength. Bit(n),bitvarying(n) 3.Integervalues.

Tinyint,Smallint,Int|Integer,Bigint 4.Floating-pointnumbers

Real,double,float Decimal|dec(precision,scale),numeric(precision,scale) 5.Datesandtimes

Dateyyyy-mm-dd Timehh:mm:ss.sssss 6.Boolean True,False,Unknown2.3.3

SimpleTableDeclarations

CreateatableCREATETABLEsalesman( empid char(5)PRIMARYKEY,

idno char(18)UNIQUE, name char(8)NOTNULL, gender bitNOTNULL, phone char(20), deptid intNULL);Notethatthereisnocognominal(同名的)tablesinadatabase.2.3.4

ModifyingRelationSchemasDeleteatableDROPTABLEsalesman;ModifytheschemaofanexistingrelationALTERTABLER Addcolumn-namedatatype

[NOT]NULL；ALTERTABLERDropcolumn-name；Example:AlterTablesalesmanAddbirthdatedatetime;2.3.5DefaultValues

Theuseofdefaultvalues:Whenwecreateormodifytuples,wesometimesdonothavevaluesforallcomponents.

SQLprovidestheNULLvalueasdefaultvalue.Whenwedeclareanattributeanditsdatatype,wemayaddthekeywordDEFAULT

andanappropriatevalue.2.3.5DefaultValues

Example:

CREATETABLEsalesorder(ordernointPRIMARYKEY,

signdatedatetimeNOTNULLDEFAULTgetdate(),

empidchar(5)NOTNULL,custidchar(4)NOTNULL,);Forintegertype，thereisausuallydefaultvaluesinSQLServer：IDENTITY(n1,n2)

ordernoint

IDENTITY(1000,1)

PRIMARYKEY,2.3.6DeclaringKeysTherearetwowaytodeclareaprimarykeyinSQLstatement.1.

Wemaydeclareanattributetobeaprimarykeywhenthatattributeislistedintherelationschema.Example:CREATETABLEdepartment( deptidintPRIMARYKEY, namechar(40)NOTNULL, headeridchar(5)NULL);2.3.6DeclaringKeys2.

Wemayaddtothelistofitemsintheschemaanadditionaldeclarationthatsaysaparticularattributeorsetofattributesformstheprimarykey.Example:CREATETABLEsalesitem( ordernoint, linenoint, prodidchar(6)NOTNULL,unitpricedecimal(8,2)NOTNULL,quantityintNOTNULL,

PrimaryKey(orderno,lineno));2.3.6DeclaringKeysThedistinguishandrelationshipbetweenPrimaryKeyandUnique.AnytwotuplesintherelationcannotagreeonalloftheattributesofPrimaryKeyorUniqueattributesset.Anyattempttoviolatetheruleisrejectedbythesystem.AtablehasandonlyhasonePrimaryKey,whileitmayhasanynumberofUniquedeclarations.AnUnique

attributecanbeNULL.AttributesinPrimaryKeyarenotallowedtohaveNULL.2.3DefiningaRelationSchemainSQLExercise:P362.3.1

a)-d)ReferencesP5AnAlgebraicQueryLanguageAdatamodelisnotjuststructure,itneedsawaytoquerythedataandtomodifythedata.Theoperationsintherelationalmodelcanbeexpressedineitheranalgebra,called“relationalalgebra(代數(shù))”,orinaformoflogic,suchas“Datalog”.Relationalalgebraconsistsofsomesimplebutpowerfulwaystoconstructnewrelationsfromgivenrelations.RelationalalgebraisnotusedtodayasaquerylanguageincommercialDBMS’s,but…2.4.2WhatisanAlgebra?Analgebraexpressionconsistingof:Operandsarerelationsorvariablesthatrepresentrelationsfromwhichnewvaluescanbeconstructed.Operatorsaresymbolsdenotingproceduresthatconstructnewrelationsfromgivenrelations.Therelationalalgebracanbeusedasaquerylanguageforrelations.2.4.3OverviewofRelationalAlgebraTheoperationsofthetraditional(傳統(tǒng)的)relationalalgebrafallintofourbroadclasses:Theusualsetoperations:union,intersectionanddifferenceOperationsthatremovepartsofarelation:Selection:eliminates(消除)somerows(tuples)Projection(投影):eliminatessomecolunms(attributes)Operationsthatcombine(組合)thetuplesoftworelationsCartesianproduct(笛卡爾積):pairsthetuplesoftworelationsinallpossiblewaysJoin(連接):selectivelypairtuplesfromtworelationsNaturaljoinTheta-joinRenaming:doesnotaffectthetuplesofarelation,butchangestherelationschema.2.4.4SetOperationsonRelationsOperationsonsetsRandS:R∪S={t|t∈Rort∈S},theunionofRandS,isthesetofelementsthatareinRorSorboth,andanelementappearsonlyonce.R∩S={t|t∈Randt∈S},

theintersectionofRandS,isthesetofelementsthatareinbothRandS.R–S={t|t∈Randnott∈S},

thedifferenceofRandS,isthesetofelementsthatareinRbutnotinS.NotethatR–SisdifferentfromS–R.2.4.4SetOperationsonRelationsConditionsonRandS:RandSmusthaveschemaswithidentical(同樣的)setsofattributes,andthetypes(domains)foreachattributemustbethesameinRandS.Beforewecomputetheset-theoretic(集合論)operations,thecolumnsofRandSmustbeorderedsothattheorderofattributesisthesameforbothrelations.2.4.4SetOperationsonRelationsABa1a1b1b2ABa1a1a2b1b2b1ABa1b2Sopposewehavethetworelations：RandS.ABa2a1b1b2ABa1b1RSR∪SR∩

SR－

S2.4.5ProjectionProjection(投影):ProducefromarelationRanewrelationthathasonlysomeofR’scolumns.Denotation:R1:=

πA1,A2,…,An(R)R1isarelationthathasonlythecolumnsforarttibutesA1,A2,…,AnofR.TheschemaofR1isthesetofattributes{A1,A2,…,An}.Eliminate(消除)duplicatetuples,ifany.2.4.5ProjectiontitleyearlengthgenrestudioNameProducerC#StarWarsGalaxyQuestWaynes’sWorld19771999199212410495sciFicomedycomedyFoxDreamWorksParamount123456789099999titleyearlengthStarWarsGalaxyQuestWaynes’sWorld19771999199212410495genresciFicomedyMoviesπtitle,year,length(Movies)πg(shù)enre(Movies)2.4.6Selection

Selection(選擇):ProducesfromarelationRanewrelationwithasubsetofR’stuples.Denotation:R1:=

σC(R)TheschemaofR1isthesameasR’sschema.ThetuplesofR1arethosethatsatisfysomeconditionCthatinvolvestheattributesofR.ExpressionofconditionC:Operands:constantsorattributesofROperators:=≠>≥<≤NOTANDOR2.4.6SelectiontitleyearlengthgenrestudioNameProducerC#StarWarsGalaxyQuest19771999124104sciFicomedyFoxDreamWorks1234567890σlength≥100(Movies):titleyearlengthgenrestudioNameProducerC#StarWars1977124sciFiFox12345σlength≥100ANDstudioName=‘FOX’(Movies):titleyearlengthgenrestudioNameProducerC#StarWarsGalaxyQuestWaynes’sWorld19771999199212410495sciFicomedycomedyFoxDreamWorksParamount123456789099999Movies2.4.7CartesianProduct

Cartesianproduct:TheCartesianproductoftwosetsRandSisthesetofpairsthatcanbeformedbychoosingthefirstelementofthepairtobeanyelementofRandthesecondanyelementofS.Denotation:R×S2.4.7CartesianProductR×S:TherelationschemafortheresultingrelationistheattributesofRandthenS,inorder.Todisambiguate(消除歧義)anattributeAthatisintheschemasofbothRandS,weuseR.AfortheattributefromRandS.AfortheattributefromS.ThetuplesintheresultingrelationarepairsthetuplesofRandSinallpossibleways.ThenumberoftuplesintheresultingrelationisNR×NS.2.4.7CartesianProductAB1324BCD24957106811AR.BS.BCD1113332224442492495710571068116811RSR×SExample:2.4.8NaturalJoinsNaturaljoinTheneedtojointworelationsbypairingonlythosetuplesthatmatchinsomeway.Thenaturaljoin

oftworelationsRandS

connectstworelationsby:Equatingattributesofthesamename.Projectoutonecopyforeachpairofequatedattributes.Denotation:RS

2.4.8NaturalJoinsLetA1,A2,...,AnbealltheattributesthatareinboththeschemaofRandtheschemaofS.AtuplerfromRandatuplesfromSaresuccessfulpairedifandonlyifrandsagreeoneachoftheattributesA1,A2,...,An,andtheresultofthepairingisatuple,calledjoinedtuple(連接元組),withonecomponentforeachoftheattributesintheunionoftheschemasofRandS.

2.4.8NaturalJoinsAB1324BCD24957106811RSExample:RSABCD13245768

NotethatthenaturaljoinoftworelationsRandSisinvalid(無效的),ifRandShavenocommonattributes.2.4.8NaturalJoinsBCD2273384510UVExample:UVABC169278387ABCD116227338

4510

Atuplethatfailstopairwithanytupleoftheotherrelationinjoinissometimessaidtobeadangling(懸掛)tuple.

2.4.8NaturalJoinsExample:Findscore’sofstudentswhosenameis“張平”.

бSname=’張平’(StudentsEnrollment)snosnameclass01張平230102李立230103王寧2302StudentsEnrollmentsnosnameclasscnoscore01張平2301c18001張平2301c27802李立2301c29003王寧2302c175snocnoscore01c18003c17501c27802c290EnrollmentStudents2.4.8NaturalJoinsExample:Findscore’sofstudentswhosenameis“張平”.

бSname=’張平’(StudentsEnrollment)snosnameclass01張平230102李立230103王寧2302snosnameclasscnoscore01張平2301c18001張平2301c27802李立2301c29003王寧2302c175snocnoscore01c18003c17501c27802c290EnrollmentStudentsбSname=’張平’(Students)StudentsEnrollmentбSname=’張平’(Students)Enrollment2.4.9Theta-JoinsTheta-join:Pairtuplesusinganarbitrary(任意的)condition.Denotation:RCSResult:TaketheproductofRandS.SelectfromtheproductonlythosetuplesthatsatisfytheconditionC.2.4.9Theta-JoinsBCD2273384510UVExample:UA<

DVABC169278387AU.BU.CV.BV.CD111692227833387227773388845101010

=бA<

D(UV)?2.4.10CombiningOperationstoFormQueriesCombining(復(fù)合)operations:Formexpressionsofarbitrary(任意的)complexitybyapplyingoperatorseithertogivenrelationsortorelationsthataretheresultofapplyingoneormorerelationaloperatorstorelations.Useparentheses(圓括號)whennecessarytoindicategroupingofoperands.

2.4.10CombiningOperationstoFormQueriesExample:Movies(title,year,length,genre,studioName)

FindthetitlesandyearsofmoviesmadebyFoxthatareatleast100minuteslong.

πtitle,year(σlength≥100

(Movies)∩σstudioName=‘FOX’(Movies))Anequivalentform:πtitle,year(σlength≥100ANDstudioName=‘FOX’(Movies))

Example:Movies(title,year,length,genre,studioName)StarsIn(title,year,starName)

Findthestarsofthemoviesthatareatleast100minuteslong.

πstarName(σlength≥100

(MoviesStarsIn))2.4.10CombiningOperationstoFormQueries Wecanrepresentabovequeryasanexpressiontree

（表達(dá)式樹）：

πtitle,year(σlength≥100(Movies)∩σstudioName=‘FOX’(Movies))

πtitle,year

∩бlength≥100

бstudioName=‘FOX’MoviesMovies2.4.11NamingandRenamingRenaming:Denotation:ρS

(A1,A2,…,An)(R)Result:theresultingrelationhasexactlythesametuplesasR,butthenameoftherelationisS.AndtheattributesoftheresultrelationSarenamedA1,A2,...,An,inorderfromtheleft.Denotation:ρS(R)Result:onlychangethenameoftherelationtoSandleavetheattributesastheyareinR.

2.4.11NamingandRenamingAB1324BCD24957106811RSAR.BS.BCD1113332224442492495710571068116811R×SR×ρS1(X,C,D)

(S)ABXCD1113332224442492495710571068116811Example:Anequivalentexpression:ρRS(A,B,X,C,D)(R×S)

2.4.11NamingandRenamingExample:Salesman(empid,idno,name,gender,phone,deptid)

FindtheemplyeeID’sandnamesofallpairsofemployeeswhohavethesamename.

πs1.empid,s2.empid,

(ρS1(Salesman)ρS2

(Salesman))

＝ANDs1.empid≠s2.empid<2.4.12RelationshipAmongOperations

Someoftheoperationscanbeexpressedintermsofotherrelational-algebraoperations.

R∩S＝R—(R—S)θ-join:RcS＝σc(R×S)Naturaljoin：RS＝πL

(σc(R×S))CistheformasR.A1=S.A1ANDR.A2=S.A2AND...ANDR.An=S.An,whereA1,A2,...,AnarealltheattributesappearingintheschemasofbothRandS.ListhelistofattributesintheschemaofRfollowedbythoseattributesintheschemaofSthatarenotalsointheschemaofR.2.4.12RelationshipAmongOperationsThesethreeoperationsarecalleddependentoperations(派生運(yùn)算，導(dǎo)出運(yùn)算)whichcanbeexpressedbyotheroperations,Thesixotheroperationsareindependentoperations（基本運(yùn)算）.Wecaninventnamesforthetemporaryrelationstorepresentacomplexexpressionofrelationalalgebra.Thenotationweshalluseforassignmentststementsis：Arelationnameandparenthesizedlistofattributesforthatrelation.ThenameAnswerwillbeusedconventionalfortheresultofthefinalstep.Theassignmentsymbol:=Anyalgebraicexpressionontheright.Example:

Movies(title,year,length,genre,studioName)R(t,y,l,g,s):=σlength≥100(Movies)S(t,y,l,g,s):=σstudioName=‘FOX’(Movies)Answer(title,year):=πt,y(R∩S)2.4.13ALinearNotationforAlgebraicExpressions2.4.14ExampleConsiderrelationsR(A,B)andS(A,B)withsameschema,whichofthefollowingexpressionsofrelationalalgebraarecorrect?I.R∩S=R-(R-S)II.R∩S=S-(S-R)III.R∩S=RSIonlyIandIIonlyC)I,II,andIIID)Noneoftheabove2.4.14ExampleFindNO’sofallstudentswhohavelearnedboth‘C1’and‘C2’.

πSno(бCno='c1'(Enrollment))∩πSno(бCno='c2'(Enrollment))FindNO’sofallstudentswhohavenotlearned‘C1’.

πSno(Enrollment)－πSno(бCno='c1'(Enrollment))πSno(бCno='c1'andCno='c2'(Enrollment))?πSno(бCno≠'c1'(Enrollment))?SnoCnoScore01c18003c17501c27802c290Enrollmentor2.4.14ExampleCustomer(custid,name,prov,city,phone,company)

Product(prodid,factory,type,spec,price,desc)

Salesman(empid,idno,name,gender,phone,deptid)

Department(deptid,name,headerid)

Salesorder(orderno,signdate,empid,custid)Salesitem(orderno,lineno,prodid,unitprice,quantity)1．FindtheID’sandphonenumbersofallsalesmennamed“張平”.2．Findthenamesandphonenumbersofallcustomerswhohavetransacted(交易