数据库设计基础

1、数据库设计基础内部公开网管软件 曹志勇 2012年03月2目录内部公开n关系数据库概念n关系数据库设计（结构设计）n数据库设计概述n需求分析n概念结构设计n物理结构设计n范式概念介绍n数据库备份策略n产品数据库介绍n关系数据库开发（行为设计）3关系数据库概念n 关系模型l 关系模型（RM即Relational Model）把世界看作是由实体Entity 和联系Relationship 构成的。l 实体就是指现实世界中具有区分于其它事物的特征或属性并与其它实体有联系的对象。在关系模型中实体通常是以表的形式来表现的。表的每一行描述实体的一个实例，表的每一列描述实体的一个特征或属性。l 联系就是指实

2、体之间的关系，即实体之间的对应关系。联系可以分为三种：一对一的联系。如：一个人只有一种性别，一个人-性别为一对一的联系。一对多的联系，反之则为多对一的联系。如：相同性别的人有许多个，性别-人为一对多的联系。多对多的联系。如：一个员工可以参与多个项目开发，一个项目需要多位员工开发，员工-项目为多对多的联系。n 关系数据库就是基于关系模型的数据库4关系数据库概念n 关系数据库管理系统l 关系数据库管理系统（RDBMS即Relational Database Management System），就是管理关系数据库的计算机软件。n 关键字l 关键字是关系模型中的一个重要概念，它是逻辑结构，不是数据库

3、的物理部分。l 侯选关键字（Candidate Key）：如果一个属性集能唯一地标识表的一行而又不含多余的属性，那么这个属性集称为侯选关键字。l 主关键字（Primary Key）：主关键字是被挑选出来作表的行的唯一标识的侯选关键字。一个表只有一个主关键字。主关键字又可以称为主键。l 公共关键字（Common Key）：两个实体之间使用相同的关键字，则该关键字称为这两个关系的公共关键字。l 外关键字（Foreign Key）：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外关键字。由此可见，外关键字表示了两个关系之间的联系。以另一个关系的外关键字作主关键字的表被称为

4、主表，具有此外关键字的表被称为主表的从表，外关键字又称作外键。5关系数据库概念n 联机事务处理（OLTP：On Line Transaction Processing）l 大用户群：有大量的用户群，而且同时要求获取相同的信息。l 极高并发性：相同信息的共享程度非常高。l 反应时间快：对数据库变化和数据库活动作出即时、实时的反应。l 小事务：用户会检索单条记录或非常小的连接。l 粒度小：高度规范化的结构会造成效率的下降，所以有时去规范化是不错的选择。l 利用面向对象的方法进行设计，即确定实体和关系。n 联机分析处理（OLAP：On Line Analytical Processing）l 小用户

5、群。l 低并发性：数据库仓库中的数据共享情况极少。l 反应时间慢：由于对大量的数据进行分析，所以反应时间相对较慢。l 数据规模庞大。l 利用面向主题的方法进行设计，即确定维度和事实（本课程不涉及数据仓库的设计方法）。6目录内部公开n关系数据库概念n关系数据库设计（结构设计）n数据库设计概述n需求分析n概念结构设计n物理结构设计n范式概念介绍n数据库备份策略n产品数据库介绍n关系数据库开发（行为设计）7关系数据库设计-数据库设计概述l 设计各级数据库模型，决定数据库系统的信息内容，包括数据库的概念模型设计和数据库的物理模型设计。l 决定数据库系统的功能，也就是说数据库开发的开始，通过存储过程，触

6、发器，视图等开组织数据的表现形式。8关系数据库设计-需求分析n 收集资料l 信息需求，确定数据内容，描述清楚系统中数据的性质和相互之间的联系。l 处理需求，确定数据处理的场景、数据处理的频率以及该数据处理对系统的影响。l 安全性和完整性需求，确定数据的完整性，一致性及安全性，保证最终的数据不会出现无用甚至破坏系统正常运行的数据出现。n 分析整理l 对以上收集的资料，分析出数据是基础数据，还是统计数据，是需要保存在数据库系统中，还是通过计算，转换来表现的数据。9关系数据库设计-需求分析n 数据流图l 可以形象的描述事务处理与所需数据的关联，便于用结构化系统分析方法，自顶向下，逐层分解，步步细化。

7、这是面向过程的设计方法。l 在数据流图中，用命名的箭头表示数据流，用圆圈表示处理，用矩形或其它形状表示数据存储。登录验证用户登录日志服务器10关系数据库设计-需求分析n 数据字典l 可以在设计完成后，提出该数据字典，数据字典的形成，需要在设计的过程中，标注出每个数据的解释说明，数据类型，唯一性，强制性（是否为空），取值范围，单位及缺省值等信息。n 用户确认11关系数据库设计-概念结构设计n 确定实体和属性n 确定实体关系n 规范化处理n 画出局部E-R图12关系数据库设计-概念结构设计n 确定实体和属性用户信息用户编号用户名称用户密码Characters (50)Variable charac

8、ters (100)Variable characters (32)user_pk设备信息设备编号设备名称设备类别Characters (50)Variable characters (100)Integerdevice_pk13关系数据库设计-概念结构设计n 确定实体关系l 明确实体之间的公共关键字。l 明确实体之间的主外健关系。l 明确实体之间的联系。n 实体关系举例如下：14关系数据库设计-概念结构设计15关系数据库设计-概念结构设计n 明确实体之间的公共关键字l 上图所示：部门编号是两个实体的公共关键字。l 下图所示：用户与角色之间没有公共关键字。n 明确实体之间的主外健关系l 上图所

9、示：部门编号是实体部门的主键，所以部门编号就是实体用户的外键。l 下图所示：因为用户与角色两个实体之间没有公共关键字，所以这两个实体之间并没有主外键关系。n 明确实体之间的联系l 上图所示：实体部门与实体用户之间的关系是一对多的关系，即一个部门有多个用户，一个用户只能隶属于一个部门。l 上图所示：实体部门与实体用户之间的关系也是一种松散的关系，即一个部门可以没有用户，一个用户也可以不隶属于任何部门。l 下图所示：用户与角色这两个实体构成多对多的联系，即一个用户可以拥有多个角色，一个角色可以有多个用户。l 下图所示：用户与角色实体之间的关系也是一种松散的关系，即一个用户可以不属于任何角色，一个角

10、色下也可以没有用户。16关系数据库设计-概念结构设计n 规范化处理l 确定规范化的级别，即关系模式达到第几范式。l 在规范化处理的同时，要根据需求分析阶段收集的数据考虑性能方面的问题，有些设计并非范式越高越好。冗余数据有可能会提高查询性能。l 一般的设计达到第三范式就足够了。17关系数据库设计-概念结构设计n 画出局部E-R图18关系数据库设计-概念结构设计n 明确实体之间的联系l 网管设备和FSU设备都是某类设备。l 网管设备与FSU设备之间通过总线连接，网管设备与总线是一对多的关系，总线与FSU设备也是一对多的关系。l 设备与网管设备和FSU设备是一种相对松散的关系，即设备实体中可以没有网

11、管设备或FSU设备，但只要有网管设备或FSU设备，就一定会有一个设备实体。l 网管设备与总线之间是一种紧凑的关系，即有网管设备就会有总线，总线 必须依附在一个网管设备上。l 总线与FSU设备是一种松散的关系，即总线下可以没有FSU设备，FSU设备也可以不依附于总线下。n 根据范式理论进行规范化处理l 网管设备和FSU的部分属性不完全依赖于设备主键（设备编号），通俗的理解就是男女有别，他们有对立也有统一。l 网管设备和FSU设备的一些属性可以完全继承设备实体的属性。l 范式理念随后讲解，以上分解即完全满足了第二范式（2NF）。19关系数据库设计-物理结构设计n 介绍数据库对象n 将概念结构转换为

12、物理结构n 索引的选择n 数据存储分类n 针对不同数据库的特殊物理设计20关系数据库设计-物理结构设计n 介绍数据库对象l 表（Table）：数据库中的表与我们日常生活中使用的表格类似，它也是由行（Row）和列（Column）组成的。列由同类的信息组成，每列又称为一个字段，每列的标题称为字段名。行包括了若干列信息项，一行数据称为一个或一条记录，它表达有一定意义的信息组合。l 索引（Index）：根据指定的数据库表列建立起来的顺序。它提供了快速访问数据的途径。索引会占用物理空间，会增加磁盘IO，以空间换时间。l 约束（Constraint）：数据库提供的自动保持数据库完整性的一种方法。一般有主键

13、约束（Primary Key Constraint），外键约束（Foreign Key Constraint），唯一约束（Unique Constraint），检查约束（Check Constraint）和缺省约束（Default Constraint）。21关系数据库设计-物理结构设计n 将概念结构设计转换为物理结构设计l 实体和实体间的关联（Association）均转换为表。l 松散联系转换为非强制（可以为空）属性。l 相对松散和紧凑联系转换为强制（不可以为空）属性。l 主键和外键转换为主、外键约束（Constraint）。l 以上转换均可以通过设计工具实现。22关系数据库设计-物理结构

14、设计n 索引的选择l 设计阶段只考虑主外键的索引建立即可，其他索引的建立在调优阶段进行。主键一般会自动产生索引（唯一非空索引），外键需要手工建立索引。l 认识索引类型，不同的数据库索引类型不同。l SYBASE有簇聚索引（主键索引为簇聚索引，该索引在一个表中只能有一个，不能与数据分开存储，查询效率高，但占用空间大，插入效率低）和非簇聚索引（一个表中可以有多个，可以与数据分开存储，不会对数据进行排序，查询效率低，但插入效率相对高）。l ORACLE有B-tree索引（与非簇聚索引相同，均采用B-tree索引结构），位图索引（修改代价高，适合于基数较少的查询，例如只有男、女分类的查询）和反向B-t

15、ree索引（反转了索引码中每列的字节，降低索引叶块的争用 ，适用于RAC）。23关系数据库设计-物理结构设计n 数据存储分类l 根据数据类型进行分类，一般采用数据、索引、日志、临时数据分开存储。l 根据数据更新频率进行分类，即将更新频率较低的基础数据，更新频率一般的关系数据，更新频率很高的实时数据以及只有插入操作，没有更新操作并且保留时间较长的历史分开存储。l 对于数据量非常庞大的数据表进行分区或分表处理。24关系数据库设计-物理结构设计n 针对不同数据库的特殊物理设计l SYABASE需要考虑数据表锁类型的选择，其锁类型有全页锁（All Pages），数据页数（Data Pages）和数据行

16、数（Data Rows）。l ORACLE可以选择数据表是否需要压缩。l 还有一些内存的设置，需要在测试和调优过程中逐步完善，不可能一蹴而就。25关系数据库设计-范式概念介绍n 第一范式（1NF）l 有主键（非空，唯一），字段不可再分。n 第二范式（2NF）l 在第一范式的前提下，其属性字段完全依赖于主键。n 第三范式（3NF）l 在第二范式的前提下，其属性字段不依赖于其他非主键属性。n BCNFl 在第三范式的前提下，主键属性之间也不存在依赖关系。n 其他范式介绍：第四范式，第五范式26关系数据库设计-范式概念介绍n 第一范式规范化处理前n 数据特征l User_id重复，不能唯一标示这一行

17、，需要对user_id增加主键约束。l contact可以再分为“电话”和“email”两个字段。n 第一范式规范化处理后user_iduser_namecontact300015001admin电话:25658888,email:300015001ga_admin电话:25658888,email:ga_user_id(PK)user_300015002ga_admin25658888ga_27关系数据库设计-范式概念介绍n 第二范式规范化处理前n 数据特征l Device_name不完全依赖主键（user_id,device_id），该字段部份依赖于主键device_id。由此会造成当修改

18、device_name时，要同时修改这两条数据，否则会造成device_name不一致。n 第二范式规范化处理后user_id(PK)device_id(PK)device_nameprivilege_flag0501015391 050101121009975 盛世宏基1#111111105010160117 050101121009975 盛世宏基1#1111111user_id(PK)device_id(PK,FK)privilege_flag0501015391 050101121009975 111111105010160117 050101121009975 1111111devi

19、ce_id(PK)device_name050101121009975 盛世宏基1#28关系数据库设计-范式概念介绍n 第三范式规范化处理前n 数据特征l principal_tel不依赖于主键(device_id)，而依赖于Device_principal，会造成的问题就是，当要修改负责人的电话时，要同时修改这两条数据，否则会负责人电话不一致的现象。n 第三范式规范化处理后device_id(PK)device_namedevice_principalprincipal_tel050101111006924 盛世宏基1#赵文深13633116525050101111006926 盛世宏基2#

20、赵文evice_id(PK)device_namedevice_principal(FK)050101111006924 盛世宏基1#赵文深050101111006926 盛世宏基2#赵文深device_principal(PK)principal_tel赵文深1363311652529关系数据库设计-范式概念介绍n BCNF规范化处理前n 数据特征l 一个用户负责管理一个大客户，一个大客户中可以有多个用户，大客户中可以有多个设备，一个设备可以属于多个大客户，一个大客户中的设备由专人负责，一个人可以管理多个设备。由此可知，用户，大客户和设备可以唯一决定一个权限，以上表

21、格符合三范式。l 在以上主键组合中，大客户依赖于用户，所以会出现如果在大客户中的用户不对某一设备拥有权限，则在上表中无法表示，因为device_id是主键的一部份，不可以为空。所以还要做以下分解，分解后主键组合中不存在依赖关系，就是BCNFn BCNF规范化处理后user_id(PK)enterprise_id(PK)device_id(PK)privilege_flag000001300670 000001700674050101111001848 1111111000001300670 000001700674050101111001888 1111111user_id(PK)enterp

22、rise_id000001300670 000001700674user_id(PK)device_id(PK)privilege_flag000001300670 050101111001848 1111111000001300670 050101111001888 111111130关系数据库设计-范式概念介绍n 其他范式介绍l 第四范式：在第三范式的前提下，消除多值依赖，即不允许复合主键的存在。l 第五范式：在消除多值依赖的前提下，要保证数据的完整性。例如ABC组成复合主键，第五范式则要拆分成AB，BC，AC三个组合。l 第四范式和第五范式虽然避免了大量的数据冗余，减轻了维护数据的完整性

23、工作，但是操作效率不高，所以在实际运用中很少采用。其完整性可以通过触发器，存储过程来维护。31关系数据库设计-数据库备份策略n 数据库全库备份策略l 备份对数据库系统的影响。l 备份文件大小的增加对磁盘空间的影响。l 全库恢复占用的时间是否满足用户的需求。n 数据库增量备份策略l 对于失效增量备份文件的管理策略。l 备份文件大小对磁盘空间的影响。l 备份文件名称编码规则的制定n 异地备份（standby）策略l 异地备份对生产数据库的影响。l 主备切换的设计（修改监听地址或修改客户端连接程序）。32关系数据库设计-产品数据库介绍n 参考PD设计文档33目录内部公开n关系数据库概念n关系数据库设

24、计（结构设计）n数据库设计概述n需求分析n概念结构设计n物理结构设计n范式概念介绍n数据库备份策略n产品数据库介绍n关系数据库开发（行为设计）34关系数据库开发-数据操作语言（DML）n 查询语句（SELECT）l 查询语言用来对已经存在于数据库中的数据按照特定的组合、条件表达式或次序进行检索。l 非限制连接（CROSS JOIN）：指不带WHERE子句的查询。在数学上，就是表的笛卡尔积。l 外连接（OUTER JOIN）：允许限制一张表中的行，而不限制另一张表中的行。外连接又分为左连接和右连接，主要是为了表示限制哪张表。l 内连接（INNER JOIN）：多表做关联查询，返回同时满足限制条件

25、的行。l 子查询：一条SELECT语句作为另一条SELECT语句的一部分。l 存在性查询：使用EXISTS来决定数据是否在查询列表中存在。EXISTS表示一个子查询至少返回一行时条件成立。35关系数据库开发-数据操作语言（DML）n 插入语句（INSERT）l 是用VALUES选择，直接给各字段赋值。l 通过一条查询语句，把从其他表或视图中选取的数据插入。n 修改语句（UPDATE）n 删除语句（DELETE）36关系数据库开发-视图（View）n 视图（View）看上去同表似乎一模一样，具有一组命名的字段和数据项，但它其实是一个虚拟的表，在数据库中并不单独存储视图的数据，视图的数据来源于数据

26、库中的表。视图是由查询数据库表产生的，它限制了用户能看到和修改的数据。l 由于视图的定义保存在数据库中，执行计划已经给出，所以也会有效的提高查询效率。l 保证数据的安全，视图可以隐藏一些数据。l 使复杂的查询易于理解和使用。n 物化视图（Materialized View）：用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。以空间换时间。37关系数据库开发-存储过程（Procedure）n 存储过程是由一组为了完成特定功能的SQL 语句集加流控制语句组成，经编译后存储在数据库服务器中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在Oracle中，若干个有联系的过程可以组合在一起构成程序包（Package）。n 存储过程进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。n 可以降