数据库原理期中试卷A_2017423

数据库原理期中考试试卷A
班级：学号：姓名：成绩：
一、根据以下关系描述，画出ER图，注意ER图的规范表达。

（20分）
图书馆数据库对每个借阅者存有读者记录，包括读者号、姓名、地址、性别、年龄、单位。

对每本书存有书号、书名、作者、出版社、库存位置编号。

对每本被借出的书存有书号、读者号、借出日期和应还日期。

画出ER图。

二、基于SQL SERVER软件，使用T-SQL语言在D盘根目录下创建一个名
为test_db的数据库，包含主文件和log文件，文件大小都为5MB，请写出相关语句。

（10分）
CREATE DATABASE test1_db
ON
(NAME=test,
FILENAME='D:\test_db.mdf',
SIZE=5,
MAXSIZE=100)
LOG ON
(NAME=test_log,
FILENAME='D:\test_db.ldf',
SIZE=5,
MAXSIZE=100)
GO
三、根据第一题ER图的分析，使用SQL语言在test_db数据库中建立表单，
请写出相关语句，要求设置表单主键和外键约束。

（20分）
USE test1_db
GO
CREATE TABLE reader
(rno CHAR(6) NOT NULL,
rname CHAR(10) NOT NULL,
rsex CHAR(2) NOT NULL,
rage INT NOT NULL,
rdept CHAR(20),
CONSTRAINT rno_PK PRIMARY KEY (rno) )
GO
CREATE TABLE books
(bno CHAR(6) NOT NULL,
bname CHAR(20) NOT NULL,
bauthorpno CHAR(20) NOT NULL,
bpublish CHAR(20) NOT NULL,
blocation CHAR(20) NOT NULL, CONSTRAINT bno_PK PRIMARY KEY (bno)) GO
CREATE TABLE rb
(rno CHAR(6) NOT NULL,
bno CHAR(6) NOT NULL,
borrowdate DATETIME NOT NULL,
returndate DATETIME NOT NULL,
CONSTRAINT cnosno_PK PRIMARY KEY (rno,bno),
CONSTRAINT cno_FK FOREIGN KEY (rno) REFERENCES reader(rno),
CONSTRAINT sno_FK FOREIGN KEY (bno) REFERENCES books(bno)
)
GO
四、根据test_db数据库中数据表的信息和查询要求，写出对应的SQL语句。

（40分，每题5分）
1.在查询分析器中，指向test_db数据库。

USE test1_db
GO
2.查看图书馆全部书籍的信息。

SELECT * FROM reader;
GO
3.查看借书最多的五位同学的读者号、姓名、所借书籍的总数。

SELECT TOP 5 reader.rno, reader.rname, COUNT(*) MYTOTAL
FROM reader JOIN rb ON (reader.rno = rb.rno)
GROUP BY reader.rno, reader.rname
ORDER BY MYTOTAL DESC
4.查看所有借阅过“数据库原理”相关书籍的同学
SELECT *
FROM reader JOIN rb ON (reader.rno = rb.rno)
JOIN books ON (rb.bno = books.bno)
WHERE bname LIKE '%数据库原理%'
5.查询当天借阅的全部书籍的信息和读者编号、姓名，并且先按照读者编号升序排列，
再按照书号降序排列。

SELECT books.*, reader.rno, reader.rname
FROM reader JOIN rb ON (reader.rno = rb.rno)
JOIN books ON (rb.bno = books.bno)
WHERE DATEDIFF(DAY, rb.borrowdate ,GETDATE())=0
ORDER BY reader.rno, books.bno DESC
6.查询借阅过全部书籍的同学。

SELECT *
FROM reader
WHERE NOT EXISTS(
SELECT *
FROM books
WHERE NOT EXISTS(
SELECT *
FROM rb
WHERE rno = reader.rno
AND bno = books.bno
))
7.查询借阅了“数据库原理”相关书籍，没有借阅“面向对象编程”相关书籍的读者
的编号、姓名、单位。

SELECT reader.rno, reader.rname, reader.rdept
FROM reader JOIN rb ON (reader.rno = rb.rno)
JOIN books ON (rb.bno = books.bno)
WHERE bname LIKE '%数据库原理%' AND rb.rno NOT IN
(SELECT rno
FROM rb JOIN books ON (rb.bno = books.bno)
WHERE bname LIKE '%面向对象编程%' )
8.查询借了10本以上书的读者的信息。

SELECT reader.rno, reader.rname
FROM reader JOIN rb ON (reader.rno = rb.rno)
GROUP BY reader.rno, reader.rname
HAVING COUNT(*)>10
五、根据test_db数据库中数据表的信息创建视图。

（10分，每题5分）
1.创建视图，显示图书馆全部已借书籍的全部信息。

CREATE VIEW V_BORROWINFO
AS
SELECT books.*
FROM rb JOIN books ON (rb.bno = books.bno)
WHERE rb.returndate > GETDATE()
SELECT * FROM V_BORROWINFO;
2.创建视图，显示图书馆当天需归还书籍的编号、名称以及读者的读者号和姓名。

CREATE VIEW V_RETURNINFO
AS
SELECT books.bno, books.bname, reader.rno,reader.rname
FROM reader JOIN rb ON (reader.rno = rb.rno)
JOIN books ON (rb.bno = books.bno)
WHERE DATEDIFF(DAY, rb.returndate ,GETDATE())=0
SELECT * FROM V_RETURNINFO。