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

参考资料
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 relationships.
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

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

    QQ截图20120327120509

 

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.

posted on 2012-03-27 14:57  ybwang1989  阅读(1562)  评论(0编辑  收藏  举报

导航