《（本科）第11章 数据库设计与优化-MySQLppt课件.pptx》由会员分享，可在线阅读，更多相关《（本科）第11章 数据库设计与优化-MySQLppt课件.pptx（71页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、课程主讲人：第11章 数据库设计与优化-MySQL数据库设计与应用主讲：张成叔微信（QQ）：7153265 微信公众号：张成叔第 11章 数据库设计与优化工作情境和任务n高校成绩管理系统”主要是为了实现学生数据的信息化n该如何分析和设计系统所使用的数据库n使得系统能够在对学生、管理员等用户提供业务功能n可以对这些数量众多、关系复杂的数据进行管理n分析和设计数据库，并规范化数据n完成宾馆管理系统数据库的设计知识和技能目标n了解数据库设计的重要性及步骤n理解三大范式，并能够规范化数据库的数据n掌握如何绘制数据库的E-R图n能够将E-R图转换为关系模型n掌握如何绘制数据库的模型图n能够独立完成一个简
2、单应用系统的数据库设计本章重点和难点n绘制数据库的E-R图n绘制数据库的模型图n独立完成一个简单应用系统的数据库设计知识技能结构图第1章 创建和管理数据库目 录数据库设计步骤2 2概念设计逻辑设计1 13 3数据规范化4 4内容结构图为什么需要设计数据库修建茅屋需要设计吗？修建茅屋需要设计吗？修建大厦需要设计吗？修建大厦需要设计吗？结论：当数据库比较复杂时我们需要设计数据库结论：当数据库比较复杂时我们需要设计数据库n良好的数据库设计u节省数据的存储空间u能够保证数据的完整性u方便进行数据库应用系统的开发n糟糕的数据库设计：u数据冗余、存储空间浪费u内存空间浪费u数据更新和插入的异常为什么需要设
3、计数据库软件项目开发周期中数据库设计 现实世界 建模 信息世界 规范化 构建 数据库模型图 软件项目开发周期中数据库设计 n需求分析阶段：分析客户的业务和数据处理需求n概要设计阶段：设计数据库的E-R模型图u确认需求信息的正确和完整n详细设计阶段：应用三大范式审核数据库结构n代码编写阶段：物理实现数据库，编码实现应用n软件测试阶段：n安装部署：软件项目开发周期中数据库设计 n收集信息u与该系统有关人员进行交流、座谈u充分了解用户需求u理解数据库需要完成的任务n例如：宾馆管理系统u需要了解宾馆管理系统的具体功能、以及在后台数据库中保存的数据u数据库需要存放每间客房的信息，如客房号、客房类型、价格
4、等u客人在宾馆入住时要办理入住手续，需要存放客人的姓名、身份证号等设计数据库的步骤n标识实体u标识数据库要管理的关键对象或实体n实体u可以是有形的事物，如人或产品u也可以是无形的事物，如商业交易、公司部门或发薪周期u一般是名次n例如：宾馆管理系统的实体u客房：包括单人间、标准间、三人间、豪华间和总统套间u客人：入住宾馆客人的个人信息设计数据库的步骤n标识每个实体需要的详细存储信息u实体的属性u这些属性将组成表中的列u需要细分每个实体中包含的子成员信息n例如：宾馆管理系统设计数据库的步骤n标识实体之间的关系u关系数据库能够关联数据库中各个项目的相关信息u不同类型的信息可以单独存储u数据库引擎还可
5、以根据需要将数据组合起来n在设计过程中，要标识实体之间的关系u需要分析数据库表u确定这些表在逻辑上是如何相关的u然后添加关系列建立起表之间的连接设计数据库的步骤一起努力 共同提高知识技能结构图第1章 创建和管理数据库数据库设计步骤1 1概念设计逻辑设计2 23 3目 录数据规范化4 4n实体u现实世界中可以相互区分的事物u如客人、客房、假期n属性u实体的特征u如客人实体有姓名、身份证号等n联系u两个或多个实体之间的关联关系u如客人与客房之间有入住之间的联系实体-关系模型n实体-关系模型一般用E-R图来描述nE-R图绘制E-R图 符号符号含义含义实体，一般是名词实体，一般是名词属性，一般是名词属
6、性，一般是名词关系，一般是动词关系，一般是动词一对一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图n映射绘制E-R图n宾馆管理系统的E-R图【技能训练11-1】绘制员工晋级业务E-R图【技能训练11-1】绘制晋级业务E-R图n需求说明：u使用Visio绘制员工晋级业务的E-R图n提示： u如何使用Visio绘制E-R图【技能训练11-1】绘制员工晋级业务E-R图使用文件使用文件新建新建框图框图基本框图来绘制基本框图来绘制E-R图；图；在绘图窗口左侧的在
7、绘图窗口左侧的“基本形状基本形状”里选择里选择“矩形矩形”、“椭圆椭圆”、“菱形菱形”拖动到页面适当位拖动到页面适当位置，就可以绘制需要的矩形、椭圆和菱形置，就可以绘制需要的矩形、椭圆和菱形绘制直线，在工具栏：视图绘制直线，在工具栏：视图工具栏工具栏绘图，这时就会弹出绘图工具栏。选择其中的直线绘图，这时就会弹出绘图工具栏。选择其中的直线工具工具 一起努力 共同提高知识技能结构图第1章 创建和管理数据库 数据库设计步骤1 1 概念设计 逻辑设计2 23 3目 录 数据规范化4 4n关系模式：一个关系的属性名的集合n宾馆管理系统数据库的关系模式u客人（客人编号、客人姓名、身份证号、入住日期、结帐日
8、期、押金、总金额）u客房（客房号、房间描述、房间类型、房间状态、床位数、入住人数）关系模式n实体的转换规则u一个实体转换成一个关系模式u实体的属性就是关系的属性u实体的主键就是关系的主键n例如：校长实体对应的关系模型转化E-R图为数据库模型图n1:1联系的转换规则u方式1：与任意一端的关系模式合并 可将相关的两个实体分别转换为两个关系 并在任意一个关系的属性中加入另一个关系的主关键字u方式2：转换为一个独立的关系模式 联系名为关系模式名 与该联系相连的两个实体的关键字及联系本身的属性为关系模式的属性 其中每个实体的关键字均是该关系模的候选键E-R图中的联系转换为数据库模型n1:1联系的转换规则
9、n例如：校长与学校间1：1的联系的转换E-R图中的联系转换为数据库模型n1:N联系的转换规则u方式1：将1：N联系与N端关系合并 1端的关键字及联系的属性并入N端u方式2：将1：N联系转换为一个独立的关系模式 联系名为关系模式名 与该联系相连的各实体的关键字及联系本身的属性为关系模式的属性 关系模式的关键字为N端实体的关键字 其中每个实体的关键字均是该关系模的候选键E-R图中的联系转换为数据库模型n1:N联系的转换规则n例如：教研室与教师间1：N的联系的转换E-R图中的联系转换为数据库模型n1:N联系的转换规则u多对多(M：N)联系转换为一个关系模式u关系模式名为联系名u与该联系相连的各实体的
10、关键字及联系本身的属性为关系模式的属性u关系模式的关键字为联系中各实体关键字的并集E-R图中的联系转换为数据库模型n1:N联系的转换规则n例如：学生与课程间M：N的联系的转换E-R图中的联系转换为数据库模型n工具软件uMicrosoft Vision绘图的目的u可以通过图形化的方式显示数据库的存储信息和表间关系n绘制步骤u新建数据库模型图u添加实体u添加数据列及相应的属性u添加实体之间的映射关系绘制数据库模型图n步骤1：新建数据库模型图u选择“文件”“新建”“数据库”“数据库模型图”u出现一空白页面u绘图页面左侧是绘图模具，其中包含很多实体关系图绘制数据库模型图n步骤2：添加实体u在绘图窗口左
11、侧的实体关系中选择实体u将其拖动到页面的适当位置u在“数据库属性”中定义数据表的物理名称及概念名称绘制数据库模型图n步骤3：添加数据列及相应的属性u在“数据库属性”中选择类别为“列”u添加列、数据类型和注释等u物理名称：表示列名，一般输入英文，如GuestIDuPK：表示主键u必需的：表示是否可以为绘制数据库模型图n步骤3：添加数据列及相应的属性绘制数据库模型图n步骤4：添加实体之间的映射关系u同添加GuestRecord(客人)实体一样添加“客房”实体Roomu为GuestRecord添加外键约束列RoomID，对应Room表中的RoomID列n单击左侧实体关系中的“连接线”工具u将“连接线
12、”工具放在Room表的中心上，使表的四周出现方框u并拖到GuestRecord表的中心，当GuestRecord表四周出现方框时，松开u两个连接点均变为红色，同时将Room表中主键RoomID作为外键添加到子表GuestRecord中u默认添加子表GuestRecord中的列RoomID与Room表中的列RoomID外键约束绘制数据库模型图n步骤4：添加实体之间的映射关系绘制数据库模型图一起努力 共同提高【技能训练11-1】绘制图书管理系统数据库模型图【技能训练11-2】绘制数据库的模型图【技能训练11-2】绘制数据库的模型图n熟练掌握使用SQL语句实现用户创建于管理的方法n熟练掌握使用SQL
13、语句实现权限控制的方法技能目标技能目标需求分析需求分析n管理SchoolDB数据库的用户和权限一起努力 共同提高知识技能结构图第1章 创建和管理数据库 数据库设计步骤1 1 概念设计 逻辑设计2 23 3目 录 数据规范化4 4n不合规范的表设计u信息重复u更新异常u插入异常u删除异常为什么需要数据规范化 客人编号姓名地址客房号客房描述客房类型客房状态床位数价格入住人数C1001张三Addr11001A栋1层单人间入住1128.001C1002李四Addr22002B栋2层标准间入住2168.000C1003王五Addr32002B栋2层标准间入住2168.002C1004赵六Addr4200
14、3B栋2层标准间入住2158.001C8006A1Addrm8006C栋3层总统套房入住31080.001C8008A2Addrn8008C栋3层总统套房空闲31080.000解决方法：使用三大范式规范数据库表的设计解决方法：使用三大范式规范数据库表的设计CustIDAddress1 1中国北京市中国北京市2 2美国纽约市美国纽约市3 3英国利物浦英国利物浦4 4日本东京市日本东京市CustIDCountryCity1 1中国中国北京北京3 3英国英国利物浦利物浦4 4日本日本东京东京2 2美国美国纽约纽约52/43n第一范式的目标是确保每列的原子性u 如果每列都是不可再分的最小数据单元（也称
15、为最小的原子单元），则满足第一范式（1NF）第一范式 (1st NF)讲解讲解Guest Registration字段例子客人编号客人编号C1002C1002姓名姓名李四李四地址地址Addr1Addr1客房号客房号20022002客房状态客房状态入住入住客房类型客房类型标准间标准间床位数床位数2 2入住人数入住人数2 2价价 格格$168.00$168.00GuestRoom字段例子客人编号客人编号C1002C1002姓名姓名李四李四地址地址Addr1Addr1客房号客房号20022002字段例子客房号客房号20022002客房状态客房状态入住入住客房类型客房类型标准间标准间床位数床位数2 2
16、入住人数入住人数2 2第二范式 (2nd NF)n第二范式要求每个表只描述一件事情RoomRoomType字段字段例子例子客房号客房号20022002客房状态客房状态入住入住客房类型客房类型标准间标准间床位数床位数2 2入住人数入住人数2 2价价 格格$168.00$168.00字段字段例子例子客房号客房号20022002客房状态编号客房状态编号001001客房类型编号客房类型编号002002入住人数入住人数2 2字段字段例子例子客房类型编号客房类型编号002002客房类型名称客房类型名称标准间标准间床位数床位数2 2价价 格格$168.00$168.00RoomRoomState字段例子客房
17、状态编号客房状态编号001001客房状态名称客房状态名称入住入住第三范式 (3nd NF)n如果一个关系满足2NF，并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式（3NF） 一起努力 共同提高规范化的宾馆管理系统E-R图n满足第二范式的E-R图规范化的宾馆管理系统E-R图n满足第三范式的E-R图58/43规范化的宾馆管理数据库模型n宾馆管理系统数据库模型图n假设某建筑公司要设计一个数据库。公司的业务规则概括说明如下：u公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等u公司有多名职工，每一名职工有：职工号、姓名、性别、职务（工程师、技术员）等u公司按照工时和小时工资
18、率支付工资，小时工资率由职工的职务决定（例如，技术员的小时工资率与工程师不同）规范化 演示2讲解讲解工程工程号号工程名工程名称称职工号职工号姓名姓名职务职务小时小时工资率工资率工时工时实发工资实发工资A1A1花园大花园大厦厦10011001齐光明齐光明工程师工程师65651313845.00845.0010021002李思岐李思岐技术员技术员60601616960.00960.0010041004葛宇宏葛宇宏律师律师606019191140.001140.00小计小计2945.002945.00A2A2立交桥立交桥10011001齐光明齐光明工程师工程师65651515975.00975.00
19、10031003鞠明亮鞠明亮工人工人55551717935.00935.00小计小计1910.001910.00A3A3临江饭临江饭店店10021002李思岐李思岐技术员技术员606018181080.001080.0010041004葛宇洪葛宇洪技术员技术员60601414840.00840.00小计小计1920.001920.00工程号工程号工程名称工程名称职工号职工号姓名姓名职务职务小时工资率小时工资率工时工时A1A1花园大厦花园大厦10011001齐光明齐光明工程师工程师65651313A1A1花园大厦花园大厦10021002李思岐李思岐技术员技术员60601616A1A1花园大厦花园
20、大厦10041004葛宇宏葛宇宏律师律师60601919A2A2立交桥立交桥10011001齐光明齐光明工程师工程师65651313A2A2立交桥立交桥10031003鞠明亮鞠明亮工人工人55551717A3A3临江饭店临江饭店10021002李思岐李思岐技术员技术员60601818A3A3临江饭店临江饭店10041004葛宇洪葛宇洪技术员技术员6060141460/43n公司定期制定一个工资报表规范化 演示2讲解讲解n表中包含大量冗余数据，可能会导致数据异常：u更新异常u添加异常u删除异常 n如何解决？ 规范化 演示2问题问题工程信息工程信息员工信息员工信息项目工时信息项目工时信息一张表描述
21、了多件事情：工程号工程号工程名称工程名称职工号职工号姓名姓名职务职务小时工资率小时工资率工时工时62/43规范化 演示2讲解讲解工程表工程表员工表员工表项目工时表项目工时表满足第三范式吗？满足第三范式吗？ 应用第二范式规范化：工程号工程号工程名称工程名称职工号职工号姓名姓名职务职务小时工资率小时工资率工程号工程号职工号职工号工时工时63/43规范化 演示2讲解讲解工程表工程表员工表员工表职务表职务表工时表工时表应用第三范式规范化：工程号工程号工程名称工程名称职工号职工号姓名姓名职务职务工程号工程号职工号职工号工时工时职务职务小时工资率小时工资率64/43规范化 演示2讲解讲解n为满足某种商业目
22、标，数据库性能比规范化数据库更重要u通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间u通过在给定的表中插入计算列（如成绩总分），以方便查询n在数据规范化同时，要综合考虑数据库的性能规范化和性能的关系 【技能训练11-3】规范化图书管理系统数据库设计【技能训练11-3】以3NF为目标规范化数据库【技能训练11-3】以3NF为目标规范化数据库n理解数据库设计的三大范式的价值和实现途径n完成图书管理系统数据库的规划化技能目标技能目标需求分析需求分析n以3NF为目标规范化图书管理系统数据库设计【技能训练11-3】以3NF为目标规范化数据库n据三大范式规范化图书管理系统数据n保证程序的运行性能，对符合第三范式的数据库结构进行调整具体需求具体需求重点分析重点分析n向各表中插入数据u查看表中的每个属性列是否存在重复u是否存在插入异常、更新异常和删除异常n对照三大范式的定义，解决表中的异常问题【技能训练11-3】以3NF为目标规范化数据库n第一范式的目标是确保每列都是不可再分的最小数据单元n第二范式每要求一个表只描述一件事情n第三范式要求表中各列必须和主键直接相关，不能间接相关重点分析重点分析知识技能结构图一起努力 共同提高