6. Database Design* 数据库设计

6.1 Data Dependency and Normalization of Relational Schema 数据依赖

• Some dependent relations exist between attributes. 属性之间存在依赖关系
• Function dependency (FD): the most basic kind of data dependencies. The value of one or a group attributes can decide the value of other attributes. FD is the most important in general database design.
  函数依赖：最基本的数据依赖。一个或一组属性的值决定另外一组属性的值。
• Multi-valued Dependency (MVD): the value of some attribute can decide a group of values of some other attributes.
  多值依赖：
• Join Dependency (JD): the constraint of lossless join decomposition. 无损连接依赖

6.2 NF

1NF

every attribute of a relation must be atomic. 表中套表

2NF

• R∈1NF and no partially function dependency exists between attributes. 不存在属性之间的部分依赖
• S(S#, SNAME, AGE, ADDR, C#, GRADE)
  — non 2NF

Problems of non 2NF:

• Insert abnormity: can not insert the students’ information who have not selected course. 插入异常，不能插入没选课的学生的基本信息
• Delete abnormity: if a student unselect all courses, his basic information is also lost. 删除异常，如果一个学生推出课程，他的基本信息也会丢失
• Hard to update: because of redundancy, it is hard to keep consistency when update. 由于数据冗余，当更新时
  Resolving：
  According to the rule of “one fact in one place” to decompose the relation into 2 new relations:
• S(S#, SNAME, AGE, ADDR)
• SC(S#, C#, GRADE)
  按照一时一地的原则

3NF

• R∈2NF and no transfer function dependency exists between attributes. 不存在传递依赖
  EMP(EMP#, SAL_LEVEL, SALARY) 工资级别，工资额度
  — non 3NF
  Problems of non 3NF
• Insert abnormity: before the employees’s sal_level are decided, the correspondence between sal_level and salary can not input.
  雇员工资级别还没定前，工资等级和工资对照不能录入
• Delete abnormity: if some sal_level has only one man, the correspondence between sal_level and salary of this level will be lost when the man is deleted.
  如果某个工资等级只有一个人，当这个人被删除时，这个对照关系也被删除。
• Hard to update: because of redundancy, it is hard to keep consistency when update.
  由于冗余，更新时难以维护
  Resolving：
  According to the rule of “one fact in one place” to decompose the relation into 2 new relations:
  EMP(EMP#,SAL_LEVEL)
  SAL(SAL_LEVEL,SALARY)

Material Card

How to define relations to express the information on this card?

范式

1NF→2NF→3NF→4NF(消除多值依赖)→5NF(消除连接依赖)
BCNF(函数之间的依赖关系的决定子都必须是主键)
总结：一事一地

ER Model and ER Diagram

• Concept model: entity—relation, be independent of
  practical DBMS.
• Legend:
  

6.3 Database Design Method

• Procedure oriented method 面向过程
  This method takes business procedures as center, the database schema is designed basically in accordance directly with the vouchers, receipts, reports, etc. in business. Because of no detailed analysis on data and inner relationships between data, although it is fast at the beginning of the project, it is hard to ensure software quality and the system will be hard to fit future changes in requirement and environment So this method is not environment. suitable for the development of a large, complex system.
• Data oriented method 面向数据
  This method design the database schema based on the detailed analysis on data and inner relationships between data which are involved in business procedures. It takes data as center, not procedures. It can not only fulfill the current requirements, but also some potential requirements. It is liable to fit future changes in requirement and environment. It is recommended in the development of large, complex systems.
  在设计过程中，需要分析实际应用中各个功能模块业务的处理流程，分析数据之间的内在关系，消除数据再概念上的矛盾和冲突。在这个基础上，设计一个比较好的、符合三范式的数据模式，反过来支撑业务流程的实现。

Database Design Flow

信息需求：应用中管理的数据信息
处理需求：处理逻辑
需求分析：用户需求分析说明书
概念设计：设计独立于DBMS的设计模式
逻辑设计：考虑DBMS特性，设计外模式（视图）概念模式
物理设计：考虑DBMS特性、服务器硬件、操作系统特性，得到内模式

Requirement Analysis

A very important part of system requirement analysis. In requirement analysis phase, the data dictionary and DFD (or UML) diagrams are the most important to database design.

• Dictionary and DFD 数据字典和数据流图
  • Name conflicts 名字冲突
    • Homonym(the same name with different meanings)
    • Synonym(the same meaning in different names)
  • Concept conflicts 概念冲突
  • Domain conflicts 域冲突
• About coding 编码
  • Standardization of information 信息标准化
  • Identifying entities 标识实体
  • Compressing information 信息压缩
• Through requirement analysis, all information must be with unique source and unique responsibility.
  通过需求分析，所有的信息必须来源唯一和责任唯一

Concept Design

Based on data dictionary and DFD, analyze and classify the data in data dictionary, and refer to the processing requirement reflected in DFD, identify entities, attributes, and relationships between entities. Then we can get concept schema of the database.

• Identify Entities 识别实体
• Define the relationships between entities 定义实体之间的联系
• Draw ER diagram and discuss it with user 画ER图并和用户讨论
  • It is proposed to use ER design tools such as ERWin, Rose, etc. 建议使用ER图设计工具

Logic Design

According to the entities and relationships in ER diagram, define tables and views in target DBMS. Basic standard is 3NF.

• Translate entities and relationships in ER diagram to tables
• Naming rule of table and attribute
• Define the type and domain of every attribute
• Suitable denormalization
• Necessary view 必要的视图
• Consider the tables in legacy system 考虑遗留表
• Interface tables 接口表

Physical Design

For relational database, the main task in this phase is to consider creating necessary indexes according to the processing requirements, including single attribute indexes, multi attributes indexes, cluster indexes etc Generally the attribute often as indexes, etc. Generally, query conditions should have index.
Other problems:

• Partition design 分区设计，例如Oracle
• Stored procedure 存储过程
• Trigger
• Integrity constraints

Remarks

• 仅仅在结构上达到3NF (BCNF) 是不够的。
• “一事一地”包括每项信息的唯一，要提取出问题的本质，识别出本质上同一概念的信息项。
• 对于表达类似信息，模式相似只是取值不同的表，应尽量合并。如学习经历、进修经历；奖励信息、惩处信息等。
• 考虑到效率、用途等因素，该分开的表还应分开。如本科生基本信息和研究生基本信息。
• 结合DBMS内部实现技术，合理设计索引和文件结构，为查询优化准备好存取路径。
• 在结构规范化、减少数据冗余和提高数据库访问性能之间仔细权衡，适当折中。

数据库设计实例分析