《(4.4.1)--2.3Databaselogicalstructuredesig.ppt》由会员分享，可在线阅读，更多相关《(4.4.1)--2.3Databaselogicalstructuredesig.ppt（14页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Database logical structure designFrom conceptual model to logical modelIn the previous section,the conceptual model has been used to design the conceptual structure of the database.The task of this chapter is to transform the conceptual model into a logical model to realize the logical structure des
2、ign of the database.conceptual conceptual model model logical logical modelmodelE-R modelE-R modelRelational modelRelational modelRelational model The The relational relational model model represents represents data data in in an an entityentity set set in in a a two-dimensional table two-dimensiona
3、l table or or R Relation elation for shortfor short.Since Since the the relational relational model model is is transformed transformed from from the the E-R E-R model,model,many many concepts concepts in in the the relational relational model model correspond correspond to to the E-R model.the E-R
4、model.Relational terms are as follows:Relational terms are as follows:Relational termTuple(record)Tuple(record)Each row in a two-dimensional table is called a tuple.The tuple is the basic element of relation.A relation consists of several tuples of the same structure.1901011Li Xiaomingmale01/01/2001
5、Relational termAttributeAttribute(field)(field)Each column in a two-dimensional table is called an attribute.A collection of attributes constitutes a tuple in a relation.snosno19010111901012Relational termDomainDomainThe domain is the value range of the attribute.A reasonable definition of the domai
6、n of the attribute can improve the efficiency of data table operations.score011000100Relational term Key KeyIn one relation,there are one or more fields whose values uniquely identify a record.Such fields or groups of fields are called the Key,also known as Primary Key.For example,the primary key of
7、 a student relation is the student ID(sno).snosno19010111901012Relational termForeign keyForeign key（FKFK）If If an an attribute attribute or or a a set set of of attributes attributes is is not not the the primary primary key key of of the the current current relation,relation,but but the the primar
8、y primary key key of of another another relation,relation,then then such such an an attribute(or attribute(or attributes)attributes)is is called called a a Foreign Foreign Key(FK).Key(FK).The The foreign foreign key key builds builds a a bridge bridge between between each each data data table,table,
9、makes makes the the table table in in the the database database restrict restrict mutually,mutually,and and makes makes the the database database as a whole.as a whole.Relational termForeign keyForeign key（FKFK）snosnocnocnoscorescore190101110195.0190101110270.0190101210288.0Relational termRelational
10、 modelRelational modelAn abstract representation of a relation(similar to an entity type)Relation name(attribute 1,attribute 2,.,attribute n)Course(cno,cname,hours,credits,compulsory)cnocnocnamecnamehourshourscreditscreditscompulsorycompulsory 101 Advanced mathematics 545yes 102 College English365ye
11、sRelational termThinking and practiceA database has three relations.The relational model is:Member(ID,name,gender,age,department,Tel,E-mail)Book(book no,title,publisher,category,author,price,stock)Sales(order no,ID,book no,purchase date,quantity)Please Please analyze analyze which which attributes a
12、ttributes are are the the keys keys and and the the foreign foreign keys keys for for these 3 relations.these 3 relations.The correspondence between the conceptual model and the relational modelThe conceptual model for concept structure designThe relational model for logical structure designConceptu
13、al modelEntityEntityAttributeAttributeDomainDomainCodeCodeEntity Entity setsetEntity Entity typetypeRelational modelTupleTupleAttributAttribute(field)e(field)DomainDomainKeyKey/foreign/foreign keykeyRelationRelationRelationalRelationalschemeschemeCharacteristics of relationIn the relational model,th
14、e two-dimensional table is the standard data structure,and the two-dimensional table has the following characteristics:（1）Each attribute cannot be subdivided,that is,the table cannot contain another table.For example,the following table is not a two-dimensional table.Employee Employee numbernumberNa
15、meNameParts payableParts payableDeductionsDeductionsPaid in Paid in amountamountsalarysubsidybonuscharges for water and electricityaccumulation fundCharacteristics of relation(2)Duplicate attribute names cannot appear in the same relation.(3)There cannot be tuples with the same content in the same relation.(4)Any two rows or any two columns can be interchanged,without affecting the actual meaning of the relation.