database design ER model 续

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模型