东南大学数据库课程06-Database Design

Database Design

目录

• Database Design
• Data Dependency
• Normalization of Relational Schema
  • 1NF
  • 2NF
  • Problems of non 2NF
  • 3NF
  • Problems of non 3NF
  • Summary
• ER Model and ER Diagram
• Database Design Method
  • Requirement Analysis
  • Concept Design
  • Logic Design
  • Physical Design
• Summary

Data Dependency

Some dependent relations exit between attributes

1. 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.(学号可以决定其他属性的值，其他属性是函数依赖与学号的)

2. Multi-valued Dependency(MVD)：the value of some attribute can decide a group of values of some other attributes. (函数依赖是多值依赖的特例；决定一个值\决定一组值)

3. Join Dependency (JD): the constraint of lossless join decomposition.

   SPJ(供应商，零件，项目)

   SPJ[S,P,J] = SPJ[S,P] join SPJ[S,J] join SPJ[p,j]
   无损连接分解
   SPJ三个属性存在连接依赖

💡 多值依赖与连接依赖并不常见，所以设计数据库时大多只考虑函数依赖

Normalization of Relational Schema

1NF

every attribute of a relation must be atomic.（不允许表中套表）

2NF

1. R∈1NF
2. no partially function dependency exists between attributes.（不存在属性对主键的部分函数依赖）

S(S#, SNAME, AGE, ADDR, C#, GRADE)
--- non 2NF

(S,C)是主键，S可以决定SNAME,AGE,ADDR。三者对主键存在部分函数依赖，不属于2NF

Problems of non 2NF

• Insert abnormity: can not insert the students’
  information who have not selected course.【因为SC都是主键】
• Delete abnormity: if a student unselect all courses, his
  basic information is also lost.【C是主键，存在实体性约束】
• Hard to update: because of redundancy, it is hard to
  keep consistency when update.【比如一个学生选了50门课，那么它的基本信息没有必到地存了50次，这个更新带来了麻烦】

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

1. R∈2NF
2. no transfer function dependency exists between attributes.【属性对主键没有传递依赖】

EMP(EMP#, SAL_LEVEL, SALARY)
--- non 3NF

【SALARY依赖于SAL_LEVEL,SALE_LEVEL依赖于EMP】

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.【SALE_LEVEL和SALE只需要知道一个，冗余了】

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

Summary

一般到3NF即可，必要时会根据实际情况逆范式。基本原则是“一事一地”

ER Model and ER Diagram

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
  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

Requirement Analysis：定义数据字典，DFD(数据流图)

Concept Desing：画出ER图，与具体的DBMS无关

Logic Design：将er图转换为具体的表

Physical Design：物理实现

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 confilicts

  ➢ Homonym(the same name with different meanings)
  ➢ Synonym(the same meaning in different names)

• Concpet confilicts

  dept在一个表中是一个属性，在另一个表中是一个实体

• Domain conflicts

  性别的取值：男女，0,1，MW

About coding
➢ Standardization of information
➢ Identifying entities
➢ Compressing【压缩】 information

Through requirement analysis, all information must be with unique source and unique responsibility

Concept Design

Logic Design

从此处开始都是技术活儿了，与甲方无关了

denormalization：逆范式

Physical Design

Summary

本质：原子数据

https://www.bilibili.com/video/BV1Xt4y1L7S1/?spm_id_from=333.1007.tianma.1-1-1.click