SQL图书管理系统设计课题报告

PAGE..SQL图书管理系统设计课题报告__计网0901班小组成员：柳录秧周龙2010-12-03目录1．图书管理系统ER图2．系统功能设计3．数据表的创建4．数据库完整性设计5.SQLServer数据库对象设计1.图书管理系统设计E-R图2.系统功能设计〔1信息录入功能1添加新图书信息。当图书馆收藏新图书时,系统向用户提供新图书信息录入功能,由于同一种书可能会有多本,因此,新图书的信息有两类：某一个ISBN类别的图书信息,包括：ISBN书号、图书类别、书名、作者、出版社、出版日期、价格、馆藏数量、可借数量、图书简介；另一个具体到每一本书的信息,包括：ISBN书号、图书书号、是否可借。每一个ISBN书号和同一个ISBN书号的多本书之间是一对多的关系,每一本书的图书书号是唯一的。2添加读者信息。用于登记新读者信息。包括：借书证号、姓名、性别、身份证号、职称、可借数量、已借数量、工作部门、等。3借阅信息。用于登记读者的借阅情况信息。包括：借书证号、借阅书号、借出日期、借阅期限、归还日期等信息。归还日期为空值表示该图书未归还。〔2数据修改和删除功能1修改和删除图书信息。图书被借出时,系统需要更新图书信息的可借数量,当可借数量为0时,表示该图书都已被借出。当输入的图书信息有错误或需要进行必要更新时,可以修改图书信息；当一种图书所有馆藏图书都已损毁或遗失并且不能重新买到时,该图书信息需要删除。2修改和删除读者信息。当读者的自身信息发生变动,如部门间调动或调离本单位,或违反图书馆规定需要限制其可借阅图书数量时,需要修改读者信息。3还书处理。读者归还图书时,更新图书借阅信息表中的归还日期,读者信息表中的已借数量及ISBN类别信息表中该图书的可借数量。〔3查询和统计功能1图书查询功能。根据图书的各种已知条件来查询图书的详细信息,如书名、作者、出版社、ISBN书号等支持模糊查询。2读者信息查询。输入读者的借书证号、姓名、工作部门等信息,查询读者的基本信息。对查询到的每一个读者,能够显示其未归还的图书编号和书名。3查询所有到期未归还的图书信息。要求结果显示图书编号、书名、读者姓名、借书证号码、借出日期等信息。4统计指定读者一段时间内的某类图书或所有类别图书借阅次数及借阅总次数。3.数据表的创建根据功能要求的说明创建下列数据表：〔1图书ISBN类别信息表图书ISBN类别信息表包括以下字段：ISBN书号、图书类别、书名、作者、出版社、出版日期、价格、馆藏数量、可借数量、图书简介。〔2图书信息表图书信息表包括以下字段：ISBN书号、图书书号、是否可借。〔3读者信息表读者信息表包括以下字段：借书证号、姓名、性别、出生年月、身份证号、职称、可借数量、已借数量、工作部门、家庭地址、。〔4借阅信息表〔图书-读者关系借阅信息表包括以下字段：借书证号、借阅书号、借出日期、借阅期限、归还日期。〔5数据表结构如下表：读者信息表附：红色表主键,蓝色表外键字段名称数据类型字段长度是否为空借书证号varchar10否姓名varchar8否性别char2否出生年月datetime否身份证号varchar18否职称char10否可借数量int否已借数量int否工作部门varchar100是家庭地址varchar100是varchar15是借阅表字段名称数据类型字段长度是否为空序号int否借书证号char10否借阅书号char20否借阅日期datetime否借阅期限int否归还日期datetime否ISBN信息表字段名称数据类型字段长度是否为空ISBN书号char20否图书书号char20否是否可借char2否图书表字段名称数据类型字段长度是否为空ISNBN书号char20否图书类别char20否书名varchar60否作者char10否出版社varchar60否出版日期datetime否价格money否馆藏数量int否可借数量int否图书简介varchar100是4.数据库完整性设计设计者应认真分析和思考各个表之间的关系,合理设计和实施数据完整性原则。给每个表实施主键及外键约束。设定缺省约束。如性别。useJDGLgoaltertabledbo.读者addconstraintdz_xbiedefault'男'for性别go设置非空约束如图书信息表中的书名。useJDGLgoaltertabledbo.图书表addconstrainttu_sm--约束名defaultnotnullfor书名实施CHECK约束。如ISBN类别表中的可借数量小于馆藏数量。useJDGLgoaltertabledbo.ISBN信息表addconstraintdu_kj_kccheck<可借数量<馆藏数量>print'可借数量要小于馆藏数量'goaltertabledbo.借阅表--为借阅表里字段归还时间要大于借阅时间建立约束addconstraintjy_jysj_ghsjcheck<归还时间>借阅时间>goaltertabledbo.借阅表--为借阅期限建立约束addconstraintjy_jyqxcheck<借阅期限<=30>go实施规则。如身份证号码必须为18位。useJDGLgoaltertabledbo.读者addconstraintdz_sfzcheck<len<身份证号>=18>5.SQLServer数据库对象设计1设计一个存储过程,以图书ISBN编号为输入参数,返回借阅该图书但未归还的读者姓名和借书证号。Isnull=null=’’createprocds_ISBNISBNchar<20>asselect姓名,dbo.读者.借书证号fromdbo.读者joindbo.借阅表ondbo.借阅表.借书证号=dbo.读者.借书证号joindbo.图书信息表ondbo.图书信息表.图书书号=dbo.借阅表.借阅书号joindbo.ISBN信息表ondbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号wheredbo.图书信息表.ISBN书号=ISBNand<归还时间isnull>execds_ISBN'1001'–查询ISBN编号为1001图书未还图书的读者姓名和借书证号dropprocds_ISBN–删除此存储过程以书名为输入参数建立图书信息的模糊查询的存储过程createprocdu_smsmvarchar<40>asselectdbo.ISBN信息表.*,dbo.图书信息表.是否可借,图书书号fromdbo.ISBN信息表joindbo.图书信息表ondbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号where书名like'%'+sm+'%'goexecdu_sm政--查询政治这本书的模糊查询2读者资料查询：设计一个有多个输入参数的存储过程,返回读者的详细信息。设计另一存储过程并以读者借书证号为输入参数,返回该读者未归还的图书名称和图书编号。以读者姓名和借书证为例作为输入参数返回读者基本信息的存储过程createprocdu_jszh_xm--此存储过程名称jszhchar<10>,--借书证号xmchar<8>--姓名asselect*fromdbo.读者where借书证号=jszhand姓名=xmexecdu_jszh_xm'001','张三'--查询此存储过程以借书证号作为输入参数返回该读者未归还的图书名称和书号createprocdu_jszhjszhchar<10>--借书证号asselectdbo.读者.借书证号,dbo.图书信息表.图书书号,dbo.ISBN信息表.书名fromdbo.读者joindbo.借阅表ondbo.读者.借书证号=dbo.借阅表.借书证号joindbo.图书信息表ondbo.借阅表.借阅书号=dbo.图书信息表.图书书号joindbo.ISBN信息表ondbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号wheredbo.借阅表.归还时间isnullanddbo.读者.借书证号=jszh;dropprocdu_jszh--删除此存储过程execdu_jszh'001'--查询借书证号为001的读者未归还的图书信息3到期图书查询：设计一个视图,返回所有逾期未归还的图书的编号、书名、读者姓名等信息。Datepart<day,借阅日期,getdate<>>createviewjybiaoasselectdbo.读者.姓名,dbo.图书信息表.图书书号,dbo.ISBN信息表.书名fromdbo.读者joindbo.借阅表ondbo.读者.借书证号=dbo.借阅表.借书证号joindbo.图书信息表ondbo.借阅表.借阅书号=dbo.图书信息表.图书书号joindbo.ISBN信息表ondbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号where<getdate<>><借阅时间+借阅期限>>and<归还时间isnull>--建立所有逾期图书的视图select*fromjybiao--查询视图表4统计图书借阅次数：设计一个以两个日期作为输入参数的存储过程,计算这一段时间内各类别图书被借阅的次数,返回图书类别、借阅次数的信息。createprocISBN_jysj--cssjdatetime,--初始时间jcsjdatetime--结束时间asselectdbo.ISBN信息表.图书类别,count<dbo.ISBN信息表.ISBN书号>as次数fromdbo.读者joindbo.借阅表ondbo.借阅表.借书证号=dbo.读者.借书证号joindbo.图书信息表ondbo.图书信息表.图书书号=dbo.借阅表.借阅书号joindbo.ISBN信息表ondbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号where借阅时间>=cssjandjcsj>=借阅时间groupbydbo.ISBN信息表.图书类别,dbo.ISBN信息表.ISBN书号设以两个输入的时间参数execISBN_jysj'2009.4.3','2011.4.3'--以这两个时间参数为例查询该存储过程5加快数据检索速度,用图书编号为图书信息表建立索引。createindexdsxxb_tssh_indexondbo.图书信息表<图书书号>以图书编号为图书创建索引名为dsxxb_tssh_index6为读者信息表创建一个删除触发器,当一个读者调出本单位时,将此读者的资料从读者信息表中删除。注意实施业务规则：有借阅书的读者不得从读者信息表中删除。createtriggerdelete_duzh--读者ondbo.读者fordeleteasif<select已借数量fromdbo.借阅表innerjoindeletedondbo.借阅表.借书证号=deleted.借书证号>>0beginprint<'还有书未还不可删除该条数据'>rollbacktransactionendelseprint<'记录已删除'>droptriggerdelete_duzh–删除读者触发器7借阅处理：为借阅信息表设计INSERT触发器,在读者借阅时更改ISBN类别信息表,且可借数量减1,图书信息表是否可借列的值变为"不可借",读者信息表中该类读者已借阅数加1。createtriggerinsert_crondbo.借阅表forinsertasdeclarejyshchar<20>--借阅书号declarejszhchar<10>--借书证号declareISBNchar<20>--ISBN书号selectjysh=inserted.借阅书号,jszh=inserted.借书证号,ISBN=<selectISBN书号fromdbo.图书信息表wheredbo.图书信息表.图书书号=inserted.借阅书号>frominserted,dbo.借阅表ifjyshnotin<select图书书号fromdbo.图书信息表>beginprint<'该图书书号不存在'>rollbacktransactionendelseifjszhnotin<select借书证号fromdbo.读者>beginprint<'该借书证号不存在'>rollbacktransactionendelseifISBNnotin<selectISBN书号fromdbo.ISBN信息表>beginrollbacktransactionendupdatedbo.读者set已借数量=已借数量+1,可借数量=可借数量-1wheredbo.读者.借书证号=jszhupdatedbo.图书信息表setdbo.图书信息表.是否可借='否'wheredbo.图书信息表.图书书号=jyshupdatedbo.ISBN信息表--更改ISBN信息表中的信息set可借数量=可借数量-1whereISBN书号=ISB