ora10-数据库设计.ppt

第十章、数据库概念设计与逻辑设计1本章目标了解设计数据库的步骤掌握如何绘制数据库的E-R图掌握如何绘制数据库模型图使用三大范式实现数据库设计规范化为什么需要设计数据库良好的数据库设计节省数据的存储空间能够保证数据的完整性方便进行数据库应用系统的开发糟糕的数据库设计：数据冗余、存储空间浪费内存空间浪费数据更新和插入的异常修建茅屋需要设计吗？修建茅屋需要设计吗？修建大厦需要设计吗？修建大厦需要设计吗？结论：当数据库比较复杂时我们需要设计数据库结论：当数据库比较复杂时我们需要设计数据库软件项目开发周期中数据库设计 需求分析阶段 分析客户的业务和数据处理需求 概要设计阶段 设计数据库的ER模型图确认需求信息的正确和完整详细设计阶段 将E R图转换为；多张表，进行逻辑设计，并应用数据库设计的三大范式进行审核 代码编写阶段 选择具体数据库进行物理实现，并编写代码实现前端应用：软件测试阶段 。安装都署阶段 。现实世界现实世界建模建模信息世界信息世界规范化规范化构建构建数数数数 据据据据 库库库库数据库模型图数据库模型图在需求分析和概要设计阶段，数据库设计的步骤为：收集信息 标识对象 标识每个对象的属性 标识对象之间的关系在详细设计阶段，数据库设计的步骤为：绘制E-R图 将E-R图转换为表结构 应用三大范式规范化表软件项目开发周期中数据库设计 数据库设计的步骤收集信息与该系统有关人员进行交流、座谈，充分了解用户需求，理解数据库需要完成的任务标识实体（Entity）标识数据库要管理的关键对象或实体，实体一般是名词标识每个实体的属性（Attribute）标识实体之间的关系（Relationship）酒店管理系统的基本功能：酒店管理系统的基本功能：旅客办理入住手续：后台数据库需要存放入住客人的信息和客房信息旅客办理入住手续：后台数据库需要存放入住客人的信息和客房信息客房信息：后台数据库需要存放客房的相关信息，如房间号、床位数、价格等客房信息：后台数据库需要存放客房的相关信息，如房间号、床位数、价格等客房管理：后台数据库需要保存客房类型信息和客房当前状态信息客房管理：后台数据库需要保存客房类型信息和客房当前状态信息酒店管理系统中的实体：酒店管理系统中的实体：客人：入住酒店的旅客。办理入住手续时，需要填写客人的信息客人：入住酒店的旅客。办理入住手续时，需要填写客人的信息客房：酒店为客人提供休息的房间客房：酒店为客人提供休息的房间酒店管理系统中实体之间关系：酒店管理系统中实体之间关系：客房和客人有主从关系：表明客人入住的房间客房和客人有主从关系：表明客人入住的房间客人信息客人信息客人姓名客人姓名身份证号身份证号房间号房间号入住日期入住日期结帐日期结帐日期押金押金总金额总金额客房信息客房信息房间号房间号客房名称客房名称床位数床位数客房状态客房状态入住人数入住人数客房类型客房类型价格价格绘制E-R图3-1E-R图 符 号含含 义义实体，一般是名词属性，一般是名词关系，一般是动词绘制E-R图3-2映射基数一对一一对一X X X X Y Y Y Y X X X X Y Y Y Y 一对多一对多X X X X Y Y Y 多对一多对一X X X X Y Y Y Y 多对多多对多客户客户订单订单产品产品M ：N 1 ：N 绘制E-R图3-3酒店管理系统的数据库1N客人姓名客人姓名身份证号身份证号入住日期入住日期结帐日期结帐日期客房名称客房名称房间号房间号押金押金房间号房间号总金额总金额客人（客人（GuestRecord）入住入住床位数床位数客房类型客房类型客房（客房（Room）客房状态客房状态入住人数入住人数价格价格关系模式一个关系的属性名的集合称为关系模式酒店管理系统数据库的关系模式是：客人（客人姓名、身份证号、房间号、入住日期、结帐日期、押金、总金额）客房（房间号、房间描述、房间类型、房间状态、床位数、入住人数）转化E-R图为数据库模型图将各实体转换为对应的表，将各属性转换为各表对应的列标识每个表的主键列，需要注意的是：没有主键的表添加ID编号列，它没有实际含义，用于做主键或外键如：入住登记表中的“入住流水ID”列在表之间建立主外键，体现实体之间的映射关系 数据规范化 仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构Dr E.F.codd 最初定义了规范化的三个级别，范式是具有最小冗余的表结构这些范式是：第一范式(1st NF First Normal Form)第二范式(2nd NFSecond Normal Form)第三范式(3rd NF Third Normal Form)第一范式(1st NF)第一范式的目标是确保每列的原子性如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）CustIDAddress1中国北京市2美国纽约市3英国利物浦4日本东京市CustIDCountryCity1中国北京3英国利物浦4日本东京2美国纽约第二范式(2nd NF)如果一个关系满足1NF，并且除了主键以外的其他列，都依赖于该主键，则满足第二范式（2NF）第二范式要求每个表只描述一件事情字段字段例子例子房间号1001客房状态空闲客房类型标准间床位数2入住人数1价 格$168.00Room字段字段例子例子客房状态编号002客房状态名称空闲RoomState字段字段例子例子客房类型编号001客房类型名称标准间RoomType第三范式(3nd NF)如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF）字段例子房间号1001客房状态编号002客房类型编号001床位数2入住人数1价 格$168.00Room字段例子房间号1001客房状态编号002客房类型编号001入住人数1Room字段例子客房类型编号001客房类型名称标准间床位数2价 格$168.00RoomType规范化的酒店管理系统E-R图入住流水入住流水IDN1NN11客人姓名客人姓名身份证号身份证号入住日期入住日期结帐日期结帐日期客房名称客房名称房间号房间号押金押金房间号房间号总金额总金额入住登记表（入住登记表（registration）入住入住属于属于属于属于床位数床位数客房类型客房类型类型名称类型名称客房（客房（room）客房状态（客房状态（roomState）状态名称状态名称客房类型（客房类型（roomType）客房状态客房状态入住人数入住人数价格价格客房状态客房状态ID客房类型客房类型ID规范化的酒店管理系统数据库模型图酒店管理系统数据库模型图规范化示例6-1假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）规范化示例6-2工程号工程名称职工号姓名职务小时工资率工时实发工资A1花园大厦1001齐光明工程师6513845.001002李思岐技术员6016960.001004葛宇宏律师60191140.00小计2945.00A2立交桥1001齐光明工程师6515975.001003鞠明亮工人5517935.00小计1910.00A3临江饭店1002李思岐技术员60181080.001004葛宇洪技术员6014840.00小计1920.00公司定期制定一个工资报表工程号工程名称职工号姓名职务小时工资率工时A1花园大厦1001齐光明工程师6513A1花园大厦1002李思岐技术员6016A1花园大厦1004葛宇宏律师6019A2立交桥1001齐光明工程师6513A2立交桥1003鞠明亮工人5517A3临江饭店1002李思岐技术员6018A3临江饭店1004葛宇洪技术员6014公司定期制定的项目工时表规范化示例6-3表中包含大量冗余数据，可能会导致数据异常：更新异常添加异常删除异常 如何解决？规范化示例6-4工程号工程名称职工号姓名职务小时工资率工时工程信息工程信息员工信息员工信息项目工时信息项目工时信息一张表描述了多件事情：规范化示例6-5工程号工程名称职工号姓名职务小时工资率工程号职工号工时工程表工程表员工表员工表项目工时表项目工时表满足第三范式吗？满足第三范式吗？应用第二范式规范化：规范化示例6-6工程号工程名称职工号姓名职务职务小时工资率工程号职工号工时工程表工程表员工表员工表职务表职务表工时表工时表应用第三范式规范化：规范化和性能的关系 为满足某种商业目标，数据库性能比规范化数据库更重要通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间通过在给定的表中插入计算列（如成绩总分），以方便查询在数据规范化同时，要综合考虑数据库的性能员工晋级业务实体2-1训练要点：收集信息标识实体标识每个实体的属性标识实体之间的关系 需求说明：为了激励优秀员工为企业作出更大的贡献，企业定期安排员工晋级假设每个部门设置多个不同的岗位，每个岗位可以安排多个员工每一位员工隶属于企业的一个部门，有一个对应的岗位企业保存每位员工每次晋级记录员工晋级业务实体2-2难点指导：收集信息：在确定客户要做什么之后，收集一切相关的信息，尽量不遗漏任何信息标识实体：原则：实体一般是名词每个实体只描述一件事情不能重复出现含义相同的实体标识每个实体的属性：标识每个实体需要存储的详细信息标识实体之间的关系练习绘制员工晋级业务E-R图需求说明：使用Visio绘制员工晋级业务的E-R图提示：如何使用Visio绘制E-R图使用文件使用文件新建新建框图框图基本框图来绘制基本框图来绘制E-R图；图；在绘图窗口左侧的在绘图窗口左侧的“基本形状基本形状”里选择里选择“矩形矩形”、“椭圆椭圆”、“菱形菱形”拖动到页面适当位置，就可以绘制需要的矩形、椭圆和菱形拖动到页面适当位置，就可以绘制需要的矩形、椭圆和菱形绘制直线，在工具栏：视图绘制直线，在工具栏：视图工具栏工具栏绘图，这时就会弹出绘图工具绘图，这时就会弹出绘图工具栏。选择其中的直线工具栏。选择其中的直线工具需求说明：用Visio E-R图转化为数据库模型图在各个模型图之间体现实体之间的映射关系绘制员工晋级模型图 规范员工晋级数据库设计2-1训练要点：数据库设计的三大范式 需求说明：根据三大范式规范化员工晋级业务数据为了保证应用程序的运行性能，对符合第三范式的数据库结构进行调整难点指导：向各个表中插入数据，查看表中的每个属性列是否存在重复、插入异常、更新异常和删除异常对照三大范式解决表中的异常问题第一范式的目标是确保每列都是不可再分的最小数据单元：查看每列是否都满足第二范式每列与主键相关，不相关的放入别的表中，即要求一个表只描述一件事情第三范式要求表中各列必须和主键直接相关，不能间接相关，查看各表，满足第三范式对于不满足三大范式的表要进行表拆分规范员工晋级数据库设计2-2总结 在需求分析阶段，设计数据库的一般步骤是什么？在概要设计阶段和详细设计阶段，设计数据库的步骤是什么？为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式，分别是什么？综合训练1现有论文和作者两个实体论文实体的属性包括题目、期刊名称、年份、期刊号；作者实体的属性包括姓名、单位、地址；一篇论文可以有多个作者，且每一位作者写过多篇论文，在每一篇论文中有作者的顺序号。请完成以下操作：（1）画出E-R图（2）将E-R图转换成关系模式，并指出每个关系模式的主键（加下划线）和外键（加波浪线）。关系模式：论文（题目，期刊名称，年份，期刊号）作者（姓名，单位，地址）发表（姓名，题目，顺序号）CREATE TABLE 发表 (姓名 CHAR(7)NOT NULL,题目 CHAR(6)NOT NULL,顺序号 INT DEFAULT NULL,PRIMARY KEY(姓名,题目),FOREIGN KEY(姓名)REFERENCES 论文 ON DELETE CASCADE,FOREIGN KEY(题目)REFERENCES 作者 ON DELETE RESTRICT);综合训练2某企业集团有若干工厂，每个工厂生产多种产品，且每一种产品可以在多个工厂生产，每个工厂按照固定的计划数量生产产品，计划数量不低于300；每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址产品的属性有产品编号、产品名、规格职工的属性有职工号、姓名、技术等级。请：（1）该集团进行概念设计，画出E-R图。（2）E-R图转换成关系模式，并指出每个关系模式的主键和外键。关系模式：职工（职工号，姓名，技术等级，工厂编号，聘期，工资）工厂（工厂编号，厂名，地址）产品（产品号，产品名，规格）生产（工厂编号，产品号，数量）CREATE TABLE 生产 (工厂编号 CHAR(7)NOT NULL,产品号 CHAR(6)NOT NULL,数量 DEC(5,2),PRIMARY KEY(工厂编号,产品号),FOREIGN KEY(工厂编号)REFERENCES 工厂 ON DELETE CASCADE,FOREIGN KEY(产品号)REFERENCES 产品 ON DELETE RESTRICT,CHECK(数量=300);综合训练3设某汽车运输公司数据库中有三个实体集。一是“车队”实体集，属性有车队号、车队名等；二是“车辆”实体集，属性有车牌照号、厂家、出厂日期等；三是“司机”实体集，属性有司机编号、姓名、电话等。设车队与司机之间存在“聘用”联系，每个车队可聘用若干司机，但每个司机只能应聘于一个车队，车队聘用司机有聘期；司机与车辆之间存在着“使用”联系，司机使用车辆有使用日期和公里数，每个司机可以使用多辆汽车，每辆车可被多个司机使用。要求：（1）试画出ER图，并在图上注明属性、联系类型；（2）将ER图转换成关系模式，并说明主键和外键。解答3关系模式关系模式：1)1)车队（车队号、车队名）车队（车队号、车队名）主键：车队号主键：车队号2)2)车辆（车牌照号、厂家、出厂日期）车辆（车牌照号、厂家、出厂日期）主键：车牌照号主键：车牌照号3)3)司机（司机编号、姓名、电话，聘期，车队号）司机（司机编号、姓名、电话，聘期，车队号）主键：司机编号，外键：车队号主键：司机编号，外键：车队号4)4)使用（司机编号，车牌照号，使用日期，公里数）使用（司机编号，车牌照号，使用日期，公里数）主键：（司机编号，车牌照号，使用日期）主键：（司机编号，车牌照号，使用日期）;外键外键1 1：司机编号，外键：司机编号，外键2 2：车牌照号：车牌照号综合训练4在校田径运动会中设置了各类比赛每一比赛类别有类别编号、类别名称和主管等属性每一比赛类别包含很多比赛项目每一比赛项目有项目编号、项目名称、比赛时间和级别等属性各个系团队有团编号、团名称、领队等属性每一代表团有多名运动员组成运动员有编号，姓名，年龄，性别等属性每一名运动员可以参加多个比赛项目每一比赛项目也有多名运动员参加运动员参加比赛有成绩属性，成绩限定在07分。1)根据上述语义画出ER图，2)将ER图转换成关系模式，并指出每个关系模式的主键和外键。3)用DDL语句定义反映运动员与比赛项目之间的“参与”关系表。解答4 比赛项目比赛项目 系团队系团队 比赛类别比赛类别 姓名姓名 包含包含 组成组成 n 1 m n 级别级别 团编号团编号 领队领队 团名称团名称 类别编号类别编号 类别名称类别名称 主管主管 性别性别 年龄年龄 成绩成绩 项目编号项目编号 项目名称项目名称 比赛时间比赛时间 参加参加 运动员运动员 运动员编号运动员编号 1 n 关系模式4关系模式：关系模式：1)比赛类别（比赛类别（类别编号类别编号 ，类别名称，主管），类别名称，主管）2)比赛项目（比赛项目（项目编号项目编号 ，项目名称、比赛时间，级别，项目名称、比赛时间，级别，类别编号类别编号 ）3)系团队（系团队（团编号团编号 ，团名称，领队），团名称，领队）4)运动员（运动员（运动员编号运动员编号 ，姓名，年龄，性别，姓名，年龄，性别，团编号团编号 ）5)参加参加（项目编号项目编号 ，运动员编号，运动员编号 ，成绩），成绩）CREATE TABLE 参加 (项目编号 CHAR(7)NOT NULL,运动员编号 CHAR(6)NOT NULL,成绩 INT,PRIMARY KEY(项目编号,运动员编号),FOREIGN KEY(项目编号)REFERENCES 比赛项目(项目编号)ON DELETE RESTRICT,FOREIGN KEY(运动员编号)REFERENCES 运动员(运动员编号)ON DELETE RESTRICT,CHECK(成绩 BETWEEN 0 AND 7);数据库的物理实现数据库的物理实现一般包括：创建表添加各种约束给需要的操作表的账号授权表的约束类型约束的目的：确保表中数据的完整性。常用的约束类型（六种）：主键约束(Primary Key Constraint）要求主键列数据唯一并且不允许为空唯一约束（UniqueConstraint)要求该列唯一允许为空但只能出现一个空值。检查约束（Check Constraint）：某列取值范围限制、格式限制等如有关年龄的约束等。默认约束（DefaultConstraint)-某列的默认值 如默认处理日期为系统日期。外键约束（Foreign Key Constraint)：用干两表间建立关系需要指定引用主表的列非空约束（NOT NULL）要求该列不允许为空值。添加约束添加约束的语法ALTER TABLE table_nameADD CONSTRAINT 约束名 约束类型 具体约束说明；约束名的取名规则推荐采用：约束类型_约束字段主键（Primary key）约束：如PK_stdNo唯一（Unique key）约束：如UK_stdid检查（Check key）约束：如CK_stuage外键（Foreign key）约束：如FK_stuno添加约束举例1 Create primary keyALTER TABLE TEST_MSTER ADD CONSTRAINT PK_TEST_MASTER PRIMARY KEY(ID);Create unique keyALTER TABLE TEST_MASTER ADD CONSTRAINT UK_TEST_MASTER UNIQUE(NAME);Create check constraintsALTER TABLE TEST_MASTER ADD CONSTRAINT CK_TEST_MASTER_NAME CHECK (LENGTH(NAME)=2);Create foreign keyALTER TABLE TEST_DETAIL ADD CONSTRAINT FK_TEST_DETAIL _ID FOREIGN KEY (MASTER_ID)REFERENCES TEST_MASTER(ID);删除约束删除约束的语法Alter table 表名Drop constraint 约束名；例如：Alter table stuinfoDrop constraint DF_stuAddress收集信息与该系统有关人员进行交流、坐谈充分理解数据库需要完成的任务BBS论坛数据库设计示例 BBS论坛的基本功能：用户注册和登录，后台数据库需要存放用户的注册信息和在线状态信息用户发贴后台数据库需要存放贴子相关信息，如贴子内容、标题等论坛版块管理：后台数据库需要存放各个版块信息，如版主、版块名称、贴子数等标识对象(实体Entity)标识数据库要管理的关键对象或实体BBS论坛数据库设计示例实体一般是名词：用户：论坛普通用户、各版块的版主用户发的主帖用户发的跟帖，回帖版块：论坛的各个版块信息BBS论坛数据库设计示例主贴发帖人发贴表情回复数量标题正文发贴时间点击数状态最后回复时间论坛用户昵称密码电子邮件生日性别用户的等级备注信息注册日期状态积分回帖帖子编号回贴人回贴表情标题正文回贴时间点击数版块版块名称版主本版格言点击率发贴数标识每个实体的属性（Attribute）BBS论坛数据库设计示例标识对象之间的关系(Relationship)跟贴和主帖有主从关系：我们需要在跟贴对象中表明它是谁的跟帖版块和用户有关系：可以根据版块对象查出对应的版主用户的情况主贴和版块有主从关系：需要表明发贴是属于哪个版块的跟贴和版块有主从关系：需要表明跟贴是属于哪个版块的BBS论坛数据库设计示例绘制E-R关系图4BBS论坛数据库设计示例BBS论坛数据库设计示例