零件号颜色供应商号P1红A1P2蓝A3P3白A4《数据库原理与应用》期中测验1、 简答题（10分， 5分/题）1、试述数据模型的三要素。

（5分）答：数据模型的三要素包括数据的基本结构，数据的约束条件和定义在数据上的操作。

对于关系型数据库来讲，数据的基本结构是关系或者二维表；数据的约束条件包括实体完整性约束、参照完整性约束和用户自定义的完整性约束条件；定义在关系上的操作包括对数据的增加、删除和修改等。

2、设有供应商关系S 和零件关系P ，如图所示，S 和P 的主码分别是“供应商号”和 “零件号”，P 的外码是“供应商号”。

（5分）S P供应商号供应商名所在城市A1红星北京A2宇宙上海A3黎明天津A4立新重庆分析下列3个操作能否被正确执行，并说明理由。

Ⅰ.在P 中插入元组（′P2′，′白′，′A2′）Ⅱ.在P 中插入元组（NULL,′绿′，′A1′）Ⅲ.在P 中插入元组（′P4′，′白′，′A5′）答：（1）不可以，因为P 中已经有P2的元组，违反了实体完整性约束。

（2）不可以，因为主键不能为空，违反了实体完整性约束。

（3）不可以，因为‘A5’在S 中没有对应的供应商，违反了参照完整性约束。

三、已知下面四个关系模式：产品（制造厂商，型号，类型）PC 机（型号，CPU 速度，RAM 容量，硬盘容量，光驱速度和型号，价格）手提电脑（型号，CPU 速度，RAM 容量，显示器尺寸，光驱速度和型号，价格）打印机（型号，是否彩色，处理类型，价格）其中产品的类型包括PC机、手提电脑或打印机，并假设所有产品型号唯一。

打印机的处理类型包括激光、喷墨或点阵。

利用关系代数写出如下查询的表达式：（20分， 4分/题）1、哪些种型号的PC机的CPU速度大于1000？2、查询所有生产大于1G的RAM的手提电脑的制造厂商。

3、查询厂商B生产的所有产品的型号和价格。

4、查询所有激光打印机的型号。

5、查询价格在9000以下、RAM容量大于120的手提电脑的制造厂商、型号、CPU速度和价格。

四、基于图书馆数据库的3个表，用SQL语言完成以下数据操作：（20分）图书（书号，书名，作者，出版社，单价）读者（读者号，姓名，性别，办公电话，部门，读者类别编号）借阅（读者号，书号，借出日期，归还日期）读者类别（读者类别编号，读者类别，允许借阅册数，允许借阅天数）利用sql语言创建三张表，并定义它们的主键和外键约束。

create table 图书(书号 varchar(10) primary key,书名 varchar(10),作者 varchar(10),出版社 varchar(10),单价 money)create table 读者类别(读者类别编号 varchar(10) primary key,读者类别 varchar(10),允许借阅册数 int,允许借阅天数 int)create table 读者(读者号 varchar(10) primary key,姓名 varchar(10),性别 varchar(10),办公电话 varchar(10),部门 varchar(10),读者类别编号 varchar(10) references 读者类别(读者类别编号))create table 借阅(读者号 varchar(10) references 读者(读者号),书号 varchar(10) references 图书(书号),借出日期 datetime,归还日期 datetime,primary key(读者号,书号))五、利用sql语言完成以下查询（30分，2分/题）1、向借阅表插入一个借阅记录，表示读者‘王小平’（读者编号为rj450016）借阅了一本书，书号为’TP316/ZW6’,借出日期为当天的日期，归还日期为空值。

insert into 借阅 values('rj450016','TP316/ZW6',getdate(),null)2、读者‘王小平’在借出上述图书10天之后归还该书。

update 借阅 set 归还日期=dateadd(day,10,借出日期)where 读者号 = 'rj450016' and 书号 = 'TP316/ZW6'3、当读者‘王小平’按期归还图书时，删除上述借阅记录。

delete from 借阅from 借阅 join 读者 on 借阅.读者号 = 读者.读者号join 读者类别 on 读者类别.读者类别编号 = 读者.读者类别编号where 借阅.读者号 = 'rj450016' and 借阅.书号 = 'TP316/ZW6'and datediff(day,借出日期,getdate()) > 允许借阅天数4、查询全体图书的图书号、书名、作者、出版社和单价。

select * from 图书5、查找姓名以‘王’、‘张’或‘李’开头的所有读者的读者号及姓名。

select 读者号,姓名 from 读者 where 姓名 like '[王张李]%'6、查询单价在20元以上、30元以下的机械工业出版社出版的图书名及单价。

select 书名,单价 from 图书where 单价 between 20 and 30 and 出版社 = '机械工业出版社'7、查询机械工业出版社图书的平均价格、最高价、最低价。

select avg(单价),max(单价),min(单价) from 图书8、查询借阅图书数超过2本的读者号、总本数。

并按借阅本数值从大到小排序。

select 读者号,count(*) as 总本数 from 借阅where 归还日期 is nullgroup by 读者号having count(*) > 0order by count(*)9、查询借阅了机械工业出版社出版、并且书名中包含‘数据库’三个字的图书的读者，并显示读者号、姓名、书名、出版社、借出日期及归还日期。

select 读者.读者号,姓名,书名,出版社,借出日期,归还日期from 读者 join 借阅 on 读者.读者号 = 借阅.读者号join 图书 on 借阅.书号 = 图书.书号where 出版社 = '机械工业出版社' and 书名 like '%数据库%'10、查询与‘王小平’的办公电话相同的读者的姓名。

select 姓名 from 读者where 办公电话 in(select 办公电话 from 读者 where 姓名 = '王小平') and 姓名 <> '王小平'11、查询办公电话为‘88320701’的所有读者的借阅情况，要求包括借阅了图书的读者和没有借阅的读者，显示他们的读者号、姓名、书名及借阅日期。

select 读者.读者号,姓名,书名,借出日期from 读者 left outer join 借阅 on 读者.读者号 = 借阅.读者号join 图书 on 借阅.书号 = 图书.书号where 办公电话 = '88320701'12、查询‘科学出版社’的图书中单价比‘机械工业出版社’最高单价还高的图书书名及单价。

select 书名,单价 from 图书where 单价 > (select max(单价) from 图书 where 出版社 = '机械工业出版社')and 出版社 = '科学出版社'13、查询从未被借阅过的图书信息。

select * from 图书where 书号 not in (select distinct 书号 from 借阅)--或者select 图书.* from 图书left outer join 借阅 on 图书.书号 = 借阅.书号where 借阅.读者号 is null14、查询正在借阅的图书信息。

select 图书.* from 图书join 借阅 on 图书.书号 = 借阅.书号where 借阅.归还日期 is null15、查询借阅了机械工业出版社出版的书名中含有‘数据库‘三个字的图书，但没有借阅科学出版社出版的书名中含有‘数据库‘三个字的图书的读者姓名及书名。

select 姓名,书名from 读者 join 借阅 on 读者.读者号 = 借阅.读者号join 图书 on 借阅.书号 = 图书.书号where 出版社 = '机械工业出版社' and 书名 like '%数据库%'and not exists(select * from 借阅 join 图书 on 借阅.书号 = 图书.书号where 出版社 = '科学出版社' and 书名 like '%数据库%'and 读者.读者号 = 借阅.读者号)五、基于上述图书馆的关系模式，完成以下操作。

（25分）1、创建以下视图（5分）创建视图v1，统计每种类型的读者手中持有的图书册书（即已经借阅还没有归还的图书的总数）。

包括读者类型编号，读者类型，借阅册书create view v1asselect 读者.读者类别编号,读者类别,count(*) as 借阅册书from 读者 join 借阅 on 读者.读者号 = 借阅.读者号join 图书 on 借阅.书号 = 图书.书号join 读者类别 on 读者类别.读者类别编号 = 读者.读者类别编号where 归还日期 is nullgroup by 读者.读者类别编号,读者类别2、存储过程（15分）读者可以续借图书，但是每次借阅的图书只能续借一次。

试创建续借文献的存储过程p1。

题目有问题，需要添加一列是否续借alter table 借阅 add 续借次数 smallint default 0create proc p1(@书号 varchar(10))asbeginupdate 借阅 set 借出日期 = getdate(),续借次数 = 续借次数 + 1from 借阅 join 读者 on 读者.读者号 = 借阅.读者号join 读者类别 on 读者类别.读者类别编号 = 读者.读者类别编号where 借阅.续借次数 = 0 anddatediff(day, 借出日期, getdate()) < 允许借阅天数 and书号 = @书号end读者借阅图书时，需要查询是否有超期的图书没有归还，如果有则不能继续借阅图书，试创建一存储过程p2完成借阅图书的功能。

create proc p2(@读者号 varchar(10), @书号 varchar(10))asbeginupdate 借阅 set 归还日期 = getdate()where 读者号 = @读者号 and 书号 = @书号 and读者号 not in (select 读者.读者号from 读者 join 读者类别 on 读者类别.读者类别编号 = 读者.读者类别编号join 借阅 on 借阅.读者号 = 读者.读者号where 读者.读者号 = @读者号 anddatediff(day, 借阅.借出日期, getdate()) < 读者类别.允许借阅天数)end3、函数（5分）创建一个函数f1，查询指定读者的借阅图书清单，包括读者姓名，部门，读者类型，书名，出版社，借阅日期，归还日期，输入参数为读者姓名。