做网站去哪个平台,网站建设及托管合同模板,住房和建设厅官方网站,美容美发网站模板有一个关系模式#xff1a;工程关系#xff08;工程号#xff0c;工程名称#xff0c;职工号#xff0c;姓名#xff0c;聘期#xff0c;职务#xff0c;小时工资率#xff0c;工时#xff09;#xff0c;公司按照工时和小时工资率支付工资#xff0c;小时工资率由…有一个关系模式工程关系工程号工程名称职工号姓名聘期职务小时工资率工时公司按照工时和小时工资率支付工资小时工资率由职工的职务决定例如技术员的小时工资率与工程师不同。
分析该关系模式中属性间的函数依赖关系。
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’,112
(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 张麻子 试回答下列问题
该关系模式R存在哪些问题
· 数据冗余车间主任信息重复导致数据冗余。
· 更新异常更新车间主任信息时需要在多个记录中进行修改。
· 插入异常如果一个车间没有职工无法插入车间信息。
· 删除异常删除一个职工记录可能会丢失车间主任信息。
模式R中关键码是什么
关键码主键是 (职工编号, 日期)。因为每个职工每天只有一个日产量。
(3) 根据上述规定写出模式R的各属性的基本函数依赖和依赖的关键码
· 职工编号, 日期 → 日产量
· 职工编号 → 车间编号
· 车间编号 → 车间主任
(4) 说明R不是2NF的理由并把R分解成2NF模式集 职工表职工编号车间编号车间主任 生产表职工编号日期日产量 (5) 进而再分解成3NF模式集并说明理由。 在2NF基础上车间主任传递依赖于职工编号所在存在传递依赖所不是3NF 继续分解决为
职工表(职工编号车间编号,….)
车间表车间编号车间主任,….
生产表职工编号日期日产量,…
设关系模式RS#C#GRADETNAMETADDR其属性分别表示学生学号、选修课程的编号、成绩、任课教师姓名、教师地址等意义。 如果规定每个学生每学一门课只有一个成绩每门课只有一个任课教师每个教师只有一个地址此处不允许教师同名同姓。 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) 教师表TNAMETADDR,…. 分析
RS#C#GRADETNAMETADDR
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、3NF的定义。
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语言中用GRANTREVOKE语句实现数据库的 。
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、 R1R-S (2分) R2RS2分
2、R3SxG2分 R4R⋈G2分
3、R5σD9∧B’d’R(3分) ; R6π5,6,1(σ35(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, dhnames#c# → gradesdept → 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(姓名11)’张’
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 ERROR0 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_STATUS0 /*全局变量 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
);
