图书管理题库

图书管理系统题库 提交：在D：盘将“图书管理题库.doc”重命名为自己的“机器号+姓名”(机房号+姓名，如“3A11张三.doc”)，并将答案写到每一题后的空白处，最后压缩。 本题库所有题目都基于该处定义数据库：图书管理。该数据库中包括三个数据，每个表的逻辑结构如下说明： 1 读者信息：借书证号 char（8） 非空、主键，姓名 nvarchar（10）非空，性别 nchar（1），出生日期 datetime，专业名称 nvarchar（20）。 2 图书信息：图书编号：char（8）非空、主键，图书名称 nvarchar（20）非空，作者 nvarchar（10），出版社 nvarchar（20），ISBN  char（13），价格 decimal（5，2），复本数 tinyint，库存量 tinyint。 3 借阅信息：借书证号 参照“读者信息”表“借书证号”字段取值，图书编号 参照“图书信息”表“图书编号”字段取值，借书时间 datetime，还书时间 datetime，且将借书证号与图书编号字段联合成为该表的主键。 一、数据库表的创建 1．请用SQL数据定义语句定义读者信息表(注：按上述逻辑结构创建)。 2．请用SQL数据定义语句定义图书信息表(注：按上述逻辑结构创建)。 3．请用SQL数据定义语句定义借阅信息表（注：按上述逻辑结构创建，但此处不要求创建参照完整性）。 要求：必须按照以下格式书写命令语句，否则酌情扣分。 create table 读者信息 ( 借书证号 char(8) not null, 姓名 nvarchar(10) not null, 性别 nchar(1), 出生日期 datetime, 专业名称 nvarchar(20) ) 第一、字段的定义相对于前面的括号要有缩进；第二，字段的定义每行只能书写一个。 二、表结构的修改与数据完整性的创建 1． 修改读者信息表，将该表中出生日期的类型由datetime改为smalldatetime类型。 2． 修改图书信息表，将复本数字段由tinyint改为int类型。 3． 修改图书信息表，将价格改为decimal(5,1)。 4． 修改图书信息表，将该表中的图书名称设置为唯一约束,约束名为k_图书信息_图书名称。 5． 修改读者信息表，将该表中的姓名字段设置为唯一约束，约束名为k_读者信息_姓名。 6． 修改借阅信息表，为该表创建参照完整性，即借阅信息表的“借书证号”参照“读者信息”表“借书证号”字段。 7． 修改借阅信息表，为该表创建参照完整性，借阅信息表的“图书编号”参照“图书信息”表“图书编号”字段。 8． 修改图书信息表，要求图书的库存量小于等于图书的复本数量。 9． 修改借阅信息表，要求图书的借书时间小于等于还书时间。 10． 修改读者信息表，要求借书证号必须由0-9之间的数字组成，且不等于00000000。 11． 修改图书信息表，要求图书的价格大于0。 12． 修改读者信息表，要求性别只能为“男”和“女”。 13． 修改读者信息表，要求出生日期介于1980-9-1与1995-9-1之间。 14． 修改读者信息表，要求将性别字段的默认值设置为“女”。 15． 修改图书信息表，要求将出版社字段的默认值设置为“清华大学出版社”。 16． 修改图书信息表，要求将库存量字段的默认值设置为0. 17． 修改图书信息表，要求将复本数字段的默认值设置为0. 18． （补充:)修改读者信息， 三、数据查询 为方便数据查询，现将本试题涉及的3个表的基本数据列示如下： 表1：读者信息表 借书证号 姓名 性别 出生日期 专业名称 10000001 王琳琳 女 1990-03-14 政法系 10000002 江军 男 1989-04-21 计算机系 20000001 沈俊逸 男 1991-01-03 计算机系 20000002 赵妞 女 1990-05-12 计算机系 表2：图书信息表 图书 编号 图书名称 作者 出版社 ISBN 价格 复本数 库存量 A08001 C#程序设计 谭浩 强 清华大学出版社 7-111-06359-7 28 10 7 A08002 大型数据库 李坤 仑 电子工业出版社 7-113-04908-7 37 20 19 A08003 电子商务 郑强 清华大学出版社 7-115-07715-0 20 15 14 表3：借阅信息表 借书证号 图书编号 借书时间 还书时间 10000001 A08001 2009-04-13 10000001 A08002 2009-04-13 20000001 A08003 2009-04-13 30000001 A08001 2009-05-01 30000002 A08001 2009-04-23 1. 查询读者信息表中读者的所有信息。 2. 查询图书信息表中的所有信息。 3. 查询借阅信息表中的所有信息。 4. 查询读者信息表中读者的信息，只列出借书证号、姓名、性别，且性别列名显示为sex 5. 查询图书信息表中相关信息，只列出图书编号、图书名称，且图书编号列名显示为bookId。 6. 查询借阅信息表中的相关信息，只列出借书证号、借书时间列。 7. 查询读者信息表中的所有信息，只列出前3位读者的基本信息。 8. 查询图书信息表中的相关信息，只列出前2条记录，且只显示图书名称，作者列。 9. 查询读者信息表中计算机专业的读者信息。 10. 查询读者信息表中性别为男的读者信息。 11. 查询图书信息表中清华大学出版社的图书信息。 12. 查询读者信息表中“政法系”的女的读者信息。 13. 查询读者信息表中所有姓“赵”的读者信息。 14. 查询读者信息表中姓“江”且单名的读者信息。 15. 查询读者信息表中姓名带“军”字的读者的所有信息。 16. 查询图书信息表中图书名称含“程序”的图书信息。 17. 查询读者信息表中不在1990年出生的读者的借书证号，姓名，性别，出生日期。 18. 查询图书信息表中价格在20-30之间且库存量不在10-20之间的图书名称，作者，出版社,库存量. 19. 查询借阅信息表中曾借过书的读者的借书证号，要求只显示借书证号列，且消除重复行。 20. 查询借阅信息表中被借阅图书的图书编号，要求只显示图书编号列，且消除重复行。 21. 查询图书信息表中可借书数，显示时除显示所有列外，还要求再显示剩余书数(注：由复本数减去库存量所得)。 22. 查询读者信息表中的信息，显示时要求除显示所有列外，再显示一列为读者年龄（其中：读者年龄由出生时间计算得到）。 23. 查询所有图书的信息，且按“图书编号”降序排列。 24. 查询所有读者的借阅信息，且按“借书时间”升序排列。 25. 查询所有的图书信息，并按库存量降序排列,当库存量相同时，按图书名称的升序排序。 26. 查询图书信息表中价格最高的前两本书的图书名称，出版社，价格。 27. 列示图书信息表中“清华大学出版社”的图书名称、价格、库存量，按价格进行降序排列，价格相等的按库存量升序排列。 28. 统计读者信息表中所有的人数。 29. 统计读者信息表中男生的人数。 30. 计算图书信息表中所有图书总数，显示列名为图书总数。 31. 计算图书信息表中库存图书数,显示列名为库存图书数。 32. 列示图书信息表中图书总册数和库存总册数。 33. 统计读者信息表中各专业的人数，要求显示列为：专业名称、各专业人数。 34. 统计读者信息表中男、女生的人数，要求显示列为：性别、相应人数。 35. 统计图书信息表中各出版社的图书总数，要求显示列为：出版社、图书数。 36. 查询图书信息表中“清华大学出版社”图书的平均价格，将标题改为“平均价格”。 37. 统计借阅信息表中被借阅图书的图书编号和借阅该图书的人数。 38. 查询借书证号为“10000001”的读者所借阅图书的图书编号、图书名称、借书时间、还书时间等信息，并按照库存量降序排列。 39. 查询借阅了图书编号为‘A08001’的读者的借书证号、读者姓名、性别、出生日期，并且按照‘借书证号’降序排列。 40. 查询借阅了清华大学出版社图书的读者的借书证号、姓名、图书名称、出版社、借书时间。 41. 查询计算机专业读者借阅信息，包括读者的基本信息，图书编号、借书时间、还书时间。 42. 查询读者信息表中所有读者的信息及借阅图书的图书编号，若未借阅任何图书，也要包含其情况。 43. 查询图书信息表中所有图书的信息及借阅读者的借书证号，若该书未被借阅，也要包含其情况。 44. 查询读者信息表中的王琳琳是同一个专业的读者信息。 45. 查询图书信息表中和电子商务同一个出版社的图书信息。 46. 查询图书信息表中图书价格最高的图书名称和价格，和图书价格最低的图书名称和价格。 47. 列示出年龄大于所有读者平均年龄的读者信息。 四、索引的创建、视图的创建 1. 为图书信息表的图书名称建立唯一非聚集索引，索引名称为ix_图书信息_图书名称。 2. 为读者信息表的姓名建立非聚集索引，索引名称为ix_读者信息_姓名。 3. 为借阅信息表的借书时间建立非聚集索引，索引名称为ix_借阅信息_借书时间。 4. 创建视图view_读者信息,功能为：显示图书信息表中的图书编号、图书名称、作者、出版社、isbn列。 5. 创建视图view_读者信息_count，功能为：统计各专业的人数，显示列为专业名称、各专业人数。 6. 创建视图view_读者借阅信息,功能为：显示借书证号、姓名、图书编号、图书名称、借书时间的信息。 7. 创建视图view_图书借阅信息，功能为：显示借书证号、图书编号、图书名称、出版社、价格的信息。 8. 利用视图“view_读者信息_count”，查询“计算机专业”学生的人数情况，包括专业名称、各专业人数。 五、数据更新 1. 为读者信息表添加一条数据，其具体信息如下： 借书证号：30000003，姓名:李白，性别:男，出生日期:1989-1-1 专业名称：政法系 2. 为读者信息表添加一条数据，其具体信息如下： 借书证号: 30000004，姓名:杨红，性别:女 3. 为图书信息表添加一条数据，其具体信息如下： 图书编号:B08001，图书名称:数据库程序设计 4. 为借阅信息表添加一条数据，其具体信息如下： 借书证号：10000002，图书编号：A08002，借书时间：系统当前时间。 5. 更新读者信息表，将借书证号为30000003的专业名称改为’计算机’。 6. 更新读者信息表，将借书证号为30000003的出生日期改为’1985-9-9’。 7. 更新图书信息表，将图书编号为A08003的图书的复本数量更新为30本。 8. 更新图书信息表，将“电子商务”书的复本数更新为20。 9. 更新借阅信息表，将借阅了“大型数据库”图书的读者的还书时间改为当前系统时间。 10. 删除读者信息表中男生的记录信息。 11. 删除读者信息表中计算机专业的男同学。 12. 删除借阅信息表中还书时间晚于2009-5-1日的记录信息。 13. 删除借阅信息表中借阅了“大型数据库”书的记录信息。 六、存储过程 1. 创建存储过程，要求如下： 存储过程名:readerSelect 功能：查询读者信息表中的所有信息。 2. 创建存储过程，要求如下: 存储过程名：ReaderInfoQuery 功能：根据借书证号查询该读者的基本信息，包括姓名、性别、出生日期、专业名称。 输入参数：借书证号 3. 创建存储过程，要求如下: 存储过程名：ReaderBookQuery 功能：根据借书证号查询该读者借阅的图书信息，包括图书编号、图书名称、ISBN、出版社、借书时间。 输入参数：借书证号 4. 创建存储过程，要求如下: 存储过程名：BookInfoQuery 功能：根据图书编号，查看该编号代表图书的基本情况，图书名称、作者、出版社、ISBN、价格、复本数、库存量。 输入参数：图书编号 5. 创建存储过程，要求如下: 存储过程名：BookQuery 功能：根据图书编号查询借阅该图书的读者信息，包括借书证号、姓名、性别、专业名称。 输入参数：图书编号 6. 创建存储过程，要求如下: 存储过程名：BookPubQuery 功能：查询给定出版社价格最高的图书信息，包括图书名称、价格、出版社。 输入参数：出版社 7. 创建存储过程，要求如下： 存储过程名：ReaderInsert 功能：添加读者信息 输入参数：借书证号,姓名,性别,出生日期,专业名称 8. 创建存储过程，要求如下: 存储过程名:ReaderInsert 功能：添加读者信息 输入参数：借书证号,姓名,性别,出生日期,专业名称。其中，专业名称默认值为“专业尚未确定”。 9. 创建存储过程，要求如下: 存储过程名:ReaderInsert 功能：添加读者，且当添加读者成功，则输出信息“添加成功”，否则输出信息“添加失败”。 输入参数：借书证号,姓名,性别,出生日期,专业名称。 10. 创建存储过程，要求如下: 存储过程名:Reader_count 功能：统计读者信息表中给定专业的人数。 输入参数：专业名称 输出参数:人数 11. 创建存储过程，要求如下: 存储过程名:bookPub_sum 功能：统计给定出版社的图书数 输入参数：出版社 输出参数:图书数 12. 创建存储过程，要求如下： 存储过程名:bookPub_select 功能：查询指定出版社的图书信息，如果该出版社有书，则返回1，并显示该出版社的图书信息,否则返回-1。 输入参数：出版社 13. 创建存储过程，要求如下： 存储过程名:reader_select 功能：查询给定专业的学生信息，如果该专业有学生，则返回1，否则返回-1。 输入参数:专业名称 14. 创建存储过程，要求如下： 存储过程名:BookJYQuery 功能：根据图书编号查询图书借书情况。       如果复本数-库存量<=2，则显示“该图书库存不多，请继续进书”，返回1；       如果2<复本数-库存量<=6，则显示“该图书库存良好”，返回2；       如果复本数-库存量>6,则显示“该图书库存量充裕”,返回3. 输入参数：图书编号 七、触发器的创建 1. 创建触发器，要求如下： 触发器名称：trigReader 功能：插入、修改、删除读者信息表时，显示该表的所有记录。 2. 创建触发器，要求如下： 触发器名称：trigReader_update 功能：当用户修改表读者信息表的借书证号时，提示“不能修改借书证号”，并回滚事务。 3. 创建触发器，要求如下： 触发器名称：trigReader_delete 功能：禁止用户删除表读者信息表中计算机专业的学生记录。 4. 创建触发器，要求如下： 触发器名称：trigJy_insert 功能：当向借阅信息表中添加借书信息时，自动将图书信息表中相应的库存量减少1。 5. 创建触发器，要求如下： 触发器名称：trigJy_delete 功能：当向借阅信息表中删除借书信息时，自动将图书信息表中相应的库存量增加1。 6. 创建触发器，要求如下： 触发器名称：trigJy_insert 功能：当向借阅信息表插入数据时，如果图书信息表中的库存量等于0，则提示信息“库存量为0,不能插入”，并回滚。 7. 创建触发器 触发器名称：trigReader_insert 功能：当向读者信息表插入数据时，如果出现姓名重复的记录，则提示信息“姓名重复，不得添加”，且回滚。 8. 创建触发器 触发器名称：trigReader_delete 功能：当向读者信息表删除数据一条记录时，借阅信息表中的相应读者记录也删除。