SQL程序设计——图书管理系统.doc

SQL程序设计图书管理系统学院：计算机与信息工程学院专业：计算机科学与技术班级：2班学号：201110910521姓名：张亮亮 日期：2013-12-3分数等级：中1.开发背景数据库技术和Internet的飞速发展，使它们已经成为现代信息技术的重要组成部分，是现在计算机信息系统和计算机应用系统的基础和核心。对于任何一个企业来说，数据是企业重要的资产，如何有效利用这些数据，对于企业发展起着极其重要的作用。随着我国市场经济的迅速发展和人们生活水平的不断提高，图书馆藏书的树木逐渐增大，这也挑战了图书管理方面的技术，以前的人工管理方式已经不再适应现在的环境，取而代之的是先进的图书管理系统，通过使用PowerBuilder创建的图书管理系统可以让管理人员方便而快捷的进行管理、查询、借阅、录入等工作。2.需求分析2.1系统目标图书管理信息系统是典型的信息管理系统(MIS),其开发主要包括后台数据库的建立和维护以及前端应用程序的开发两个方面。对于前者要求建立起数据一致性和完整性强.数据安全性好的库。而对于后者则要求应用程序功能完备,易使用等特点。系统开发的总体任务是实现各种信息的系统化，规范化和自动化。2.2需求定义图书馆管理系统开发。系统开发的总的设计目标是实现图书管理的系统化、规范化和自动化，实现对图书资料的集中统一的管理。本系统主要实现对图书馆信息的管理，主要功能为管理有关读者、图书、借阅、查询、删除和管理员的信息等。本系统结构分为读者类别管理、读者档案管理、图书类型管理、图书档案管理以及图书流程管理，读者管理可以浏览读者的信息，可以对读者信息进行维护。图书管理可以浏览图书的信息，可以对图书信息进行维护。借阅管理可以显示当前数据库中书籍借阅情况，可以对借阅信息进行维护。本系统主要解决的问题是利用关键字对数据库进行查询。其系统的功能模块图如下：图书管理系统图书管理图书资料投诉管理系统管理图书赔偿用户管理密码管理退出系统图书借阅图书登记图2-1 系统功能模块图针对一般图书管理信息系统的需求，通过对图书管理工作过程的内容和数据流程分析，设计如下面所示的数据项： 读者信息 属性：读者学号，读者姓名，读者性别，联系电话，所在院系，生效日期，失效日期，违章状况，累计借书 主键：读者学号 书籍信息 属性：ISBN，书名，作者，出版社，出版日期，简介 主键：ISBN 管理员信息 属性：工作号，姓名，性别，电话，家庭住址 主键：工作号 2.3数据流程2.3.1 读者作为学生对图书管理系统的要求有：1. 能按各种方式（比如书名、编号、作者）查询图书馆的藏书情况。2. 能够方便地借阅图书、续借图书、归还图书。3. 能够查询自己的基本资料、借阅图书情况。4. 能够熟悉图书管理系统的使用。读者进入系统工作的流程图为:查询修改密码借阅续借归还注册注销 2-1进入系统工作的流程图2.3.2 图书管理员作为图书管理员，他们对图书管理系统的要求有：1. 能方便的对图书进行录入登记，注销陈旧的书籍。2. 能够方便地对新生进行登记，或注销已经毕业的学生信息（基本信息，借阅信息）。3. 能够随时发布一些诸如各学院学生借阅图书超期情况、馆内藏书情况、借情况等信息，以便各学院能够随时获知本院学生的一些借书信息。图书管理员工作的流程图为：2-2图书管理员工作流程图3.功能描述系统功能分析是在系统开发的总体任务的基础上完成。本系统需要完成的功能主要有5部分：(1) 图书基本情况的录入，修改，删除等基本操作。1书籍类别标准的制定，类别信息的输入，包括类别编号，类别名称，关键词，备注信息等。2书籍类别信息的查询，修改，包括类别编号，类别名称，关键词，备注信息等。3书籍信息的输入，包括书籍编号，书籍名称，书籍类别，作者名称，出版社名称，出版日期，书籍页数，关键词，登记日期，备注信息等。(2) 办理借书卡模块。1新生办理借书证。2丢失办理借书证。(3) 实现借书功能。1借书信息的输入，包括借书信息编号，读书编号，读者姓名，书籍编号，书籍名称，借书日期，备注信息等。2借书信息的查询，修改，包括借书信息编号，读者编号，读者姓名，书籍编号，书籍名称，借书日期，备注信息等。(4) 实现还书功能。1还书信息的输入，包括还书信息编号，读者编号，读者姓名，书籍编号，书籍名称，借书日期，还书日期，备注信息等。2还书信息的查询和修改，包括还书信息编号，读书编号，读者姓名，书籍编号，书籍名称，借书日期，还书日期，备注信息等。(5) 能方便的对图书进行查询。对超期的情况能自动给出提示信息4.概念模图书登记注册录入修改个人信息注销超期处理型设计各部分E-R图如下：(1)读者信息类别实体E-R图读者编号借书期限借书数量种类名称读者类别实体(2)书籍信息类别实体E-R图图书编号书籍类别出版社书籍名称书籍类别实体5.逻辑模型设计和优化从理论E-R模型到理论关系模型的整理转换，通过E-R模型到关系模型的转化，可以得到如下关系模式： 借阅关系 属性：工作号，读者学号，ISBN，是否续借，借书日期，还书日期，备注。 主键：工作号，读者学号，ISBN 管理员_书籍关系 属性：工作号，ISBN，添加时间，是否在馆 主键：工作号，ISBN 管理员_学生关系 属性：工作号，读者学号，确认借还 主键：工作号，读者学号通过由概念模型设计得到CDM图powerdesigner转换成对应的PDM图：PDM图5.1图书信息表列名数据类型可否为空ISBNvarchar否书名varchar否作者varchar否出版社varchar是出版日期datetime是简介varchar是5.2 读者信息表列名数据类型可否为空读者学号varchar否读者姓名varchar否读者性别varchar否联系电话varchar是所在院系varchar否5.3 管理员信息表列名数据类型可否为空工作号varchar否姓名varchar否性别varchar否电话varchar是家庭住址varchar是5.4 借阅表 列名数据类型可否为空工作号char否ISBNchar否读者学号char否借阅日期datetime否还书日期datetime否是否续借char否5.5 管理员_书籍 列名数据类型可否为空工作号char否ISBNchar 否添加时间datatime是是否在馆char是5.6 管理员_学生 列名数据类型可否为空工作号char否读者学号char否借还确认char否6.物理设计和实施 从理论关系模型到实现实施数据库建立，物理文件的安排和建立索引 6.1 建立索引 为了提高在表中搜索元组的速度，在实际实现的时候应该基于键码建立索引是各表中建立索引的表项： (1)读者信息（读者学号） (2)书籍信息（ISBN） (3)管理员信息（工作号） (4)借阅（工作号，读者学号，ISBN） (5)管理员_书籍（工作号，ISBN） (6)管理员_学生（工作号，读者学号） 6.2 SQL语句如下所示：/*=*/* Table: 书籍信息 */*=*/create table 书籍信息 ( ISBN char(20) not null, 书名 char(20), 作者 char(10), 出版社 char(15), 出版日期 char(30), 简介 char(160), constraint PK_书籍信息 primary key (ISBN);/*=*/* Index: 书籍信息_PK */*=*/create unique index 书籍信息_PK on 书籍信息 (ISBN ASC);/*=*/* Table: 投诉 */*=*/create table 投诉 ( 工作号 char(10) not null, 学号 char(10) not null, 投书意见 char(100), 投诉日期 char(30), 受诉日期 char(30), constraint PK_投诉 primary key (工作号, 学号);/*=*/* Index: 投诉_PK */*=*/create unique index 投诉_PK on 投诉 (工作号 ASC,学号 ASC);/*=*/* Index: 投诉_FK */*=*/create index 投诉_FK on 投诉 (工作号 ASC);/*=*/* Index: 投诉2_FK */*=*/create index 投诉2_FK on 投诉 (学号 ASC);/*=*/* Table: 登记 */*=*/create table 登记 ( 工作号 char(10) not null, 学号 char(10) not null, ISBN char(20) not null, 证书日期 char(30) not null, 还书日期 char(30) not null, 违章状况 char(160), 累计借书 char(160), 备注 char(160), constraint PK_登记 primary key (工作号, 学号, ISBN);/*=*/* Index: 登记_PK */*=*/create unique index 登记_PK on 登记 (工作号 ASC,学号 ASC,ISBN ASC);/*=*/* Index: 登记_FK */*=*/create index 登记_FK on 登记 (工作号 ASC);/*=*/* Index: 登记2_FK */*=*/create index 登记2_FK on 登记 (学号 ASC);/*=*/* Index: 登记3_FK */*=*/create index 登记3_FK on 登记 (ISBN ASC);/*=*/* Table: 管理员信息 */*=*/create table 管理员信息 ( 工作号 char(10) not null, 姓名 char(10), 性别 char(5), 电话 char(15), 所在院系 char(25), constraint PK_管理员信息 primary key (工作号);/*=*/* Index: 管理员信息_PK */*=*/create unique index 管理员信息_PK on 管理员信息 (工作号 ASC);/*=*/* Table: 读者信息 */*=*/create table 读者信息 ( 学号 char(10) not null, 姓名 char(10), 性别 char(5), 电话 char(15), 所在院系 char(25), constraint PK_读者信息 primary key (学号);/*=*/* Index: 读者信息_PK */*=*/create unique index 读者信息_PK on 读者信息 (学号 ASC);7.主要数据操纵语句 7.1管理员操作 (1)注册(register) INSERT INTO 管理员 (工作号,姓名,性别,电话,家庭住址,备注) VALUES(#工作号,#姓名,#性别,#电话,#家庭住址,#备注) 说明：在登记操作后,管理员得到一个唯一的工作号,可以根据这个工作号采查询和修改数据。 (2)注销(unregister) DELETE FROM Provider WHERE(工作号=#工作号)； (3)修改个人信息(update) UPdate 管理员 Set (工作号=#工作号,姓名=#姓名,性别=#性别,电话=#电话,家庭住址=#家庭住址) WHERE(工作号#工作号) (4)增加书籍（addbooks） INSERT INTO 图书 (ISBN,书名,作者,出版社,出版日期,简介,) VALUES(#ISBN,#书名,#作者,#出版社,#出版日期,#简介,#备注) INSERT INTO 管理员_书籍表(工作号,ISBN,添加时间,是否在馆) VALUES(#工作号,#ISBN,#添加时间,#是否在馆） (5)删除书籍(deletebooks) DELETE 图书 WHERE(ISBN =# ISBN) (6)修改书籍(updatebooks) UPDATE 图书(书名=#书名, 作者=#作者, 出版社=#出版社, 出版日期= #出版日期, 简介=#简介) WHERE(ISBN =# ISBN) 7.2读者操作 (1)注册(register) INSERT INTO 读者(读者学号,读者姓名,读者性别,联系电话,所在系, 生效日期,失效日期,违章状况,累计借书,备注) VALUES(#读者学号,#读者姓名,#读者性别,#联系电话,#所在系, #生效日期,#失效日期,#违章状况,#累计借书,#备注) 说明：在登记操作后,读者得到一个唯一的ID,可以根据这个ID来查询和修改自己的信息。 (2)注销(unregister) DELETE 读者 WHERE(读者学号=#读者学号) (3)修改个人信息(update) UPDATE 读者 Set(读者姓名=#读者姓名,联系电话=#联系电话, 所在系=#所在系,生效日期=#生效日期,失效日期=#失效日期, 违章状况=#违章状况,累计借书=#累计借书,备注=#备注) WHERE(读者学号=#读者学号) (4)查询（select） SELECT ISBN,书名,作者,出版社 FROM图书 WHERE ISBN=#ISBN OR书名=#书名 7.3管理员对借阅关系的操作 (1)插入读者的信息（insert） INSERT INTO 借阅(工作号,读者学号,ISBN,是否续借,借书日期,还书日期,备注) VALUES(#工作号,#读者学号,#ISBN,#是否续借,#借书日期, #还书日期,#备注) (2)更新信息（update） 更新借出信息 UPDATE借阅 SET(借书日期=#借书日期,还书日期=借书日期+30,是否续借=0) WHERE(工作号=#工作号 AND 读者学号=#读者学号 AND ISBN =#ISBN) UPDATE管理员_图书 SET(是否在馆=0) WHERE(ISBN=#ISBN) UPDATA 学生 SET (累积借书=累积借书+1) WHERE(读者学号=#读者学号) INSERT INTO管理员_学生(工作号,读者学号,确认借还,ISBN) VALUES(#工作号,#读者学号,”0”, #ISBN) 更新续借信息 UPDATE 借阅 SET(是否续借=#是否续借) WHERE(工作号=#工作号 AND 读者学号=#读者学号 AND ISBN =#ISBN) 更新还书信息 UPDATE管理员_图书 SET(是否在馆=1) WHERE(ISBN=#ISBN) UPDATE管理员_学生 SET(确认借还=”1”) WHERE(工作号=#工作号 AND 读者学号=#读者学号 AND ISBN=#ISBN)任务1借书卡表：CARD (CNO, CNAME, CLASS)，其中CNO表示卡号，CNAME表示姓名，CLASS表示班级。-CNO CNAMECLASS-C01张三计算机1班C02李四计算机1班C03王五计算机2班-图书表：BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY)，其中BNO表示书号，BNAME表示书名，AUTHOR表示作者，PRICE表示单价，QUANTITY表示库存册数。-BNOBNAMEAUTHORPRICEQUANTITY-B01水浒施耐庵305B02西游记吴承恩202B03三国演义罗贯中503B04红楼梦曹雪芹406-借书记录表：BORROW(CNO, BNO, RDATE)，其中CNO表示借书卡号，BNO表示书号，RDATE表示还书日期。-CNOBNORDATE-C01B012012-12-1C01B042012-12-5C02B022012-12-12C02B032012-12-4C03B012012-12-1C03B022012-12-15C03B032012-12-1C03B042012-12-6-备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。实现如下15个处理：1写出建立表CARD、BOOKS和BORROW的SQL语句，定义主码完整性约束和引用完整性约束，将每条记录插入表中。2找出借书超过5本的读者，输出借书卡号及所借图书册数。3查询借阅了水浒一书的读者，输出姓名及班级。4查询过期未还图书，输出借阅者（卡号）、书号及还书日期。5查询书名包括“国”关键词的图书，输出书号、书名、作者。6查询现有图书中价格最高的图书，输出书名及作者。7查询当前借了红楼梦但没有借三国演义的读者，输出其借书卡号，并按卡号降序排序输出。8将“计算机1班”同学所借图书的还期都延长一周。9从BOOKS表中删除当前无人借阅的图书记录。10如果经常按书名查询图书信息，请建立合适的索引。11在BORROW表上建立一个插入触发器，完成如下功能：如果读者借阅的书名是三国演义，就将该读者的借阅记录保存在BORROW_SAVE表中（注BORROW_SAVE表结构同BORROW表）。12建立一个视图，显示计算机1班学生的借书信息（只要求显示姓名和书名）。13查询当前同时借有红楼梦和水浒两本书的读者，输出其借书卡号，并按卡号升序排序输出。14假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。15对CARD表做如下修改：a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。b. 为该表增加1列DNAME（系名），可变长，最大20个字符。代码：create database bookuse bookcreate table card(cno char(10) primary key,cname varchar(50) not null,class varchar(20) not null);insert into card(cno,cname,class)values(c01,张三,计算机班);insert into card(cno,cname,class)values(c02,李四,计算机班);insert into card(cno,cname,class)values(c03,王五,计算机班);select * from cardcreate table books(bno char(10) primary key,bname varchar(20) not null,author varchar(20) not null,price float(20) not null,quantity int not null);insert into books(bno,bname,author,price,quantity)values(b01,水浒,施耐庵,30,5);insert into books(bno,bname,author,price,quantity)values(b02,西游记,吴承恩,20,2);insert into books(bno,bname,author,price,quantity)values(b03,三国演义,施耐庵,50,3);insert into books(bno,bname,author,price,quantity)values(b04,红楼梦,曹雪芹,40,6);select * from bookscreate table c1(cno char(10),bno char(10),rdate datetime,primary key(cno,bno)insert into c1values(c01,b01,2012-12-1);insert into c1values(c01,b04,2012-12-5);insert into c1values(c02,b02,2012-12-12);insert into c1values(c02,b03,2012-12-4);insert into c1values(c03,b01,2012-12-1);select * from c1wheregroup by cnohaving count(*) =2select * from c1,card,books /*第三题*/where o=o andc1.bno=books.bno and bname=水浒select * /*第四题*/from c1where rdate=(select max(price) from books)select * from c1,books /*第七题*/where c1.bno=books.bno andbname=红楼梦 and cnonot in(select cno from c1,bookswhere c1.bno=books.bno andbname = 三国演义)update c1 /*第八题*/set rdate=rdate+7where cno in(select cno from card where class=计算机班)delete from books /*第九题*/where bno not in(select bnofrom c1)create index indexbname /*第十题*/on book.dbo.books(bname);gocreate trigger tr_save on c1 /*第十一题*/for insertasif rowcount 0 insert into borrow_save select i.*from inserted i,books b where i.bno=b.bno and b.bname= N三国演义create table borrow_save(cno char(3),bno char(3),rdate datetime)insert into c1values()create view viewBorrow /*第十二题*/asselect cname,bname from c1,card,bookswhere o=o and books.bno=c1.bnoand class=计算机班select * from viewBorrowalter table books add foreign key (bno) 任务二：任务2题目：数据库SCT包括学生表STUDENT、课程表COURSE、成绩表SC和教师表TEACHER。学生表STUDENT(S#, SNAME, SAGE, SSEX)，其中S#：学生学号；SNAME：学生姓名；SAGE：学生年龄；SSEX：学生性别。-S# SNAME SAGESSEX-S01张鹏18男S02李莹19女S03 马丽 18女-课程表COURSE(C#, CNAME, T#)，其中C#：课程编号；CNAME：课程名字；T#：教师编号。 -C#CNAMET#-C01数据库002C02企业管理001C03英语003-成绩表SC(S#, C#, SCORE)，其中S#：学号；C#：课程编号；SCORE：成绩。-S#C#SCORE-S01C01 75S01C02 60S01C03 55S02C0170S02C0280S02C0390S03C0140S03C0250S03C0355-教师表TEACHER(T#, TNAME)，其中T#：教师编号；TNAME：教师名字-T#TNAME-T01李玲T02王冠T03 叶平-问题：1、创建数据库SCT，然后在数据库中创建学生表STUDENT、课程表COURSE、选课表SC、教师表TEACHER。2、向四个表中插入记录。3、查询课程“C01”比课程“C02”成绩高的所有学生的学号。4、查询平均成绩大于60分的学生的学号和平均成绩。5、查询所有学生的学号、姓名、选课数、总成绩。 6、查询姓“李”的老师的个数；7、查询没学过“叶平”老师课的学生的学号、姓名。8、查询每门课程被选修的学生数。9、查询只选修一门课的学生的学号和姓名。10、查询男生、女生人数，显示格式：男生 | 女生。11、查询姓“张”的学生。12、查询同名同姓学生，同时统计同名同姓人数。13、1981年出生的学生名单(表student中sage列的类型是datetime)。14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。15、查询平均成绩大于85的所有学生的学号、姓名和平均成绩。16、查询课程名称为“数据库”，且分数低于60的学生姓名和分数。17、查询所有学生的选课情况。18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数。19、查询不及格的课程，并按课程号从大到小排列。20、查询选修课程C03且课程成绩在80分以上的学生的学号和姓名。21、查询选修课程的学生人数。22、查询选修“叶平