数据库理论与技术作业.docx

2016-2017（2）湖北大学研究生课程考试试题课程编号0711B00030812B0003课程名称数据库理论与设计数据库高级设计技术开课单位任课教师姓名学号试卷说明：试卷满分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=02、教务处老师需要查看每门课程的课程名，选课人数，平均分，最高分，最低分。但程序开发人员只会写简单的SQL语句，你作为数据库设计人员针对该要求用SQL脚本的形式给出你的设计结果（6分）。SELECT Course.Cname 课程名,COUNT (Student.Sno) 选课人数, AVG(SC.Grade) 平均分, MAX(Grade) 最高分, MIN(Grade) 最低分FROM(CourseLEFT JOIN SC ON Course.Cno = SC.Cno)LEFT JOIN Student ON SC.Sno = Student.SnoGROUP BY Course.Cname;3、请给出查询“数据结构”课程的所有各级后继课程的课程号、课程名、先修课的课程号、学分的SQL语句（6分）。 SELECT * FROM Course START WITH Cname=数据结构 CONNECT BY PRIOR Cno= Cpno;4、某功能页面需要显示如下数据表格：学生课程成绩表学号姓名1号课程成绩2号课程成绩3号课程成绩请给出你的设计方案及对应的SQL语句（6分）。使用pivot实现。CREATE OR REPLACE procedure test_count(cur out sys_refcursor)asCursor cursor is select DISTINCT Cno from SC;ParastrSQL varchar2(6000); - 主语句state varchar2(600);begin for name in cursor LOOP state:=state|name.CNO|,; end LOOP;state:=RTRIM(state,); ParastrSQL :=select * from (select SC.Sno,Student.Sname,SC.Cno,SC.Grade from SC,Student WHERE SC.Sno=Student.Sno) pivot (SUM(Grade) for Cno in (|state|);open cur for ParastrSQL;end test_count;5、现在需要把百分制成绩转换为等级制成绩（100-90分为优秀、80-89分为良好、70-79分为中等、60-69分为较差、0-59分为不及格），查询结果为：学号、姓名、课程号、课程名、百分制成绩、等级制成绩，请给出相应的SQL语句（6分）。SELECT Student.Sno,Student.Sname,Course.Cno,Course.Cname,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 Course.Cno=SC.Cno;二、简单设计题（20分）1、（10分）某系统需要定义一些系统参数或系统开关，比如：系统允许访问时间、查询页面中数据表格显示数据记录的行数等等，但目前还不清楚有哪些系统参数或系统开关，随着系统的开发可能会不断的增加新的系统参数。请根据该需要设计一个系统参数表，给出建表语句，表名为：系统参数表。表名和字段名都使用中文。CREATE TABLE 系统参数表( 参数编号 VARCHAR(50) primary key, 参数名称 VARCHAR(30) UNIQUE not null, 参数内容 VARCHAR(300), 下一个参数编号 VARCHAR(50), FOREIGN KEY (下一个参数编号) REFERENCES 系统参数表 (参数编号);2、（10分）某管理机构要对相关单位进行测评，测评指标体系如下表所示，在使用过程中，该指标体系可能会增加某些新的列。为了保存指标体系的数据，请给出能够适应该需求变化的数据表设计，只写出关系模式即可，指出主外键。测评指标体系（100分）测评项目指标名称测评内容测评标准评分细则及操作方法-1 AAAA（9分）-1 AAAA1（3分）-1 AAAA1_1(1.5分）1）XXXX（0.8分）（1）AAA（0.4分）（2）BBB（0.4分）2）YYYY（0.7分）（1）CCC（0.2分）（2）DDD（0.3分）（3）EEE（0.2分）-2 AAAA1_2(1.5分)1）XXX（0.7分）（1）FFF（0.7分）2）YYY（0.8分）（1）GGGG（0.8分）.数据表设计：评测结点(编号，父编号，标题，内容，分数，测评标准，评分细则及操作方法)主键：编号外键：父编号（自关联）三、综合设计应用题（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 职工信息表 (职工号) ON DELETE CASCADE DEFERRABLE ) （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 IN