Structural Constraints 结构约束• Apply on the entity types that participate in a relationship • Come from the real world constraints in client’s domain 适用于参与关系的实体类型• We focus on binary relationships which have two participating entity types • Three types of binary relations — one-to-one – 1:1 — one-to-many – 1:* — many-to-many – *:*三种参与实体类型的二元关系一对一,一对多,多对多
Diagrammatic Representation of 1:1 Relationships
• For example, Staff Manages Branch• Meaning — At least one and a maximum of one staff manages a branch — A member of staff manages zero or one branch- 至少一名、最多一名工作人员管理一个分支机构 - 一名工作人员管理零个或一个分支机构
Diagrammatic Representation of 1:*
• For example, Staff oversees PropertyForRent
• Meaning — At least zero and a maximum of one staff oversees a property — A member of staff oversees zero or many properties除了*代表多个以外其他与上面的一对一规则一致
Diagrammatic Representation of *:*
• For example, NewsPaper Advertises PropertyForRent
• Meaning — At least zero and a maximum of many newspapers advertise a property — A newspaper advertises one or many properties除了*代表多个以外其他与上面的一对一规则一致
• Multiplicity range is for this specification has two parts — Min — Max — For example, for a multiplicity range of 0..1 ◦ Min = 0 ◦ Max = 1 • Max of a multiplicity range denotes Cardinality • Min of a multiplicity range denotes Participation
Enhanced ER (EER) Modelling • ER modelling does not capture all the semantics of client’s domain, such as — ‘ISA’ (‘is a’) relationship or specialization-generalization ◦ ‘Manager’ entity type ‘is a’ subentity of ‘Staff’ entity — ‘HASA’ (‘has a’) relationship or ‘is-part-of’ relationship or aggregation ◦ A relationship between the ‘whole’ and the ‘part’ ◦ Branch (whole) Has Staff (part) ◦ Composition is a special form of aggregation – ‘part’ is strongly owned by the ‘whole’ • Enhanced ER models represent the above relationships — Therefore capture client’s domain more comprehensively '经理'实体类型'是''员工'实体的'子实体'。 ◦ 分公司(整体)拥有员工(部分) ◦ 组成是聚合的一种特殊形式 ISA即具体实体类型可以为抽象实体类型的子实体,HASA即整体攘括了部分
Diagrammatic Representation of ‘ISA’ Relationship
Diagrammatic Representation of ‘HASA’ Relationship
Step-by-Step Procedure for Conceptual Design • Identify entity types • Identify relationship types • Identify and associate attributes with entity or relationship types • Determine attribute domains • Determine candidate, primary and alternate key attributes • Consider use of enhanced modelling concepts (optional) • Check model for redundancy • Validate conceptual model against user transactions • Review conceptual data model with user • We will focus on only some of these steps (see C&B for more)概念设计首先确定实体类型,关系类型和其相关联属性;然后确定属性的定义域;接着确定主键与备键和候选键;考虑EER并检查模型冗余;根据用户具体验证概念模型并审查数据模型
Identify Entity Types
• Determine the main concepts in the domain about which the database has to store data 确定数据库中的数据的定义域• In the user requirement specification, identify — Nouns and noun phrases — Places, people and concepts — Objects with independent existence — Watch out for synonyms and homonyms 用户需求:名词 地点人物概念 独立存在的对象• Draw the entity types in the ER diagram • Document entity details in the data dictionary
Example • In the DreamHome domain the main concepts are: — Property For Rent – the whole business revolves around this concept — Client – once again an important concept for the business — Owner of the property — Staff and the Branches they manage
Identify Relationship Types • Determine the relationships among the entity types identified in the previous step — Relationships may open up new entity types!! • In the user requirement specification, identify — Verbs and verb groups (verbal expressions) — First identify binary relationships — Only then identify complex relationships — Check the possibility of a relationship between each pair of entity types ◦ Time consuming but possible on smaller design problems 确定实体类型之间的关系 用户需求:动词 确定二元关系进而确定复杂关系 检查实体里欸选哪个之间可能存在的关系— Determine the structural constraints • Draw the relationship types in the ER diagram • Add information about structural constraints to the ER diagram • Document relationship details in the data dictionary
Specify Structural Constraints指定结构约束 • A relationship has some participating entities — e.g., Staff manage Branch has Staff and Branch as the participating entities • The main task in relationship specification is to specify structural constraints (min-max constraints) on the participating entities — e.g., Many Staff might manage a Branch • These constraints specify how many instances of data from one participating entity correspond to one instance from the other participating entity — e.g., One Branch may have many Staf 分析清楚关系中的参与实体 关系指定上主要是参与实体的最小值与最大值即前文提到的一对一,一对多,多对多问题
Identify and Associate Attributes识别并关联属性 – I • For each entity/relationship identified in the previous steps — Determine required information about that entity/relationship — If an attribute is composite ◦ If the user wants to access parts of the composite attribute ◦ Represent it in terms of the constituent simple attributes — If an attribute is multi-valued ◦ Model it as a separate entity at this stage Or ◦ Leave it alone at this stage – logical design process will anyway model it as a separate relation首先需要确定每个实体和关系所需的信息也就是记录的属性,如果属性是复合的话,在用户访问时用简单属性的组合来表示;如果属性有多值情况,则作为单独的实体重新建模
Identify and associate attributes – II • Alternatively make a list of attributes from user requirements specification • Tick them off the list as you associate them with an entity/relationship • When attributes appear to be associated with more than one entity/relationship, either — have a potential relationship between the entity types — Or have a case for applying generalization/specialization • Add attribute information to the ER diagram and data dictionary根据用户所需列出属性表时,将与对应实体或关系关联的属性从表输出 当属性与多个实体或关系关联时,有可能时由于实体类型之间存在潜在关系,也有可能时存在泛化和特化情况
Guidelines for Identifying Primary Key确定主键 • The candidate key with the minimal set of attributes • The candidate key that is least likely to have its values changed • The candidate key with fewest characters • The candidate key with smallest maximum values • The candidate key that is easiest to use from the user’s point of view具有最少属性,值不易更改,字符最少,最大值最小且容易被用户区分的候选键
Conceptual Design of DreamHome
Transaction Pathways一种验证EER模型的方法 • An approach to validate EER model — by manually executing user specified transactions • The entities and relationships involved in the execution are directly marked on the EER diagram — not possible for large number of transactions – the diagram will become unreadable • Useful visualization showing — areas of the diagram that are essential for transactions and — areas of the diagram that are not required for transactions 手动执行用户指定的事务,过程中涉及的实体和关系标注在EER图 但是不能处理大量事务,可视化上划分为对指定事务重要的区域和不需要的区域
• Conceptual design yields an EER Model • EER Model — is a high level description of data — represent data semantics in a way that non-experts (client’s) can read them and validate them (hopefully!) — is subjective – depends upon the selective view of the data taken by the designer ◦ Entity vs attribute dilemma, entity vs relationship dilemma, binary vs tertiary relationship dilemma and so on概念设计产生EER模型