[学习笔记]数据库设计概览_MySQL

参考资料
《Database design – Conceptual Design , Logical Design , Physical Design》
《Conceptual ~ Logical ~ Physical Models》

数据库设计的步骤：

一般来讲，设计一个数据库需要经过五个阶段：需求分析、概念设计、逻辑设计、物理设计四个阶段。Note that there is no general agreement which defines these terms, nevertheless data modelers generally understand the approximate scope of each.

• The conceptual model is concerned with the real world view and understanding of data;
• The logical model is a generalized formal structure in the rules of information science;
• The physical model specifies how this will be executed in a particular DBMS instance.

概念设计(conceptual design)

定义:A conceptual entity-relationship model shows how the business world sees information.

概念设计阶段要做如下三件事情

• Constructing the ER Model
• Check the model for redundancy
• Validating the model against user transactions to ensure all the scenarios are supported

注意:

1) A conceptual model suppresses non-critical details in order to emphasize business rules and user objects.
2) It typically includes only significant entities which have business meaning, along with their relationshiphotoshop/ target=_blank class=infotextkey>ps.
3) A conceptual model may include a few significant attributes to augment the definition and visualization of entities. No effort need be made to inventory the full attribute population of such a model.
4) A conceptual model may have some identifying concepts or candidate keys noted but it explicitly does not include a complete scheme of identity, since identifiers are logical choices made from a deeper context.

逻辑设计(logical design)

逻辑设计阶段要做两件事

• Table Generation From ER Model
• Normalization of Tables

注意:

1) 根据概念设计阶段的到的E-R图来生成具体的数据表时，要考虑E-R图中entity之间的对应关系怎样转换成数据表的对应关系。E-R图中的不同entity之间可能存在如下三种关系

• one-to-one: 在建表时，一对一的关系是用A表的主键作为外键指向B表的主键来实现。建议将一对一关系的两个实体合并成一个实体，即将A、B两个表合并成一个表
• one-to-many:将A表中的某个“非unique”的字段作为外键指向B表的主键，以此来实现一对多关系
• many-to-many:一般需要在A、B量表之外另建一张新表C来实现A、B之间的多对多关系

2) 数据表的规范化，即要使数据表满足各个范式，但通常只要满足前三范式就足够了。（关于范式,请参考《数据库设计三大范式与BCNF》）
3) 亦有人说,逻辑设计阶段并不要得出数据库中具体的数据表,而是得出更完整的entity示图,其实说这个示图是entity还是数据库中的table都是无妨的,无论怎样叫,这一步公认的关键点在于:每一个entity都要具备完整的attribute和identity,而不像上一步建立概念模型时只需要表示出主要的字段和键表达出entity之间的逻辑关系即可。另外logical data model不能有many-to-many的关系，要将conceptual design阶段的many-to-many关系转化成中间表(或说中间实体)来实现。

物理设计(physical design)

定义：The physical data model specifies implementation details which may be features of a particular product or version, as well as configuration choices for that database instance

概念设计、逻辑设计、物理设计结果的示意图：

E-R图：

E-R图并非只用来设计概念模型，实际上：There are three types of ERDs: the conceptual, the logical, and the physical, which support modeling of data in three levels of abstraction from conceptual data model to implementation of a specific database.