数据库系统原理(英文)-6-09.ppt

Part Two Database Design,Chapter 6 Database Design and E-R Model,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,3,Part 1.DB design phases(6.1)requirement analysis,conceptual design,logical design,physical designPart 2.The Entity-Relationship Modelbasic E-R model modeling elements(6.2.1-6.2.3):entity sets,relationship sets,attributesconstraints(6.3.1-6.3.3):mapping cardinality,keys,participation constraint(6.6)weak entity sets,Four Parts in Chapter 6,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,4,extended E-R model(6.7)(6.7.1-6.7.4)induce OO features into E-R model,that is,specialization,generalization,attributes inheritance,constraints on generalization(6.7.5)aggregation:relationship among relationships(6.4)entity-relationship diagrams(6.8)E-R diagram,i.e.,conceptual design,for banking enterprise,Four Parts in Chapter 6,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,5,Part 3.E-R design issues(6.5)when applying E-R model to model the objects in real worlds,some issues(considerations and principles)should be addressed,to guarantee DBS effectiveness and efficiency for data Part 4.Reduction to relational schemas(6.9)mapping elements in E-R model to that in relational models,i.e.conceptual schema initial logical schema,Four Parts in Chapter 6(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,6,Database design consists of two sequential phasesanalyzing of user requirements what data should be stored in the database what operations/transaction,such as insert,delete,update and retrieve are needed to conducted on these data designing of DB schemas,in accordance with the three-level of data abstract(refer to Fig.1.1)conceptual design logical design,at the logical level and view level physical design,at the physical level refer to Fig.1.0.1,6.1 Overview of the Design Process,Specification of user requirements(需求分析说明书),DB conceptual schema,i.e.E-R diagram(chapter 6),DB logical schema,i.e.relational data schema,DB physical schema,e.g.physical storage structure and access method(chapter 11,12),initial relational schema generating(6.9),relational schema normalizing(chapter 7),Fig.6.0.1 DB design phases,Application areas/problems in real worlds,Requirements analysis,Conceptual DB design,Logical DB design,Physical DB design,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,9,数据库应用系统DBAS设计 DB,DBMS,users,application programs refer to Fig.6.0.2 电信网管系统示意图DBAS设计refer to Fig.6.0.3 DBAS 生命周期模型参照软件工程中软件开发瀑布模型原理，DBAS的生命周期由项目规划、需求分析、系统设计、实现与部署、运行管理与维护等5个基本活动组成 根据DBAS的软件组成和各自功能，分为数据组织与存储设计、数据访问与处理设计、应用设计三条设计主线，分别用于设计数据库、数据库事务和应用程序,6.1 Overview of the Design Process(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,10,根据数据库系统三级模式结构，DBAS设计阶段分为概念设计、逻辑设计、物理设计三个步骤，每一步设计内容涵盖了三条设计主线,6.1 Overview of the Design Process(cont.),人机界面模块,配置管理,性能管理,ODBC/JDBC 数据库接口,DB2/Sybase 数据库管理系统,数据库DB：配置数据、性能数据、故障数据、安全数据、计费数据,故障管理,安全管理,计费管理,配置数据访问事务,性能数据访问事务,故障数据访问事务,安全数据访问事务,计费数据访问事务,Fig.6.0.2 电信网管系统示意图,Fig.6.0.3 DBAS 生命周期模型,需求分析,概念设计,逻辑设计,物理设计,性能/存储/安全需求,数据项分析,数据流与事务分析,程序需求分析,程序概要设计,程序详细设计,系统总体设计,数据访问与处理,应用 程序,数据组织与存储,系统运行维护,设计,DB概念模式设计,DB逻辑模式设计,系统实现和部署,事务详细设计,DB物理模式设计,事务概要设计,构造原型(可选),系统实现,数据转换与加载,系统测试、部署与交付,规划与分析,项目规划,运行维护,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,13,6.2.1 Entity Sets（实体集，实体型）A conceptual database can be modeled asa collection of entities,relationship among entities.An entity is a“thing”or“object”in the real world that is distinguishable from all other objectse.g.a customer,a loana entity has a set of properties,such as customer_nameAn entity set is a set of entities of the same type that share the same properties,or attributese.g.the entity set customer=set of all customers,6.2 The Entity-Relationship Model,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,14,An entity is represented by a set of attributes,i.e.the descriptive properties possessed by each member of an entity sete.g.customer=(customer-id,customer-name,customer-street,customer-city)loan=(loan-number,amount),6.2.1 Entity Sets(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,15,A entity consists of all values of its all attributese.g.Fig 6.1Entity-set customer-set=|ID-value D1,name-value D2,street-valueD3,city-value D4 D1 D2 D3 D4,6.2.1 Entity Sets(cont.),Fig.6.1 Entity Sets customer and loan,customer-id customer-customer-customer-loan-amount name street city number,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,17,A relationship is an association among several entitiese.g.in Fig.6.2 Jone borrower L-17customer relationship loan entity set entity setA Relationship set（联系集，联系型）is a set of relationship of the same typenote:more than one relationship set among the same entity sets,6.2.2 Relationship Sets,Fig.6.2 Relationship Set borrower,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,19,A relationship set R among entity set E1,E2,En,n 2,is a mathematical relation,each taken from entity sets(e1,e2,en)|e1 E1,e2 E2,en En E1 E2 En where(e1,e2,en)is a relationshipe.g.(Jones,L-17)borrowerThe entity sets E1,E2,En participate in relationship set RA relationship instance in an E-R schema represents an association between the named entities in the real-world enterprise that is being modelede.g.in Fig.6.2,the relationship instance between Hayes and the loan L-15,6.2.2 Relationship Sets(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,20,Rolethe functions that an entity plays in a relationship is called that entitys rolerefer to Fig.6.11 Attributes can also be used for describing the properties of the relationship sete.g.in Fig.6.3,the depositor relationship set between entity sets customer and account may have the attribute access-date,6.2.2 Relationship Sets(cont.),Fig.6.3 Access-data as the attribute of the depositor relationship set,25 March 2000?,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,22,The degree of a relationship setthe number of entity sets that participate in the relationship setquantitative information about association among entitiesBinary relationship the relationship with degree two most relationship sets in a database system are binaryA relationship set may involve more than two entity setse.g.suppose employees of a bank may have jobs(responsibilities)at multiple branches,with different jobs at different branches.Then there is a ternary relationship set between entity sets employee,job and branch,6.2.2 Relationship Sets(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,23,The domain or value set of the attribute the set of permitted values for the attributeAttribute typessimple posite attributeFig 6.4single-valued vs.multi-valued attributee.g.attribute phone-numberderived attributeits value can be derived from the values of other related attributes or entitiese.g.age derived from date of birth,6.2.3 Attributes,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,24,Fig.6.4 Composite attributes customer-name and customer-address,6.2.3 Attributes(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,25,Null value for a attribute means the attribute“not applicable“for the entity,not existingthe value for the attribute exists,but is“unknown”,6.2.3 Attributes(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,26,6.3 Constraints,Data model=syntax+semantic/constraints+operationsSemantic/constraints as integrity constraints in DBS,to maintain DBS consistencyThree types of integrity constraints in E-R model mapping cardinalities(6.3.1)keys(6.3.2)participation-constraints(6.3.3),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,27,Mapping cardinalitiessemi-quantitatively expressing the number of entities to which another entity can be associated via a relationship setFor a binary relationship set R,the mapping cardinality must be one of the following types:Fig.6.5,Fig.6.6,from A to B one to oneone to manymany to onemany to manyE.g.in Fig.6.2(),the relationship set from customer to loan is many-to-many,6.3.1 Mapping Cardinalities,Note:some elements in A or B may not be mapped to any elements in the other set,Fig.6.5 Mapping cardinalities,注意many-to-one和one-to-many 定义和方向！,Many to one,Many to many,Note:Some elements in A or B may not be mapped to any elements in the other set,Fig.6.6 Mapping cardinalities,注意many-to-one和one-to-many 定义和方向！,b4,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,30,Key is a set of attributes(of a entity set or relationship set)the values of these attributes in one entity can be used to uniquely distinguish this entity from others,or the values of these attributes in one relationship are used to uniquely identify the relationshipKeys includesuperkey(超键),candidate key(候选键),primary key(主键),6.3.2 Keys,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,31,A super key of an entity set is a set of one or more attributes,whose values uniquely determine each entity in the entity sete.g.customer_id+customer_namethe super key may contain extraneous attributese.g customer_name A candidate key is the minimal super keynon-redundant super keye.g.customer-id is the candidate key of customere.g.account-number is the candidate key of account,6.3.2.1 Keys For Entity Sets,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,32,The primary key is a candidate key chosen by the database designer as the principal means of identifying entities within an entity setalthough several candidate keys may exist,one of the candidate keys is selected to be the primary keyneed to consider semantics of relationship set in selecting the primary key in case of more than one candidate key,6.3.2.1 Keys For Entity Sets(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,33,Keys for relationship sets R on entities E1,E2,EnR=(e1,e2,en)|e1E1,enEn E1 E2 En,how to uniquely distinguish each relationship instances(e1,e2,en)?R is the combination of E1,E2,En,each Ei can be uniquely distinguished by primary_key(Ei),1 i n,so the set of all attributes in primary_key(E1),primary_key(E2),primary_key(En)can be used to recognize(e1,e2,en),6.3.2.2 Keys For Relationship Sets,March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,34,The super_key for Rprimary_key(E1)primary_key(E2).primary_key(En)e.g.in Fig.6.3,(customer-id,account-number)is the super key of depositornoteif the attribute names of primary-keys are not unique,the attributes with the same names should be renamed The candidate keys for R minimal,non-redundant super keys,6.3.2.2 Keys For Relationship Sets(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,35,The candidate keys or the primary key for a binary relationship set R among entity sets A and B can be decided as follows,in accordance with the mapping cardinality of R R is many-to-many,primary_key(R)=primary_key(A)primary_key(B)R is many-to-one from A to B,!primary_key(R)=primary_key(A)R is one-to-many,!primary_key(R)=primary_key(B),6.3.2.2 Keys For Relationship Sets(cont.),March 2009,Database System Concepts-Chapter6 Database Design and E-R Model,36,R is one-to-one,primary_key(R)=primary_key(A)or:primary_key(R)=primary_key(B),