数据库期末题

解答题

【 创建数据库、表（含完整性约束）】

1：使用 SQL 语句在 MySQL 上创建教务数据库jw_db

create database if not exists jw_db;

2：使用 SQL 语句在 jw_db 数据库中创建4个关联关系，并按照要求建立完整性约束：学生表 S 、课程表 C、教师表 T 和选课表 SC，表结构如下。

3：使用 SQL 语句插入数据如下。

写出建库、建表和数据插入的 SQL 语句，并截图说明这些语句执行成功。

（表结构和数据省略，因为没截图）

use jw_db;

create table S(

Sno char(8) COMMENT '学号' PRIMARY KEY not NULL,

Sname char(10) COMMENT '姓名' NOT NULL,

SGender char(2) COMMENT '性别',

SAge int COMMENT '年龄',

SEnDate date COMMENT '入学日期'

);

插入记录：

insert into S values('20150203' , '张云杰' , '男',23,'2015-8-18');

insert into S values('20150305' , '赵明' , '女',22,'2015-8-18');

insert into S values('20150308' , '季然' , '女',22,'2015-8-18');

insert into S values('20150413' , '张天天' , '男',23,'2015-8-18');

insert into S values('20150208' , '李小圆' , '女',20,'2015-8-18');

insert into S values('20150601' , '王文宏' , '男',22,'2015-8-18');

create table T(

Tno char(8) COMMENT '教师编号' PRIMARY KEY not NULL,

Tname char(10) COMMENT '姓名' not null,

TAge int COMMENT '年龄',

TTitle char(10) COMMENT '职称'

);

插入数据：

insert into T values('20001242' , '孙平',40,'教授');

insert into T values('20012101' , '周明新',32,'讲师');

insert into T values('20032151' , '张军',37,'副教授');

insert into T values('20050520' , '王晓璇',38,'副教授');

insert into T values('20050521' , '刘云云',34,'副教授');

insert into T values('20050523' , '陈建',32,'讲师');

insert into T values('20071420' , '赵婷',28,'助教');

建立课程表C：

create table C(

Cno char(5) COMMENT '课程号' PRIMARY KEY not null ,

Cname char(10) COMMENT '课程名' not NULL,

Cclass char(2) COMMENT '课程类别' not NULL,

Tno char(8) COMMENT '教师编号' ,

FOREIGN KEY (Tno) REFERENCES T(Tno)

);

插入数据：

insert into C values('00011' , '计算机基础' ,'1','20050520');

insert into C values('00012' , '高等数学' ,'1','20001242');

insert into C values('00013' , '基础化学' ,'4','20071420');

insert into C values('00014' , '大学物理' ,'2','20050523');

insert into C values('00015' , '数据库' ,'3','20032151');

insert into C values('00016' , '数据结构' ,'2','20012101');

创建选课表SC：

create table SC(

Sno char(8) COMMENT '学号',

Cno char(5) COMMENT '课程号',

grade int COMMENT '成绩',

Tno char(8) COMMENT '教师编号' ,

PRIMARY KEY (sno,cno),

FOREIGN KEY (Tno) REFERENCES T(Tno),

FOREIGN KEY (Sno) REFERENCES S(Sno),

FOREIGN KEY (Cno) REFERENCES C(Cno)

);

插入数据：

insert into SC values('20150203' , '00011' , 83,'20050520');

insert into SC values('20150305' , '00011' , 78,'20050520');

insert into SC values('20150308' , '00011' , 81,'20050520');

insert into SC values('20150305' , '00012' , 75,'20001242');

insert into SC values('20150208' , '00011' , 88,'20050520');

insert into SC values('20150308' , '00012' , 90,'20001242');

【SQL语言设计】

用 SQL 语言完成以下设计：

（1）单科成绩大于 85 分的前5名学生姓名。

SELECT s.Sname from s,sc where s.Sno=sc.Sno and sc.grade>85 LIMIT 0,5;

（2）查询学过「王晓璇」老师授课的学生信息，输出其SNO、SName、CName和TName四列内容。

SELECT s.sno,s.Sname,c.Cname,t.Tname from s,sc,c,t where s.Sno=sc.Sno and sc.Cno=c.Cno and c.Tno=t.Tno and t.Tname='王晓璇';

（3）单科成绩大于本课程平均分的学生姓名

SELECT S.Sname

FROM S

JOIN SC ON S.Sno = SC.Sno

WHERE SC.grade > (

    SELECT AVG(grade)

    FROM SC

    WHERE Cno = '00011'

)

AND SC.Cno = '00011';

SELECT S.Sname

FROM S

JOIN SC ON S.Sno = SC.Sno

WHERE SC.grade > (

    SELECT AVG(grade)

    FROM SC

    WHERE Cno = '00012'

)

AND SC.Cno = '00012';

（4）查询出只选修两门课程的学生学号和姓名两列信息

SELECT s.sno,s.sname from s,sc where s.Sno=sc.sno group by sc.Sno HAVING count(*)=2;

（5）创建一个“不同等级课程的青年骨干教师授课人数情况”的视图，包括“课程级别”和“骨干教师数”两列信息，这里约定，青年骨干教师为年龄在 30～45（含 30，45）之间的副教授和教授。针对该视图完成下列查询：查询不同等级课程的青年骨干教师授课人数，输出“课程级别”和“骨干教师数”两列信息。

CREATE VIEW YoungEliteTeacherCourseCount AS

SELECT c.Cclass AS 课程类别,

       COUNT(DISTINCT t.Tno) AS 骨干教师数

FROM C c

JOIN T t ON c.Tno = t.Tno

WHERE t.Ttitle IN ('副教授', '教授')

AND t.Tage BETWEEN 30 AND 45

GROUP BY c.Cclass;

（6）为表S增加完整性约束，要求年龄的范围为小于等于30岁。

ALTER TABLE S ADD CONSTRAINT C1 CHECK (Sage <=30);

【编程操作】

答题要求附上对应设计的SQL程序和测试语句，并附上运行截图！

1.试编写一个有参存储过程proc1，要求实现以下功能。

以参数ttname为教师姓名条件来查询该教师的授课课程号和对应学生最高成绩。

最后调用设计好的存储过程proc1，查询教师“王晓璇”的授课信息，输出“课程号”和“最高成绩”两列内容。

答题要求附上对象设计的SQL程序和调用文本，并粘贴必要的运行结果截图。

CREATE PROCEDURE proc1(in ttname char(10))

BEGIN

select c.Cno, sc.grade

from s,sc,c,t

where s.sno=sc.sno and sc.cno in

(select c.cno from c,t where c.Tno=t.Tno and t.tname=ttname)

order by sc.grade desc

limit 0,1;

END;

call proc1('王晓璇');

2.试编写一个触发器trig，要求实现以下功能。

向C表中添加新课（课号和课名）时， TNo教师编号字段值自动填入对应SC表中按教师编号分组成绩平均分最高的教师编号。   

请测试追加新课程“00017，操作系统”的效果。(切记：测试操作请放在最后进行！！！)

       答题要求附上对应设计的SQL程序和测试语句，并附上运行截图！

DELIMITER //

CREATE TRIGGER trig

BEFORE INSERT ON c FOR EACH ROW

BEGIN

    SELECT t.Tno INTO @tn FROM sc, t

    WHERE NEW.Tno = t.Tno AND NEW.Cno = sc.Cno

    GROUP BY t.Tno ORDER BY AVG(sc.grade) LIMIT 1;

    SET NEW.Tno = @tn;

END//

DELIMITER ;

insert into C values('00017', '操作系统', '2', '20050520');

【分析设计】

1.已知关系模式R<U，F>，其中

U={A，B，C，D，E，I}，F={A→D，AB→E，BI→E，CD→I，E→I},

计算(AE)+   ,要求写出算法的每个步骤。

第一步：初始（AE）+=AE。

第二步：

①对（AE）+中的A，存在A→D，所以(AE)+=（AE）+  U D=AED；

②对（AE）+中的E，存在E→I，所以(AE)+=（AE）+  U I=AEDI；

③对（AE）+中的I，因为在函数依赖集F中，I不单独出现在任何函数依赖关系的左边，因此（AE）+将不会再扩大

④最终，（AE）+=AEDI。

2.已知关系模式R(U，F)，U=(A，B，C，D，E)，F={AB→C，C→A，BE→C，BC→D，ACD→B, AE→CD, A→E}，试求最小函数依赖集。

1.  右边单属性 将F中每个函数依赖的右部变成单属性。

F={AB→C，C→A，BE→C，BC→D，ACD→B, AE→C,AE→D, A→E} （2）左部多余属性 AB→C：验证属性B是不是多余属性。计算A+=AECDB，A+包含属性C，因此，B是左部多余的属性，可以去掉，A→C

BE→C：验证属性E是不是多余属性。计算E+=E，不包含属性C，因此，E不是左部多余的属性；同理，B也不是左部多余属性。

BC→D: 验证属性B是不是多余属性，C+=ACEDB，包含D，所以B是左部多余属性，可以去掉，C→D；

ACD→B：同理，CD是左部多余属性，可以去掉，A→B;

AE→C：同理验证属性E是多余属性，可以去掉，A→C;

AE→D：同理验证属性E是多余属性，可以去掉，A→D

F={C→A,BE→C,C→D，A→B, A→C,A→D, A→E}

（3）冗余函数依赖A→D可以通过 A→C和C→D推出，因此可以去掉A→D。

所以Fmin= F={C→A,BE→C,C→D，A→B, A→C, A→E}

3.现有如下关系模式：

借阅（书号，书名，作者，出版社，读者号，读者，借阅日期，归还日期）；基本函数依赖集F={图书编号->（书名，作者名，出版社），读者编号->读者姓名，(图书编号，读者编号，借阅日期)->归还日期。

(1)读者编号是候选码吗？

 (2)写出该关系模式的主码。

(3)该关系模式中是否存在非主属性对码的部分函数依赖，如果存在，请写出一个。

(4)该关系模式满足第几范式并说明理由

  (1)

不是，因为读者编号的闭包为{读者编号，读者姓名}，不包括全部属性，即不能唯一地标识一个元组，所以它不是候选码。

 (2)

{图书编号、读者编号、借阅日期}

(3)

存在，例如读者编号->读者姓名、图书编号->（书名，作者名，出版社）

(4)

第一范式，因为每一个分量都不可以再分，但是因为存在非主属性部分依赖于码，所以不属于第二范式。综上，该关系模式满足第一范式。

4.今有三个事务的一个调度r3(B)r1(A)w3(B)r2(B)r2(A)w2(B)r1(B)w1(A)，该调度是冲突可串行化的调度吗?为什么?

r3(B)r1(A)w3(B)r2(B)r2(A)w2(B)r1(B)w1(A)是冲突可串行化调度，因为交换两个不冲突操作后w1(A)和r2(B)后，r3(B)r1(A)w3(B)w1(A)r2(A)w2(B)r1(B)r2(B)与原来调度的结果不一致。

五、（3分）设有商品表，结构和包含的数据如下表所示：

商品号	商品号	类别	单价
G001	ipad平板电脑	电脑	2800
G002	华为平板电脑	电脑	2000
G003	小米手机	手机	1500
G004	华为p4	手机	5000

有如下事务定义语句：

Start Transaction;  

Update 商品表 set单价=单价-500 Where类别=’手机’;

Update 商品表Set单价=单价+200 Where类别=’电脑’;

Rollback;

执行上述代码后。商品表中各商品’单价’列的值是:

商品号	单价
G001
G002
G003
G004

六、（3分）t1,t2是两个事务，图（a）、（b）、（c）给出了这两个事务的三种调度，这三种调度分别会出现什么问题？请说明。

1、第一种调度出现丢失修改的问题

因为两个事务T1和T2读入同一数据A并修改，T2的提交结果破坏了T1提交的结果，导致T1的修改被丢失。

2、出现不可重复读的问题

因为事务T1读取数据A后，事务T2对A执行更新操作，T1再一次读取A，无法再现前一次读取结果。

3、出现读“脏”数据的问题

事务T2修改数据A，并将其写回磁盘， 事务T1读取同一数据A后，T2由于某种原因被撤销，这时T2已修改过的数据A恢复原值，T1读到的数据A就与数据库中的数据不一致，T1读到的数据为“脏”数据，即不正确的数据