22-23数据库原理期末试题

那个人有梦想

已于 2024-01-10 13:34:19 修改

阅读量2.1k

点赞数 24

CC 4.0 BY-SA版权

文章标签：数据库 sql

于 2024-01-10 13:32:21 首次发布

本文链接：https://blog.csdn.net/qq_42898149/article/details/135501138

本文讨论了数据库基础操作，如在学生表中插入记录的错误分析，创建D1院系视图，针对大规模数据的索引策略，SQL权限设置，查询性能分析，ER模型设计，规范化理论及数据库编程中的触发器和并发控制问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

8-1 数据库基础知识应用

分数 20

全屏浏览题目

作者曹文平

单位湖北文理学院

教学数据库中有四个关系模式如下（带#号表示主键）：学生（学号#、姓名、性别、年龄、院系号）、院系（院系号#、系名、负责人）、课程（课程号#、课程名、学分）、选课（学号#、课程号#、成绩）。数据如下：

学生表：

院系表：

课程表：

选课表：

请回答如下问题:

1、基于以上的表数据，在学生表中插入一条记录（'2022517232','佘玉梅','女',19,'D5'）,显示插入失败信息，请分析原因。（4分）

2、某个应用需要查询院系表，但是约束其只能看到D1院系的信息。解决方案是创建D1院系视图给该应用访问。请写出创建该视图的sql语句（4分）

3、如果学生表数据量很大，经常需要用姓名或性别作为查询条件进行查询，请说明分别在姓名和性别属性上建立索引是否合适。（4分）

4、现要对用户wang授予选课表上的select和insert权限，请写出该操作的sql语句。（4分）

5、现有如下查询语句，分析该查询的查询效率（4分）。

SELECT 学号,姓名 FROM 学生 WHERER EXISTS (

SELECT * FROM 选课 WHERE 选课.学号=学生.学号

)

8-2 数据库建模和设计

分数 22

全屏浏览题目

作者曹文平

单位湖北文理学院

公司的物资管理系统某应用中，有四个实体。一是“仓库”实体，属性有仓库号、仓库名、地址；二是“物品”实体，属性有物品号、物品名、规格、单价；三是“职工”实体，属性有职工号、姓名、性别。四是“供应商”实体，属性有供应商号、供应商名、地址。一个仓库有一名职工管理，一个职工可以管理多个仓库；一个仓库可以存放多种物品，一个物品只存放在一个仓库里；一个物品可以由多个供应商供应，每个供应商可以供应多种物品。供应商供应一种物品有对应的数量和时间信息。

1、根据以上说明，利用word、画图等工具，用贴图形式，建立一个反映上述应用的ER模型，要求标注联系类型(联系属性不可省略，实体属性可省略)。(10分)
2、根据一般转换规则，将ER模型转换为关系模型，要求在每个关系模式后面，用文字标注每个关系模型的主键和外键(没有外键则标注无)。(10分)

3、如果该系统的另一个应用中抽象出了一个员工实体，属性有员工号、姓名、性别和工资级别。当将两个应用的ER图合并时，如何解决职工实体和员工实体的冲突问题？(2分)

8-3 规范化分析

分数 18

全屏浏览题目

作者曹文平

单位湖北文理学院

1、有如下关系模式：

R（A，B，C，D，E，F），函数依赖集F={A->C,B->D,B->E,E->F}。
试回答如下问题：
（1）写出该关系模式的侯选码。（3分）
（2）该关系模式最高满足第几范式？并说明理由。（4分）
（3）该关系模式在实际应用中会存在什么问题？ (3分)
（4）如果该关系模式不满足3NF，将该关系模式分解为满足3NF的关系模式集。（5分）
2、有如下关系模式：

R（A，B，C，D，E），函数依赖集F={A->B,A->C,B->D,B->E}。如果将R分解为R1（A，C）和R2（B，D，E），试问该分解是否符合无损连接性，并说明理由。（3分）

8-4 数据库编程及控制

分数 10

全屏浏览题目

作者曹文平

单位湖北文理学院

1、数据库有产品、销售商及销售三个关系模式：

    产品（产品编号，产品名称，单价，库存量）；

    销售商（销售商编号，销售商名称，地址）；

    销售（产品编号，销售商编号，销售时间，销售数量）。

    当在销售表中插入一条销售记录时，需要从产品表对应的产品库存量中减去销售数量（假设库存数量足够大）。为保证该完整性约束，在销售表中创建一个insert触发器。请写出创建该触发器的sql语句。（5分）

2、并发控制分析。

如图所示的事务调度，其中事务T1、T2并发执行，回答以下问题：

（1）上述事务T1、T2并发执行中采用了几级封锁协议？T1T2并发执行时会产生什么问题？ (3分)

（2）使用封锁锁协议来解决该问题，需要使用几级封锁协议？介绍该封锁协议内容（2分）

10-1 查询每种产品的名称和重量(用kg表示)

本题目要求编写SQL语句，
检索出product表中每种产品的名称和重量(用kg表示)。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：
CREATE TABLE `product` (
  `Pid` varchar(20),   --商品编号
  `PName` varchar(50), --商品名称
  `Weight_g` decimal(10, 3)    --重量
);
表样例

product表：

Pid PName Weight_g
P01 M6螺栓 30
P02 M8螺栓 40
P03 M16螺栓 140
P04 螺帽 30
P05 螺母 45
P06 垫片 10
P07 铰链 70
P21 螺钉 3
输出样例:

PName Weight_kg
M6螺栓 0.030
M8螺栓 0.040
M16螺栓 0.140
螺帽 0.030
螺母 0.045
垫片 0.010
铰链 0.070
螺钉 0.003

Pid	PName	Weight_g
P01	M6螺栓	30
P02	M8螺栓	40
P03	M16螺栓	140
P04	螺帽	30
P05	螺母	45
P06	垫片	10
P07	铰链	70
P21	螺钉	3

PName	Weight_kg
M6螺栓	0.030
M8螺栓	0.040
M16螺栓	0.140
螺帽	0.030
螺母	0.045
垫片	0.010
铰链	0.070
螺钉	0.003

SELECT PName, Weight_g / 1000 AS Weight_kg
FROM product;

10-2 查询既不是’上海’，也不是’北京’和’天津’的供货商信息

本题目要求编写SQL语句，
检索出supplier表中所有符合既不是’上海’，也不是’北京’和’天津’的供货商信息的记录。

提示：请使用SELECT语句作答。

表结构:
CREATE TABLE `supplier` (
  `Sid` varchar(10),   --供应商编号
  `SName` varchar(50), --供应商名称
  `City` varchar(20)   --供应商地址
);
表样例

supplier表：

Sid SName City
S01 东风机械厂武汉
S02 天鹰紧固件厂温州
S05 长城机电杭州
输出样例:

Sid SName City
S01 东风机械厂武汉
S02 天鹰紧固件厂温州
S05 长城机电杭州

Sid	SName	City
S01	东风机械厂	武汉
S02	天鹰紧固件厂	温州
S05	长城机电	杭州

Sid	SName	City
S01	东风机械厂	武汉
S02	天鹰紧固件厂	温州
S05	长城机电	杭州

SELECT *
FROM supplier
WHERE City NOT IN ('上海', '北京', '天津');

10-3 查询产品名中包含’M’且重量大于30的产品编号

本题目要求编写SQL语句，
检索出product表中所有符合包含’M’且重量大于30产品编号。

提示：请使用SELECT语句作答。

表结构:
CREATE TABLE `product` (
  `Pid` varchar(20),   --商品编号
  `PName` varchar(50), --商品名称
  `Weight` decimal(10, 3)    --重量
);
表样例

product表：

Pid PName Weight
P01 M6螺栓 30
P02 M8螺栓 40
P03 M16螺栓 140
P04 螺帽 30
P05 螺母 45
P06 垫片 10
P07 铰链 70
P21 螺钉 3
输出样例:

Pid
P02
P03

Pid	PName	Weight
P01	M6螺栓	30
P02	M8螺栓	40
P03	M16螺栓	140
P04	螺帽	30
P05	螺母	45
P06	垫片	10
P07	铰链	70
P21	螺钉	3

Pid
P02
P03

SELECT Pid
FROM product
WHERE PName LIKE '%M%' AND Weight > 30;

10-4 查询没有分配工作的员工编号、姓名

本题目要求编写SQL语句，
检索出employee表中没有分配工作的员工编号、姓名。

提示：请使用SELECT语句作答。

表结构:
CREATE TABLE `employee` (
  `Eid` varchar(10),   --职工编号
  `EName` varchar(30), --职工姓名
  `Wno` varchar(10),   --所在仓库
  `Salary` int(11)     --职工工资
);
表样例

employee表：

Eid EName Wno Salary
0010 张三 A01 3600
0011 刘勇 A01 2700
0012 张立 A01 8500
0021 刘靖 A02 2500
0022 王强 A02 5600
0023 李军 5000
0031 王林 3500
输出样例:

Eid EName
0023 李军
0031 王林

Eid	EName	Wno	Salary
0010	张三	A01	3600
0011	刘勇	A01	2700
0012	张立	A01	8500
0021	刘靖	A02	2500
0022	王强	A02	5600
0023	李军		5000
0031	王林		3500

Eid	EName
0023	李军
0031	王林

SELECT Eid, EName
FROM employee
WHERE Wno IS NULL OR Wno = '';

10-5 查询订货数量不小于20的订单编号，供货商编号，按照订单日期降序排列

本题目要求编写SQL语句，
检索出orders表中订货数量不小于20的订单编号，供货商编号，按照订单日期降序排列。

提示：请使用SELECT语句作答。

表结构:
CREATE TABLE `orders` (
  `OrdNo` int(11),   --订单编号
  `Sid` varchar(10), --供应商编号
  `Eid` varchar(10), --职工编号
  `Pid` varchar(20), --商品编号
  `Price` decimal(10,2), --价格
  `QTY` int(11),     --订购数量
  `ordDate` date    --订单日期
);
表样例

orders表：

OrdNo Sid Eid Pid Price QTY ordDate
1 S01 0011 P01 11 23 2022-02-13
2 S02 0012 P01 12 25 2022-02-14
3 S03 0012 P03 60 55 2022-02-14
输出样例:

OrdNo Sid
2 S02
3 S03
1 S01

OrdNo	Sid	Eid	Pid	Price	QTY	ordDate
1	S01	0011	P01	11	23	2022-02-13
2	S02	0012	P01	12	25	2022-02-14
3	S03	0012	P03	60	55	2022-02-14

OrdNo	Sid
2	S02
3	S03
1	S01

SELECT OrdNo, Sid
FROM orders
WHERE QTY >= 20
ORDER BY ordDate DESC;

10-6 查询平均成绩高于75分的学生

本题目要求编写SQL语句，
在sc表 中查询平均成绩高于75分的学生。

表结构:

请在这里写定义表结构的SQL语句。例如：
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
);
表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

输出样例:

请在这里给出输出样例。例如：

select sno as 学号,avg(grade) as 平均成绩
from sc
group by sno
having avg(grade)>75

10-7 查询选修某两门课程的学生

本题目要求编写SQL语句，
检索出 sc表中至少选修了’C001’与’C002’课程的学生学号。

提示：MySQL不允许使用intersect语句。

表结构:

请在这里写定义表结构的SQL语句。例如：
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,  -- 学号
  `cno` char(4) NOT NULL,  -- 课程号
  `grade` decimal(6,1) DEFAULT NULL,  -- 成绩
  PRIMARY KEY (`sno`,`cno`)
);
表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

输出样例:

请在这里给出输出样例。例如：

SELECT sno as 学号
FROM sc
WHERE cno IN ('C001', 'C002')
GROUP BY sno
HAVING COUNT(DISTINCT cno) >= 2

10-8 统计各系的老师人数，并按人数升序排序

本题目要求编写SQL语句，
统计出``teachers```表中各系的老师人数，并按人数升序排序，要求结果中列名分别显示“系别”、“教师人数”。

提示：请使用SELECT语句作答。

表结构:

请在这里写定义表结构的SQL语句。例如：
CREATE TABLE teachers (
  tno char(3) ,
  tname char(8),
  ps char(10),
  tbirthday date ,
  tdept char(16) ,
  tsex char(2),
  PRIMARY KEY (tno)
) ;
表样例

请在这里给出上述表结构对应的表样例。例如

``teachers```表：

tno tname ps tbirthday tdept tsex
001 谭浩强教授 1958-01-01 计科男
002 王珊教授 1962-02-13 计科女
003 萨师煊教授 1953-05-01 计科男
004 严蔚敏副教授 1968-07-02 软工女
005 李琳讲师 1988-11-15 软工女
006 韩万江助教 1992-10-17 信管男
输出样例:

请在这里给出输出样例。例如：

系别教师人数
信管 1
软工 2
计科 3

tno	tname	ps	tbirthday	tdept	tsex
001	谭浩强	教授	1958-01-01	计科	男
002	王珊	教授	1962-02-13	计科	女
003	萨师煊	教授	1953-05-01	计科	男
004	严蔚敏	副教授	1968-07-02	软工	女
005	李琳	讲师	1988-11-15	软工	女
006	韩万江	助教	1992-10-17	信管	男

系别	教师人数
信管	1
软工	2
计科	3

SELECT tdept AS '系别', COUNT(*) AS '教师人数'
FROM teachers
GROUP BY tdept
ORDER BY '教师人数' ASC;

10-9 修改高数不及格的学生成绩

本题目要求编写UPDATE语句，
将sc表中“高等数学”课程不及格的成绩全改为60分

表结构:

请在这里写定义表结构的SQL语句。例如：
CREATE TABLE `cou` (
  `cno` char(4) NOT NULL,
  `cname` varchar(30) NOT NULL,
  `credit` smallint(6) DEFAULT NULL,
  `ptime` char(5) DEFAULT NULL,
  `teacher` char(10) DEFAULT NULL,
  PRIMARY KEY (`cno`)
)
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,
  `cno` char(4) NOT NULL,
  `grade` decimal(6,1) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`cno`)
)
表样例

请在这里给出上述表结构对应的表样例。例如

cou表：

sc表：

输出样例:

请在这里给出输出样例。例如：

UPDATE sc
set grade='60'
where grade<=60
and 
sc.cno 
in(select cno from cou where cname='高等数学')

10-10 查询S001学生选修而S003学生未选修的课程

检索出 sc表中学号为S001的学生选修的而S003学号学生未选修的课程号。

提示：MySQL不允许使用 except语句。

表结构:

请在这里写定义表结构的SQL语句。例如：
CREATE TABLE `sc` (
  `sno` char(4) NOT NULL,  -- 学号
  `cno` char(4) NOT NULL,  -- 课程号
  `grade` decimal(6,1) DEFAULT NULL,  -- 成绩
  PRIMARY KEY (`sno`,`cno`)
);
表样例

请在这里给出上述表结构对应的表样例。例如

sc表：

输出样例:

请在这里给出输出样例。例如：

SELECT 
    cno 课程号
FROM 
    sc
WHERE 
    sno = 'S001' 
    and cno not IN(SELECT cno FROM sc WHERE sno = 'S003' )