《02 Data Modeling Using The EntityRelationship (Er) Data Model.ppt》由会员分享，可在线阅读，更多相关《02 Data Modeling Using The EntityRelationship (Er) Data Model.ppt（70页珍藏版）》请在三一办公上搜索。
1、Data Modeling Using the Entity-Relationship(ER)Data Model,(Based on Chapter 3 in Fundamentals of Database Systems by Elmasri and Navathe,Ed.3),Contents,1Using High-Level Conceptual Data Models for Database Design2An Example Database Application(COMPANY)3ER Model Concepts3.1Entities and Attributes3.2
2、Entity Types,Value Sets,and Key Attributes3.3Relationships and Relationship Types3.4Structural Constraints and Roles3.4Weak Entity Types4ER Diagrams Notation5Relationships of Higher Degree6Extended Entity Relationship(EER)Model,FIGURE 3.1,Example COMPANY Database,Requirements for the COMPANY Databas
3、e:The company is organized into DEPARTMENTs.Each department has a name,number,and an employee who manages the department.We keep track of the start date of the department manager.A department may have several locations.,Example COMPANY Database,Requirements for the COMPANY Database:Each department c
4、ontrols a number of PROJECTs.Each project has a name,number,and is located at a single location.,Example COMPANY Database,Requirements for the COMPANY Database:We store each EMPLOYEEs social security number,address,salary,sex,and birth date.Each employee works for one department but may work on seve
5、ral projects.We keep track of the number of hours per week that an employee currently works on each project.We also keep track of the direct supervisor of each employee.,Example COMPANY Database,Requirements for the COMPANY Database:Each employee may have a number of DEPENDENTs.For each dependent,we
6、 keep their name,sex,birth date,and relationship to the employee.,FIGURE 3.2,ER Model Concepts,Entities and AttributesEntity Types,Value Sets,and Key AttributesRelationships and Relationship TypesStructural Constraints and RolesWeak Entity Types,Entities 個體,An entity is a“thing”in the real world wit
7、h an independent existence（conceptual or physical）.Entities are specific objects or things in the mini-world that are represented in the database;for example the EMPLOYEE John Smith,the Research DEPARTMENT,the ProductX PROJECT.,FIGURE 3.3,Attributes 屬性、特性,Attributes are properties used to describe a
8、n entity;for example an EMPLOYEE entity may have a Name,SSN,Address,Sex,BirthDate.,Particular Entities 特定的個體：各個屬性有其一個對應值,A particular entity(specific entity)will have a value for each of its attributes;for example a specific employee entity may have Name=John Smith,SSN=123456789,Address=731 Fondren,
9、Houston,TX,Sex=M,BirthDate=09-JAN-55.,Types of Attributes 屬性類型,Simple 簡單型（不可拆解）versus Composite 複合型（可拆解）Single-value 單一一個值versus Multi-valued多重值（多個值）Stored 儲存型versus Derived 推導型,Simple Attributes:簡單型（不可拆解）,Each entity has a single atomic value for the attribute;for example SSN or Sex.,Composite Attr
10、ibutes:複合型（可拆解）,The attribute may be composed of several components;for example Address(Apt#,House#,Street,City,State,ZipCode,Country)or Name(FirstName,MiddleName,LastName).Composition may form a hierarchy where some components are themselves composite.,INSERT FIGURE 3.4,Single-valued Attributes一個值,
11、Most attributes have a single value 一個值 for a particular entity;such attributes are called single-valued.For example,Age is a single-valued attribute of person.,Multi-valued Attributes:多重值（多個值）,An entity may have multiple values for that attribute;for example Color of a CAR or PreviousDegrees of a S
12、TUDENT.Denoted as Color or PreviousDegrees.,Stored預存的 VS.Derived 推導出Attributes,In some cases two(or more)attribute values are related.Some attribute values can be derived from related entities.For example,The value of Age can be determined from the current date and the value of that persons BirthDat
13、e.,Null Values 空值,In some case a particular entity may not have an applicable value for an attributes.For such situations,a special value called null is created.,Complex Attributes 複雜屬性,In general,composite and multi-valued attributes may be nested arbitrarily to any number of levels although this i
14、s rare.For example,PreviousDegrees of a STUDENT is a composite multi-valued attribute denoted by PreviousDegrees(College,Year,Degree,Field).,FIGURE 3.5,Entity Types 個體類型,Entities with the same basic attributes are grouped or typed into an entity type.For example,the EMPLOYEE entity type or the PROJE
15、CT entity type.,Entity Sets 個體集合,The collection 集合of all entities of a particular entity type in the database at any point in time is called an entities set.The entities set is usually referred to using the same name as the entity type.,FIGURE 3.6,An Entity Type,An entity type describes the schema o
16、r intension for a set of entities that share the same structure.The collection of entities of a particular entity type are grouped into an entity set,which is also called the extension of the entity type.,Key Attributes of an Entities Type,An attribute of an entity type for which each entity must ha
17、ve a unique value is called a key attribute of the entity type.For example,SSN of EMPLOYEE.,Key Attributes of an Entities Type,A key attribute may be composite.複合鍵值For example,VehicleRegistrationNumber is a key of the CAR entity type with components(Number,State).,Key Attributes of an Entities Type,
18、An entity type may have more than one key.For example,the CAR entity type may have two keys:VehicleIdentificationNumber and VehicleRegistrationNumber(Number,State).,Value Sets(Domains)of Attributes,Each simple attributes of an entity type is associated with a value set(or domain of values),which spe
19、cifies the set of values that may be assigned to that attribute for each individual entity.,FIGURE 3.7,FIGURE 3.8,Relationships 關係,A relationship relates two or more distinct entities with a specific meaning;For example,EMPLOYEE John Smith works on the ProductX PROJECT or EMPLOYEE Franklin Wong mana
20、ges the Research DEPARTMENT.,Relationship Types 關係類型,Relationships of the same type are grouped or typed into a relationship type.For example,the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate,or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate.,Re
21、lationship Types,A relationship type R among n entity types E1,E2,En defines a set of associations or a relationship set among entities from these types.,Degree 程度（維度）of a Relationship Type,The degree of a relationship type is the number of participating entity types.(個體類型的參與數目）Both MANAGES and WORK
22、S_ON are binary relationships.（二位元關係類型）,Relationship types,More than one relationship type can exist with the same participating entity types;（同樣的個體類型可能會有一個以上的關係類型）for example,MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and DEPARTMENT participate.,FIGURE 3.9,關係,關係類型,個體類型,個體類型,個
23、體,個體,Relationships of Higher Degree,Relationship types of degree 2 are called binaryRelationship types of degree 3 are called ternary and of degree n are called n-aryIn general,an n-ary relationship is not equivalent to n binary relationships,FIGURE 3.10,Roles Names 角色,Each entity type that particip
24、ates in a relationship type plays a particular role in the relationship.The role name signifies the role that a participating entity from the entity type plays in each relationship instance 實例案例,and helps to explain what the relationship means.,Roles Name,For example,in the WORK_FOR relationship typ
25、e,EMPLOYEE plays the role of employee or worker and DEPARTMENT plays the role of department or employer.,Recursive Relationships 遞迴關係,A relationship can relate two entities of the same entity type;for example,a SUPERVISION relationship type relates one EMPLOYEE(in the role of supervisee)to another E
26、MPLOYEE(in the role of supervisor).This is called a recursive relationship type.,FIGURE 3.11 遞迴關係（自我參照）,Structural constraints 結構限制 on relationships,Cardinality ratio 數目比率(of a binary relationship):二位元的關係類型1:1,1:N,N:1,or M:N.Participation constraint 參與限制(on each participating entity type):total 完全參與
27、(called existence dependency 實體相依)or partial.部分參與,I.Cardinality ratios for Binary Relationships.,The cardinality ratio for a binary relationship specifies the number of relationship instances that an entity can participate in.一個個體可以對應的關係數目The possible cardinality ratios for binary relationship types
28、 are 1:1,1:N,N:1,M:N.,II.Participation Constraints,The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type.There are two types of participation constraints total and partial.,FIGURE 3.12（1:1）部分參與對完全參與,FIGURE 3
29、.13(M：N)完全參與對完全參與,Relationships as Attributes.,A relationship type can have attributes;for example,HoursPerWeek of WORKS_ON;its value for each relationship instance describes the number of hours per week that an EMPLOYEE works on a PROJECT.,Weak Entity Types 弱勢個體類型(1/2),An entity type that does not
30、have a key attribute.A weak entity type must participate in an identifying relationship type with an owner or identifying entity type.,Weak Entity Types 弱勢個體類型(2/2),Entities are identified by the combination of:A partial key of the weak entity typeThe particular entity they are related to in the ide
31、ntifying entity type,Example:,Suppose that a DEPENDENT entity is identified by the dependents first name and birthdate,and the specific EMPLOYEE that the dependent is related to.DEPENDENT is a weak entity type with EMPLOYEE as its identifying entity type via the identifying relationship type DEPENDE
32、NT_OF.,FIGURE 3.14(1/2),56,FIGURE 3.14(2/2),Alternative(min,max)notation for relationship structural constraints:,Specified on each participation of an entity type E in a relationship type R.Specifies that each entity e in E participates in at least min and at most max relationship instances in R.De
33、fault(no constraint):min=0,max=n.Must have min0,max1.Derived from the mini-world constraints.,Examples(a):,A department has exactly one manager and an employee can manage at most one department.Specify(1,1)for participation of DEPARTMENT in MANAGESSpecify(0,1)for participation of EMPLOYEE in MANAGES
34、,Examples(b):,An employee can work for exactly one department but a department can have any number of employees.Specify(1,1)for participation of EMPLOYEE in WORKS_FORSpecify(0,n)for participation of DEPARTMENT in WORKS_FOR,FIGURE 3.15,ER-to-Relational Mapping(1/4),Step 1:一般個體形成一個關聯表regular entity ty
35、pe mapped to a relation;one key of the entity type chosen as primary key for the relation.Step 2:弱勢個體形成一個關聯表For weak entity type,include the key attribute(s)of the owner relation;Primary key is combination of owner key attributes and partial key of weak entity type.,ER-to-Relational Mapping(2/4),Ste
36、p 3:1:1形成外來鍵 Each binary 1:1 relationship type mapped to a foreign key from one relation referring to other relation.(通常是完全參與方參照部分參與方）Step 4:1:N形成外來鍵Each binary 1:N relationship type mapped to a foreign key in relation at N-side referring to relation at 1-side.(N方參照到1方）,ER-to-Relational Mapping(3/4)
37、,Step 5:M:N形成額外的關聯表Each binary M:N relationship type mapped to a relation whose primary key includes the keys of both participating relations.Step 6:多重值形成額外的關聯表Each multi-valued attribute mapped to a relation R that includes the key of the owner relation.,ER-to-Relational Mapping(4/4),Step 7:多元關係形成額
38、外的關聯表(同M:N)Each n-ary relationship mapped to a relation that includes the keys of all participating relations.,Notes on ER-to-Relational Mapping(1/4),Composite attributes represented by their simple components.（,）A separate relation is created for each multi-valued attribute.,Notes on ER-to-Relation
39、al Mapping(2/4),Relationships in ER are mapped to foreign key attributes.A single foreign key needed for 1:1 or 1:N relationshipsA extra relation with two foreign keys needed for binary M:N relationship.For n-ary relationship,n2,we need an extra relation with n foreign keys.,Notes on ER-to-Relationa
40、l Mapping(3/4),EQUIJOIN 相等（等位）合併operations are needed to materialize the relationships by combining related tuples:A single EQUIJOIN needed to materialize 1:1 or 1:N relationships.Two EQUIJOINS needed to materialize binary M:N relationshipN EQUIJOINS needed to materialize the full n-ary relationship
41、.,Notes on ER-to-Relational Mapping(4/4),Primary key of weak entity type includes key of owner relation.Primary key of relation representing an n-ary relationship determined from the structural participation constraints.,69,ER-Model 錯誤可能之處(1/2),ER內容錯誤符號用錯(或是自創）、及一致性（採用同一套）關係類型搞錯1:1、1:M、1:N參與限制搞錯（完全vs.部分）PK未標、標FK（另一學派贊成）ER轉關聯表過程錯誤ER沒有的屬性會出現在關聯表（或反之）關聯表的FK位置放錯缺少關聯表(忽略n:m 會額外產生一個關聯表）,70,ER-Model 錯誤可能之處(2/2),一個N元關係(n-ary)的形成一定是兩兩都符合才會放在一起Ex.n-m-k vs.n-m-1弱實體的判斷：可依FK的刪除限制來判斷，通常是連鎖刪除者（本尊刪除跟著刪除分身的關係），其分身關係即為弱實體。,
