小型数据库的实现与应用报告.doc

小型数据库设计与实现图书馆借阅系统组长：杨森 10071125组员：赵鸿鹄 10071124任子宇 10071107王世豪 10071108施杨乐 10071114张 沛 10071209完成时间：2012年12月16日目录摘要2Abstract2一、基本想法的确定3二、关系的设计方案42.1 表的具体设计方案42.2 E-R图5三、本表的标准SQL语句63.1生成数据库的标准SQL语句63.2 插入数据的标准SQL语句83.3某些查询的标准SQL语句10四、报告总结与感想114.1 组员感想114.2 组员分工12摘要本报告记录了该小组设计图书馆借阅系统的整个过程，包含基本想法的产生、设计方案的确定以及实体关系和与数据相关的SQL语句的生成、插入数据的SQL语句与某些查询的SQL语句，具有一定的参考意义与实际价值，体现了组员之间分工协作的良好成果。AbstractThis report recorded the whole process the group design for the library system , including the basic ideal generation , determination of design scheme as well as entity-relationship and the SQL statement for generating ,inserting or selecting data from the database, which has much value ,both in praticle and functioning ,to be referred ,representing the better result the group achieved dueing to the division of labor and cooperation.一、基本想法的确定提到图书馆管理系统，大家其实并没有感到有多么陌生，毕竟在大学里面我们经常会与图书馆打交道，但是当真正轮到我们去自己设计一套图书馆管理系统的时候，很多问题接踵而至，这个是大家始料未及的：首先对于究竟是哪十个表，大家的意见很不一致，一次又一次的想法确定而被驳倒，让我们的进程受到阻隔；其次，具体到每一个表上，大家对表该存在的键也难达成共识；最后表与表之间的联系，有些过于模糊，没有清晰的关联。这都是前期构思时候遇到的种种问题，通过则一段时间以来组员之间的相互磨合，最终我们提出了这样一套成体系的数据系统。该系统包含十个关系：书、借阅信息、借书者、限借书书目、预约表、书库、图书状态、工作人员、书商和待采购书目。每个关系均分担这一定的逻辑联系，并且协同能达到一个完整图书馆管理系统的真正需求。二、关系的设计方案2.1 表的具体设计方案对每一个表而言，我们的设计方案和细节结果如下所示(每个关系的第三行指的是该键对应数据所存贮的类型)：1. BOOK (B_ISBN, B_NAME, B_AUTHOR, B_PRESS, B_DATA, B_PRICE,)书 （isbn，书名，责任者，出版社，出版日期，定价）Char, char, char, char, date, float2. BORROW_INFO ( BI_CERTIFICATE, BI_CODE, BI_DATE, BI_STATE, BI_RENEW)借阅信息 （借书证号，条码号，借书时间，是否已还，是否续借）Bigint, int ,char ,date , ENUM(已还,未还),int3. USER (U_NUM, U_NAME, U_SEX, U_AGE, U_ID, U_Department)借书者 （借书证号，姓名，性别，年龄，身份，院系）Int ,char , enum(男,女), int , char, char4. LIMITS_OF_NUM (L_ID, L_NUM)限借书书目 （身份，可借书总数）Char, int5. BOOK_ORDER (BO_CALLNUM,B O_CERTIFICATE,B O_DATE)预约表 （索书号，借书证号，预约时间） Char , int ,datetime6. ROOM (R_ROOMNUM, R_AREA, R_BOOKNUM, R_ADD)书库 （书库号，面积，藏书量，位置） Int , float ,int ,char 7. STATE (S_CODE, S_ISBN, S_CALLNUM , S_ROOMNUM, S_STATE)图书状态 （条码号，isbn，索书号，所在书库，书刊状态） Char , char ,char ,int , enum(借出,已还,阅览)8. WORKER (W_NUM, W_NAME, W_AGE, W_TEL, W_WORKPLACE)工作人员 （职工号，姓名，年龄，联系方式，工作地点）Int ,char ,int ,char ,char 9. BOOKMAN (BM_NUM, BM_NAME, BM_ADD, BM_TEL)书商 （书商编号，姓名，地址，联系方式）Int , char ,char char 10. PURCHASE (P_ISBN, P_BOOKNAME, P_NUM, P_DATA, P_COST)待采购书目 （isbn，书名，采购量，采购时间，总费用）Char, char, int, date,float 以上就是十个关系的明细表，它们之间的联系会在之后的E-R图上进行完整的展示。由于我们的数据库管理系统并没有做成界面化，因此之后的语句是在MySql中进行操作的。2.2 E-R图E-R图中矩形框表示为实体(Entity)，椭圆形表示(属性)，菱形表示关系或联系(Relationship)。下面的图是我们中整个图书馆借阅系统所有表的E-R联系展示：“书”与“书商”、“预约单”、“借书者”和“藏书库”的联系分别是：采购、预约、借阅和馆藏。而“采购”、“借阅”和“馆藏”也是以表的形式出现。“藏书库”和“工作人员”之间的联系是：管理。“借书者”和“借书量”之间的联系是：权限。三、本表的标准SQL语句本表共涉及到三种SQL语句：生成数据库的标准SQL语句、插入数据的标准SQL语句和某些查询的标准SQL语句。下面从三部分分别具体说明SQL语句的使用过程。3.1生成数据库的标准SQL语句1.“书”表： create table BOOK(B_ISBN varCHAR(20) primary key unique not null ,B_NAME varchar(40) NOT null,B_AUTHOR varchar(20) NOT null,B_PRESS varchar(40) NOT null,B_DATE date not null, B_PRICE decimal(10,2) not null);2.“借阅信息”表：create table BORROW_INFO(BI_CERTIFICATE int unique not null,BI_CODE varchar(20) NOT null,BI_DATE date not null, BI_STATE ENUM(是,否) not null,BI_RENEW int not null default 0,primary key(BI_NUM, BI_CERTIFICATE,BI_DATE);3.“借书者”表：create table USER (U_NUM int primary key unique not null,U_NAME varchar(20) not null,U_SEX enum(男,女) not null,U_AGE int,U_ID varchar(20),U_DEPARTMENT varchar(40);4.“限借书书目”表：create table LIMITS_OF_NUM (L_ID varchar(10) primary key unique not null ,L_NUM int default 3);5.“预约表”表：create table BOOK_ORDER(BO_CALLNUM varchar(30) unique not null,BO_CERTIFICATE int not null,BO_DATE datetime not null,primary key (BO_CALLNUM, BO_CERTIFICATE);6.“书库”表：create table ROOM(R_ROOMNUM int primary key unique not null,R_AREA decimal(10,2),R_BOOKNUM int ,R_ADD varchar(40);7.“图书状态”表：create table STATE(S_CODE varchar(40) primary key unique not null,S_ISBN varCHAR(40) unique not null,S_CALLNUM varCHAR(20) not null,S_ROOMNUM int not null,S_STATE enum(借出,可借,仅供阅览) not null);8“工作人员”表：create table WORKER(W_NUM int primary key unique not null,W_NAME varchar(20) not null ,W_AGE int,W_TEL varchar(20),W_WORKPLACE varchar(40);9.“书商”表:create table BOOKMAN (BM_NUM int primary key unique not null auto_increment,BM_NAME varchar(20) not null,BM_ADD varchar(40)not null,BM_TEL varchar(20) not null);10.“待采购书目”表：create table PURCHASE(P_ISBN VARCHAR(40) primary key unique not null,P_BOOKNAME VARCHAR(40) NOT NULL,P_NUM INT NOT null,P_DATE date not null,P_COST decimal(10,2) not null);设置外键：Alter table STATE add constraint isbn foreign key( S_ISBN) references BOOK(B_ISBN);Alter table BORROW_INFO add constraint CERTIFICATE foreign key(BI_CERTIFICATE) references USER (U_NUM);Alter table BORROW_INFO add constraint CODE foreign key(BI_CODE) references STATE (S_CODE);Alter table USER add constraint ID foreign key(U_ID) references LIMITS_OF_NUM (L_ID);Alter table STATE add constraint ROOMNUM foreign key(S_ROOMNUM) references ROOM (R_ROOMNUM);3.2 插入数据的标准SQL语句insert into PURCHASE values(978-7-81124-035-1,OpenCV教程.基础篇,8,2012-12-12,320.0);insert into PURCHASE values(7-111-18777-6,算法导论,6,2012-12-13,510.0);insert into PURCHASE values(7-5163-1071-6,苏菲的世界,2,2012-12-14,58.0);insert into PURCHASE values(978-7-301-15865-4,工程伦理导论,5,2012-12-15,130.0);insert into WORKER values(39071124 ,李雷,24,图书馆三楼);insert into WORKER values(39071145,韩梅梅,21图书馆一楼);insert into WORKER values(39054852,赵鹏,22图书馆三楼);insert into WORKER values(39584716,杨思程,24图书馆二楼);insert into LIMITS_OF_NUM values(本科生,5);insert into LIMITS_OF_NUM values(研究生,10);insert into LIMITS_OF_NUM values(教师,15);insert into book values(978-7-5399-4393-0,卑鄙的圣人,王晓磊,南京:江苏文艺出版社,2011,28.5);insert into book values(7-5418-1638-8,平凡的世界,路遥,西安:陕西旅游出版社,1999,66);insert into book values(7-121-02530-2,VC+深入详解,孙鑫,北京:电子工业出版社,2006,89);insert into book values(978-7-5357-4451-7,时间简史：普及版,史蒂芬霍金,长沙:湖南科学技术出版社,2006,38);insert into book values(7-206-03004-1,自私的基因,道金斯,长春:吉林人民出版社,1998,24);insert into book values(978-7-5057-2102-9,蘑菇,弗莱彻,北京:中国友谊出版公司,2008,49);insert into book values(7-115-14327-7,MATLAB7.0从入门到精通,周文,北京:人民邮电出版社,2006,54);insert into book values(7-5612-1279-8,理论力学典型题解析及自测试题,和兴锁,西安:西北工业大学出版社,2008,15);insert into book values(7-81077-297-X,系统测试性设计分析与验证,石君友,北京:北京航空航天大学出版社,2003,32);insert into book values(7-5379-3022-8,史努比全集,查尔斯舒尔茨,太原:希望出版社,2002,800);insert into book values(978-7-80671-821-6,黄帝内经 养生智慧,曲黎敏,厦门:鹭江出版社,1998,24);insert into state values(H403/18/V2,978-7-5399-4393-0,A16614,101,可借);insert into state values(N403/18/V3,978-7-5057-2102-9,A16615,101,可借);insert into state values(K403/18/V4,7-115-14327-7,A16616,101,可借);insert into state values(C403/18/V5,7-5612-1279-8,A16617,101,借出);insert into state values(M403/18/V6,7-81077-297-X,B16618,102,借出);insert into state values(K403/18/V7,7-5379-3022-8,B16619,102,借出);insert into state values(D403/18/V8,978-7-80671-821-6,B16620,102,借出);insert into state values(A403/18/V9,7-206-03004-1,B16621,102,借出);insert into state values(L403/18/V10,978-7-5357-4451-7,C16622,103,借出);insert into state values(J403/18/V11,7-121-02530-2,C16623,103,借出);insert into state values(R403/18/V12,7-5418-1638-8,C16624,103,仅供阅览);insert into ROOM values(101,1000.00,100000,J-102);insert into ROOM values(102,800.00,8000,J-201);insert into ROOM values(103,1200.00,12000,J-102);insert into BOOKMAN values(20120100,王容,北四环西路68号新华书店中关村图书大厦;insert into BOOKMAN values(20120101,赵曼语,朝阳区光华路9号1号楼世贸天阶商业中心B1-119铺;insert into BOOKMAN values(20120102,周波鸿,复兴路甲23号城乡贸易中心5层;insert into BOOKMAN values(20120103,谢凯复,朝阳区安立路慧忠北里309号楼第五大道2-3楼;insert into BOOK_ORDER values(A16617,10031084,2012-09-02);insert into BOOK_ORDER values(B16618,10081111,2012-09-03);insert into BOOK_ORDER values(B16620,10062022,2012-09-04);insert into BOOK_ORDER values(B16621,10071104,2012-09-05);insert into BORROW_INFO values(10071025,C403/18/V5,2002-08-01,否,1);insert into BORROW_INFO values(10071025,M403/18/V6,2002-08-02,否,1);insert into BORROW_INFO values(10031084,K403/18/V7,2002-08-03,否,1);insert into BORROW_INFO values(10011111,D403/18/V8,2002-08-04,否,1);insert into BORROW_INFO values(16254944,A403/18/V9,2002-08-05,否,1);insert into BORROW_INFO values(14208113,L403/18/V10,2002-08-06,否,0);insert into BORROW_INFO values(18181870,J403/18/V11,2002-08-07,否,0);insert into user values(10071025,杨成,男,20,本科生,机械工程及自动化);insert into user values(10031126,李准,男,21,本科生,自动化);insert into user values(100311113,苏和,男,23,本科生,自动化);insert into user values(10031084,佩德罗,男,20,研究生,自动化);insert into user values(10011111,李梦瑶,女,21,研究生,材料科学与工程);insert into user values(16254944,任思美,女,21,教师,机械工程及自动化);insert into user values(16254916,邬秀丽,女,21,教师,机械工程及自动化);insert into user values(14208113,柳俊茂,男,40,教师,计算机);insert into user values(18181870,云曼吟,女,56,教师,自动化);3.3某些查询的标准SQL语句select B_NAME AS 书名,S_CALLNUM AS 索书号, S_STATE AS 图书状态 FROM BOOK,ROOM,STATE WHERE( BOOK.B_NAME LIKE %自私的基因% AND BOOK.B_ISBN=STATE.S_ISBN AND STATE.S_ROOMNUM=ROOM.R_ROOMNUM;)select U_NAME AS 姓名, U_SEX AS 性别, U_AGE AS 年龄, U_ID AS 身份, U_DEPARTMENT AS 部门 from book,borrow_info,user,state where book.b_name LIKE 自私的基因 AND book.b_isbn=state.s_isbn AND state .s_code= borrow_info.bi_code AND borrow_info.bi_certificate=user.u_num;select u_num as 借书证号,u_name as 姓名,b_name as 书名,bi_code as 条码号,bi_date as 结束时间,bi_date+INTERVAL bi_renew*30 day as 应还时间,bi_state as 是否已还from book,borrow_info,user,state where (u_name LIKE杨成 AND user.u_num=borrow_info.bi_certificate AND borrow_info.bi_code=state.s_code AND state.s_isbn=book.b_isbn);四、报告总结与感想4.1 组员感想通过这一次数据