MySQL高级查询与编程笔记 • 【第1章数据库设计原理与实战】

数据需求分析结果是否准确反映了用户的实际需求，将直接影响到后面各阶段的设计，并影响到设计结果是否合理和实用
数据需求分析需要依赖于系统需求，此阶段的任务是明确系统需要做什么，需要实现哪些功能，有哪些用户参与，系统实现某项具体功能时，需要对哪些数据进行加工、处理和存储，以及如何对这些数据进行加工、处理和存储。
新系统必须充分考虑今后可能的扩充和改变，不能仅按当前应用需求来设计数据库

数据需求分析的重点是调查、收集与分析用户在数据管理中的信息要求、处理要求、安全性要求和完整性要求

数据需求分析的主要步骤：获取需求、识别问题；分析需求，建立目标系统的逻辑模型；需求文档化；验证需求

数据需求分析调查方法：跟班作业、开调查会、请专人介绍、询问、设计调查表，请用户填写、查阅记录

数据流程图（Data Flow Diagram，DFD/Data Flow Chart，DFC）是描述系统数据流程的工具，它将数据独立抽象出来，通过图形方式描述信息的来龙去脉和实际流程

数据流程图的 4 种基本图形符号如下：箭头表示数据流、圆或椭圆表示加工、双杠表示数据存储、方框表示数据的源点或终点

数据流程图没有对图中各成分进行说明
数据字典（Data Dictionary）是对数据流程图中出现的全部被命名的图形元素在数据字典中作为一个词条加以定义，使每一个图形元素的名称都有一个确切解释
数据字典是关于数据库中数据的描述，即元数据，而不是数据本身。数据字典是在需求分析阶段建立的
数据字典通常包括数据项、数据结构、数据流、数据存储和处理过程 5 个部分
数据字典通过对数据项和数据结构的定义来描述数据流、数据存储的逻辑内容

数据项是数据的最小组成单位。对数据项的描述通常包括数据项名称、含义说明、别名、类型、长度、取值范围、取值含义、与其他数据项的逻辑关系和数据项之间的联系

示例：

数据项编号：D01-001                数据项名称：凭证编号
别名：凭证流水号                符号名：PZBH
数据类型：数值型                长度：4
取值范围：1 ～ 9999                其余省略。

数据结构反映了数据之间的组合关系。一个数据结构可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成

对数据结构的描述通常包括数据结构名称、数据结构含义说明和组成

示例：

数据结构编号：DS01-003            数据结构名称：会计分录
别名：分录                    简述：记账凭证的基本组成部分
组成：科目代码＋借贷方向＋金额        其余省略。

数据流是数据结构在系统内传输的路径

对数据流的描述通常包括数据流名称、简述、数据流来源、数据流去向、数据流组成（数据结构）、平均流量和高峰期流量

示例：

数据流编号：D13            数据流名称：电子读物查询要求
简述：读者需要查询的图书信息        数据流来源：读者
数据流去向：电子读物处理模块        数据流组成：图书编号＋图书名＋出版社等
其余省略。

数据存储是数据结构停留或保存的地方，也是数据流的来源和去向之一

对数据存储的描述通常包括数据存储名称、说明、编号、输入的数据流、输出的数据流、组成（数据结构）、数据量、存取频度和存取方式

示例：

数据存储编号：F04
数据存储名称：明细账
来源：由登记明细账产生
组成：科目代码＋凭证日期＋凭证类别＋凭证编号＋摘要＋借方金额＋贷方金额＋余额方向＋余额

处理过程的具体处理逻辑一般用判定表或判定树来描述

数据字典中只需要描述处理过程的说明性信息即可，通常包括处理过程名称、说明、输入数据流、输出数据流和处理的简要说明

示例：

加工编号

P2.1

处理过程名称

现金日记账

输入数据流：记账凭证

输出数据流：现金日记账

处理周期：每日一次

处理逻辑：如果记账凭证已经通过审核，则对记账凭证中的所有收付凭证执行以下操作：从记账凭证中取收付凭证，判断会计分录的科目，若为现金科目，则登记现金日记账；否则停止记账。

登账方法如下

（1）将凭证的日期、凭证类别、摘要分别记入日记账簿相应的栏内，根据发生方向，将金额记入账簿的借方或贷方栏内，结出余额记入账簿的余额栏内。

（2）在记账凭证的相应分录上做已经记账的标记。

1.2 数据库概念结构设计

1、模型是对现实世界中的事物、对象和过程等客观系统中感兴趣的内容的模拟和抽象表达

2、数据模型也是一种模型，它是对现实世界数据特征的抽象。数据模型一般应满足以下 3 个要求

能比较真实地模拟现实世界
容易被人们理解
便于在计算机上实现

3、一种数据模型能同时满足以上 3 个方面的要求，在目前是比较困难的，所以在数据库系统中可以针对不同的使用对象和应用目的来采用不同的数据模型

DBMS 数据库，特别是关系型数据库，均是基于某种数据模型来开发建设的。因此我们需要把现实世界中的具体事物抽象，组织为与各种 DBMS 相对应的数据模型，即从现实世界到机器世界

这种转换在实际操作时不能直接执行，还需要一个中间过程，这个中间过程就是信息世界

实体（Entity）：客观存在并可相互区别的事物称为实体

属性（Attribute）：实体所具有的某一特性称为属性。一个实体可以由若干个属性来描述

码（Key）：唯一标识实体的属性集称为码

实体型（Entity Type）：具有相同属性的实体必然具有共同的特征和性质。用实体名及其属性名集合来抽象和描述同类实体，称为实体型

实体集（Entity Set）：同一类型实体的集合称为实体集

联系（Relationship）：实体之间的联系有一对一、一对多和多对多等多种类型

在现实世界中，事物内部以及事物之间不是孤立存在的，而是有联系的，这些联系反映在信息世界中，表现为实体内部的联系和实体之间的联系

一度联系（单向联系）。一度联系称为单向联系，也称递归联系，指一个实体集中实体之间的联系

二度联系（两向联系）。二度联系称为两向联系，即两个不同实体集实体之间的联系

三度联系（三向联系）。三度联系称为三向联系，即 3 个不同实体集实体之间的联系

联系的联通词（Connectivity），是指联系涉及到的实体集之间实体对应的方式

两向联系的连通词有 3 种：一对一、一对多和多对多

一对一联系。如果实体集 A 中的每一个实体在实体集 B 中至多有一个实体与之联系，反之亦然，则称实体集 A 与实体集 B 具有一对一联系，记为 1 ∶ 1

一对多联系。如果实体集 A 中的每一个实体在实体集 B 中有 n（n ≥ 1）个实体与之联系，而实体集 B 中的每一个实体在实体集 A 中至多有一个实体与之联系，则称实体集 A 与实体集 B 具有一对多联系，记为 1∶n

多对多联系。如果实体集 A 中的每一个实体在实体集 B 中有 n（n ≥ 1）个实体与之联系，而实体集 B 中的每一个实体在实体集 A 中有 m（m ≥ 1）个实体与之联系，则称实体集 A 与实体集 B 具有多对多联系，记为 m:n

联系的基数（Cardinal Number）:在“一对一”“一对多”和“多对多”的联系中，可以把两个实体集中有联系的实体的联系数量分成两种类型，“唯一”和“不唯一”

但现实中有时需要更精确的描述，这种有联系的实体数目的最小值（min）和最大值（max）称为这个联系的基数，用（min，max）形式表示

数据库概念结构设计的核心内容是概念模型的表示方法
概念模型的表示方法有很多，其中最常用的是“实体 -关系”方法，简称 E-R 模型或 E-R 图，即实体 - 关系图（Entity Relationship Diagram）
E-R模型提供了表示实体、实体属性和实体间关系的方法 E-R
模型具备以下几个主要特点：真实、充分地反映了现实世界中事物和事物之间的联系；简明易懂；易于修改；便于向数据逻辑模型转换

E-R 模型具备以下几个主要特点：

实体（Entity）：实体由矩形表示，矩形框内写明实体名称

属性（Attribute）：属性由椭圆表示，并用无向边将其与相应的实体相连接

联系（Relationship）：实体之间的相互关联称为联系。在 E-R 模型中，联系由菱形表示，菱形框内写明联系名称。用无向边分别与相关实体相连接，并在直线上标注联系的类型

对于一对一联系，需要在两个实体连线每个方向各写 1；对于一对多联系，要在“一”的一方写 1，“多”的一方写 m 或 n；对于多对多联系，则需要在两个实体连线方向上分别写 m 和 n

通过对当今社会上主流影院售票业务和票务统计的调查和分析，归纳了影院在线售票系统的基本需求

电影院有若干放映厅，每个放映厅均有固定的座位
电影院负责引进影片，并维护影片的基本信息，例如片名、片长、导演介绍、主演介绍、情节介绍等
根据影片的基本情况、市场反响和放映厅情况，制订出电影放映的排片表，即影院会将影片安排在不同放映厅的不同时段进行放映
每个客户可以订购一张或多张电影票，这些电影票可以是不同放映厅、不同场次的电影。客户可以在买票时选择座位

根据上述所列的需求，抽象出影院在线售票系统所有的实体，如下所示（约定带有下划线的属性为实体的码）

客户（客户编号、用户名、密码、联系电话）

电影类型（电影类型编号、电影类型名）

电影（电影编号、电影名、导演名、主演、片长、票价、电影简介）

放映厅（放映厅编号、放映厅名、最大排数、每排最大座位数）

通过分析，我们还可以抽象出影院在线售票系统中实体之间的联系，其中，实体“电影”与“放映厅”之间的联系为“排片”，实体“客户”与联系“排片”之间的联系为“售票”

排片（排片编号、反映日期、放映时间）

售票（售票编号、座位排数、座位列数）

标识实体的原则：

实体通常是一个名词，其名称应简明扼要、恰如其分
每个实体仅描述一件事情或一个事物
每个实体都是唯一的，即不能出现含义相同的实体
联系通常是一个动词或动名词，其名称应反映出实体之间的内在关联

概念结构设计的实质是对系统中的事物进行抽象形成实体，并明晰实体之间的联系。实体抽象的关键是需要从实际的人、物、事和概念中抽取所关心的共同特性，而忽略非本质的细节，至于实体间的联系则可由实体间相互的业务操作或内在关联来确定。

1.3 数据库逻辑结构设计

由于目前使用的数据库基本上都是关系数据库，因此首先需要将实体—关系图转换为关系模型，然后根据具体数据库管理系统的特点和限制转换为指定数据库管理系统支持下的数据模型，最后进行优化

实体—关系图转换为关系模型的一般规则：

将实体、实体的属性和实体之间的联系转换为关系模式。
实体和联系表示为关系，它们的属性则转换成关系的属性。

在数据库逻辑结构设计过程中，可以将实体直接转换为关系，关系名与实体同名，实体的属性就是关系的属性，实体的主键就是关系的主键

4个实体分别转换成以下 4 个关系模式：

病房（病房号、床位号）

科室（科室名、地址）

医生（职工号、姓名、出生年月、职称）

病人（病历号、姓名、性别、年龄）

一个一对一联系可以转换为一个独立的关系模式，也可以与任意一端实体所对应的关系模式合并

如果转换为一个独立的关系模式，则与该联系相连的各实体的主键以及联系本身的属性转换为关系的属性，每个实体的主键均可以作为该关系的主键

如果是与联系的任意一端实体所对应的关系模式合并，则需要在该关系模式的属性中加入另一个实体的主键和联系本身的属性

如果将联系“管理”与“班长”一端所对应的关系模式合并，则转换成以下两个关系模式：

（1）班级（班级名、院系、专业、年级），其中“班级名”为主键。

（2）班长（学号、姓名、特长、班级名、任职日期），其中“学号”为主键，“班级名”为引用“班级”关系的外键

如果将联系转换为一个独立的关系模式，则转换成以下 3 个关系模式

（1）班级（班级名、院系、专业、年级），其中“班级名”为主键。

（2）班长（学号、姓名、特长），其中“学号”为主键。

（3）管理（班级名，学号，任职日期），其中“班级名”与“学号”均可作为主键（此处将“班级名”作为主键），它们同时也都是外键。

一般情况下，1 ∶ 1 联系不转换为一个独立的关系模式，而是与任意一端实体所对应的关系模式合并

一个一对多（1∶n）联系可以转换为一个独立的关系模式，也可以与 n 端实体所对应的关系模式合并

如果转换为一个独立的关系模式，则与该联系相连的各实体的主键以及联系本身的属性转换为关系的属性，其中 n 端实体的主键为该关系的主键

如果与 n 端实体所对应的关系模式合并，则应该在 n 端实体所对应的关系模式中添加一端实体的主键作为外键

一般情况下，1∶n 联系不转换为一个独立的关系模式，而是与 n 端实体所对应的关系模式合并

对于一个多对多（m∶n）联系，则需要将该联系转换为一个独立的关系模式

转换规则：与该联系相连的各实体的主键以及联系本身的属性均转换为关系的属性，该关系的主键为各实体主键的组合

1.4 使用 PowerDesigner 设计数据模型

一般情况下，我们会先将现实世界抽象为概念世界，然后再将概念世界转换为机器世界

即先将现实世界中的客观对象抽象为实体（Entity）和联系（Relationship），这种不依赖于具体的计算机系统或某个 DBMS系统的模型即为概念数据模型（Conceptual Data Model，CDM）

再将概念模型转换为计算机上某个DBMS 所支持的数据模型，这种模型即为物理数据模型（Physical Data Model，PDM

PowerDesigner 是 Sybase 公司的企业建模和设计解决方案，广泛应用于数据库建模

利用 PowerDesigner 可以制作数据流程图、概念数据模型、物理数据模型，还可以为数据仓库制作结构模型，也能对团队设计模型进行控制

接下来介绍 PowerDesigner 工作平台的核心布局和主要组件

我们以影院在线售票系统作为应用场景，在 PowerDesigner 概念数据模型视图中，以“电影类型”与“电影”一对多关系为例，绘制它们的概念模型

点击工具栏 Entity，即实体（“ ”），分别绘制实体“电影类型”和“电影”，并添加相应的属性，此时该模型并未反映出实体间的联系

点击工具栏 Relationship，即关系（“ ”），选中一方实体“电影类型”，绘制一条指向多方实体“电影”的线段，则生成了实体“电影类型”与“电影”之间的联系，并将该联系命名为“属于”

我们以影院在线售票系统作为应用场景，在 PowerDesigner 物理数据模型视图中，以“电影”与“放映厅”多对多关系为例，绘制它们的概念数据模型

分别创建实体“电影”和“放映厅”，并点击工具栏 Association 即联系（“ ”），创建一个联系，并将该联系命名为“排片”。此时该模型还未反映出实体间的联系

在默认情况下，PowerDesigner 绘制出的一对多关系中两端实体的基数为零对多（即 0 和 n），如果需要更改标准的一对多（即 1 和 n），可进行相应配置。具体做法是双击联系线，下拉选择 Role（角色）的 Cardinality（基数）选项为“1，n”即可

使用 PowerDesigner 设计数据库物理模型有两种方式：第一种是由概念数据模型直接生成物理数据模型，另一种是在物理数据模型视图中通过绘制生成

在概念数据模型视图中，点击菜单“工具”（Tools） “生成物理数据模型”（Generate Physical DataModel），即可生成数据库物理数据模型

我们以影院在线售票系统作为应用场景，在 PowerDesigner 物理数据模型视图中，以“电影类型”与“电影”一对多关系为例，绘制它们的物理数据模型

点击工具栏 Table，即表（“ ”），分别绘制电影类型表和电影表，并在表中添加属性。选择 Reference，即参照（“ ”），选中多方即电影表，绘制一条连接到一方即电影类型表的直线。此时将在电影表中生成一个参照电影类型表的外键，即“电影类型编号”

在 PowerDesigner 物理数据模型视图中，以“电影”与“放映厅”多对多关系为例，绘制它们的物理数据模型

分别绘制电影表与放映厅表，并在表中添加属性

绘制电影表与放映厅表之间的关联表，即排片表，并在表中添加属性

由于电影表与排片表之间的关系，以及放映厅表与排片表之间的关系均为一对多，因而可按照一对多关系的绘制方法创建物理数据模型

选择 Reference，选中多方即排片表，绘制一条连接到一方即电影表的直线，此时将在排片表中新增一个引用电影表的外键

选择 Reference，选中多方即排片表，绘制一条连接到一方即放映厅表的直线，此时将在排片表中新增一个引用放映厅表的外键

最终形成了电影表、放映厅表和两表之间的关联表，排片表的物理数据模型如图