MySQL数据库设计与应用项目化教程（微课版） 课件 项目5 设计教学管理数据库

项目5设计教学管理数据库数据库设计步骤、概念设计、逻辑设计方法设计教学管理数据库5.1了解数据库设计5.2需求分析5.3概念结构设计5.4逻辑结构设计5.5物理结构设计5.6数据库的实施和维护

什么是数据库设计？数据库设计（DatabaseDesign）是指对于一个给定的应用环境，设计出优化的数据库逻辑模式和物理结构，并在此基础上建立数据库及其应用系统，使之能够有效存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求。信息管理要求是指在数据库中应该存储和管理哪些数据对象；数据操作要求是指对数据对象需要进行哪些操作，如查询、增加、删除、修改、统计等。数据库设计基本步骤数据库设计基本步骤需求分析

-信息的收集、确定数据概念设计-绘制ER模型图

逻辑设计

-转化成关系表

物理设计-建表设计教学管理数据库5.1了解数据库设计5.2需求分析5.3概念结构设计5.4逻辑结构设计5.5物理结构设计5.6数据库的实施和维护

需求分析需求分析简单地说就是分析用户的要求。需求分析是设计数据库的起点，其结果是否准确，是否反映用户的实际要求，将直接影响到后面各阶段的设计。

需求分析的任务是通过详细调查现实世界中要处理的对象（组织、部门、企业等），充分了解原系统（手工系统或者之前的计算机系统）的工作概况，明确用户的各种需求，然后在此基础上确定新系统的功能。需求分析

在需求分析阶段，设计人员和用户需要进行深入的沟通，以避免理解不准确导致后续的工作出现问题。（1）收集数据。可以利用数据流图等工具辅助分析与理解。（2）解决冲突。解决冲突包括解决命名冲突、属性冲突、结构冲突。（3）为数据形成一些标准。设计教学管理数据库5.1了解数据库设计5.2需求分析5.3概念结构设计5.4逻辑结构设计5.5物理结构设计5.6数据库的实施和维护

概念模型中的基本概念(1)实体（Entity）客观存在并可相互区别的事物称为实体。实体可以是具体的人、事、物，也可以是抽象的概念或联系.(2)属性（Attribute）

实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。

(3)码（Key）唯一标识实体的属性集称为码。例如，学号是学生实体的码。概念模型中的基本概念(续)(4)实体型（EntityType）

用实体名及其属性名集合来抽象和刻画同类实体称为实体型。例如，学生（学号，姓名，性别，出生年月，所在院系，入学时间）就是一个实体型。(5)实体集（EntitySet）同一类型实体的集合称为实体集。例如，全体学生就是一个实体集。概念模型中的基本概念(续)(6)联系（Relationship）

现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系。实体内部的联系通常是指组成实体的各属性之间的联系实体之间的联系通常是指不同实体集之间的联系实体之间的联系一对一联系（1:1）

实例一个班级只有一个正班长一个班长只在一个班中任职定义：如果对于实体集A中的每一个实体，实体集B中至多有一个（也可以没有）实体与之联系，反之亦然，则称实体集A与实体集B具有一对一联系，记为1:1。班级班级-班长班长111:1联系1.两个实体之间的联系实体之间的联系(续)一对多联系（1：n）实例一个班级中有若干名学生，每个学生只在一个班级中学习定义：如果对于实体集A中的每一个实体，在实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中至多只有一个实体与之联系，则称实体集A与实体集B有一对多联系，记为1:n班级组成学生1n1:n联系实体之间的联系(续)多对多联系（m:n）实例课程与学生之间的联系：一门课程同时有若干个学生选修一个学生可以同时选修多门课程定义：如果对于实体集A中的每一个实体，实体集B中有n个实体（n≥0）与之联系，反之，对于实体集B中的每一个实体，实体集A中也有m个实体（m≥0）与之联系，则称实体集A与实体B具有多对多联系，记为m:n课程选修学生mnm:n联系实体之间的联系(续)实例：课程、教师与参考书三个实体型，一门课程可以有若干个教师讲授，使用若干本参考书，每一个教师只讲授一门课程，每一本参考书只供一门课程使用。课程讲授教师1m两个以上实体型间1:n联系参考书n2.两个以上实体之间的联系一般的，两个以上实体之间也存在一对一、一对多、多对多联系。实体之间的联系(续)两个以上实体型间的多对多联系实例

供应商、项目、零件三个实体型一个供应商可以供给多个项目多种零件，每个项目可以使用多个供应商供应的零件，每种零件可由不同供应商供给供应商供应项目mp两个以上实体型间m:n联系零件n一对多联系实例

职工实体型内部具有领导与被领导的联系某一职工（干部）“领导”若干名职工一个职工仅被另外一个职工直接领导这是一对多的联系职工领导1n单个实体型内部1:n联系3.单个实体内的联系实体之间的联系(续)同一个实体集内的各实体之间也可以存在一对一、一对多、多对多联系。

E-R图E-R图提供了表示实体、属性和联系的方法用E-R图来描述现实世界的概念模型E-R图也称为E-R模型表示方法：（1）实体用矩形表示，矩形框内写明实体名。

（2）属性用椭圆形表示，并用无向边将其与相应的实体连接起来。

（3）联系用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1∶1、1∶n或m∶n）。E-R

图实体型用矩形表示，矩形框内写明实体名。属性用椭圆形表示，并用无向边将其与相应的实体连接起来学生教师E-R图(续)联系

用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1:1、1:n或m:n）班长属于班级111:1联系课程选修学生mnm:n联系班级拥有学生1n1:n联系联系的属性联系的属性：联系本身也是一种实体型，也可以有属性。如果一个联系具有属性，则这些属性也要用无向边与该联系连接起来E-R图

【例5.1】根据用户需求信息设计E-R图。

某网上商城系统在进行数据库设计时，收集到的用户需求包括：网站可以进行用户的注册，注册需要填写用户名、密码、E-mail、手机号、真实姓名等信息；每一个用户可以有多个派送地址，每个用户在下单时可以选择相应的派送地址；商品信息包括商品ID、商品名称、价格、库存数量、生产厂家等信息；用户可以直接购买多种商品，同时下单；每个订单可以有订单详情，用于记录用户所购买的商品信息、数量等信息；此外，用户随着消费累计金额的增长而属于不同的等级，不同的用户等级决定用户享受的折扣。E-R图概念结构设计过程1．实体和属性的划分原则为了简化E-R图，现实世界中的事物能作为属性对待的尽量作为属性对待。符合以下条件的事物一般作为属性对待。（1）作为属性，不能有需要描述的性质，即属性必须是不可分的数据项，不能包含其他属性。（2）属性不能与其他实体具有联系，即E-R图所表示的联系是实体之间的联系。概念结构设计过程2．E-R图的集成

在开发一个大型信息系统时，最经常采用的策略是自顶向下进行需求分析，再自底向上设计概念结构。也就是首先设计各子系统的分E-R图，然后将它们集成起来，得到全局E-R图。E-R图的集成一般需要分为以下两步。（1）合并。解决各分E-R图之间的冲突，将分E-R图合并起来生成初步E-R图。（2）修改和重构。消除不必要的冗余，生成基本E-R图。设计教学管理数据库5.1了解数据库设计5.2需求分析5.3概念结构设计5.4逻辑结构设计5.5物理结构设计5.6数据库的实施和维护

5.4.1关系模型

逻辑结构模型是指数据库中数据的组织形式和联系方式，简称数据模型。现有的数据库管理系统都基于某种数据模型。按照数据库中数据采取的不同联系方式，数据模型分为层次模型、网状模型和关系模型。

关系模型是目前最重要的一种数据模型。1970年，美国IBM公司的研究员埃德加·弗兰克·科德（EdgarFrankCodd，E.F.Codd），首次提出了数据库系统的关系模型。

从用户观点看，关系模型由一组关系组成，每个关系的数据结构是一张规范化的二维表。5.4.1关系模型1.关系术语

（1）关系。一个关系就是一个二维表，都有一个关系名。（2）记录（元组）。表中的一行即为一个记录，又称元组。（3）字段（属性）。表中的一列即为一个字段，又称属性。（4）域。域就是属性的取值范围，即不同记录对同一个属性的取值予以限定的范围。例如，“性别”属性的域为（男,女），“成绩”属性的域是0~100。（5）码（key）。码也称为关键字，是表中的一个属性或属性组，它的值可以唯一确定一个元组。例如，表5-1中的学号可以唯一确定一个学生，因此成为学生关系的码。（6）关系模式。对关系的描述称为关系模式，一般表示如下。关系名（属性1,属性2,…,属性n）一个关系模型通常是若干个相联系的关系模式的集合。5.4.1关系模型2.规范化的关系

关系模型要求关系必须是规范化的，即要求关系必须满足一定的规范条件，这些规范条件有以下几点。（1）关系的每个属性都是不可分割的数据单元，即属性不能再分，不允许“表中有表”。（2）在同一个关系中不能出现相同的属性名。（3）任意交换列的次序，不会改变关系的实际意义。（4）关系中的行又称为元组，代表一个实体，因此关系中不存在完全相同的两行。（5）任意交换行的次序，不会改变关系的实际意义。5.4.2E-R图转换成关系模型1.转换规则

（1）一个实体转换为一个关系模式，关系的属性就是实体的属性，关系的码就是实体的码。（2）一个1∶1联系可以与任意一端对应的关系模式合并，合并时需要在该关系模式的属性中加入另一个关系模式的码和联系本身的属性。（3）一个1∶n联系通常与n端对应的关系模式合并，即在n端关系中加入1端关系的码和联系本身的属性，其中，1端关系的码加入n端关系后应作为关系的外码。（4）一个m∶n联系将转换为一个关系模式，与该联系相连的各实体的码及联系本身的属性均转换为关系的属性，各实体的码组成关系的码或关系码的一部分。5.4.2E-R图转换成关系模型1.转换规则

（5）3个或3个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码及联系本身的属性均转换为关系的属性，各实体的码组成关系的码或关系码的一部分。（6）同一实体集的实体间的联系，即实体内部的联系，可参照实体间的联系1∶1、1∶n、m∶n3种情况分别处理。（7）具有相同码的关系模式可合并。合并方法是将其中一个关系模式的全部属性加入另一个关系模式，去掉其中的同义属性，调整属性的次序。5.4.2E-R图转换成关系模型【例5.2】继续设计例5.1中的网上购物系统数据库，将图5-8所示的E-R图转化为关系模型。

用户（用户名，密码，E-mail，手机号，真实姓名，消费金额，等级）。商品（商品id，商品名称，价格，库存数量，生产厂家）。地址（地址id，地址名称，用户名）。订单（订单id，用户名，订单金额，下单时间，邮寄地址，是否付款）。订单详情（详情id，订单id，商品id，购买数量）。经过分析，转换后的关系模型如下：5.4.3关系模型的优化

在数据库逻辑设计过程中，为了避免不规范的数据库出现数据冗余，造成插入、删除、更新等操作异常情况，需要满足一定的规范化要求，这就是范式（NormalForm）。

根据要求的程度不同，范式有多种级别，常用的有第一范式（1NF）、第二范式（2NF）和第三范式（3NF），它们由E.F.Codd于1971年相继提出。后来又有人提出了BC范式（Boyce_x0002_CoddNormalForm，BCNF）、第四范式（4NF）和第五范式（5NF）等。一般来说，数据库设计只需满足第三范式即可。5.4.3关系模型的优化1.第一范式（1NF）

第一范式（1NF）是指数据表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值，或不能有重复的属性。第一范式遵从原子性，即属性不可再分。学

号联系方式101

李伟

邮箱：liwei@，手机号：18900000000102

张兰

邮箱：zhanglan@，手机号17300000000学

号姓名邮箱手机号手机号101李伟liwei/p>

102张兰zhanglan@15900000000173000000005.4.3关系模型的优化2.第二范式（2NF）

第二范式是在第一范式的基础上建立起来的，满足第二范式必须先满足第一范式。第二范式要求实体的属性完全依赖于主键，不能仅依赖主键的一部分（对于复合主键而言）。简而言之，第二范式遵从唯一性，非主键字段需完全依赖主键。学号姓名性别专业所属院系院系地址课程号成绩101李伟男计算机应用信息工程系笃信楼C0185101李伟男计算机应用信息工程系笃信楼C0278102张兰女软件技术信息工程系笃信楼C0290103王艳女数控技术机电工程系智造楼C0265学号和课程号组成了复合主键，成绩完全依赖于复合主键，而姓名、性别和专业等学生信息只依赖于学号。5.4.3关系模型的优化2.第二范式（2NF）

学号姓名性别专业所属院系院系地址101李伟男计算机应用信息工程系笃信楼101李伟男计算机应用信息工程系笃信楼102张兰女软件技术信息工程系笃信楼103王艳女数控技术机电工程系智造楼学号课程号成绩101C0185101C0278102C0290103C02655.4.3关系模型的优化3.第三范式（3NF）

第三范式（3NF）是在第二范式的基础上建立起来的，即满足第三范式必须先满足第二范式。第三范式要求一个数据表的每一列数据都和主键直接相关，而不能间接相关。

简而言之，第三范式就是非主键字段不能相互依赖。学号姓名性别专业所属院系院系地址101李伟男计算机应用信息工程系笃信楼101李伟男计算机应用信息工程系笃信楼102张兰女软件技术信息工程系笃信楼103王艳女数控技术机电工程系智造楼同一院系的地址在同一个地方，所以院系地址依赖于所属院系，与主键学号不直接相关，而是间接相关5.4.3关系模型的优化3.第三范式（3NF）

学号姓名性别专业101李伟男计算机应用101李伟男计算机应用102张兰女软件技术103王艳女数控技术专业所属院系计算机应用信息工程系软件技术信息工程系数控技术机电工程系院系名称院系地址信息工程系笃信楼机电工程系智造楼拆分后的学生表、专业表和院系表都满足第三范式。设计教学管理数据库5.1了解数据库设计5.2需求分析5.3概念结构设计5.4逻辑结构设计5.5物理结构设计5.6数据库的实施和维护

5.5物理结构设计

物理结构设计阶段的任务是把逻辑设计阶段得到的数据库逻辑在物理上加以实现，其主要内容是根据DBMS提供的各种手段，设计数据的存储形式和存取路径，如文件结构、数据类型、索引设计等，即设计数据库的内模式或存储模式。一.索引存取方法B-Tree索引和Hash索引是数据库中经典的存取方法。1.使用B-Tree索引规则一般来说，符合下列条件可以使用B-Tree索引。（1）如果一个（或一组）字段经常在查询条件中出现，则考虑在这个（或这组）字段上建立索引（或组合索引）。（2）如果一个字段经常作为最大值和最小值等聚集函数的参数，则考虑在这个字段上建立索引。（3）如果一个（或一组）字段经常在连接操作的连接条件中出现，则考虑在这个（或这组）属性上建立索引。一.索引存取方法

5.5物理结构设计2．使用Hash索引规则选择Hash索引的规则如下。如果一个表的字段主要出现在等值连接条件或等值比较选择条件中，且满足下列两个条件之一，则此表可以选择Hash索引。（1）一个表的大小可预知，而且不变。（2）表的大小动态改变，但DBMS提供了动态Hash存取方法。MySQL数据库目前仅有MEMORY存储引擎和HEAP存储引擎支持这类索引。其中，MEMORY存储引擎支持B-Tree索引和Hash索引，且将Hash索引当成默认索引。二、MySQL存储引擎

5.5物理结构设计数据库的存储引擎决定了表在计算机中的存储方式。存储引擎就是存储数据、为存储的数据建立索引和更新、查询数据等技术的实现方法。MySQL8.0支持的存储引擎有MEMORY、MRG_MYISAM、CSV、FEDERATED、PERFORMANCE_SCHEMA、MyISAM、In