logical database design 逻辑设计

From ER Model to Relational Model • Our method of designing relational (logical) model uses information from ER Model — We assume that ER modelling is performed before relational modelling • Informally, ER Model — Partitions information in a domain into Entities (boxes) and attributes — Links entities up into a network to reflect the relationships from the real world domain — The network of entities represents the real world domain • Informally, relational model — Partitions information into tables (relations) — Links tables up into a network to reflect the relationships existing among data — The network of tables store data from the real world domain • We can notice similarities between ER Models and Relational Models — Entities correspond to Tables (relations) — Network of entities correspond to network of tables 实体对应表即关系 实体网络对应表网络

Step-by-Step Procedure for Logical Database Design • Derive relations for logical data model • Validate relations using normalization • Validate relations against user transactions • Check integrity constraints • Review logical data model with user • Merge logical data models into global model (optional) • Check for future growth  重要的是前两个步骤 逻辑数据模型推导关系和使用规范验证关系

Entities & their Attributes • Individual tables are derived from strong entities (entities with a clear Primary key) • Fields in the tables are derived from attributes associated with entities — Define the data types of the fields • Define the primary key of the table  • Foreign keys are decided later while modelling the relationships — Not all tables (relations) have foreign keys — At this stage, however, bear in mind that relation model is incomplete without deciding foreign keys 表个体都来自具有明确主键的实体 表中的数据来自与实体相关的属性（需要定义其数据类型） 定义表的主键 至于外键则在关系模型建立后确定 不是所有的表（关系）都有外键 没有确定的外键关系模型就不完整

• Consider the Staff entity in the DreamHome domain — Staff can be represented as a table at the relational level as

• Modelling relationships at the relational (logical) level involves a good understanding of the nature of the relationships • Recall that relationships can have different degrees – the number of entities participating in the relationship — Binary relationships have two entities participating in the relationship — Complex relationships have greater than two entities participating in the relationship • Binary relationships are modelled differently from complex relationships 在逻辑层面建立关系模型需要了解关系的性质 以参与关系的实体数量划分 二元关系与复杂关系（两个以上）

二元关系分为1..1 1..* *..*

One-to-many (1:*) Relationships • These are the most common type of relationships • Also known as ‘parent:child’ relationship — ‘One’ parent can have ‘many’ children — The entity on the ‘One’ side of the relationship is known as the Parent entity — The entity on the ‘many’ side is known as the Child entity • Our task: how 1:* relationship between two entities at ER Model level is represented in a relational model — We assume that both the participating entities are modelled as tables (as explained earlier) — Do we make any changes to these tables to reflect the relationship between them? ◦ Yes, we use a foreign key to mark the relationship — Recall that while modelling entities (as explained earlier) we have postponed foreign key decision — We make foreign key decision while modelling 1:* relationship 最常见的一对多 也叫父子关系 父实体为1 子实体为多 需要通过确定外键建模

• In a 1:* relationship — Foreign key is designed as a column in the child table (table on the * side) — Foreign key references the parent table (table on the 1 side) • In other words, when you post a foreign key to a table it means — This table is the child table and — For every row in the parent table, this table may have more than one (many) corresponding rows • Create a few rows of data in the tables participating in the 1:* relationship and check if the foreign key is acting as a link for information from the child table to the information from the parent table — Example data is always useful in designing foreign keys 外键为子表中的一列位于*侧并且引用外表位于1测，也就是或设置外键，该表一定是子表并且父表中的任意一行都可能对应子表的多行 可以通过参与一对多关系的表中创建一些数据来验证外键是否是子表和父表信息链接 所以实例数据对设计外键很重要

• In this case, — Staff is the Parent entity ◦ Because it is on the ‘one’ side of the relationship — PropertyForRent is the child entity ◦ Because it is on the ‘many’ side of the relationship • When we model this relationship at the relational level — We assume that Staff and PropertyForRent are modelled as tables as discussed earlier — We post a copy of the PrimaryKey, StaffNo from the Parent entity, Staff, as a foreign key in the child entity, PropertyForRent父实体的主键staffNo作为子实体的外键

Many-to-many (*:*) Relationships • There are two methods to tackle *:* relationships • First method: At the ER level, replace the *:* relationship to equivalent 1:* relationships — Then model the resulting 1:* relationships as explained earlier — In this method *:* relationship is reduced to two equivalent parent:child relationships • Second method: Create a new table to represent the relationship — We assume that the two entities participating in the relationship are already modelled as tables as explained earlier — The third table is created to represent the relationship • Both methods result in similar solutions — Three tables, where one of the tables (relationship table) links both the entity tables through foreign keys 处理多对多关系可以替换为等价的一对多关系即两个父子关系；还可以通过创建新的表即第三个表来表示关系 两种方法都是其中一个表关系表，两个实体表通过外键链接

这是根据第一种方法对 多对多关系建模• (a) in the above figure shows the *:* views relationship between PropertyForRent and Client • (b) shows an equivalent ER model that creates — Viewing as a new entity representing the relationship and — Takes and Requests as two new relationships of the type 1:* • Now model Viewing (entity), Takes and Requests (1:* relationships) as explained earlier 在（b）中将 PropertyForRent 和 Client两个实体之间的关系抽象为新的实体，Takes 和 Requests 作为两个新的一对多类型的关系

这是根据第二种方式对 多对多关系建模 Client和PropertyForRent之间的多对多关系直接表示为一个新表Viewing 新实体的外键来自两个参与实体的主键Primary key for the new entity includes the two foreign keys from the two participating entities（/）  要保证两种方法指向同一个表

One-to-one (1:1) Relationships • Generally, in relationship modelling we always identify the parent table — Then post a copy of its primary key as the foreign key in the child table • In this case of 1:1, max (cardinality) constraints which are 1:1 do not help to identify the parent table • Therefore we use min (participation) constraints to identify the parent table • For example, we choose the entity with min value zero as the parent entity, if the other participating entity has min value of one   在一对一关系中，关系建模时，先确定父表然后将父表主键作为子表的外键 ，通过选择最小值为0的实体来确定父实体

• Complex relationships too can be simplified into simpler 1:1 or 1:* relationships first and then modelled at the logical level • Alternatively, a new table can be created to represent a complex relationship and • Foreign keys are posted in the new table from all the participating entities在复杂关系中，将其简化为一对一，一对多关系再建模或者创建新表来表示复杂关系；所有新表的外键来自每个参与实体主键

Superclass/Subclass Relationships • In modelling the previous cases of relationships we focused on identifying the parent table in the relationship — Because its copy of the primary key is posted as the foreign key in the child table • Modelling superclass/subclass relationship is not about identifying foreign key • In this case, the focus is on deteriming the number of tables required to store the data corresponding to the classes and subclasses • We once again use constraints defined on the superclass/subclass relationships 

• Mapping from conceptual model to logical model mainly involves — Designing tables with primary keys — And linking tables with foreign keys 总结从概念模型到逻辑模型了两个步骤 设计带有主键的表然后用外键去连接表