数据库原理 期中考试试卷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