广东广播电视大学2001-2002学年度第一学期期末考试

广东广播电视大学2001-2002学年度第一学期期末考试计科技99级第五学期数据库系统概论试题B2002年1月一、（共12分）一个航空公司的数据库包括乘客类和航班类的信息。

乘客Customer的信息包括身份证号No，姓名name、地址address等，航班Flight的信息包括航班号Number，日期Date和机型Kind等。

每次航班可搭乘多位乘客，而每位乘客在不同的时间可搭乘多班航班。

要求：1）、用ODL给出每个类的说明，要求为所有的属性和联系选择适当的类型，并且指出每个类的键码。

（8分）2）、用OQL查询乘客“陈晓东”所乘坐过的所有航班的航班号、日期和机型等信息。

（4分）二、（共12分）一个简单的大学选课数据库，涉及如下数据：学生Student：学号SNo、姓名Sname、年龄Age、性别Ssex、系别Dept；课程Course：课程号Cno、课程名CName、课程学分Credit；教师Teacher：教师姓名Tname、教师性别Tsex、职称Post；一个学生可以选修多门课，每门课可以由多个学生选修；一位教师可讲授多门课程，每门课程可由多位教师讲授，学生在选课的同时可以选老师，学生选修课程会有成绩Score。

1）、设计简单大学选课系统的E/R图；（6分）2）转换成关系模型（请注明每个关系的键码）（6分）三、设有一个简单的图书管理数据库如下：图书关系：Book（BNO, Title, Author, Publisher, Price），其中BNO, Title, Author, Publisher, Price分别表示图书的总编号、书名、作者、出版单位和单价；读者关系：Reader（LNO, Name, Unit），其中LNO, Name, Unit分别表示读者的借书证号、姓名和所在单位；借阅关系：Loan（LNO, BNO, Date）其中LNO, BNO, Date分别表示借阅图书的借书证号、所借图书的总编号和借书日期。

要求查询：读者“李小波”于2001年元旦前所借的所有图书的书名及借书日期。

（共20分）1）、给出以笛卡尔积为基础的表达上述查询要求的关系代数表达式；（4分）2）、画出1）所给出的关系代数式对应的关系表达树；（3分）3）、对以笛卡尔积为基础的关系代数表达式进行优化，给出优化后的关系代数表达式，并画出优化后的关系表达树；（5分）4）、用元组关系演算表达上述查询要求；（4分）5）、用数据逻辑规则表达上述查询要求。

（4分）四、对于题三所给出的图书管理数据库，用SQL语言表达如下查询要求：（共12分）1）、查找高等教育出版社的所有图书的书名和单价，结果按单价降序排列；2）、求出各个出版社图书的最高价格、最低价格和图书册数；3）、查询2000年7月以后没有借书的读者的姓名和单位。

五、用SQL的有关语句定义：1)、图书关系Book，包括书号BN、书名Title、作者Author、出版社Publisher和单价Price；2）、学生关系Student，包括姓名Name，班级Class和借书证号LN；3）、借阅关系Loan，包括借书证号ＬＮ，书号ＢＮ和借书日期Ｄ。

要求说明每个关系的主键码和外键码（如果有的话），并在键码属性上建立索引。

（共12分）六、在题五定义的关系模式中，用SQL的有关语句实现：（共12分）1）、授予王小兵对学生关系Student的查询和修改权限；（3分）2）、插入任意3个学生的完整信息；（3分）3）、修改图书关系，使得所有图书的单价上浮５％；（3分）4）、对借阅关系的属性加约束，使得其借书日期不能在２００１年以前。

（3分）七、（共12分）设有关系模式R(A,B,C,D), 模式中有函数依赖集F={AB→C, C→D, D→A }，要求：1）、求蕴含于给定函数依赖的所有非平凡函数依赖；2）、求R的所有键码；3）、说明R属于第几范式，给出理由。

八、按嵌入式SQL的格式从题五定义的数据库中从借阅关系Loan中删除某个读者（给定姓名）的所有元组。

（8分）广东广播电视大学2001-2002学年度第一学期期末考试计科技99级第五学期数据库系统概论试题B参考答案2002年1月一、（共12分）1）、interface Customer(key No ){ attribute string No;attribute string name;attribute string address;relationship Set <Flight> flightsinverse Flight：：customer; }；(4分)interfae Flight(key (Number , Date) ){ attribute string Number ;attribute string Date;attribute string Kind ;relationship Set <Customer> customersinverse Customer：：flights ; } ；（4分）2）、SELECT f1.Nunber, f1.Date, f1.KindFROM Customer c1, c1.flights f1WHERE =”陈晓东” ;（4分）二、1）、简单大学选课数据库系统的E/R图为：(6分)2Course (CNo, CName, Credit)Teacher (TName, Tsex,Post)STC(SNO, CNO, Tname, Score)(6分)三、（共20分）1）、以笛卡尔积为基础的表达查询要求的关系代数表达式为：∏Title,Date (σsname=”李小波” AND Date< (σ Read.LNO=Loan.LNO AND Loan.BNO=Book.BNO (Reader ×Loan ×Book))) (4分)2）、其原始关系表达树为： 3)、优化后的关系代数表达式及优化后的关系表达树为： ∏ Title,Date (σsname=” 4)5)、 U(T, D) AND Book (BNo,T,A, P, Pr) AND SN=‘李小波’AND D<四、SQL 查询语句：1）、查找高等教育出版社的所有图书的书名和单价，结果按单价降序排列SELECT Title, PriceFROM BookWHERE Publisher=”高等教育出版社”ORDER BY Price Desc;2)、求出各个出版社图书的最高价格、最低价格和图书册数SELECT Publisher As 出版单位, Max(Price) As 最高价, Min(Price) As 最低价, count(*) AS 总册数FROM BookGROUP BY Publisher ；3）、查询2000年7月以后没有借书的读者的姓名和单位：∏Title,DateSELECT name, unitFROM ReaderWHERE LNO NOT IN(SELECT LNOFROM LoanWHERE Date> );五、用SQL的有关语句定义：1）、CREATE TABLE Book (Title CHAR(30),Author CHAR(20),Publisher CHAR(20),BNO INT PRIMARY KEY,);CREATE INDEX Bkey ON Book (BNO);CREATE TABLE Student (Name CHAR(20) PRIMARY KEY,Class CHAR(8),LN INT PRIMARY KEY);CREATE INDEX Skey ON Student (LN);CREATE TABLE Loan (LN INT,BN INT,D Date,PRIMARY KEY(LN, BN),FOREIGN KEY(LN ) REFERENCES Student (LN ),FOREIGN KEY(BN) REFERENCES Book (BN));CREATE INDEX Lkey ON Loan (BN,LN);六SQL的有关语句实现：1）、GRANT SELECT, UPDA TE ON Student To 王小兵2）、INSERT INTO Student( TABLE ( “张小鲁”, “C1202”, 120203)，（“陈佳音”，”C1203”,120305），（“李鹏”，”D1102”, 110202）) ;3)、UPDATE BookSET Price=Price+Price*0.05;4)、在定义Loan时，加入对Date的属性约束如下：D Date CHECK ( D>=) ,七、1）、设有关系模式R(A,B,C,D), 模式中有函数依赖集F={AB→C, C→D,D→A }，，求各属性组合的封闭集：A+={A }, B+={ B}, C+={A,C,D} (新依赖：C→A),D+={A, D},AB+={A,B,C,D}(新依赖：AB→D)，AB为键码AC+={A,C,D} (新依赖：AC→D)AD+={A, D}BC+={A,B,C,D}(新依赖：BC→A, BC→D) BC为键码BD+={A,B,C,D}(新依赖：BD→C,BD→A) BD为键码；CD+={A,C,D} (新依赖：CD→A )ABC+={A,B,C,D} (新依赖：ABC→D )BCD+={A,B,C,D} (新依赖：BCD→A)ABCD+={A,B,C,D}由上分析可知：蕴含于其中的函数依赖为：{ C→A，AB→D ,AC→D，BC→A, BC→D，BD→C,BD→A , CD→A , ABC→D ,BCD→A }2）、由上面1）的分析可知：AB、BC、BD为键码。

3）、R属于第三范式，因为没有非主属性，不存在非主属性对键码的部分和传递依赖。

八、1）说明：九、EXEC SQL BEGIN DECLARE SECTION;char sname[30];char SQLSTATE[6];EXEC SQL END DECLARE SECTION;2）赋值：sname:=’赵华’;3）删除：EXEC SQL DELETEFROM LoanWHERE LNO=(SELECT LNOFROM StudentWHERE Name=:sname);。