数据库实验——简单数据库应用系统设计与实现

某学生宿舍管理系统，涉及的部分信息如下：
学生：学号，姓名，性别，专业，班级。
寝室：寝室号，房间电话。
管理员：员工号，姓名，联系电话。
其中：每个寝室可同时住宿多名学生，每名学生只分配一个寝室；每个寝室指定其中一名学生担当寝室长；每个管理员同时管理多个寝室，但每个寝室只有一名管理员。
每组同学从以上选题中任选一个进行相应的设计与实现。要求如下：
（1）从简单的选题语义出发，按照数据库设计步骤，分别完成如下内容：概念设计（ER模型），给出满足需求的最终全局ER图，要求模型尽量精简，消除不必要的冗余，并给出理由或说明。
（2）逻辑设计（关系模型），把ER模型转换成适当的关系模式，并进行适当地规范化（可选），设计相关完整性约束。
（3）物理设计，在某一主流DBMS中创建数据库，根据业务需求规划适当索引（包括聚簇索引和非聚簇索引），并设计必要的用户视图（即外模式），完成相关查询或更新要求。
四、实验要求
1、完成上述任务；
2、完成实验报告（包括上述任务中的E-R图、转换后的关系模型及优化过程、创建数据库的SQL语句）

---

E-R图

---

关系模式

学生（学号，姓名，性别，专业，班级，寝室号）

寝室（寝室号，房间电话，员工号）   管理员（员工号，姓名，联系电话）

---

物理实现

使用mysql8.0

第一步

创建数据库，并选择

CREATE DATABASE shiyan;
use shiyan;

第二步

创建学生表，宿舍表，管理员表

//学生表
CREATE TABLE student(
 sno int(5) PRIMARY KEY ,
 name varchar(6) NOT NULL,
 sex ENUM('男','女') DEFAULT '女',
 dept varchar(10),
 class varchar(10),
 dno int(5));
 
//宿舍表
CREATE TABLE dormitory(
 dno int(5) PRIMARY KEY ,
 phone varchar(11) NOT NULL,
 mno int(5)
);

//管理员表
CREATE TABLE manager(
 mno int(5) PRIMARY KEY ,
 name varchar(10) NOT NULL,
 mphone varchar(11) NOT NULL
);

第三步

添加约束和索引

给student表的dno添加外键约束

给dormitory表的mno添加外键约束

若这是一个实际应用的系统，那么学生查询无异于频繁的，所以为学生的姓名添加普通索引

//添加普通索引
alter table add index s_name('name');
//添加dno的外键约束
ALTER TABLE student ADD CONSTRAINT FK_ST_DO foreign key(dno) REFERENCES dormitory(dno);
//添加mno的外键约束
ALTER TABLE dormitory ADD CONSTRAINT FK_DO_MA foreign key(mno) REFERENCES manager(mno)

第四步

创建视图

create view v_student as select sno,name,sex,dept,class,dno from student;

第五步

插入数据

//manager表
INSERT INTO manager VALUES(12345,18989341833)
//dormitory表
INSERT INTO dormitory VALUES(12345,18989341832,12346)
//student表
INSERT INTO `student` (`sno`, `name`, `sex`, `dept`, `class`, `dno`) VALUES ('12345', '李铭炜', '男', '计算机科学与技术', '计科一班', '12345');

以下是外键生效的效果

 

 

 

 

 

 

 

 

 

 

 

 

4.1软件体系结构与设计过程 
4.1.1软件体系结构 
软件体系结构又称软件架构，软件体系结构={构件，连接件，约束}。其中，构件（Component）是组成系统的具有一定独立功能的不同粒度的程序模块、独立程序或软件子系统，是组成软件的系统元素；连接件（Connector）将不同的构件连接起来，表示了构件间的相互作用，如信号量的传递、功能和方法的调用、数据传送和转换、构件间的同步等。构件可以是过程调用、管道、远程过程调用等；约束一般为对象连接时的规则，或指明了构件连接的条件。 
4.1.2软件设计过程 

4.2DBAS总体设计 
DBAS 总体设计的任务是依据系统规划与分析结果，特别是其中的技术可行性分析内容，以及系统需求规范，确定系统总体框架，作为后续设计活动的基础。主要内容包括： 
（1）DBAS 体系结构设计；
（2）DBAS 软件总体设计；
（3）软硬件选型与配置设计；
（4）业务规则初步设计。
4.2.1DBAS体系结构设计
以下是两种较为常见的 DBAS 体系结构。
1.客户/服务器体系结构
客户/服务器（C/S）结构是网络环境下一种主流的数据库应用系统体系结构，已得到广泛应用。两层C/S 结构的数据库应用系统，其特点为： 

两层 C/S 结构数据库应用系统如下图所示。 

2.浏览器/服务器结构
三层浏览器/服务器（B/S）结构的数据库应用系统是一种互联网环境下的新型数据库应用系统结构，如下图所示。 

三层浏览器/服务器（B/S）结构将数据处理功能分解并分布在表示层、功能层和数据层三个层次上，分别由 Web 浏览器、Web 应用服务器和数据库服务器来实现，其特点为： 

两层 C/S 结构和三层浏览器/服务器（B/S）结构比较： 

4.3BAS功能概要设计 
从功能角度 DBAS 系统通常可以划分为 4 个层次实现。 
●表示层：负责所有与用户交互的功能，用户对数据库应用系统的最直观感受均在这层实现。
●业务逻辑层：负责根据业务逻辑需要将表示层获取的数据进行组织后，传递给数据访问层，或将数据访问层获取的数据进行相应的加工处理后，传送给表示层用于展示。 
●数据访问层：负责与 DBMS 系统进行交互，提取或存入应用系统所需的数据。
●数据持久层：负责保存和管理应用系统数据。
4.3.1表示层概要设计
表示层概要设计的主要任务是进行人机界面（也称为用户界面，人机交互界面）设计。不同 DBAS 对用户界面的要求与具体应用领域密切相关，因此并无统一的界面设计方法，但应遵守以下设计原则： 
（1）用户应当感觉系统的运行始终在自己的控制之下，即便当系统程序取得对系统的控制权时，人机界面也应向用户提供视觉和听觉上的反馈，保持用户与人机界面间的双向交流。 
（2）当系统发生错误或程序运行时间较长时，用户界面应该为用户提供有意义的反馈信息，并有上下文感知的帮助功能。 
（3）—个好的用户界面应该容忍用户在使用过程中发生的各种操作错误，并且能够方便地从错误中恢复过来，保证系统运行不受或尽可能小地受到用户错误操作的影响。 
（4）用户界面应该遵循一定标准和常规。
（5）用户界面应采取灵活多样的数据输入方式，尽量减少用户的输入负担；提供字符、图形、图像、声音等多种形式的数据输出方式。 
（6）如果使用 Web 界面，设计要求具有简洁性，避免使用过于复杂的图形和动画等，以免用户在使用系统时分心；界面布局还应当清晰地表示各类信息，并具有与之匹配的导航性； 
4.3.2业务逻辑层概要设计 
业务逻辑概要设计的主要任务是梳理 DBAS 的各项业务活动，将其表示为各种系统构件（如类、模块、组件等）。业务逻辑层设计需要考虑高内聚与松耦合原则。这一原则在设计过程中体现在以下方面： 
（1）构件本身应由相关性很强的代码组成，一个构件或一个模块只负责完成一项任务，也就是常说的单一责任原则。 
（2）组成系统业务逻辑层的各个构件均应具有独立的功能，并且最大限度地减少与其他构書功能重叠。 
（3）构件之间的接口应尽量简单明确。 
（4）如果某两个构件间的关系比较复義的话，应考虑进一步进行模块划分。 
（5）如果构件过于复杂，可以考虑将其细分。 
4.3.3数据访问层概要设计 
数据访问层概要设计过程包括： 
（1）从数据流图中识别出该事务对应的子数据流图。 
（2）确定子数据流图中的信息流类型，划定流界。 
（3）将子数据流图映射为事务的结构和处理流程，即事务处理逻辑（或事务业务规则）。 
（4）修正和细化事务设计，识别事务所访问的数据库对象和数据库用户。 
（5）确认事务概要设计与数据库设计。 
事务概要设计中的事务逻辑定义了具有独立逻辑功能的一系列操作以及这些操作的执行流程，这些操作和流程应当是与具体 DBMS平台和实现方法无关的。为此，引入与具体 DBMS平台无关的两个元操作 read、write。其中，read（X）操作表示将数据库磁盘文件中的数据项 X 读入内存缓冲区，write（X）操作表示将内存缓冲区中数据项 X 的当前值写入数据库磁盘文件。事务对数据的操作，如查询、插入、删除、修改等，可以用这两个元操作来抽象表示。 
4.4DBAS功能详细设计 
4.4.1表示层详细设计 
作为数据库应用系统功能设计的一部分，人机界面的设计与用户感知密切相关，由于其特殊性，人机界面设计最好釆用原型迭代法。原型迭代法由如下几个步骤组成： 
1.初步设计 
从用户人机交互需求出发，设计人机交互命令系统，并对其进行优化。 
2.用户界面细节设计 
在命令树基础上，设计命令系统的具体实现方式，命令系统实现可以采用命令行、菜单、按钮、Web界面等多种方式，并注意考虑界面的组织形式、风格、色彩、操作方式等实现细节。 
3.原型设计与改进 
在上述设计结果基础上，首先构造一个人机界面原型系统。利用原型系统，可以为用户演示人机交互效果，让用户直观感受系统的使用方法，并评判用户界面功能是否齐全、是否方便易用；然后，根据用户评审反馈意见修改原型系统。通过这种反复迭代过程，不断完善用户界面，直至符合用户预期要求。 
4.4.2业务逻辑层详细设计 
业务逻辑层详细设计的任务是根据概要设计中定义的各程序模块功能和输入输出数据需求，结合具体的程序设计环境和机制，设计各模块的内部处理流程和算法、具体数据结构、对外详细接口等，是对概要设计的细化。在业务逻辑详细设计阶段，设计人员也可以通过 UML 的类图、活动图、协作图来描述 DBAS 对用户业务的详细实现逻辑。 
4.5应用系统安全架构设计 
4.5.1数据安全设计 
数据是所有信息系统的基础，如何保障数据安全也是信息系统设计和建设过程中需要考虑的首要问题。数据安全涉及的范围很广，从数据存储安全角度出发（通常数据安全设计者会从 DBAS 所必需的数据库系统的安全和保护角度来设计），主要有以下五个方面： 
●安全性保护：即防止非法用户对数据库的非法使用，以避免数据的泄露、篡改或破坏。 
●完整性保护：即保证数据源的正确性和一致性。 
●并发控制：即保证多个用户能共享数据库，并维护数据的一致性。 
●数据库的备份与恢复：即在系统失效后的数据库恢复，配合定时备份数据库，使数据库不丢夫数据。 
●数据加密传输：即将一些高密级的敏感数据通过一定的加密算法加密后传输。 
1.数据库的安全性保护 
在 DBAS 系统设计时，通常采用前台和后台相结合的安全性保护措施来共同维护 DBAS 的安全性。主要的保护方式有： 
（1）用户身份鉴别。 
（2）权限控制。 
（3）视图机制。 
2.数据库的完整性保护 
数据库的完整性是指数据库中数据的正确性、一致性和相容性。 
完整性机制将防止合法用户使用数据库时向数据库中加入不合语义的数据。保证数据库中数据的完整性的方法之一是设置完整性检查，即对数据库中的数据设置一些约束条件。完整性约束条件的作用对象分为列、元组和关系三种级别，其中对列的约束主要指对其值类型、范围、精度、排序等的约束条件。对元组的约束主要指对记录中各个属性之间的联系的约束条件。对关系的约束是指对若干记录间、关系集合上以及关系之间联系的约束条件。 
3.数据库的并发控制 
在数据库系统运行过程中，如果数据库管理系统可以同时接纳多个事务，事务可以在时间上重叠执行，则称这种执行方式为并发访问。实现数据库并发控制的方法很多。常用的是封锁技术。所谓封锁即是在同一时间内禁止某用户对数据对象做某些操作以避免产生数据不一致的问题。基本的封锁一般有排它锁（x锁）和共享锁（s 锁）两种类型。通常为了避免死锁，可以考虑以下原则： 
（1）按同一顺序访问资源。 
（2）避免事务中的用户交互。 
（3）采用小事务模式，尽量缩短事务的长度，减少占有锁的时间。 
（4）尽量使用记录级别的锁（行锁），少用表级别的锁。 
（5）使用绑定连接，使同一应用程序所打开的两个或多个连接可以相互合作。 
4.数据库的备份与恢复 
数据库恢复的基本原理就是数据库中任何一部分数据可以利用存储在系统其他存储器上的冗余数据
来重建。数据库恢复首先要建立冗余数据（即进行数据备份），然后利用这些冗余数据实施恢复。针对数据的不同可靠性级别要求，数据备份与恢复策略可有不同的解决方案。 
（1）双机热备。 
（2）数据转储。 
（3）数据加密存储。 
5.数据加密传输 
常用的数据加密传输手段有： 
●数字安全证书。 
●对称密钥加密。 
●数字签名。 
●数字信封。 
4.6DBAS实施 
在完成对数据库的物理设计之后，就开始进入数据库系统的实施阶段，在这个阶段，设计人员需要用目标数据库的数据定义语言和其他程序设计语言描述逻辑设计和物理设计的结果，并经调试以后在数据库建立目标模式，并且组织数据入库，系统开始试运行。然后，进入系统的运兮和维护阶段。DBAS 的实施阶段主要包括以下工作： 
（1）创建数据库。 
（2）装载数据。 
（3）编写与调试应用程序。 
（4）数据库试运行。 
4.6.1创建数据库 
在 DBAS 实施过程中，为了保障系统的性能和稳定性，创建数据库时需要考虑以下因素： 
（1）初始空间大小。 
（2）数据库增量大小。 
（3）访问性能。 
4.6.2数据装载 
存入数据库，具体步骤如下： 
（1）筛选数据。 
（2）转换数据格式。 
（3）输人数据。 
（4）校验数据。 

在数据库应用系统开发阶段中，在哪个阶段考虑数据库创建？

• 需求分析
• 系统设计
• 系统实现
• 系统测试

在数据库设计中，不包括下面哪个模型？

• 概念数据模型
• 逻辑数据模型
• 物理数据模型
• 数据流图模型

下面哪个不是E-R模型的基本元素？

• 实体
• 标识符
• 联系
• 关系表

ER模型及基本概念
联系（Relationship）

概念：反映为实体内部的联系和实体之间的某种关系。

    实体内部的联系通常是指组成实体的各属性之间的联系
    实体之间的联系通常是指不同实体集之间的联系

    示例：选课是学生与课程之间的联系。

联系的属性：联系也可有描述属性，记录联系的信息而非实体的信息。

    示例：选课的成绩和修课学期；零售的商品数量。

联系的识别：联系由参与的实体唯一确定。

    示例：选课(学号、课程号)

在E-R模型中，一个实体的其他关联实体数量称为什么？

• 最小基数
• 最大基数
• 联系度数
• 实例数

“产品“实体与”厂商“实体在E-R模型中，它们是哪种联系？

• 1:1
• 1:n
• m:n
• 继承l联系

在下面哪种模型中，可设计存储过程要素？

• 概念数据模型
• 逻辑数据模型
• 物理数据模型
• 以上都不是

为了消除关系表属性之间的传递依赖，至少需要规范该关系到下面哪种范式？

• 第1范式
• 第2范式
• 第3范式
• 第4范式

第一范式（1NF）：关系表R不存在复合属性及多值属性

第二范式（2NF）：R中不存在非主属性对键的部分函数依赖

（A,B）->C        B->C        C部分依赖

第三范式（3NF）：R中不存在非主属性对键的传递函数依赖

X->Y，Y->Z，Y-x->X，X传递决定Z

改进的3NF（BCNF）：R中不存在主属性对键的传递函数依赖或部分依赖。

第四范式（4NF）：R中不存在非平凡多值依赖

X→→Y，则X的一个值唯一决定一组Y值

X->Y，Y是X子集，平凡依赖

对于一个非规范的关系表，会出现下面哪项访问异常问题？

• 删除数据异常
• 插入数据异常
• 修改数据复杂
• 以上都是

在逻辑数据模型转换为关系模型时，下面哪项不一定正确？

• 实体转换为关系表
• 属性转换为列
• 标识符转换为主键
• 联系转换为参照完整性约束

在下面哪种模型中，可设计数据库索引要素？

• 概念数据模型
• 逻辑数据模型
• 物理数据模
• 以上都不是

在E-R模型中，一个实体要么是强实体，要么是弱实体。x

弱实体(Weak Entities):没有键的实体

前面所讲的实体总存在键。但实际情况中，并不总是如此。

概念：不存在键的实体，称为弱实体。

不同弱实体的属性值可能完全相同，因此，难以区别。

为此，弱实体型需要与一般的实体相关联。
识别实体型与识别联系：弱实体通过关系结合实体识别

假如联系R关联弱实体A和一般实体B，

A的弱实体可以通过与实体B相结合来加以区别，

则B称为弱实体A的识别实体，R称为弱实体A的识别联系。

在E-R模型图中，在定义一个实体时，必须指定它的标识符。x

在系统逻辑数据模型中，可以设计关系表。x

在系统物理数据模型中，可以设计数据库存储方案。v

在进行1:N实体联系转换到关系模型时，N侧表的主键在1侧表中作为外键。x

一个关系若其属性消除了多值依赖，则一定满足第四范式。x

在进行关系数据库设计时，关系表的规范化程度越高，数据库性能就越好。x

在关系数据库设计时，逆规范化处理就是不需要进行关系表的规范化设计。x

在将数据库设计模型转换为数据库实现时，需要先将其转换为SQL程序。v

数据库建模设计的目的是为数据库实现提供方案v

第4章 数据库设计与实现--本章重点问题讨论

答案非标准答案，仅供参考

1．在数据库应用系统设计中，系统数据架构与数据库模型有何区别？

系统数据架构：是一种数据结构，体现整个数据体系；

数据库模型：是可以转化为实际的可操作的数据库的前序

2．概念数据模型、逻辑数据模型、物理数据模型之间是什么关系?

概念数据模型：面向用户的系统数据模型，用来描述显示时间的系统概念化数据结构。

逻辑数据模型：从系统设计角度描述系统的数据对象组成及其关联结构

物理数据模型：针对DBMS所设计的数据模型。

3．针对特定数据库应用系统，如何确定数据库设计策略？

根据用户实际业务需要进行：自底向上设计，自定向下设计，自内向外设计，混合策略设计

4．在数据库应用系统开发中，数据库设计建模如何实施？

使用powerdesigner进行设计

5．在E-R模型图中，如何理解标识符依赖弱实体和非标识符依赖弱实体？

标识符依赖弱实体：只依靠自己的主键不能够唯一体现关系，需要通过加入强实体的主键来进行标识。

非标识符依赖弱实体：依靠自己的主键可以进行唯一标识

6．在E-R模型图中，如何表示系统的数据对象组成及其对象联系？

实体表示表中数据对象，关系通过不同符号来体现

7．一个非规范关系表在数据操作中会出现哪些问题？

数据冗余度高，数据一致性比较低，对数据库中某个数据进行操作时，不能够很好的维持数据的一致性。删除、插入、更新操作会出现问题。

8．出现非规范关系表的主要原因是什么？

数据比较冗杂

9．如何理解不同程度的规范化设计范式？

INF1,INF2,INF3,BCNF,INF4,逐渐规范程度提高，数据冗杂逐渐降低，数据一致性逐渐升高

10．为解决数据库访问性能，可采取哪些逆规范化处理方案？

（1）增加冗余列或派生列 （2）多个关系表合并为一个关系表

11.如何将如下图书销售系统CDM分别转换为LDM、PDM 数据模型？

LDM：先转化为实体属性表，用关系连接起来。 PDM：用sql语句进行执行

12.如下工资关系表在数据库访问操作中会出现哪些数据异常？如何规范化设计该表到4NF范式？

增加一个新的员工可能属于同一部门，但是工作地点不同，就会导致数据不一致。

INF1：首先检查是否有可拆分的属性，工作地点可拆分为工作城市，工作区域。

INF2 ：消除属性的部份依赖函数关系，如工号可以唯一确定其他属性，即可以把年月作为另一个表踢出去，把工号加 入其中作为外键。

INF3：切断关系中的属性传递依赖，工号可推职称，职称不可推出工号，职称可推出所在部门，把职称和所在部门踢出去。

BCNF：所有函数的依赖决定因子都是候选键。

INF4：消除了多值依赖，办公电话和工作地点之间存在多值依赖，所以要去除。

13.针对如下数据库E-R模型设计，如何SQL编程实现该模型设计? 需要考虑业务规则中的级联处理。

create table school( 学校编号 varchar primary key not null, 学校名称 varchar not null, 地址 varchar , 联系电话 character(11), ) 
create table student( 学号 character(20) primary key not null, 学校编号 varchar, 学生姓名 varchar , 性别 character(1) default '男' check in ('男','女', 身份证号 character(18), 手机 character(11), constraint foreign key (学校编号) references 学校（学校编号） on delete cascade )