有一个关系模式：工程关系（工程号，工程名称，职工号，姓名，聘期，职务，小时工资率，工时），公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定（比方，技术员的小时工资率与工程师不同）。
PK(工程号，职工号)
依靠有：工程名称部分依靠于主码工程号；
姓名，聘期，职务，工时部分依靠于主码职工号；
小时工资率依靠于职务，传递依靠于职工号。
（2）该关系模式的主码（主属性）是什么？主码（主属性）是 (工程号, 职工号)。
（3）对于主码（主属性）而言，其他非主属性哪些是部分函数依靠，哪些是完全函数依靠？
部分函数依靠：
工程名称 依靠于 工程号 (工程号 → 工程名称)
姓名, 聘期, 职务, 工时 依靠于 职工号 (职工号 → 姓名, 聘期, 职务, 工时)
完全函数依靠：
由于 (工程号, 职工号) 是主码，而上述属性均不完全依靠于主码，所以没有属性是完全函数依靠于主码。
（4）是否有传递依靠？假如有指出是什么。存在传递依靠：职工号 → 职务 → 小时工资率
（5）判断该关系模式满意第几范式？
当前关系模式存在部分函数依靠和传递依靠，因此不满意第二范式（2NF）和第三范式（3NF）。
（6）把该关系模式转变成3NF，给出转换分解后的关系模式（标注主码和外码）。
工程(工程号pk，工程名称)
职工（职工号,姓名, 聘期，职务，工程号FK）
职务工资率（职务，小时工资率）
工时表（职工号，工时）
（7）实验根据分解后的关系模式画出E-R图。
（8）用SQL语句创建所有的表，同时添加主键和外键。
（9）在每个表中插入两条数据。
-- 插入工程数据
INSERT INTO 工程 (工程号, 工程名称) VALUES (1, '工程A'), (2, '工程B');
-- 插入职工数据
INSERT INTO 职工 (职工号, 姓名, 聘期, 职务) VALUES (101, '张三', '2024-12-31', '技术员'), (102, '李四', '2024-12-31', '工程师');
-- 插入职务工资率数据
INSERT INTO 职务工资率 (职务, 小时工资率) VALUES ('技术员', 50.00), ('工程师', 70.00);
-- 插入工程职工数据
INSERT INTO 工程职工 (工程号, 职工号, 工时) VALUES (1, 101, 100), (1, 102, 150);
（10）根据实体完整性和参考完整性分析这些表中的主键和外键取值有什么约束？
实体完整性：每个表的主键不能为空且唯一。
参考完整性：外键值必须存在于其引用的表中。
（11）统计各工程参与人数(至少有1人才统计)，和匀称工作时间，并按人数降序排列。
SELECT 工程.工程名称, COUNT(工程职工.职工号) AS 参与人数, AVG(工程职工.工时) AS 匀称工作时间
FROM 工程
JOIN 工程职工 ON 工程.工程号 = 工程职工.工程号
GROUP BY 工程.工程名称
HAVING COUNT(工程职工.职工号) > 0
ORDER BY 参与人数 DESC;
（11）创建一个视图，用于计算各名职工在各个工程中的收入情况。
CREATE VIEW 职工收入视图 AS
SELECT 工程职工.工程号, 工程职工.职工号, (工程职工.工时 * 职务工资率.小时工资率) AS 收入
FROM 工程职工
JOIN 职工 ON 工程职工.职工号 = 职工.职工号
JOIN 职务工资率 ON 职工.职务 = 职务工资率.职务;
（12）界说一个显式事务，事务中实现将聘期即将到期（少于3个月）的职工聘期延伸到2024-12-31日，并提交事务。
START TRANSACTION;
UPDATE 职工
SET 聘期 = '2024-12-31'
WHERE DATEDIFF(聘期, CURDATE()) < 90;
COMMIT;
（13）创建触发器，禁止更新职工表中的职工号。
CREATE TRIGGER 禁止更新职工号
BEFORE UPDATE ON 职工
FOR EACH ROW
BEGIN
IF NEW.职工号 <> OLD.职工号 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '禁止更新职工号';
END IF;
END;
（14）授权用户User对项目工时表的插入、删除和更新权限
-- 授权User对项目工时表的插入、删除和更新权限
GRANT INSERT, DELETE, UPDATE ON 工程职工 TO User;
（15）接纳User的对借阅表的删除权限。
-- 接纳User对借阅表的删除权限
REVOKE DELETE ON 借阅 FROM User;
某医院病房的计算机管理系统中需要如下信息。
科室：科室名，科室地点，科室电话 病房：病房号，床位号，科室名
医生：工作证号，姓名，职称，科室名，年龄
病人：病历号，姓名，性别，诊治，主管医生，病房号
此中，一个科室有多个病房、多位医生，一个病房只属于一个科室，一个医生只属于一个科室，但是可以负责多名病人的诊治，一名病人的主管医生只能有一位。
完成.涉及该计算机管理系统的E-R图。
假设要建立一个企业数据库，该企业有多个下属单位，每一单位有多个职工，一个职工仅隶属于一个单位，且一个职工仅在一个工程中工作，但一个工程中有很多职工参加工作，有多个供应商为各个工程供应不同设备。单位的属性有：单位名、电话。职工的属性有：职工号、姓名、性别。设备的属性有：设备号、设备名、产地。供应商的属性有：姓名、电话。工程的属性有：工程号、地点。请完成如下处置处罚：
（1）设计满意上述要求的E-R图。（3分）
（2）将该E-R图转换为等价的关系模式，并标示出主码（5分）
假设要建立一个企业数据库，该企业有多个下属单位，每一单位有多个职工，一个职工仅隶属于一个单位，且一个职工仅在一个工程中工作，但一个工程中有很多职工参加工作，有多个供应商为各个工程供应不同设备。
提示：各主要关系的主要属性有（详细设计时需要根据实际情况增减须要的属性）：单位的属性有：单位ID、单位名、电话 等
职工的属性有：职工号、姓名、性别、单位ID等
设备的属性有：设备号、设备名、产地等
供应商的属性有：姓名、电话等
工程的主要属性有：（工程名，地点）等
供应的主要属性有：供应商姓名，工程名，设备号，数目，单价等
请完成如下处置处罚：
1.设计满意上述要求的全局E-R图。（7分）
2.将该E-R图转换为等价的关系模式，并标示出主码和外码（3分）
3.用SQL语句实现：（每小题3分，共15个小题，共45分）
(1)建立单位表，此中单位号为主码 (各属性参数根据实际需要设置)
(2)建立职工表，职工号为主码，单位号外码到单位表的单位号
(3)对（2）题已建职工表增加一个“政治面貌”属性，约束为只能取值”共产党员”、”民主党派”、“群众”之一
(4)为供应商S提供的设备建立视图。
(5)删除设备号为“005”设备
(6)将设备号为“001”的设备的产地改为“上海”
(7)统计工程地点在成都的工程数目
(8)查询参加成都工程的职工情况
(9)插入一条供应信息（‘S’，‘P’，‘005’,11，2）
(10)对职工表的职工号建立一个非聚集索引，索引名为“职工NO”
(11)查询出单位名称为“材料科”的所有姓张的男性职工的数据，包罗：职工号，姓名，性别，电话
(12)在“供应”表中按各种设备分组来统计各种设备的总数目,结果按总数目降序显示
(13)根据供应商的姓名”杨春”，查询出该供应商参与了哪些工程，查询结果包罗：供应商姓名，工程名，供应商电话，设备名
(14)根据设备名“搅拌机”，查询出搅拌机供应数目最高的前三名的数据，结果包罗：供应商姓名，工程名，设备名，数目
(15)根据工程名汇总出每个工程的总人数，只查询出总人数超过15人的工程信息，包罗：工程名，人数
-- (1) 建立单位表
CREATE TABLE 单位 (
单位ID INT PRIMARY KEY,
单位名 VARCHAR(100) NOT NULL,
电话 VARCHAR(15) NOT NULL
);
-- (2) 建立职工表
CREATE TABLE 职工 (
职工号 INT PRIMARY KEY,
姓名 VARCHAR(50) NOT NULL,
性别 CHAR(1) NOT NULL,
单位ID INT,
FOREIGN KEY (单位ID) REFERENCES 单位(单位ID)
);
-- (3) 增加政治面貌属性
ALTER TABLE 职工
ADD 政治面貌 ENUM('共产党员', '民主党派', '群众') NOT NULL;
-- (4) 为供应商提供的设备建立视图
CREATE VIEW 供应商设备视图 AS
SELECT 供应商.姓名, 设备.设备名, 设备.产地
FROM 供应商
JOIN 供应 ON 供应商.姓名 = 供应.供应商姓名
JOIN 设备 ON 供应.设备号 = 设备.设备号;
-- (5) 删除设备号为“005”设备
DELETE FROM 设备 WHERE 设备号 = '005';
-- (6) 将设备号为“001”的设备的产地改为“上海”
UPDATE 设备
SET 产地 = '上海'
WHERE 设备号 = '001';
-- (7) 统计工程地点在成都的工程数目
SELECT COUNT(*) AS 成都工程数目
FROM 工程
WHERE 地点 = '成都';
-- (8) 查询参加成都工程的职工情况
SELECT 职工.*
FROM 职工
JOIN 工程职工 ON 职工.职工号 = 工程职工.职工号
JOIN 工程 ON 工程职工.工程号 = 工程.工程号
WHERE 工程.地点 = '成都';
-- (9) 插入一条供应信息
INSERT INTO 供应 (供应商姓名, 工程号, 设备号, 数目, 单价)
VALUES ('S', 'P', '005', 11, 2);
-- (10) 对职工表的职工号建立一个非聚集索引
CREATE INDEX 职工NO ON 职工(职工号);
-- (11) 查询单位名称为“材料科”的所有姓张的男性职工
SELECT 职工号, 姓名, 性别, 电话
FROM 职工
JOIN 单位 ON 职工.单位ID = 单位.单位ID
WHERE 单位.单位名 = '材料科' AND 姓名 LIKE '张%' AND 性别 = '男';
-- (12) 按设备分组统计总数目
SELECT 设备号, SUM(数目) AS 总数目
FROM 供应
GROUP BY 设备号
ORDER BY 总数目 DESC;
-- (13) 根据供应商的姓名查询参与的工程
SELECT 供应商.姓名, 工程.工程名, 供应商.电话, 设备.设备名
FROM 供应商
JOIN 供应 ON 供应商.姓名 = 供应.供应商姓名
JOIN 工程 ON 供应.工程号 = 工程.工程号
JOIN 设备 ON 供应.设备号 = 设备.设备号
WHERE 供应商.姓名 = '杨春';
-- (14) 查询搅拌机供应数目最高的前三名
SELECT 供应商.姓名, 工程.工程名, 设备.设备名, 供应.数目
FROM 供应
JOIN 供应商 ON 供应.供应商姓名 = 供应商.姓名
JOIN 工程 ON 供应.工程号 = 工程.工程号
JOIN 设备 ON 供应.设备号 = 设备.设备号
WHERE 设备.设备名 = '搅拌机'
ORDER BY 供应.数目 DESC
LIMIT 3;
-- (15) 汇总每个工程的总人数，人数超过15人的工程
SELECT 工程.工程名, COUNT(工程职工.职工号) AS 人数
FROM 工程
JOIN 工程职工 ON 工程.工程号 = 工程职工.工程号
GROUP BY 工程.工程名
HAVING 人数 > 15;
四、分析题（共15分）
设工厂里有一个记载职工每天日产量的关系模式：
R（职工编号，日期，日产量，车间编号，车间主任）。
假如规定：每个职工每天只有一个日产量；
每个职工只能隶属于一个车间；
每个车间只有一个车间主任。
分析：
职工编号，日期， 日产量，车间编号，车间主任
001 2023-1-1 12 cj01 张麻子
002 2023-1-1 15 cj02 李四
001 2023-1-2 18 cj01 张麻子
试回答下列题目：
· 数据冗余：车间主任信息重复，导致数据冗余。
· 更新异常：更新车间主任信息时需要在多个记载中举行修改。
· 插入异常：假如一个车间没有职工，无法插入车间信息。
· 删除异常：删除一个职工记载可能会丢失车间主任信息。
关键码（主键）是 (职工编号, 日期)。由于每个职工每天只有一个日产量。
(3) 根据上述规定，写出模式R的各属性的基本函数依靠和依靠的关键码；
· 职工编号, 日期 → 日产量
· 职工编号 → 车间编号
· 车间编号 → 车间主任
(4) 阐明R不是2NF的理由，并把R分解成2NF模式集；
职工表（职工编号，车间编号，车间主任）
生产表（职工编号，日期，日产量）
(5) 进而再分解成3NF模式集，并阐明理由。
在2NF基础上，车间主任传递依靠于职工编号，地点存在传递依靠，所不是3NF
继续分解决为
职工表(职工编号，车间编号,….)
车间表（车间编号，车间主任,….）
生产表（职工编号，日期，日产量,…）
设关系模式R（S#，C#，GRADE，TNAME，TADDR），其属性分别表示门生学号、选修课程的编号、结果、任课教师姓名、教师地点等意义。
假如规定，每个门生每学一门课只有一个结果；每门课只有一个任课教师；每个教师只有一个地点（此处不允许教师同名同姓）。
（1）该关系模式R存在哪些题目
· 数据冗余：教师地点重复，导致数据冗余。
· 更新异常：更新教师地点需要在多个记载中举行修改。
· 插入异常：假如没有门生选某门课，则无法记载教师信息。
· 删除异常：删除某个门生的记载可能导致丢失教师信息。
（2）试写出关系模式R基本的函数依靠和候选码。
S#，C# → GRADE, TNAME, TADDR
C# → TNAME
TNAME → TADDR
候选码：
S#，C#（由于每个门生每门课只有一个结果）
（3）判断R属于几范式，并阐明理由。
R不属于3NF，由于存在以下传递依靠：
S#，C# → TNAME → TADDR
（4）假如R不满意3NF，请对R举行模式分解。使分解后得到的所有关系模式满意3NF。
门生（S#,…..）
课程（C#, TNAME,….）
门天生绩(S#，C#，GRADE)
教师表（TNAME，TADDR,….）
分析：
R（S#，C#，GRADE，TNAME，TADDR）
S01 c01 89 张三 大件路
S01 C02 88 李四 小件路
S02 c01 78 张三 大件路
S02 C02 56 李四 小件路
S01 c03 78 王二 东门
数据界说：创建、修改和删除数据库结构的功能。
数据利用：插入、更新、删除和查询数据的功能。
数据控制：权限管理、并发控制和事务管理。
数据完整性：界说和确保数据的正确性和一致性。
数据安全性：掩护数据免受未经授权的访问。
- 简述数据库系统的三级模式，并阐明如何实现数据的独立性。
外部模式：用户视图，界说用户可以看到和利用的数据。
概念模式：全局视图，界说所有用户的公共视图。
内部模式：物理视图，界说数据的物理存储结构。
通过模式之间的映射来实现数据独立性：
逻辑独立性：外部模式和概念模式的独立性。
物理独立性：概念模式和内部模式的独立性。
锁：是一种机制，用于管理多个事务对数据库资源的并发访问。
基本锁范例：
共享锁 (S锁)：允许并发读取，但不允许写入。
排他锁 (X锁)：禁止其他事务对该资源的任何操作（读或写）。
- 数据库设计包罗哪六个阶段？简单叙述每个阶段的任务是什么？
需求分析：确定用户需求和业务规则。
概念设计：利用E-R图等工具建立概念模型。
逻辑设计：将概念模型转换为逻辑模型，如关系模型。
物理设计：界说数据的存储结构和访问方法。
实行：创建数据库和应用程序。
维护：监控和优化数据库性能，修复题目。
- 数据库系统中，发生故障的种类有哪些？并简述每种故障的恢复计谋。
事务故障：由于错误或死锁导致事务失败。恢复计谋是事务回滚。
系统故障：硬件或操作系统故障导致系统崩溃。恢复计谋是重启并利用日记举行恢复。
介质故障：磁盘损坏导致数据丢失。恢复计谋是从备份中恢复数据。
基本表：物理上存在的表，存储实际数据。
视图：基于基本表的虚拟表，不存储实际数据。
区别：
基本表存储数据，视图仅存储查询界说。
视图可简化查询，提供数据安全和逻辑独立性。
安全性：掩护数据库免受未经授权的访问和修改。
完整性：确保数据的准确性和一致性。
- 什么是事务？ 写出界说事务的三条语句和寄义，事务具有哪四个特性？
事务：是一组逻辑操作单位，要么全做，要么全不做。
事务的四个特性 (ACID)：
原子性 (Atomicity)：事务中的所有操作要么全部完成，要么全部不完成。
一致性 (Consistency)：事务完成后，数据库必须处于一致状态。
隔离性 (Isolation)：并发事务之间的操作互不干扰。
长期性 (Durability)：事务完成后，其结果是永久性的。
界说事务的三条语句：
BEGIN TRANSACTION; -- 开始事务
-- 执行SQL操作
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务
1NF：所有字段的值都是原子值。
2NF：满意1NF，而且所有非主属性完全依靠于主键。
3NF：满意2NF，而且所有非主属性不传递依靠于主键。
- 什么是数据完整性? 分别简述三大类完整性的作用,且以实例阐明.
数据完整性：确保数据的正确性和一致性。
三大类完整性：
实体完整性：主键值唯一且非空。
参照完整性：外键值必须为null或匹配主表中的主键值。
域完整性：确保字段值符合界说域的规则。
数据库视图：基于基本表的虚拟表，不存储实际数据。
应用利益：
简化复杂查询。
提供数据安全性。
提供逻辑数据独立性。
视图应用示例
CREATE VIEW 门天生绩视图 AS
SELECT 门生.S#, 门生.姓名, 课程.C#, 课程.名称, 结果.GRADE
FROM 门生
JOIN 结果 ON 门生.S# = 结果.S#
JOIN 课程 ON 结果.C# = 课程.C#;
一、选择题（在每个小题四个备选答案中选出一个正确答案）（本大题共5小题，每小题1分，总计5分）
1、SQL语言中，用GRANT／REVOKE语句实现数据库的 （ ）。
A.并发控制 B.完整性控制 C.一致性控制 D.安全性控制
2、SQL Sever 是（ ）
A、层次数据库 B、网状数据库 C、关系数据库 D、树状数据库
3、在数据库技术中，数据模型作为一组面向计算机的概念集合，其三个构成部分不包罗（ ）
A、数据约束 B、数据结构 C、数据操作 D、数据处置处罚
4、数据库中，导致数据不一致的根本缘故起因是（ ）
A、数据量太大 B、数据安全性不高 C、数据冗余 D、数据完整性约束不强
5、jdbc数据操作组件里面，建立应用程序与数据库之间毗连的对象是（ ）
A、Connection对象 B、Command对象
C、DataAdapter对象 D、Result对象
二、填空题（本大题共10个空格，每个空格1分，总计10分）
1、包含在任何一个候选键中的属性称为主属性。
2、数据库内部体系结构中的三级模式包罗 外部模式、概念模式和内部模式 。
3、在SQL Sever 中，要求索引行的顺序与表中数据记载的物理顺序雷同的索引是
聚集索引。
4、两个事务处于互相等候状态而不能结束，这种现象称为 死锁 。使某个事务永远处于等候状态，而得不到执行的现象称为 饥饿。
5、DBMS对数据库的利用功能主要包罗: 查询、更新和删除 。
三、简答题（共3小题，第1、2小题每题4分，第三小题7分，共15分）
设有关系R、S、G和H , 其值如下：
求 1、 R1=R-S (2分) ，R2=RＵS（2分）；
2、R3=SxG（2分） ，R4=R⋈G（2分）；
3、R5=σD=9∧B=’d’（R）(3分) ; R6=π5,6,1(σ3=5(SXH)) （4分）
四、分析设计题（共2小题，第1小题5分，第2小题15分，共20分）
1、设有商店和顾客两个实体，“商店”有属性：商店编号、商店名、地点、电话，“顾客”有属性：顾客编号、姓名、地点、年龄、性别。假设一个商店有多个顾客购物，一个顾客可以到多个商店购物，顾客每次去商店购物有一个斲丧金额和日期，而且规定每个顾客在每个商店里每天最多斲丧一次。
（1）试画出E-R图，并注明属性和联系范例。（5分）
实体：
商店（商店编号、商店名、地点、电话）
顾客（顾客编号、姓名、地点、年龄、性别）
联系：
购物（商店编号、顾客编号、斲丧金额、日期）
2、假设要建立一个学校教务数据库，涉及的对象包罗门生的学号（s#）、门生姓名（sname）地点系(sdept)、系主任名(dhname)、课程号（c#）、结果(grade)。此中，一个学号能唯一地确定一个门生，学号中包含了门生地点的系号，一个系号唯一地确定一个系主任。一个门生可以选多门课程并取得对应的结果。它们可描述为一个关系模式：sd(s#, sname, sdept, dhname, cno, grade)。
（1）写出该关系模式的基本函数依靠，并写出主码；（5分）
关系模式：sd(s#, sname, sdept, dhname, c#, grade)
(1) 基本函数依靠和主码：
- s# → sname, sdept, dhname
- s#，c# → grade
- sdept → dhname
主码：(s#, c#)
（2）辨别关系是否满意2NF？简单阐明理由，假如不满意，请将其分解成满意2NF的关系模式集。（5分）
不满意2NF，由于存在部分依靠：
s# → sname, sdept, dhname
分解成2NF模式集：
门生（s#, sname, sdept, dhname）
结果（s#, c#, grade）
系（sdept, dhname）
（3）辨别关系是否满意3NF？简单阐明理由,假如不满意，请将其分解成满意3NF的关系模式集,并简单阐明理由。（5分）
不满意3NF，由于存在传递依靠：
s# → sdept → dhname
分解成3NF模式集：
门生（s#, sname, sdept）
系（sdept, dhname）
结果（s#, c#, grade）
CREATE TABLE 门生 (
s# INT PRIMARY KEY,
sname VARCHAR(50),
sdept INT
);
CREATE TABLE 系 (
sdept INT PRIMARY KEY,
dhname VARCHAR(50)
);
CREATE TABLE 结果 (
s# INT,
c# INT,
grade INT,
PRIMARY KEY (s#, c#),
FOREIGN KEY (s#) REFERENCES 门生(s#),
FOREIGN KEY (sdept) REFERENCES 系(sdept)
);
五、程序题（本大题共5小题，第1小题14分，第2、3、4、5每小题9分，共计45分）
门生管理数据库有三个表，分别是门生表，课程表和结果表。三个表的关系模式如下：
门生表（学号，姓名，性别，出生年月，专业代码，班级）
课程表（课程号，课程名，学时）
结果表（学号，课程，分数）
（1）简单查询门生表中全体门生学号、姓名、班级。（1分）
（2）简单查询门生所学课程的最高分数。（1分）
Select 学号,max(分数) as 最高分数 from 结果表 group by 学号
（3）查询按学号递增的顺序显示门生的基本信息。（1分）
（4）查询门生表中姓张的门生的学号和姓名。（1分）
Where left(姓名,1)=’张’
Where 姓名 like ’张%’
Where substr(姓名，1，1)=’张’
（5）查询门生表中所有女同学的基本信息。（1分）
（6）分组查询各个同学的匀称分数（3分）
Select 学号,avg(分数) as 匀称分数 from 结果表 group by 学号
（7）利用嵌套查询，查询学习了“数据库”课程的门生的学号和姓名。（3分）
门生表（学号，姓名，性别，出生年月，专业代码，班级）
课程表（课程号，课程名，学时）
结果表（学号，课程，分数）
Select 学号,姓名 from 门生表 where 学号 in(select 学号 from 结果表 where 课程号=(select 课程号 from 课程表 where 课程名称=’数据库’))
（8）查询出生日期在2000年1月1日到2000年12月31日之间的所有门生的学号、姓名和出生日期，并按递增顺序排列。（3分）
Select 学号,姓名,出生日期 from 门生表 where year(出生日期)=2000
Select 学号,姓名,出生日期 from 门生表 where 出生日期>=’2000-1-1’ and 出生日期<=’2000-12-31’
Select 学号,姓名,出生日期 from 门生表 where 出生日期 between ’2000-1-1’ and ’2000-12-31’
-- (1) 简单查询门生表中全体门生的学号、姓名、班级
SELECT 学号, 姓名, 班级 FROM 门生表;
-- (2) 简单查询门生所学课程的最高分数
SELECT 学号, MAX(分数) AS 最高分数 FROM 结果表 GROUP BY 学号;
-- (3) 查询按学号递增的顺序显示门生的基本信息
SELECT * FROM 门生表 ORDER BY 学号;
-- (4) 查询门生表中姓张的门生的学号和姓名
SELECT 学号, 姓名 FROM 门生表 WHERE 姓名 LIKE '张%';
-- (5) 查询门生表中所有女同学的基本信息
SELECT * FROM 门生表 WHERE 性别 = '女';
-- (6) 分组查询各个同学的匀称分数
SELECT 学号, AVG(分数) AS 匀称分数 FROM 结果表 GROUP BY 学号;
-- (7) 利用嵌套查询，查询学习了“数据库”课程的门生的学号和姓名
SELECT 学号, 姓名
FROM 门生表
WHERE 学号 IN (
SELECT 学号
FROM 结果表
WHERE 课程号 = (SELECT 课程号 FROM 课程表 WHERE 课程名 = '数据库')
);
-- (8) 查询出生日期在2000年1月1日到2000年12月31日之间的所有门生的学号、姓名和出生日期，并按递增顺序排列
SELECT 学号, 姓名, 出生年月
FROM 门生表
WHERE 出生年月 BETWEEN '2000-01-01' AND '2000-12-31'
ORDER BY 出生年月 ASC;
- 在门生管理数据库上创建一个存储过程SCG_name，其实现的功能是根据某门生的学号和课程号返回门生的姓名、所学课程的课程名和分数。假设字段的范例和长度为：学号是char(9)，姓名是char(10)、课程名是char(16)、结果是int。（9分）
门生管理数据库有三个表，分别是门生表，课程表和结果表。三个表的关系模式如下：
门生表（学号，姓名，性别，出生年月，专业代码，班级）
课程表（课程号，课程名，学时）
结果表（学号，课程号，分数）
Create proc SCG_name(@xh char(9),@kch char(5),@xm char(10) output, @kcm char(16) output,@cj int output)
Begin
Select @xm=姓名 from 门生表 where 学号=@xh
Select @kch=课程名 from 课程表 where 课程号=@kch
Select @cj=分数 from 结果表 where 学号=@xh and 课程号=@kch
end
Create proc SCG_name(IN xh char(9), IN kch char(5),OUT xm char(10) , OUT kcm char(16), OUT cj int )
Begin
Select xm=姓名 from 门生表 where 学号=xh
Select kch=课程名 from 课程表 where 课程号=kch
Select cj=分数 from 结果表 where 学号=xh and 课程号=kch
End
- 在课程表上创建一个插入范例的触发器TR_CLASSH_CHECK，当在表中插入数据时，触发检查学时数是否在0和64之间，不是的话提示堕落。（9分）
CREATE TRIGGER TR_CLASSH_CHECK
ON 课程表
FOR INSERT
AS
BEGIN
DECLARE @学时 INT;
SELECT @学时 = 学时 FROM inserted;
IF @学时 NOT BETWEEN 0 AND 64
BEGIN
PRINT '你插入的学时不在0~64之间！';
ROLLBACK TRANSACTION;
END;
END;
- 创建事务：界说一个显式事务trs1，在门生表中删除学号为‘202101001’的门生信息，假如堕落则并回滚事务，否则提交事务。（9分）
begin transaction trs1 2分
delete from s where 学号=’202101001’ 2分
IF @@ERROR<>0 2分
BEGIN
PRINT ‘删除门生信息表时出现错误’
ROLLBACK TRANSACTION /*回滚事务 */ 2分
END
ELSE
commit transaction trs1 /*提交事务 */ 1分
5 创建一个游标CC2，用于查询课程关系中的学分>=3的课程所有数据记载。（9分）
USE 门生管理 /* 打开并利用名为JXGL的数据库 */
GO /* 结束前一个批处置处罚 */
DECLARE CC2 CURSOR /* 界说游标CC2，默以为FORWARD_ONLD游标*/
FOR SELECT * FROM 课程 where 学时>=3;
OPEN CC2; /* 打开游标 */
FETCH NEXT FROM CC2; /* 读取第1行数据 */
WHILE @@FETCH_STATUS=0 /*全局变量
BEGIN
FETCH NEXT FROM CC2;
END
CLOSE CC2; /* 关闭游标 */
DEALLOCATE CC2; /* 删除不再利用的游标 */
GO
门生管理数据库有三个表，分别是门生表，课程表和结果表。三个表的关系模式如下：
门生表（学号，姓名，性别，出生年月，专业代码，班级）
课程表（课程号，课程名，学时）
结果表（学号，课程，分数）
CREATE TRIGGER TR_CLASSH_CHECK
ON 课程表 FOR INSERT
AS
DECLARE @课程表_classh tinyint
SELECT @课程表_classh= 学时 FROM inserted
IF (@课程表_classh NOT BETWEEN 0 and 4 )
PRINT '你插入的学时不在0~64之间！'
rollback
GO
5、创建一个游标，用于查询结果表关系中的所有数据记载。 (本小题共9分)
（1）简单查询门生表中全体门生的学号和姓名和专业代码。（1分）
（2）简单查询门生所学课程的最高分数。（1分）
（3）查询按学号递增的顺序显示门生的基本信息。（1分）
（4）查询门生表中姓李的门生的学号和姓名。（1分）
（5）查询结果表中所有分数大于60分的门生的学号。（1分）
（6）查询2000年1月1日以后出生的所有女同学的学号和姓名（3分）
（7）查询学号为202101001的同学地点班的男同学的学号和姓名（3分）
（8）查询出生日期在2000年1月1日到2000年12月31日之间的所有门生的学号、姓名和出生日期，并按递减顺序排列。（3分）
2、建立一个存储过程pro1, 完成按指定课程名和姓名条件,查出出该人该课程的结果情况：学号,姓名,课程名称,结果假设字段的范例和长度为：姓名char(10)、课程名是char(16)、（9分）
3、在门生表上创建一个插入范例的触发器TR_SBIRTH_CHECK，当在表中插入数据时，触发检查出生日期是否在1996-01-01和2000-12-31之间，不是的话提示堕落。（9分）
4、创建事务：界说一个显式事务TRS1，在门生表中删除姓名为‘李四’学号为‘202001001’的门生信息，假如堕落则并回滚事务，否则提交事务。（9分）
5、在门生管理数据库上创建一个用户自界说表值函数S_table，用于返回某班门生关系表。此中，班级的数据范例和长度为VACHAR(7)。（9分）
-- 1. 查询其他示例
-- (1) 查询门生表中全体门生的学号、姓名和专业代码
SELECT 学号, 姓名, 专业代码 FROM 门生表;
-- (2) 简单查询门生所学课程的最高分数
SELECT 学号, MAX(分数) AS 最高分数 FROM 结果表 GROUP BY 学号;
-- (3) 查询按学号递增的顺序显示门生的基本信息
SELECT * FROM 门生表 ORDER BY 学号;
-- (4) 查询门生表中姓李的门生的学号和姓名
SELECT 学号, 姓名 FROM 门生表 WHERE 姓名 LIKE '李%';
-- (5) 查询结果表中所有分数大于60分的门生的学号
SELECT 学号 FROM 结果表 WHERE 分数 > 60;
-- (6) 查询2000年1月1日以后出生的所有女同学的学号和姓名
SELECT 学号, 姓名 FROM 门生表 WHERE 性别 = '女' AND 出生年月 > '2000-01-01';
-- (7) 查询学号为202101001的同学地点班的男同学的学号和姓名
SELECT 学号, 姓名 FROM 门生表 WHERE 班级 = (SELECT 班级 FROM 门生表 WHERE 学号 = '202101001') AND 性别 = '男';
-- (8) 查询出生日期在2000年1月1日到2000年12月31日之间的所有门生的学号、姓名和出生日期，并按递减顺序排列
SELECT 学号, 姓名, 出生年月 FROM 门生表 WHERE 出生年月 BETWEEN '2000-01-01' AND '2000-12-31' ORDER BY 出生年月 DESC;
-- 2. 存储过程 `pro1`
CREATE PROCEDURE pro1
@姓名 CHAR(10),
@课程名 CHAR(16)
AS
BEGIN
SELECT 学号, 姓名, 课程名, 分数
FROM 门生表
JOIN 结果表 ON 门生表.学号 = 结果表.学号
JOIN 课程表 ON 结果表.课程号 = 课程表.课程号
WHERE 门生表.姓名 = @姓名 AND 课程表.课程名 = @课程名;
END;
-- 3. 插入范例的触发器 `TR_SBIRTH_CHECK`
CREATE TRIGGER TR_SBIRTH_CHECK
ON 门生表
FOR INSERT
AS
BEGIN
DECLARE @出生年月 DATE;
SELECT @出生年月 = 出生年月 FROM inserted;
IF @出生年月 NOT BETWEEN '1996-01-01' AND '2000-12-31'
BEGIN
PRINT '插入的出生日期不在1996-01-01和2000-12-31之间！';
ROLLBACK TRANSACTION;
END;
END;
-- 4. 显式事务 `TRS1`
BEGIN TRANSACTION TRS1;
DELETE FROM 门生表 WHERE 学号 = '202001001' AND 姓名 = '李四';
IF @@ERROR <> 0
BEGIN
PRINT '删除门生信息表时出现错误';
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
COMMIT TRANSACTION TRS1;
END;
-- 5. 用户自界说表值函数 `S_table`
CREATE FUNCTION S_table(@班级 VARCHAR(7))
RETURNS TABLE
AS
RETURN (
SELECT * FROM 门生表 WHERE 班级 = @班级
);
1．关于实体A和B之间的一对一联系理解正确的是（ ）
A 实体A中每个实例在实体B中必须有一个与之关联 B 实体A中每个实例在实体B中最少有一个与之关联
C 在实体A中的每个实例在实体B中最多有一个与之关联D 实体A中每个实例在实体B中可以有多个之与关联
2. 在数据库的三级模式结构中,面对用户的是（ ）
A 内模式 B 模式C 外模式 D 视图
3.下列不属于数据库管理系统的数据界说功能的命令是（ ）
A alter table B create table C select D drop table
4. 在实体-联系模型中主要涉及到的三个方面是（ ）
A 实体、属性、联系 B 数据、实体、属性C 数据、属性、方法 D 实体、变乱、方法
5.下列不是数据技术中用到的组织层数据模型的是（ ）
A 逻辑型 B 层次型 C 网状型 D 关系型
6. 传统的关系运算不包罗（ ） A 并 B 交 C 除 D 广义笛卡儿积
7. 在关系型数据库中，下列哪项不属于数据完整性概念（ ）
A 实体完整性 B 参照完整性 C 查询完整性 D 用户自界说的完整性
8.下列关于主码和候选码说法不正确的是（ ） A 在此个关系上可以有多个候选码B 一个关系中只能有一个主码C 主码和候选码不能同时存在于一个关系中D 主码可以唯一确定一个元组
9. SQL的四大功能是（ ）
A 界说功能、控制功能、查询功能、利用功能B 界说功能、查询功能、修改功能、更新功能
C 查询功能、删除功能、更新功能、插入功能D 查询功能、修改功能、控制功能、删除功能
10. 在界说表时可以界说列的完整性约束，下列哪项不是完整性约束的界说（ ）
A WHERE B CHECK C PRIMARY KEY D NOT NULL
11．对工人表添加一个约束，限定工资在[1000-3000]之间，正确的命令是（ ）
A create table 工人 add constraint kk check (工资>=1000 and <3000)
B alter table 工人 add constraint kk check (工资 between 1000 and 3000)
C create table 工人 add constraint kk check (between工资>=1000 and工资<=3000)
D alter table 工人 add constraint kk check (between工资>=1000 and工资<=3000)
12. 在数据库建立索引的目标是（ ）
A 进步插入数据的速度 B 进步数据查询速度C 进步数据更新的速度 D 进步数据的删除速度
13. 数据库设计中的结构设计主要包罗概念结构设计,逻辑结构设计和（ ）
A 功能设计 B 事务设计 C 程序设计 D 物理结构设计
14. 下列有关视图说法错误的是（ ） A 外模式对应到数据库中的概念就是视图B 视图是数据库中的一个对象C 视图是提供给用户以多种角度观察数据库中数据的一种机制D 视图就是基本表
15. 设计数据库关系模式时,假如存在非主属性对主码部分函数依靠,如(学号,课程号) 姓名,则它肯定不满意（ ）范式 A 1NF和2NF B 3NF C 1NF D 2NF
三、简单应用题（10小题，每小题4分，共40分）
[ 有三个表,其结构分别如下：(1)门生(学号 char(7), 姓名 varchar(5),性别 char(2),出生日期 smalldatetime, 系别 char(2)）
(2)课程（课程号 char(6), 课程名称 char(20), 学分 tinyint, 任课老师 char(10)）
(3)结果( 学号 char(7), 课程号 char(6)， 结果 tinyint)
如有需要，以下各小题的数据来源参考以上各表 ]
1. 利用SQL Server命令建立“图书表”，其结构如下：
书号：统一字符编码定长，长度为6，主码; 书名:统一字符编码变长型，长度为30，非空;
作者：平凡编码定长字符，长度为10，非空；出版日期：小日期型;
订价:定点小数，整数3位，小数1位.2. 请查询出门生表中的学号,姓名,出生日期数据
3. 查出所有姓张的门生情况4 查出哪些门生选了课程号为“C05”的课程.
5 在结果有中查出选了课程号为c01,c02,c03课程的记载6 求选c05课程的门生人数
7 向门生表中添加一个新生数据（李勇，男，1988-2-12 ,计算机系）
8 查询出选课门数最多的前2名的情况（姓名，系别，选课门数）
9 用子查询实现：查询出数学系的结果在80分以上的门生的数据（学号，姓名，课程号，结果）
10 把“信息系”的门生的“计算机文化学”课程的结果加上5分
假设有三个表，其结构分别如下：
门生表 (学号 char(7), 姓名 varchar(5), 性别 char(2), 出生日期 smalldatetime, 系别 char(2))
课程表 (课程号 char(6), 课程名称 char(20), 学分 tinyint, 任课老师 char(10))
结果表 (学号 char(7), 课程号 char(6), 结果 tinyint)
利用SQL Server命令建立“图书表”
CREATE TABLE 图书 (
书号 CHAR(6) PRIMARY KEY,
书名 VARCHAR(30) NOT NULL,
作者 CHAR(10) NOT NULL,
出版日期 SMALLDATETIME,
订价 DECIMAL(3, 1)
);
查询出门生表中的学号, 姓名, 出生日期数据
SELECT 学号, 姓名, 出生日期 FROM 门生表;
查出所有姓张的门生情况
SELECT * FROM 门生表 WHERE 姓名 LIKE '张%';
查出哪些门生选了课程号为“C05”的课程
SELECT * FROM 门生表
WHERE 学号 IN (SELECT 学号 FROM 结果表 WHERE 课程号 = 'C05');
在结果表中查出选了课程号为c01, c02, c03课程的记载
SELECT * FROM 结果表 WHERE 课程号 IN ('c01', 'c02', 'c03');
求选c05课程的门生人数
SELECT COUNT(DISTINCT 学号) AS 门生人数 FROM 结果表 WHERE 课程号 = 'C05';
向门生表中添加一个新生数据（李勇，男，1988-2-12, 计算机系）
INSERT INTO 门生表 (学号, 姓名, 性别, 出生日期, 系别)
VALUES ('新生学号', '李勇', '男', '1988-02-12', '计算机系');
查询出选课门数最多的前2名的情况（姓名，系别，选课门数）
SELECT TOP 2 姓名, 系别, COUNT(课程号) AS 选课门数
FROM 门生表
JOIN 结果表 ON 门生表.学号 = 结果表.学号
GROUP BY 姓名, 系别
ORDER BY 选课门数 DESC;
用子查询实现：查询出数学系的结果在80分以上的门生的数据（学号，姓名，课程号，结果）
SELECT 学号, 姓名, 课程号, 结果
FROM 门生表
JOIN 结果表 ON 门生表.学号 = 结果表.学号
WHERE 系别 = '数学系' AND 结果 > 80;
把“信息系”的门生的“计算机文化学”课程的结果加上5分
UPDATE 结果表
SET 结果 = 结果 + 5
WHERE 学号 IN (
SELECT 学号 FROM 门生表 WHERE 系别 = '信息系'
) AND 课程号 = (SELECT 课程号 FROM 课程表 WHERE 课程名称 = '计算机文化学');
四、综合应用（共3小题，共30分）
1. (8分) 假设要建立一个企业数据库，该企业有多个下属单位，每一单位有多个职工，一个职工仅隶属于一个单位，且一个职工仅在一个工程中工作，但一个工程中有很多职工参加工作，有多个供应商为各个工程供应不同设备。单位的属性有：单位名、电话。职工的属性有：职工号、姓名、性别。设备的属性有：设备号、设备名、产地。供应商的属性有：姓名、电话。工程的属性有：工程号、地点。请完成如下处置处罚：
（1）设计满意上述要求的E-R图。（3分）
（2）将该E-R图转换为等价的关系模式，并标示出主码（5分）
2 (10分) 假如执行以下命令,可以返回每个课程的结果等级:
select 学号,结果,dbo.grade(结果) from 结果
此中grade函数返回结果的等级,90及以上为良好,[80,90)为良好,[70,80)为中等,[60,70)为及格,60以下为不及格. 请完成grade(@cj int)函数的建立,写出建立本函数的完整命令.
(2) 关系模式
单位(单位名, 电话) [单位名]
职工(职工号, 姓名, 性别, 单位名) [职工号] (单位名 -> 单位(单位名))
工程(工程号, 地点) [工程号]
设备(设备号, 设备名, 产地) [设备号]
供应商(姓名, 电话) [姓名]
工作(职工号, 工程号) [职工号, 工程号] (职工号 -> 职工(职工号), 工程号 -> 工程(工程号))
一、单项选择题( 共15小题，每小题2分，共30分)
1．下列有关数据库的说法不正确的是（ ） A 数据库是存放数据的仓库B 数据库是存储在计算机中有组织的、可共享的大量数据的集合C 数据库有三个基本特点是：永久存储、有组织、可共享
D 数据库就是数据库管理系统
2. 数据库管理系统的主要功能不包罗( ) A 数据界说功能 B 数据利用功能 C 数据查询功能D 数据压缩功能
3．下列有关数据库概念中，说法正确的是（ ） A 数据库系统主要是由数据库管理系统、数据库、应用程序和数据管理员构成B 数据库管理系统并不一定需要操作系统的支撑C 数据库中的基本单位是数据项
D 一个数据库中只能存放一个表
4．实体之间的联系常用有哪几种范例（ ） A 一对一、一对多、多对多 B 二对一，一对多，一对一
C 多对多，多对一，三对一 D 二对一，一对一，三对一
5. 下列哪项不属于数据库的三级模式之一（ ） A 外模式 B 模式 C 内模式 D 关联模式
6. 数据库中专门的关系运算包罗（ ） A 选择、毗连、投影 B 交运算、毗连、乘积
C 选择、毗连、查询 D 选择、更新，查询
7．下列哪项不属于关系模型中的术语（ ） A层次 B 值域 C 元组 D 属性
8. 下列有关实体完整性说法正确的是（ ） A 实体完整性无法保证关系中的实体唯一性B 实体完整性要求表中必须有主码C 实体完整性可以让表中存在主码雷同的多个记载D 实体完整性对表的记载无任何限定
9．下列不属于SQL支持的字符串型的是（ ） A char(n) B varchar(n) C text D bit
10 在界说表时可以界说列的完整性约束，下列哪项不是完整性约束的界说（ ）
A UNIQUE B FOREIGN KEY C PRIMARY KEY D WHERE
11. 为工人表的“职工号”列添加一个唯一约束 ( )
A alter table 工人 add constraint kk unique(职工号)B alter 工人 add constraint kk unique(职工号)
C set table 工人 add constraint kk unique(职工号)D update table 工人 add constraint kk unique(职工号)
12．索引一般采用B树结构，它由索引项构成，此中索引项由（ ）构成
A 表的一个列的值 B 表的一个或多个列的值C 表的多个列的值 D 表的全部列
13下列有关索引说法不正确的是( )
A 索引分为聚集索引和非聚集索引B 非聚集索引不对数据举行真正物理性的排序
C 索引一般采用B树结构D 在唯一索引之前，数据表中不能有重复值
14下列有关视图说法错误的是（ ） A 视图中的显示数据始终与基本表的数据保持一致B 视图是数据库中的一个对象C 视图是从基本表中选出来的数据构成的一个逻辑窗口D 视图可以脱离基本表而单独存在
二、简单应用题（10小题，每小题5分，共50分）
[ 有三个表,其结构分别如下：
(1)门生（学号 char(7), 姓名 varchar(5),性别 char(2),生日 smalldatetime, 系别 char(2)）
(2)课程（课程号 char(6), 课程名称 char(20), 学分 tinyint, 任课老师 char(10)）
(3)结果( 学号 char(7), 课程号 char(6)， 结果 tinyint)
如有需要，以下各小题的数据来源参考以上各表结构 ]
1 利用SQL Server命令建立“书店表”，其结构如下:书店号: 统一字符编码定长，长度6，主码；店名：统一字符编码可变型，长度为30，非空；地点：平凡编码可变长字符，长度40；电话：平凡编码定长字符型，12位
2 查询出计算机系的全部男生3 查询出在1992年后出生的门生情况4 查出选了c03课程的门生的学号,姓名,地点系名5 查出所有姓张,李,王的门生情况 6求c01课程的结果前两名记载
7 向课程表中添加一门新课程（C11,计算机文化基础，3，张林）
8 查询出门生人数最多的系（系名，人数）
9 用毗连查询实现：查询出“数据结构”课程结果最高的门生数据（姓名，系别，性别，结果）
10 把选课人数最小的课程的学分淘汰1分
假设有三个表，其结构分别如下：
门生（学号 char(7), 姓名 varchar(5), 性别 char(2), 生日 smalldatetime, 系别 char(2)）
课程（课程号 char(6), 课程名称 char(20), 学分 tinyint, 任课老师 char(10)）
结果（学号 char(7), 课程号 char(6)， 结果 tinyint）
利用SQL Server命令建立“书店表”
CREATE TABLE 书店表 (
书店号 CHAR(6) PRIMARY KEY,
店名 VARCHAR(30) NOT NULL,
地点 VARCHAR(40),
电话 CHAR(12)
);
查询出计算机系的全部男生
SELECT * FROM 门生
WHERE 性别 = '男' AND 系别 = '计算机系';
查询出在1992年后出生的门生情况
SELECT * FROM 门生
WHERE 生日 > '1992-01-01';
查出选了c03课程的门生的学号,姓名,地点系名
SELECT 门生.学号, 门生.姓名, 门生.系别
FROM 门生
JOIN 结果 ON 门生.学号 = 结果.学号
WHERE 结果.课程号 = 'c03';
查出所有姓张,李,王的门生情况
SELECT * FROM 门生
WHERE 姓名 LIKE '张%' OR 姓名 LIKE '李%' OR 姓名 LIKE '王%';
求c01课程的结果前两名记载
SELECT TOP 2 学号, 结果
FROM 结果
WHERE 课程号 = 'c01'
ORDER BY 结果 DESC;
向课程表中添加一门新课程（C11, 计算机文化基础，3，张林）
INSERT INTO 课程 (课程号, 课程名称, 学分, 任课老师)
VALUES ('C11', '计算机文化基础', 3, '张林');
查询出门生人数最多的系（系名，人数）
SELECT 系别, COUNT(*) AS 人数
FROM 门生
GROUP BY 系别
ORDER BY 人数 DESC
LIMIT 1;
用毗连查询实现：查询出“数据结构”课程结果最高的门生数据（姓名，系别，性别，结果）
SELECT 门生.姓名, 门生.系别, 门生.性别, 结果.结果
FROM 门生
JOIN 结果 ON 门生.学号 = 结果.学号
JOIN 课程 ON 结果.课程号 = 课程.课程号
WHERE 课程.课程名称 = '数据结构'
ORDER BY 结果.结果 DESC
LIMIT 1;
把选课人数最小的课程的学分淘汰1分
UPDATE 课程
SET 学分 = 学分 - 1
WHERE 课程号 = (
SELECT 课程号
FROM 结果
GROUP BY 课程号
ORDER BY COUNT(学号)
LIMIT 1
);
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
