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
-
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.
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
- R∈1NF
- 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
- R∈2NF
- 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