图书馆借阅系统数据库设计

经典word整理文档，仅参考，双击此处可删除页眉页脚。本资料属于网络整理，如有侵权，请联系删除，谢谢！二年3月日一、实验环境二、实验目的四．概念结构设计(概念模型)的过图书借阅系统的E-R图nmm11m1nmnnnmR图书：图将图书借阅E-R图转换为关系模型图书列名数据类型Char可否为空notnullnotnullnotnullnotnullnotnullNotnull说明BookIDBookNo图书编号Char图书的索书号图书的书名图书BookNameBookWriterBookPulishBooknumBookDateBookClassBooknumsBookStateBookRNoCharCharChar图书出版社图书数量SmallintDate出版日期Char图书的分类图书可借数量图书是否可借图书所在馆室号smallintCharNotnullnotnullnotnullChar读者列名数据类型Char可否为空notnullnotnullnotnullnotnull说明读者编号ReaIDReaNameReaSexReaNoChar读者姓名Char读者性别Char读者学号ReaDepReaPrefChar读者所在学院读者所属专业CharReaGradeReaDateReaBsCharDate读者的年级办证日期已借本数借书状态借书上限notnullNotnullNotnnllNotnullSmallintCharReastateReasxSmallint管理员列名主键数据类型Char可否为空notnull说明管理员编号管理员姓名管理员性别CharnotnullChar馆室列名数据类型Char可否为空notnullnotnullnotnull说明馆室号Char馆室管理员编号馆室拥有图书数目馆室地址CharCharChar馆室类型借阅信息列名ReaID数据类型CharCharCharCharDate可否为空notnullnotnullnotnull说明读者编号图书编号图书名BookIDBookNameBookWriterOutdateIndatenotnull借阅时间归还时间应还时间罚款金额处理状态管理员编号DateYHdateFineDatenotnullNotnullCharCharCharCLStateMIDnotnull创建数据库及表格男'女),,)’,)’,,男'女),)建立视图,,,,)))))建立索引建立触发器))’)存储过程1.Book_Insert的定义:CREATEPROCEDUREBook_Insert@BookIDchar(9),char(20),char(50),@BookNo@BookName@BookWriterchar(30),@BookPublishchar(20),@BookDate@BookClassdatetime,char(20),@Booknumssmallint,@Booknumsmallint,@BookStatechar(10),@BookRNoaschar(5)insertintoBookvalues(@BookID,@BookNo,@BookName,@BookWriter,@BookPublish,@BookDate,@BookClass,@BookState@Booknums,@Booknum);2.Reader_Insert的定义:CREATEPROCEDUREReader_Insert@ReaIDchar(9),char(10),char(2),char(9),char(20),char(5),char(20),Datetime@ReaName@ReaSex@ReaNo@ReaDep@ReaGrade@ReaPref@ReaDate@ReaBssmallint,@Reastatechar(5),@Reasxsmallint,asinsertintoReadervalues(@ReaID,@ReaName,@ReaSex,@ReaNo,@ReaDep,@ReaGrade,@ReaPref,@ReaDate,@ReaBs,@Reastate,@Reasx;3.Maneger_Insert的定义:CREATEPROCEDUREManeger_Insert@MIDchar(10),@MNamechar(10),@MSexchar(2),asinsertintoManegeralues(@MID,@MName,@MSex);4.Borrow_Insert的定义:CREATEPROCEDUREBorrow_Insert@BookIDchar(9),@ReaIDchar(9),@OutdateDatetime,@YHdateDatetime,@IndateDatetime,@Finechar(5),@CLStatechar(8),@MIDchar(10)asinsertintoBorrowvalues(@BookID,@ReaID,@Outdate,@YHdate,@Indate,@Fine,@CLState,@MID);5.Query_Reader_R的定义：createprocedureQuery_Reader_R@ReaIDchar(9)asselect*fromReaderviewwhere编号=ltrim(@ReaID);6.Query_Reader_M的定义：select*fromReaderview7.Query_Book_Writer的定义：@BookWriterchar(50)*wherelike'%'+ltrim(@BookWriter)+'%';8.Query_Book_Name_Publish的定义：createprocedureQuery_Book_Name_Publish@BookNamechar(50),@BookPublishchar(20)asselect*fromBookviewwhere书名=ltrim(@BookName)and=ltrim(@BookPublish);9.Delete_Reader的定义：createprocedureDelete_Reader@ReaNochar(9)asdel