数据库理论与技术作业

2016-2017（2）湖北大学研究生课程考试试题

试卷说明：试卷满分100分，开卷考试，可在计算机上实际操作，考试时间180分钟。

一、简单应用题（30分）

根据学生-课程数据库的如下三个表:

(1)学生表：Student（Sno, Sname, Ssex, Sage, Sdept），属性分别为：学号、姓名、性别、年龄、所在系；

(2)课程表：Course(Cno, Cname, Cpno, Ccredit)，属性分别为：课程号、课程名、先修课的课程号、学分；

(3)学生选课表：SC(Sno, Cno, Grade)，属性分别为：学号、课程号、成绩，成绩是百分制。完成如下任务：

1、安排教学计划的老师需要知道Course中每门课程的课程号、课程名及其间接先修课的课程号和课程名，即他需要看到这样一个数据表：课程号、课程名、间接先修课课程号、间接先修课课程名，按课程号升序排序。若要在功能页面实现这个功能，请给出你的设计方案及对应的SQL语句（6分）。

select CONNECT_BY_ROOT(Cno) 课程号,CONNECT_BY_ROOT(Cname) 课程名,Cno 间接先修课程号,Cname 间接先修课程名 from Course

where LEVEL > 1

start with Cno in (select Cno from Course)

connect by Cno=prior Cpno

UNION select Cno,Cname,0,'无' FROM Course where CPNO=0

2、教务处老师需要查看每门课程的课程名，选课人数，平均分，最高分，最低分。但程序开发人员只会写简单的SQL语句，你作为数据库设计人员针对该要求用SQL脚本的形式给出你的设计结果（6分）。

SELECT https://www.360docs.net/doc/e39940170.html,ame 课程名,

COUNT (Student.Sno) 选课人数,

AVG(SC.Grade) 平均分,

MAX(Grade) 最高分,

MIN(Grade) 最低分

FROM

(

Course

LEFT JOIN SC ON https://www.360docs.net/doc/e39940170.html,o = https://www.360docs.net/doc/e39940170.html,o

)

LEFT JOIN Student ON SC.Sno = Student.Sno

GROUP https://www.360docs.net/doc/e39940170.html,ame;

3、请给出查询“数据结构”课程的所有各级后继课程的课程号、课程名、先修课的课程号、学分的SQL语句（6分）。

SELECT * FROM Course

START WITH Cname='数据结构'

CONNECT BY PRIOR Cno= Cpno;

4、某功能页面需要显示如下数据表格：

学生课程成绩表

使用pivot实现。

CREATE OR REPLACE

proceduretest_count(cur out sys_refcursor)

as

Cursor cursor is select DISTINCT Cno from SC;

ParastrSQL varchar2(6000); -- 主语句

state varchar2(600);

begin

for name in cursor LOOP

state:=state||https://www.360docs.net/doc/e39940170.html,O||',';

end LOOP;

state:=RTRIM(state,',');

ParastrSQL :='select * from

(selectSC.Sno,Student.Sname,https://www.360docs.net/doc/e39940170.html,o,SC.Grade

fromSC,Student

WHERE SC.Sno=Student.Sno)

pivot

(SUM(Grade) for Cno in ('||state||'))';

open cur for ParastrSQL;

endtest_count;

5、现在需要把百分制成绩转换为等级制成绩（100-90分为优秀、80-89分为良好、70-79分为中等、60-69分为较差、0-59分为不及格），查询结果为：学号、姓名、课程号、课程名、百分制成绩、等级制成绩，请给出相应的SQL语句（6分）。

SELECT Student.Sno,Student.Sname,https://www.360docs.net/doc/e39940170.html,o,https://www.360docs.net/doc/e39940170.html,ame,SC.Grade,

(CASE WHEN Grade>=90 THEN '优秀'

WHEN Grade>=80 THEN '良好'

WHEN Grade>=70 THEN '中等'

WHEN Grade>=60 THEN '较差'

ELSE '不及格' END)GRANK

FROM Student,Course,SC

WHERE Student.Sno=SC.Sno AND https://www.360docs.net/doc/e39940170.html,o=https://www.360docs.net/doc/e39940170.html,o;

二、简单设计题（20分）

1、（10分）某系统需要定义一些系统参数或系统开关，比如：系统允许访问时间、查询页面中数据表格显示数据记录的行数等等，但目前还不清楚有哪些系统参数或系统开关，随着系统的开发可能会不断的增加新的系统参数。请根据该需要设计一个系统参数表，给出建表语句，表名为：系统参数表。表名和字段名都使用中文。

CREATE TABLE 系统参数表

(

参数编号 VARCHAR(50) primary key,

参数名称 VARCHAR(30) UNIQUE not null,

参数内容 VARCHAR(300),

下一个参数编号 VARCHAR(50),

FOREIGN KEY (下一个参数编号) REFERENCES 系统参数表 (参数编号)

);

2、（10分）某管理机构要对相关单位进行测评，测评指标体系如下表所示，在使用过程中，该指标体系可能会增加某些新的列。为了保存指标体系的数据，请给出能够适应该需求变化的数据表设计，只写出关系模式即可，指出主外键。

评测结点(编号，父编号，标题，内容，分数，测评标准，评分细则及操作方法)

主键：编号

外键：父编号（自关联）

三、综合设计应用题（50分）

1、（20分）假设你被要求为一演出公司设计一网上门票销售系统。已知需求如下：

①用户在网上能看到各演出已销售票数、剩余票数、票价（假定一场演出的所有票价相同，也不分座号）。

②能够查询各用户的订票情况，包括如下信息：用户号、用户名、订票时间、演出场次、所订票数、送票地点。

根据以上基本需求，再考虑到如下问题：送票的方便性、票价定义的灵活性、订票优惠的灵活性、订购与退订、付款与退款等，设计出完整的E-R模型（包括实体、联系及属性）或类模型。

2、（30分）某单位的职工的基本信息主要有：职工号、姓名、性别、出生日期、身份证号、家庭住址、联系电话，职工的登陆信息主要有：登陆账号、登陆密码和证书摘要。这些数据项都非空。其类图如下图所示，请给出基于Oracle实现的相关SQL脚本，具体包括：（1）建表语句（10分）、（2）保证主码职工号修改的一致性（5分）、（3）保证两个表的对应数据同时删除（5分）、（4）保证两个表的对应数据同时插入（10分）。

（1）建表语句

create table 职工信息表(

职工号varchar2(50)primary key,

姓名varchar2(30) not null,

性别 char(3) not null,

出生日期date not null,

身份证char(18) UNIQUE not null,

联系电话varchar2(11) not null,

家庭住址varchar2(100) not null

)

create table 登陆信息表(

登陆账号varchar2(20) primary key,

职工号 varchar2(50) unique not null,

登陆密码varchar2(20) not null,

证书摘要 char(256) not null,

FOREIGN KEY (职工号) REFERENCES 职工信息表(职工号)ONDELETECASCADEDEFERRABLE

)

（2）保证主码职工号修改的一致性

CREATE OR REPLACE TRIGGER BIF_UPDATE_LIF 职工号 AFTER UPDATE OF 职工号ON 职工基本信息表

FOR EACH ROW

BEGIN

UPDATE 职工登陆信息

SET 职工号=:NEW.职工号

WHERE 职工号=:OLD.职工号;

END;

（3）保证两个表的对应数据同时删除

CREATE OR REPLACE TRIGGER BIF_DELETE_LIF

AFTER DELETE ON 登陆信息表

FOR EACH ROW

BEGIN

DELETE FROM 职工信息表

WHERE 职工号=:OLD.职工号;

END;

（4）保证两个表的对应数据同时插入

/*1创建与职工信息表结构相同的临时表user_temp*/ CREATE TABLE user_temp

AS SELECT * FROM 职工信息表 WHERE 1=2;

/*2 插入数据的存储过程*/

CREATE OR REPLACE PROCEDURE PR_INSERT_TEA_SEC(

职工号varchar2（50）,

姓名varchar2(20),

身份证char(18),

性别char(3),

出生日期date,

家庭住址varchar2(100),

联系电话varchar2(11),

登陆账号varchar2(20),

登陆密码varchar2(20),

证书摘要char(100),

P_RESULT OUT VARCHAR2(200)

BEGIN

P_RESULT:='';

--先插入user_temp

INSERT INTO user_temp

VALUES(登陆账号,职工号,登陆密码,证书摘要);

--再插入职工信息表

INSERT INTO 职工信息表VALUES

(职工号,姓名,性别,出生日期,身份证,家庭住址,联系电话); COMMIT;

--删除user_temp

DELETE FROM user_temp

WHERE 登陆账号=登陆账号;

COMMIT;

P_RESULT:='插入成功！';

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

P_RESULT:='插入时报错:'||SUBSTR(SQLERRM,1,200);

END;

/*3）插入职工信息表数据的触发器

——保证同时插入到登陆信息表，这样做的目的是防止绕过前台应用程序而直接在后台数据库操作时也能够保证同时插入。*/

CREATE OR REPLACE TRIGGER BIF_INSERT_LIF

AFTER INSERT ON 职工信息表

FOR EACH ROW

DECLARE

V_CNT INT;

BEGIN

SELECT COUNT(*) INTO V_CNT

FROM user_temp

WHERE 职工号=:NEW.职工号;

IF V_CNT=1 THEN

INSERT INTO 登陆信息表

SELECT * FROM user_temp

WHERE 职工号=:NEW.职工号;

ELSE

RAISE_APPLICATION_ERROR('-20020','必须同时插入！');

END IF;

END;