湖南大学数据库复试资料湖南大学829考研资料44a89ca5-278f-4f31-8a43-b3cd86f3927f

1,DatabaseFundamentsTheRelationalDataModel,CSEEofHunanUniversityJin-MinYang2016.03,2,关系数据模型,关系数据库中的基本概念；数据模型，关系数据模型;关系数据模型的数据完整性约束;四类约束：主键，外键，属性的取值域，业务规则约束；关系代数：数据的运算；,3,LearningContents,Relatinoaldatamodel:relation,attribute,domain,tuple.relationschema,intension,meta-datarelationinstance,data,relationextension.Degree,cardinality.RelationalIntegrity:Domainconstraint.Entityintegrityconstraint:primarykey.Referentialintegrityconstraint:foreignkeyEnterpriseconstraintsorsemanticconstraints.RelationalOperation:Selection,projection,Union,Difference,Intersection,Cartesianproduct,Join,Division.,4,房产中介公司的数据库-propertybrokingcompany,出租服务部的业务表单:给房东提供服务。,5,Theforminclientdepartment,客户部的业务表单(Form):给要租房的客户提供服务。,6,TheFormincontractdepartment,成交合同部的业务表单:与客户签订租房合同,7,Branch,TableinDatabase,8,TableinDatabase,Staff,9,PropertyForRent:,PrivateOwner(host):,TableinDatabase,10,Client,TableinDatabase,11,TableinDatabase,Viewing,12,Lease(契约）,TableinDatabase,13,Lease,ProperityForRent,Client,合同部门需要的数据,14,ProperityForRent,Client,Branch,staff,Lease,host,viewing,表之间的关系,15,DataModel,Integratedcollectionofconceptsfordescribingdata,relationshipsbetweendata,andconstraints(约束)onthedatainanorganization.DataModelcomprises:astructural（结构）part;asetofintegrity(完整性）constraint(约束)rules;amanipulative（操作）part;Purpose:Torepresentdatainanaccurateandunderstandableway;Facilitating(方便)theimplementation(实现)ofmanipulation(操作)andcorrectness(正确性)；,16,关系中的一些概念,PropertyForRent,Tuples,Relation,Attributes,Degree=6,Domainiscurrency,Domainisinteger:1.100,Cardinality=75,17,AlternativeTerminologies（术语）,18,RelationSchemasandInstances,relationschema,relationintension(内涵）,meta-data(元数据）；relationinstance（实例）,relationextension（外延）,Data,rows,records,tuples;databaseschemaisasetofrelationschemas；Inarow,Avalueofafieldisnullrepresentsamissingorunknownvalue.,19,ExampleofAttributeDomain,20,RelationPracticeQuestions,1)Whatisthenameoftherelation?2)Whatisthecardinalityoftherelation?3)Whatisthedegreeoftherelation?4)WhatisthedomainofRentStart?WhatisthedomainofPaymentMethod?5)Whatislargerthesizeoftheintensionorextension?,Lease,21,RelationalModelFormalDefinition,Therelationalmodelmaybevisualizedastablesandfields,butitisformallydefinedintermsofsetsandsetoperations.ArelationschemaRwithattributesA=isdenotedR(A1,A2,An)whereeachAiisanattributenamethatrangesoveradomainDidenoteddom(Ai).Example:Relationschema:PrivateOwner(OwnerNo,FirstName,LastName,Address,TelephoneNo)R=PrivateOwnerA=OwnerNo,FirstName,LastName,Address,TelephoneNodomain(TelephoneNo);domain(OwnerNo);,22,关系的特性,Arelationhasseveralproperties:1)Eachrelationnameisunique.Notworelationshavethesamename.2)Eachattributeofarelationhasadistinctname.3)Eachcelloftherelationcontainsexactlyoneatomicvalue4)Thevaluesofanattributearelimitedindomain.5)Eachtupleisdistinct.Therearenoduplicatetuples.6)Theorderofattributesisnotreallyimportant.7)Theorderoftupleshasnosignificance.,23,关系的键,Aprimarykey(主键)isaminimalsetofattributesthatuniquelyidentifiesatupleinarelation.Acandidatekey(候选键)：一个关系中能作为主键的单个属性或者是多个属性；Aforeignkey(外键)：一个表中充当外键的一个属性或者属性集合，它在另外一个表中必定充当主键；注意：键的概念不是就关系实例relationalinstance而言，而是关系模式relationalschema层面的概念。,24,表中主键的本质含义,PropertyForRent:,PrivateOwner(host):,25,在一个表中，为什么主键非常重要？,Staff,Howmuchshouldthecompanypaysthesalaryeverymonth?Howmanystaffsinthecompany?Avergesalaryinthecompany?WhenMikeshissalaryispromotedto2800,howtoupdatetherelation?,26,如何来确定一个表的主键,Staff,对于对象实例，选择既具有唯一标识性，又对任何对象实例都肯定存在，而且很少变动的字段。设计时，必须认真考虑。mobile_phone，Identity_id能否分别作为一个主键？,27,在一个表中，为什么外键非常重要,Staff,外键是保持关系（relationship）的手段。无外键，联系就可能被中断。例如：其它人无法与你联系.在一个表中充当外键的一个属性集合，它在另外一个表中必定充当主键；实施完整性约束；,Lease,28,在一个表中，为什么外键非常重要,Therelationashipamongrelationswillbebroken.Associationbecomeimpossible.,ProperityForRent,Viewing,Client,29,Questions,Questions:1)IsnameakeyforClient?2)IsviewDateandviewTimeakeyforviewing?3)Listthecandidatekeysforviewing,andforeignkey,ProperityForRent,Viewing,Client,30,关系完整性约束,IntegrityConstraints(完整性约束):insurethedataisaccurate.Typesofconstraints:1)Domain（域）constraint.nullrepresentsavaluethatiscurrentlyunknownornotapplicable.nullisnotthesameaszerooranemptystring.2)Entity（实体）integrityconstraint-primarykey,对任何一行记录，它的取值不允许为null.3)Referential(引用）integrityconstraint-foreignkeyforeignkeyvaluemustmatchaprimarykeyvalueofatupleinthereferencedrelationorbenull.,31,IntegrityQuestions,Question:1)Findallviolationsofintegrityconstraintsinthesethreerelations.,ProperityForRent,Viewing,Client,32,第四类约束：业务规则约束,数据库中的数据，要求完整。也就是必须满足业务规则要求(enterpriseconstraints/semanticintegrityconstraints.Examples:学生选课一个学期不能超过30学分.学年平均成绩低于75分不能拿一等奖学金.挂科24个学分不能再选课.Howtoperformtheenterpriseconstraints?Usingtriggersandassertions.,第二次作业,就前一个作业，指出每个表的主键，外键，每个字段的域；就前一章所举的一个大学的教务数据库，设它包含有6个表：学生表，老师表，课程表，老师开课表，学生选课表，排课表。对这6个表，分别确定其包括哪些字段？并就每个表分别写出它的4行数据；检查表的设计是否合理？然后确定每个表的主键；分析外键，如果有，则标识出来，指明它在哪个表中是主键。对每个表的每个字段，给出域约束（数据类型，取值范围）；对每个表给出主键约束；如有外键，给出外键约束；举三个业务规则约束的例子；一旦发现哪个学生对此三约束熟视无睹？就请他明年重修这门课！,如何理解数据的完整性,主键约束暗示我们，在数据库中，数据是分类存储在不同的表中，一个类对应一张表；每个类别都有它的标识性字段（主键），对于同一个东东（内容），确保表中仅只一行数据来记录它，不会出现重复的行；外键约束是保证不同表之间的数据，其关系不中断的一种措施；例如房子表中的一行数据记录了某个房子的信息，而合同表是记录房子被租出的信息，因此，如果合同表中的某行数据（即某个租房成交合同）中的房子编号的值在房子表中没有对应的行记录，那么这行数据（这个合同）就变得毫无含义。Domain约束，则是描述某个表中，某个字段的含义，数据库类型，取值范围；确保表中的数据有含义，表达想要的意思；例如是手机号码，就必须1开头，11个数字，就可计算属于哪个公司，哪个地市？,如何理解数据的完整性,完整性约束是属于数据模式（Schema）中的内容，因此是属于原则和逻辑范畴的概念；而不是就当前已知的数据而言的，要求事先就要设定；当用户对数据库中某个表，添加一行数据，修改某行数据，删除某行数据时，就要进行完整性检查，如果用户的操作违背了完整性约束，那么用户的操作就会被拒接，这样就确保了数据库中数据的完整性；例如，当用户给房子表中添加一行记录时，数据库管理系统就要拿用户要添加的这个房子的房子编号与数据库中房子表中已有的每行数据进行比对，如果发现相同，那么就说明用户要添加的这个房子，已经在数据库中存在了，用户的这个添加操作是重复性行为。,关系代数,数据库的数据是所有数据，而对于某个具体用户来说，仅只关心他职责范围内的数据，例如，对催缴员来说，仅只需要合同表上“客户姓名，电话，月租金”，好及时电话通知客户来交租金。对用户想要的数据，如何来满足？通过关系运算来解决。,Lease,Client,37,RelationalAlgebra(代数）,Relationalalgebraconsistsofoperands(操作数）andasetofoperators（操作符）.Input：oneormorerelations.Output：arelation.Closureproperty-inputisrelations,outputisrelationsUnaryoperations-operateononerelationBinaryoperations-havetworelationsasinputAsequenceofrelationalalgebraoperatorsiscalledarelationalalgebraexpression.,38,RelationalAlgebraOperators,RelationalOperators:SelectionProjectionCartesianproductJoinUnionDifference-IntersectionDivisionNotethatrelationalalgebraisthefoundationofallrelationaldatabasesystems.SQLgetstranslatedintorelationalalgebra.,39,SelectionOperation,Theselectionoperationisaunaryoperation;output：asubsetofthetuplesoftheinputrelation.theoutputrelationhasthesamenumberofcolumnsastheinputrelation,butmayhavelesstuples.F(R)=t|tRandF(t)istrueWhereRisarelation,tisatuplevariable,Fisapredicate,40,SelectionExample,TypeHouse(PropertyForRent),PropertyForRent:,TypeflatANDRent400(PropertyForRent),41,SelectionQuestions,Questions:Writetherelationalalgebraexpressionthat:1)ReturnsallclientsviewingpropertyPA14.2)Returnsallallclientsviewingduring1-May-01and30-Jul-013)ReturnsallpropertythathavebeenviewedbyclientCR74.,Viewing,42,ProjectionOperation,aunaryoperation;outputcontainsasubsetoftheattributesoftheinputrelationandallnon-duplicatetuples.A1,Am(R)=tA1,Am|tRWhereRisarelation,tisatuplevariableA1,AmisasubsetoftheattributesofRoverwhichtheprojectionwillbeperformed.OrderofA1,Amissignificantintheresult.,43,ProjectionExample,ClientNo,Comments(Viewing),Viewing,Questions:Whichclientshaveatleastaviewingtoproperty?,ClientNo(Viewing),Whenareweguaranteedtoneverhaveduplicateswhenperformingaprojectionoperation?,44,Unionoperation,abinaryoperation;input:tworelationsRandS;output:alltuplesinRorinSorinbothRandS.Duplicatetuplesareeliminated.RS=t|tRortSwhereR,Sarerelations,tisatuplevariable.RandSmustbeunion-compatible.havethesamenumberofattributeswiththesamedomains.,45,ProperityNo(Viewing)ProperityNo(PropertyForRent),Viewing,PropertyForRent,46,Question?,Viewing,Whichpropertiesareviewedorrentedout?,Lease,47,SetDifference,abinaryoperationoutput:allthetuplesofRthatarenotinS.RS=t|tRandtSwhereRandSarerelations,tisatuplevariable.Notethat:R-SS-RRandSmustbeunioncompatible.,48,ProperityNo(Viewing)-ProperityNo(PropertyForRent),Viewing,PropertyForRent,Question:Whichpropertiesisnotviewedbyanyclient?Whichpropertieshavebeenviewed,butarenotrentedoutinlease?,49,Intersection,abinaryoperation;output:alltuplesthatareinbothRandS.RS=t|tRandtSwhereR,Sarerelations,tisatuplevariable.RandSmustbeunion-compatible.RS=R-(R-S)=S-(S-R).,50,ProperityNo(Viewing)ProperityNo(PropertyForRent),Viewing,PropertyForRent,Question:Whichpropertiesarebothviewedandrentedout?,51,CartesianProduct,TheCartesianproductreturnsthesetofallorderedpairs.Example:D1=1,2,3D2=A,BD1D2=(1,A),(2,A),(3,A),(1,B),(2,B),(3,B)PracticeQuestions:1)ComputeD2D1.2)ComputeD2D2.3)howmanyelementsarethereinD1D2?.4)WhatisthecardinalityofD1D2D1D1?,52,CartesianProduct,Abinaryoperation;Output:aconcatenationofeverytupleofRwitheverytupleofS.RS=t|tA1,Ak1RandtAk1+1,Ak1+k2SDegreeofRSisdegree(R)+degree(s);CardinalityofRSis|R|*|S|.TheCartesianproductisalsoknownascrossproduct.,53,CartesianProductExample,StaffPropertyForRent,Staff,PropertyForRent,54,-Join,RFS=F(RS).,55,-JoinExample,StaffType=FlatPropertyForRent,Staff,PropertyForRent,56,TypesofJoins,equijoin:ViewingViewing.PropertyNo=PropertyForRent.PropertyNoPropertyForRentNaturaljoin:RS;theequijoinofRandSoverasetofattributescommontobothRandS.Itremovesthe“extracopies”ofthejoinattributes.Normallyassumestheattributeshavethesamenameinbothrelations.,57,EquiJoinExample,StaffStaff.staffNo=PropertyforRent.StaffNoPropertyForRent,Staff,PropertyForRent,58,NaturalJoinExample,StaffPropertyForRent,Staff,PropertyForRent,59,NaturalJoinExample,StaffPropertyForRent,Staff,PropertyForRent,要有共同的属性，在一个表中是外键，在另一个表中是主键；实现了列的扩加功能；没有关联的行都不在结果中；,60,JoinPracticeQuestions,Computethefollowingjoins:ClientViewingPropertyForRent,PropertyForRent,Viewing,Client,Question:Pleasegeneratealistofclientname,propertyaddress,hostnameforthoseclientsinlease.,61,JoinPracticeQuestions,Question:Pleasegeneratealistofclientname,propertyaddress,hostnameforthoseclientsinlease.,LeaseClientprivateOwnerPropertyForRent,Why?,62,典型案例,请给出一个清单，列出客户名，电话，以及所租的房子的地址分析：客户姓名，电话在Client表中，所租的房子的地址在PropertyForRent表中；涉及2个表；但是Client表和PropertyForRent表并没有直接的联系，也就是说ClientPropertyForRent没有意义，其结果为空，无任何记录；因此要使Client表和PropertyForRent表关联起来，必须通过lease表来充当桥梁：ClientLeasePropertyForRent,63,JoinPracticeQuestions,Listclientname,propertyaddress,hostnameforthosewhohavesignedupalease:Lease(clientNo,propertyNo);Client(clientNo,clientName);propertyForRent(propertyno,address,ownerNo);privateOwner(ownerNo,hostName);,64,OuterJoins,1)Leftouterjoin-RS-TheoutputcontainsalltuplesofRthatmatchwithtuplesofS.IfthereisatupleinRthatmatcheswithnotupleinS,thetupleisincludedinthefinalresultandispaddedwithnullsfortheattributesofS.2)Rightouterjoin-RS.3)Fullouterjoin-RS.,65,LeftouterjoinExample,PropertyForRent,Viewing,PropertyForRentViewing,66,DivisionOperator,Requirement:thesetofattributesofSmustbeasubsetoftheattributesofR.LetAisthesetofattributesofR,BisthesetofattributesofS,BA;C=AB.RSNotethatRS=R-S(R)-R-S(R-S(R)S)-R).,67,DivisionExample,PropertyForRent,Viewing,ViewingPropertyForRent,Question:CanyougivetherelationalalgebraexpressiontofindthepropertythatareviewedonbyallClients?2)Ifthereare6propertiesinthedatabase,andtheresultofthequeryClientNo,PropertyNo(Viewing)PropertyNo(PropertyForRent)is2records,whatistheminimum#ofrecordsthatmustbeintheViewingrelation?,68,CombiningOperations,Relationalalgebraexpression:Address(NameMike(Staff)TypeHouse(PropertyForRent)usingtemporaryrelationvariablestoholdintermediateresults:HousePropertyTypeHouse(PropertyForRent),69,RenameOperation,Renamingcanbeappliedwhenassigningaresult:Result(StaffNum,FullName,Earning)staffno,fName,salary(Staff),70,OperatorPrecedence(优先级）,Justlikemathematicaloperators,therelationaloperatorshaveprecedence.Theprecedenceofoperatorsfromhighesttolowestis:unaryoperators:,Cartesianproductandjoins:,intersection,division:,unionandsetdifference：U,-Parentheses(括号)canbeusedtochangedtheorderofoperations.,71,fivefundamentaloperations,Thefivefundamentaloperationsinrelationalalgebra:,，OtheroperationscallbederivedfromThefivefundamentaloperations.,72,Conclusion,Therelationalmodelrepresentsdataandrelations.Relationschema:itsname,asetofattributes;everyattributes:name,datatype,domain.4kindsofconstraintstoguaranteedataintegrity:domain,entityintegrityconstraint;primarykey;referentialintegrityconstraint;foreignkey;Semanticconstraints;Relationalalgebraisasetofoperationsforansweringqueriesondatastoredintherelationalmodel.The5basicrelationaloperatorsare:,-,.,73,第三次作业,ThefollowingtablesformpartofadatabaseheldinarelationalDBMS.Hotel(hotelNo,name,city)Room(roomNo,hotelNo,type,price)Booking(hotelNo,guestNo,dateFrom,dateTo,roomNo)Guest(guestNo,name,guestcity)whereHotelcontainshoteldetailsandhotelNoistheprimarykey.RoomcontainsroomdetailsforeachhotelandroomNo,hotelNoformstheprimarykey.BookingcontainsdetailsofthebookingsandtheprimarykeycompriseshHotelNo,guestNoanddateFrom.GuestcontainsguestdetailsandguestNoistheprimarykey.,74,第三次作业,Generatetherelationalalgebraforthe