数据库系统_relation(2)

Chapter2TheRelationalModelofData 第2章关系数据模型 2TheRelationalModelofData Whatisadatamodel Whatisarelationaldatamodel HowtodefinearelationschemainSQL Whichoperationscanbeintherelationalmodel Whataretheresultoftheseoperations Theoperationsintherelationalmodelcanbeexpressedineitheranalgebra called relationalalgebra Relationalalgebracanexpressnotonlyoperations butalsoconstraintsonrelations 关系数据模型 关系代数 约束 2 1AnOverviewofDataModels Whatisadatamodel Mathematicalrepresentationofdata Examples relationalmodel tables semistructuredmodel trees graphs Operationsondata Constraints 2 1AnOverviewofDataModels SeveraldatamodelsTherelationalmodel includingobject relationalextensionsThesemistructured datamodel includingXMLandrelatedstandardsThehierarchicalmodel层次模型Thenetworkmodel网状模型 2 2BasicsoftheRelationalModel Relation关系Atwo dimensionaltablecalledarelation 2 2BasicsoftheRelationalModel WhyRelations Verysimplemodel Oftenmatcheshowwethinkaboutdata AbstractmodelthatunderliesSQL themostimportantdatabaselanguagetoday 2 2 1Attributes Attribute属性Namesforthecolumnsoftherelation describethemeaningofentriesinthecolumnbelow SuchaslengthofMovies Anattributehaveaname Anytwoattributesofarelationcan thavesamename 2 2 2Schemas Relationschema relationnameandattributelist Optionally typesofattributes Database collectionofrelations Databaseschema setofallrelationschemasinthedatabase Example Movies title year length filmtype orMovies title string year int length int filmtype string 关系模式 2 2 3Tuples Tuples元组Therowsofarelation otherthantheheaderrowcontainingtheattributes arecalledtuples Theremaybenotupleinarelation Atuplehasonecomponentforeachattributeoftherelation 分量 2 2 3Tuples Howtodescribeatuple Usecommastoseparatecomponents anduseparenthesestosurroundthetuple Example StarWars 1977 124 color Weshouldalwaysusetheorderinwhichtheattributeswerelistedintherelationschema 2 2 3Tuples Themappingoftuplesandobjects Arelation aclassAtuple aobjectAcomponentofatuple apropertyofaobject 2 2 3Tuples Thedifferenceoftuplesandobjects Objectshaveidentities whiletupleshavenot Aclasscouldhavetwodifferentobjectswiththesamevaluesinallattributes butatuplecan tappearmorethanonceinarelation 2 2 4Domains Domains域Adomainisanelementarytype suchasinteger char n date time Eachattributeofarelationisadomain thatis aparticularelementarytype Eachcomponentofanytuplemustbeatomic Movies title string year int length int filmtype string 2 2 5EquivalentRepresentationsofaRelation Wecanreordertheattributesofarelation withoutchangingtherelation Wecanreorderthetuplesofarelation withoutchangingtherelation 2 2 7KeysofRelations Whatisakey Asetofattributesformsakeyforarelationifwedonotallowtwotuplesinarelationinstancetohavethesamevaluesinalltheattributeofthekey Movies title year length genre employee ID Social Securitynumber student ID drivers licensenumbersandautomobileregistrationnumber 键 关键字 码 2 2 7KeysofRelations KeyoftherelationMovies title year length genre studioName starName title year title year starName 2 2 8AnExampleDatabaseSchema Followingisanexampleofdatabaseapplication We llbuildamarketingdatabasesystemforasalecompany supermarket Itwillmanageallthefollowinginformation 1 Managealldepartments informationinthecompany suchas Shanghaisalesdepartment JiangSusalesdepartment Alsomanageeverysalesmaninformationinthosedepartmentsincludingexclusiveemployeenumber IDcardnumber andsomeprivateinformation suchasname gender birthdayandphonenumber Bythewayoneofsalesmenwillactasthedepartmentmanagerinhisdepartment 2 2 8AnExampleDatabaseSchema 2 Manageagroupofcustomers name province city companyname phonenumber 3 Manageallthemerchandises information 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 Eachmerchandiseintheordershouldhaveitsquantityandunitpricewhichwillbeusedtocalculatethetotalprices 2 2 8AnExampleDatabaseSchema Customer custid name prov city phone company Merchandise merid manufacturer type spec price desc Salesman empid idno name gender phone deptid Department deptid name headerid Salesorder orderno signdate empid custid Salesitem orderno lineno merid unitprice quantity 2 2BasicsoftheRelationalModel Exercises p282 2 12 2 3 2 3DefiningaRelationSchemainSQL SQLisprimarilyaquerylanguage forgettinginformationfromadatabase SQLalsoincludesadata definitioncomponentfordescribingdatabaseschemas 2 3 1RelationsinSQL Threekindsofrelations Tablesexistinthedatabaseandcanbemodifiedbychangingtheirtuples aswellasqueried Viewsaredefinedbyacomputation TemporarytablesareconstructedbytheSQLlanguageprocessorwhenitperformsitsjobofexecutingqueriesanddatamodifications 2 3 2DataTypes Allattributesmusthaveadatatype 1 Characterstringsoffixedorvaryinglength Char n varchar n 2 Bitstringsoffixedorvaryinglength Bit n bitvarying n 3 integervaluesTinyint Smallint Int integer Bigint4 Floating pointnumbersReal double floatDecimal dec precision scale numeric precision scale 5 DatesandtimesDateyyyy mm ddTimehh mm ss sssss6 BooleanTure False Unknown 2 3 3SimpleTableDeclarations Howtodeclarearelationschema CREATETABLEsalesman empidchar 10 PRIMARYKEY idnochar 18 UNIQUE namechar 8 NOTNULL gendertinyintNOTNULL phonechar 20 NULL deptidintegerNULL 2 3 3SimpleTableDeclarations CREATETABLEdepartment deptidintegerPRIMARYKEY namechar 40 NOTNULL headeridchar 10 NULL Notethatthereisnocognominaltablesinadatabase 2 3 4ModifyingRelationSchemas Howtodeleteatable DROPTABLET NotethatallrecordsinTwillbedeleted 2 3 4ModifyingRelationSchemas Howtomodifytheschemaofanexistingrelation ALTERTABLERAddcolumn namedatatype NOT NULLALTERTABLERDropcolumn nameALTERTABLERModifycolumn namedatatype NOT NULLALTERTABLERRenameSALTERTABLERRenamecolumn name1Tocolumn name2Example AlterTabledepartmentRenamenameTodeptname 2 3 5DefaultValues Theuseofdefaultvalues Whenwecreateormodifytuples wesometimesdonothavevaluesforallcomponents SQLprovidestheNULLvalueasdefaultvalue Whenwedeclareanattributeanditsdatatype wemayaddthekeywordDEFAULTandanappropriatevalue Example Inrelationsalesorder signdatedataNOTNULLDEFAULTcurrentdate Autoincrementisadefaultvaluetointegervalues 缺省值 2 3 6DeclaringKeys Howtodeclareaprimarykey TherearetwowaytodeclareaprimarykeyinSQLstatement 1 Wemaydeclareanattributetobeaprimarykeywhenthatattributeislistedintherelationschema Example CREATETABLEdepartment deptidintegerPRIMARYKEY namechar 40 NOTNULL headeridchar 10 NULL 2 3 6DeclaringKeys 2 Wemayaddtothelistofitemsintheschemaanadditionaldeclarationthatsaysaparticularattributeorsetofattributesformstheprimarykey Example CREATETABLEsalesitem ordernochar 10 linenochar 4 prodidchar 6 NOTNULL unitpricefloatNOTNULL quantityintNOTNULL PrimaryKey orderno lineno 2 3DefiningaRelationSchemainSQL Exercises p362 3 1 2 4AnAlgebraicQueryLanguage Mathematicalsystemconsistingof Operands variablesorvaluesfromwhichnewvaluescanbeconstructed Operators symbolsdenotingproceduresthatconstructnewvaluesfromgivenvalues 2 4 2WhatisanAlgebra WhatisRelationalAlgebra Analgebrawhoseoperandsarerelationsorvariablesthatrepresentrelations Operatorsaredesignedtodothemostcommonthingsthatweneedtodowithrelationsinadatabase Theresultisanalgebrathatcanbeusedasaquerylanguageforrelations 2 4 3OverviewofRelationalAlgebra Theoperationsofrelationalalgebrafallintofourbroadclasses theusualsetoperations union intersectionanddifference ontworelations operationsthatremovepartsofarelation onarelation Selection eliminatessomerows tuples Projection eliminatessomecolunms attributes 2 4 3OverviewofRelationalAlgebra operationsthatcombinethetuplesoftworelationsCartesianproduct pairsthetuplesoftworelationsinallpossiblewaysJoin selectivelypairtuplesfromtworelationsNaturaljoinTheta joinrenaming doesnotaffectthetuplesofarelation butchangestherelationschema 2 4 4SetOperationsonRelations OperationsonsetsRandS R S t t Rort S unionthesetofelementsthatareinRorSorboth andanelementappearsonlyonce 并 2 4 4SetOperationsonRelations 2 4 4SetOperationsonRelations OperationsonsetsRandS Cons R S t t Randt S intersectionthesetofelementsthatareinbothRandS 交 2 4 4SetOperationsonRelations 2 4 4SetOperationsonRelations OperationsonsetsRandS Cons R S t t Randnott S differencethesetofelementsthatareinRbutnotinS NotethatR SisdifferentfromS R 差 2 4 4SetOperationsonRelations 2 4 4SetOperationsonRelations ConditionsonRandS RandSmusthaveschemaswithidenticalsetsofattributes Beforewecomputetheset theoreticoperations thecolumnsofRandSmustbeorderedsothattheorderofattributesisthesameforbothrelations 2 4 4SetOperationsonRelations RSR SR SR S 2 4 4SetOperationsonRelations RSR SR SR S 2 4 5Projection Projection投影 ProducefromarelationR1anewrelationthathasonlysomeofR2 scolumns Denotation R1 A1 A2 An R2 A1 A2 AnisalistofattributesfromtheschemaofR2 R1isconstructedbylookingateachtupleofR2 extractingtheattributesonlistA1 A2 An intheorderspecified andcreatingfromthosecomponentsatupleforR1 Eliminateduplicatetuples ifany 2 4 5Projection Example Movie title year length Movie inColor Movie Example Student Sid name phone sex FindIDsandnamesofallstudents sid name student 2 4 6Selection Selection选择 ProducefromarelationRanewrelationwithasubsetofR stuple Denotation C R Result TheschemafortheresultingrelationisthesameasR sschema andthetuplesintheresultingrelationarethosethatsatisfysomeconditionCthatinvolvestheattributesofR ExpressionofconditionC Operands constantsorattributesofROperators NOTANDOR 2 4 6Selection length 100 Movie length 100ANDstudioName FOX Movie Example Movie Example Student Sid name phone sex Findallfemalestudents sex F student 2 4 7CartesianProduct Cartesianproduct笛卡尔积TheCartesianproductoftwosetsRandSisthesetofpairsthatcanbeformedbychoosingthefirstelementofthepairtobeanyelementofRandthesecondanelementofS Denotation R S 2 4 7CartesianProduct Result TherelationschemafortheresultingrelationistheattributesofRandthenS inorder TodisambiguateanattributeAthatisintheschemasofbothRandS weuseR AfortheattributefromRandS AfortheattributefromS ThetuplesintheresultingrelationarepairsthetuplesofRandSinallpossibleways ThenumberoftuplesisNR NS 2 4 7CartesianProduct 演示 2 4 7CartesianProduct A R B S B C D 111333 222444 249249 57105710 68116811 R S R S Example 2 4 8NaturalJoins Naturaljoinconnectstworelationsby Equatingattributesofthesamename andProjectingoutonecopyofeachpairofequatedattributes Denotation RS 2 4 8NaturalJoins 演示 2 4 8NaturalJoins R S Example RS NotethatthenaturaljoinoftworelationsRandSisinvalid ifRandShavenocommonattributes 2 4 8NaturalJoins U V Example UV Atuplethatfailstopairwithanytupleoftheotherrelationinjoinissometimessaidtobeadanglingtuple 2 4 9Theta Joins Theta join Pairtuplesusinganarbitrarycondition Denotation RCSResult TaketheproductofRandS SelectfromtheproductonlythosetuplesthatsatisfytheconditionC 2 4 9Theta Joins U V Example UA DV A U B U C V B V C D 11169 22277 33389 22777 33888 45101010 2 4 10CombiningOperationstoFormQueries Combiningoperations Formexpressionsofarbitrarycomplexitybyapplyingoperatorseithertogivenrelationsortorelationsthataretheresultofapplyingoneormorerelationaloperatorstorelations Useparentheseswhennecessarytoindicategroupingofoperands Example Student Sid name Course Cid name Enrollment Sid Cid score Findnamesofallstudentswholearned C1 Findnamesofallstudentswholearnedboth C1 and C2 Findnamesofallstudentswhodidn tlearn C1 2 4 11NamingandRenaming Renaming改名Denotation S A1 A2 An R Result theresultingrelationhasexactlythesametuplesasR butthenameoftherelationisS AndtheattributesoftheresultrelationSarenamedA1 A2 An inorderfromtheleft Denotation S R Result onlychangethenameoftherelationtoSandleavetheattributesastheyareinR 2 4 11NamingandRenaming R S R S R S X C D S Example Anequivalentexpression RS A B X C D R S 2 4 11NamingandRenaming Example Salesman empid name FindtheemployeeIDsandnamesofallpairsofemployeeswhohavethesamename s1 empid s2 empid s1 name S1 Salesman s1 name s2 nameANDs1 empid s2 empid S2 Salesman 2 4 12RelationshipsAmongOperations Someoftheoperationscanbeexpressedintermsofotherrelational algebraoperations R S R R S join R cS c R S Naturaljoin R S L c R S CistheformwhereR A1 S A1ANDR A2 S A2AND ANDR An S AnwhereA1 A2 AnarealltheattributesappearingintheschemasofbothRandS ListhelistofattributesintheschemaofRfollowedbythose 2 4 12RelationshipsAmongOperations Thesethreeoperationsarecalleddependentoperationswhichcanbeexpressedbyotheroperations andthesixotheroperationsareindependentoperations SchemasforResults Union intersection anddifference theschemasofthetwooperandsmustbethesame sousethatschemafortheresult Selection schemaoftheresultisthesameastheschemaoftheoperand Projection listofattributestellsustheschema SchemasforResults Product schemaistheattributesofbothrelations UseR A etc todistinguishtwoattributesnamedA Theta join sameasproduct Naturaljoin unionoftheattributesofthetworelations Renaming theoperatortellstheschema 2 4 14Example Customer custid name prov city phone company Merchandise merid factory type spec price desc Salesman empid idno name gender phone deptid Department deptid name headerid Salesorder orderno signdate empid custid Sales