数据库期末复习题库

1. Mysql日志功能有哪些?

• 记录日常操作和错误信息，以便了解Mysql数据库的运行情况，日常操作，错误信息和进行相关的优化。

2. 数据库有哪些备份方法

• 完全备份：全部都备份一遍
• 表备份：只提取数据库中的数据，而不备份表的结构和定义
• 增量备份：在完全备份后，只备份数据的变化。用于定期备份和自动恢复

3. 事务的相关操作

-- 开启事务
START TRANSACTION;

-- 插入操作
INSERT INTO product (id, name, function, company, address) VALUES (2, 'xyz', '功能描述', '某公司', '某地址');

-- 更新操作
UPDATE product SET address = '新地址' WHERE id = 2;

-- 提交事务
COMMIT;

-- 如果需要回滚事务
ROLLBACK;

4. 数据库角色的分类？及其对应的权限

• db_owner: 在数据库中拥有全部权限
• db_accessadmin: 可以添加或删除用户ID
• db_securityadmin: 可以管理全部权限，对象所有权，角度和角色成员资格
• db_ddladmin: 可以发出 ALL DDL，但不能发出 GRANT, REVOKE, 或 DENY语句

5. 触发器(trigger)与事件(event)

触发器: **是一种特殊的存储过程，它在试图更改触发器所保护的数据时自动执行。**它被定义为在对表或视图发出 update, insert 或 delete语句时自动执行，在有数据修改时自动强制执行其业务规则

事件调度器(event scheduler): **Mysql基于特定时刻或时间周期调用的过程式数据库对象。**例如在某一时刻定期激活事件向表中insert, update ,delete记录

差异: 与触发器不同的是，一**个事件可调用一次，也可周期性调用，它由一个特定的线程来管理，**该线程被称作事件调度器(event scheduler)

6. 存储过程

1. 存储过程增强了sql语言的功能和灵活性
2. 存储过程能实现较快的执行速度
3. 存储过程允许标准组件式编程
4. 存储过程能够减少网络流量
5. 存储过程可被作为一种安全机制来充分利用

7. 存储过程和存储函数的定义与调用

存储过程和存储函数是十分相似的，但它们之间有一些微妙的差别

创建一个存储过程，根据学生的学号查询学生的姓名

CREATE PROCEDURE getnamebysno(IN xh CHAR(10), OUT name CHAR(20))
BEGIN
    SELECT sname INTO name FROM student WHERE sno = xh;
END;
CALL getnamebysno('1101', @name);
SELECT @name;

创建一个名为 numstu的存储函数，查询学生的人数

CREATE FUNCTION numstu()
RETURNS INTEGER
BEGIN
    RETURN (SELECT COUNT(*) FROM student);
END;

SELECT numstu();

8. 请简述使用视图的优点

1. 视图能够集中数据，简化用户的数据查询和处理
2. 视图便于用户共享数据
3. 视图提高了数据的逻辑独立性
4. 视图能够对机密数据提供安全保护

9. 写出相应的 sql

9.1 建表

学生表: Student(Seo，Sname, Ssex，Sage，Sdept)(学号，姓名，性别，年龄)，所在系Sno为主键。

课程表: Course(Cno，Cname)(课程号，课程名)，Cno为主键。

学生选课表: SC(Sm，Cno，Score)(号，课程号，成绩)，Sno，Cno 为主健。

create table Student (
	Sno CHAR(10) PRIMARY KEY, -- 学号
    Sname VARCHAR(20) NOT NULL, -- 姓名
    Ssex CHAR(2), -- 性别
    Sage INT, -- 年龄
    Sdept VARCHAR(20) -- 所在系
)

create table Course {
	Cno char(10) primary key, -- 课程号
	Cname VARCHAR(20) NOT NULL -- 课程名
}

create table sc (
	Sno char(10), -- 学号
    Cno char(10), -- 课程号
)

create table SC (
	Sno char(10), -- 学号
    Cno char(10), -- 课程号
    Score float, -- 成绩
    primary key (Sno, Cno),
    foreign key (Sno) references Student(Sno), -- 外键
    foreign key (Cno) references Course(Cno) -- 外键
)

9.2 创建视图

9.2.1 查询全体学生的姓名，性别，课程名，成绩

create view stu_info as
select Sname Ssex, Cname Score
from Student
natural join SC
natural join Course;

9.2.2 查询全体学生的基本情况，并按所在系升序，年级降序排列

create view stu_info as
select Sno, Sname, Ssex, Sage, Sdept, Cno, Cname
FROM Student
natural join SC
natural join Course
order by Sdept asc, Sage desc

9.3.3 查询所有比"李四"年龄大的学生姓名，年龄和性别

create view stu_info as
select Sname, Sage, Ssex
from Student
where Sage > (select Sage from student where Sname = '李四')

9.4.4 查询学生表中成绩在前三位的学生的学号，姓名及所在系

create view stu_info as
select Sno, Sname, Sdept
from Student
where Sno IN(select Sno from SC order by Score desc limit 3)

9.3 修改视图中的数据

9.3.1 给选修了1号课程且成绩低于 70 的学生每人成绩增加5分

update SC
set Score = Score + 5
where Con = '1' and Score < 70;

9.3.2 向 Student 表添加一条记录

insert info Student (Sno, Sname, Ssex, Sage, Sdept)
values ('201801', '李一本', '男', 20, '计算机');

9.3.3 删除视图

drop view if exists stu_info

10.创建，查看，删除索引的 sql

创建(create)

create unique index index_name on table_name(column_list) 

查看(show)

show index from table_name

删除索引

drop index index_name on table_name

11.Mysql中的存储引擎和其特点

MyISAM: 拥有较高的插入，查询速度，但不支持事务

InnoDB: 5.5 版本后 Mysql 的默认数据库，事务型数据库的首选引擎，支持 ACID 事务，支持行级锁定

Memory: 所有数据内置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 Mysql 充型启动时丢失。

CSV: 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每一个数据表创建一个.CSV文件索引.这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。

12.数据类型选择的原则

1. 在符合要求的前提下，尽量选择短数据类型(省内存,减少索引大小，提高索引的查找速度)
2. 数据类型越简单越好容易处理和维护，并且相较于复杂数据类型会有更好的性能
3. 尽量采用精确小数类型，而不采用浮点数类类型浮点数存在舍入误差，decimal可以确保数值精确，特别是在财务管理方面
4. 应该用内置的日期和时间数据类型简化开发，提高性能和数据完整性
5. 尽量避免 NULL 字段减少查询复杂度，并且引入null值在索引和计算时可能导致性能问题和不可预见的行为

13.常见的约束和用法

1. 主键(primary key)
2. 外键约束(foreige key)
3. 非空(not null)
4. 唯一性(unique)
5. 默认值(default)
6. 自增(auto_increment)
7. 检查(check)

• 实体完整性约束: 每一行反应不同的实体，通过索引，唯一约束。主键约束或标识属性来体现
• 域完整性约束: 指给定列的输入有效性，通过限制数据类型，检查约束，输入格式，外键约束，默认值，非空等体现
• 引用完整性约束：(表之间联系，通过主外键)
• 自定义完整性约束：(根据用户需求)

14. sql语句编写

1. 设有三个关系：student(学号,姓名,性别)、course(课程号,课程名,学分)、sc(学号,课程号,分数)

(1) 查询既没有选修课程07002，也没有选修课程07004的学生的学号、课程号和成绩：

SELECT sc.学号, sc.课程号, sc.分数
FROM sc
WHERE 学号 NOT IN (SELECT 学号 FROM sc WHERE 课程号 = '07002')
AND 学号 NOT IN (SELECT 学号 FROM sc WHERE 课程号 = '07004');

(2) 查询选修了“数据库技术基础”课程且成绩在60分以下的所有学生的姓名、成绩：

SELECT student.姓名, sc.分数
FROM student
JOIN sc ON student.学号 = sc.学号
JOIN course ON sc.课程号 = course.课程号
WHERE course.课程名 = '数据库技术基础'
AND sc.分数 < 60;

2. 设教务管理系统数据库jxgl里包含有三个关系表：student(学号,姓名,性别,出生日期,民族,政治面貌)、course(课程号,课程名称,课程简介,课时,学分,开课学期) 、sc(学号,课程号,成绩)

(1) 查询学生信息表student中前8条记录：

SELECT * 
FROM student
LIMIT 8;

(2) 查询学生信息表student中姓李的男生的学生信息：

SELECT * 
FROM student
WHERE 姓名 LIKE '李%'
AND 性别 = '男';

(3) 查询学生信息表student中年龄从20到25岁的学生信息：

SELECT * 
FROM student
WHERE TIMESTAMPDIFF(YEAR, 出生日期, CURDATE()) BETWEEN 20 AND 25;

(4) 查询选修了两门以上课程的学生学号：

SELECT 学号
FROM sc
GROUP BY 学号
HAVING COUNT(课程号) > 2;

(5) 查询选修了课程的学生的学号、姓名，并按学号升序排序：

SELECT DISTINCT student.学号, student.姓名
FROM student
JOIN sc ON student.学号 = sc.学号
ORDER BY student.学号 ASC;

15.什么是数据库管理系统，以及它的主要功能有哪些

数据库管理系统安装于操作系统之上，是一个管理，控制数据中各种数据库对象的系统软件

功能

1. 数据库的建立和维护
2. 数据定义功能
3. 数据组织，存储和管理
4. 数据操作功能
5. 数据库事务管理和运行管理

16.什么是模式

• 模式：也称逻辑模式, 是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图
• 外模式: 子模式或用户模式，是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示
• 内模式：也称存储模式: 它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。通过外模式-模式映射和模式-内映射这两个映射保证了数库系统中的数据就有较高的逻辑独立性和物理独立性

17. 设计数据库相关的概念

• 实体: 客观存在的实体事务
• 实体型：用实体类型名和所有属性来共同表示同一类实体
• 实体集： 同一类型实体的集合
• 属性：实体所具有的某一特性
• 码：可以唯一标识一个实体的属性集
• E-R图：E-R图也称实体-联系图(Entity Relationship Diagram)提供了表示实体类型，属性和联系的方法，用来描述现实世界的概念模型

18.试述数据库设计过程的各个阶段

1. 需求分析阶段
2. 概念结构设计阶段
3. 逻辑结构设计阶段
4. 数据库物理设计阶段
5. 数据库实施阶段
6. 数据库运行和维护阶段

19.局部E-R图集成为全局E-R图过程中的关键问题是什么？有什么方法?

1. 关键是消除各局部 E-R 图中的冲突
2. 属性冲突的解决方法是与用户协商后解决
3. 命名冲突解决方式是需要与各部门协商讨论后解决
4. 结构冲突解决方法是根据应用语义对实体联系的类型进行综合或调整

20.ER图

运动队方面

运动队: 队名、教练姓名、队员姓名

队员: 队名、队员姓名、性别、项名其中，一个运动队有多个队员，一个队员仅属于一个运动队，一个队一般有一个教练。

运动会方面

运动队: 队编号、队名、教练姓名

项目: 项目名、参加运动队编号、队员姓名、性别、比赛场地

其中，一个项目可由多个队参加，一个运动员可参加多个项目，一个项目一个比赛场地请你协助其完成如下设计。

1. 分别设计运动队和运动会两个局部 E-R图。

2. 将它们合并为一个全局E-R图。

3. 合并时存在什么冲突，你是如何解决这些冲突的?

20.1 运动队局部Ｅ-Ｒ图，

20.2 运动会局部Ｅ-Ｒ图

20.3 全局Ｅ-Ｒ图

（3） 命名冲突：项名、项目名异名同义，统一命名为项目名。

结构冲突：项目在两个局部Ｅ-Ｒ图中，一个作属性，一个作实体，合并统一为实体.

21.概念模型向逻辑模型的转换原则有哪些？

转换原则：一个实体转换为一个关系模式。实体的属性就是关系的属性。

联系类型的转换：

• 若实体间联系是 1∶1，可以在两个实体类型转换成的两个关系模式中任意一个关系模式中加入另一个关系模式的码和联系类型的属性。

• 若实体间的联系是 1∶n，则在 n 端实体类型转换成的关系模式中加入 1 端实体类型的码和联系类型的属性。

• 若实体间联系是 m∶n，则将联系类型也转换成关系模式，其属性为两端实体类型的码加上联系类型的属性，而码为两端实体码的组合。

22.某医院病房计算机管理中心需要如下信息。

科室: 科名、科地址、科电话、医生姓名

病房: 病房号、床位号、所属科室名

医生: 姓名、职称、所属科室名、年龄、工作证号

病人: 病历号、姓名、性别、诊断、主管医生、房号

其中，一个科室有家个病房、多个医生，一个病房只能属于一个科室，一个医生只属于个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。完成如下设计。

1. 设计读计算机管理系统的E-R图

2. 将该E-R图转换为关系模式结构。

3. 指出转换结果中每个关系模式的候选码。

(1)

（2） 对应的关系模型结构如下：

• 科室（科名，科地址，科电话）

• 病房（病房号，床位号，科室名）

• 医生（工作证号，姓名，职称，科室名，年龄）

• 病人（病历号，姓名，性别，诊治，主管医生，病房号）

（3） 每个关系模式的候选码如下： 科室的候选码是科名；

• 病房的候选码是科室名、病房号；

• 医生的候选码是工作证号；

• 病人的候选码是病历号。

23.E-R图

4、某商业集团数据库中有3个实体集，

• 一是:“商品”实体集，属性有商店编号、商店名、地址等;

• 二是:“商品”实体集，属性有商品号、商品名、规格、单价等;

• 三是:“职工”实体集，属性有职工编号、姓名、性别、业绩等。

商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可以放在多个育店销售，每个商店销售的商品有月销售量:商店与职工之同存在“用”联系，每个商店有多名职工，每个职工只能在一个商店工作，商店聘用职工有聘期和工。

1. 试面出E-R图。

2. 将该E-R 图转换成关系模式，并指出主码和外码。

（1）

（2）这个 ER 图可转换 4 个关系模式：

• 商店（商店编号，商店名，地址）

• 主码：商店编号

• 职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪）

• 主码：职工编号 外码： 商店编号

• 商品（商品号，商品名，规格，单价）

• 主码：商品号

• 销售（商店编号，商品号，月销售量）

• 主码：商店编号，商品号

24.关系模型的完整性规则有哪几类？举例说明什么是实体完整性和参照完整性。

关系模型的完整性有三类：实体完整性，参照完整性和用户定义完整性。

• 实体完整性： 例如学生关系中，学号为主码，则学号的值必须唯一且非空。

• 参照完整性： 成绩关系中，学号是外码，则学号的取值必须是学生关系中学号的有效值或取空值。

25. 关系范式

设有一个教师任课的关系，其关系模式如下:TDC(Tno，Tname，Tite，Dno，Dname,Dloc，Cno，Cname，Credit)。其中各个属性分别表示教师编号、教师姓名、职称、系编号、系名称、系地址、课程号、课程名、学分。

1. 写出该关系的函数依赖，分析是否存在部分依赖，是否存在传递依赖。

2. 该关系的设计是否合理，存在哪些问题。

3. 对该关系进行规范化，使规范化后的关系属于 3NF

（1） 基本的 FD 有：

• 教师编号→ 教师姓名，教师职称
• 系编号→ 系名称，系地址
• 课程号→ 课程名，学分
• 教师编号 → 系编号，系名称，系地址
• R 的关键码为（教师编号，课程号）。

部分依赖： 教师编号→ 教师姓名，教师职称 课程号→ 课程名，学分

传递依赖： 教师编号 → 系名称，教师编号 →系地址

（2） 设计不合理，存在插入异常，更新异常，删除异常和数据冗余

（3） 规范化为 R1（教师编号，教师姓名，教师职称）R2（系编号，系名称，系地址）R3（课程号，课程名，学分）

26.设有关系R和S，如图所示。

计算R×S，π3,2(S)，σB<'5’®，

27.设计数据库范式

关系模式 R 的规范化

设关系模式 R (A, B, C, D)，F 是 R 上成立的函数依赖集，F = { AB → CD, A → D }。

1. 说明 R 不是 2NF 模式的理由

从已知函数依赖集 F 可以确定 R 的候选键是 AB。

• 候选键：AB
• 部分依赖：A → D（A 是 AB 的一部分）

由于存在 A → D 这个局部依赖（部分依赖），即非主属性 D 依赖于候选键的一部分 A，因此 R 不是 2NF 模式。

2. 将 R 分解成 2NF 模式集

为了将 R 分解成 2NF 模式集，需要消除部分依赖。具体步骤如下：

• 将包含部分依赖的属性分解出来。

分解后的关系模式集 ρ 如下：

1. 关系模式 AD (A, D)
2. 关系模式 ABC (A, B, C)

分解后的关系模式集 ρ 是 2NF 模式集，因为在每个分解后的关系模式中，非主属性都完全依赖于候选键。

总结

1. R 不是 2NF 模式的理由：
   • 由于存在部分依赖 A → D，R 不是 2NF 模式。
2. 将 R 分解成 2NF 模式集：
   • 分解后的 2NF 模式集为 ρ = { AD (A, D), ABC (A, B, C) }。

28.sql语句编写

SQL 查询语句格式化

以下是格式化后的 SQL 查询语句，针对 S、SC、C 三个基本表的查询需求：

在表 C 中统计开设课程的教师人数。

SELECT COUNT(DISTINCT TEACHER)
FROM C;

求选修 C4 课程的女学生的平均年龄。

SELECT AVG(AGE)
FROM S, SC
WHERE S.S# = SC.S# AND C# = 'C4' AND SEX = 'F';

求 LIU 老师所授课程的每门课程的平均成绩

SELECT C.C#, AVG(GRADE)
FROM SC, C
WHERE SC.C# = C.C# AND TEACHER = 'LIU'
GROUP BY C.C#;

统计每个学生选修课程的门数（超过 5 门的学生才统计）。要求输出学生学号和选修门数，查询结果按门数降序排列，若门数相同，按学号升序排列。

SELECT S#, COUNT(C#)
FROM SC
GROUP BY S#
HAVING COUNT(*) > 5
ORDER BY COUNT(C#) DESC, S#;

检索学号比 WANG 同学大，而年龄比他小的学生姓名。

SELECT SNAME
FROM S
WHERE S# > (SELECT S#
            FROM S
            WHERE SNAME = 'WANG')
  AND AGE < (SELECT AGE
             FROM S
             WHERE SNAME = 'WANG');

在表 SC 中检索成绩为空值的学生学号和课程号。

SELECT S#, C#
FROM SC
WHERE GRADE IS NULL;

检索姓名以 L 打头的所有学生的姓名和年龄。

SELECT SNAME, AGE
FROM S
WHERE SNAME LIKE 'L%';

求年龄大于女同学平均年龄的男学生姓名和年龄。

SELECT SNAME, AGE
FROM S
WHERE SEX = 'M'
  AND AGE > (SELECT AVG(AGE)
             FROM S
             WHERE SEX = 'F');

求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECT SNAME, AGE
FROM S
WHERE SEX = 'M'
  AND AGE > ALL (SELECT AGE
                 FROM S
                 WHERE SEX = 'F');