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

概念设计(conceptual design)

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

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

注意:

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)

逻辑设计阶段要做两件事

注意:

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

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.