database design 有关ER model

• Database design achieved in three phases — Conceptual – model data independent of all physical considerations独立建模 — Logical – refine and map conceptual model onto relational model (or some other database model such as object-oriented)完善概念模型映射到关系模型 — Physical – map logical model onto a specific DBMS (say, MySQL)将逻辑模型映射到DBMS • In simple domains where we have good understanding of the domain data, we may not use the three phase method — We open MSAccess (or any other RDBMS) and directly define tables and their links (not adviced)直接定义表和连接 • For real-world applications, you should use the three phase method • A good design facilitates efficient storage and retrieval of data

logical design involves — Designing a collection of connected (linked) tables in which the domain data is stored and 设计连接表集合存储数据— The table design facilitates SQL to work with these tables表的设计便于SQL与表协同工作 ◦ Insert, update, delete and select operations must be efficiently run

• Conceptual design – Modelling data in the domain数据域模型 — What are the individual data concepts? ◦ Staff – name, position, etc ◦ Properties – address, rent, etc ◦ Branches – address ◦ Clients – name, contact, etc — How are these items related? 项目如何关联◦ e.g., staff manage properties and ◦ staff work at a branch

• Logical Design – Given the conceptual design, logical design involves — Designing individual tables 设计单表such as Staff and Branch and — Link these tables using foreign keys 使用外键标记关系(to mark the relationships)

• Physical Design – Given the logical design, physical design involves — Implementing the logical design in MSAccess/MySQL (a specific RDBMS)在MySQL事件逻辑设计

• Two levels of design — Conceptual design — Logical (Relational) design — Relational design uses information from conceptual design ◦ Without the information from conceptual level relational design is not possible • Tables at relational level are obtained from both entities and relationships (formally introduced later) at conceptual level — Staff table derived from Staff Concept from conceptual design — Viewing table derived from ViewedBy relationship from conceptual design

• Conceptual design = ER Modelling• Model data independent of DBMS, application programs, programming languages, hardware platform etc

• Models a domain of discourse • Central ideas — Domains are made up of entities定义域有实体组成 — Relationships link associated Entities关系连接实体 — Entities and relationships have properties called attributes 二者都有属性— Certain attributes are special, call them primary keys and alternate keys特殊属性主键和备键 — Need integrity constraints to preserve domain consistency通过完整性约束保持定义域一致性 • Deliverables — ER Models – documented diagrammatically以图解方式记录 — Data dictionary • Documentation is an important component of ER modelling

• A group of objects with the same properties, which are identified by the enterprise as having an independent existence具有相同属性的一组对象称为实体 — Not a formal definition — Different designers may identify different entities • Entity Occurrence – a uniquely identifiable object of an entity type • Can have physical existence — For example, Staff, Property, Customer • Can have conceptual existence — For example, Viewing and Inspection可以是物理存在也可以是概念

A rectangle labelled with the name of the entity • First letter of each word in the entity name is uppercase — e.g., PropertyForRent • Normally an entity type is named using a ‘noun’ or ‘noun phrase’实体图形化表示为标有实体名称的矩形 每个单词首字母大写  通常都为名词或名词短语

• A set of meaningful associations among entity types 关系类型是实体类型间一组有意义关联• For example, ‘Branch’ and ‘Staff’ can be associated with a relationship ‘Has’ • Relationship occurrence – a uniquely identifiable association, which includes one occurrence from each participating entity type唯一可识别关联 包括参与实体的发生率

Shown as a line connecting the associated entity types, labelled with the name of the relationship • First letter of each word in the entity name is uppercase • Normally a relationship is named using a ‘verb’ or ‘verb group’ • The direction in which the relationship makes sense is marked using an ‘arrow’实体类型用动词且箭头

Degree of Relationship Type • The number of participating entity types in a relationship • Relationships can be classified based on their degree into — Binary 二元– relationship with two participants — Ternary 三元– relationship with three participants — Quaternary四元 – relationship with four participants关系度决定于参与者数量

A diamond is used to represent ternary and quaternary relationships用菱形表示三元四元关系

Attributes • A property of an entity or a relationship type • For example, Staff has 4 attributes — StaffNo — Name — Position — Salary一种实体或关系类型的属性

Diagrammatic Representation of Attributes • Entity rectangle is divided into two parts — Upper part displays the entity name — Lower part displays the list of attributes — Normally an attribute is named using an ‘noun’ or ‘noun group’ • Primary key should be the first in the list 属性图实体军星组成分为两部分 上半部分为实体名称 下半部分为属性列表 属性通常为名词或名词组 主键要在列表中排到第一位