实验四+数据库设计

图书馆数据库设计实验目的 1、理解并运用数据库设计的常见步骤来设计满足给定需求的关系数据模型； 2、结合有关的关系数据理论〔如标准化理论〕对模型进行优化处理； 3、锻炼学生的团队意识。实验要求每班分成5个小组〔5~7人/组〕，每组交一份实验报告，并在实验报告中列出该组的成员及其分工。实验内容从用户需求〔详见所给材料〕出发，按照数据库设计步骤〔详见教材第七章〕，分别完成如下内容：1、数据字典〔不要求画数据流图〕 给出主要的数据结构、数据项的数据字典，具体参考教材P207。2、概念设计：ER模型 给出最终的全局ER图，要求：与需求一致；适当给出中间过程；模型力求精简，消除不必要的冗余〔详见教材P222〕，并给出理由或说明。3、逻辑设计：关系模型把ER模型转换成各个关系模式〔参照教材小节〕；进行适当地标准化〔要求各关系模式至少满足3NF要求〕；设计相关完整性约束。4、物理设计：根据业务需求，适当地添加索引〔包括聚簇索引和非聚簇索引〕；设计相关用户视图〔即外模式〕。〔一〕需求分析，图书和杂志的借阅与归还图书的购进新书上架与旧书移除图书的预约当图书已经借出或没有所借的数时，当预约的图书归还时或新购进时通知用户。用户：学生权限借阅，预约和查询和图书管理员权限管理图书的外借，图书上架，旧书移去，新书购进等。系统能进行各种操作如，更新，增加，删除关于图书信息还有用户和管理员的增删改等操作。图书管理系统的运行环境windows操作系统上系统的功能易于扩展根据以上需求设计数据字典如下1，数据项数据项名数据类型长度别名取值范围Sno字符型10学号Sname字符型10姓名Ssex字符型2性别男、女Sgrade字符型10年级Sdept字符型10学院Smajor字符型10专业Sclass字符型10班级Bno字符型10条码号Bname字符型10书名Bahthor字符型10作者Bpress字符型10出版社Bprice浮点型〔单精度〕10定价Pno字符型10馆藏地编号1—10Bstate字符型10图书状态Bbstate字符型10借阅状态Cno字符型10卡号Bno字符型10条码号Bbno字符型10借书代号Bbtime日期型10借书时间〔应还时间—借书时间〕小于一个月Brtime日期型10应还时间Pno字符型10馆藏地编号1—10Pname字符型10馆藏地名称Cno字符型10卡号Rtime日期型10时间Rstate字符型10欠书状态Rfine浮点型〔单精度〕10超时罚款Cno字符型10卡号Cstate字符型10卡状态Sno字符型10学号Qname字符型10书名Qahthor字符型10作者Qpress字符型10出版社Qprice浮点型〔单精度〕4定价Qfrequence整型4搜索频率2.，数据结构数据结构名含义说明组成学生信息记录学生的根本信息学号、姓名、性别、年级、学院、专业、班级图书信息记录图书的根本信息条码号、书名、作者、出版社、定价、馆藏地编号、图书状态、借阅状态借阅信息记录学生借书信息卡号、条码号、借书代号、借书时间、应还时间馆藏地信息图书的分布情况馆藏地编号、馆藏地名称学生日常事务信息反映学生借书状态，如是否允许借书等卡号、时间、欠书状态、超时罚款借书卡信息学生借书卡根本信息，放映学生借书卡状态卡号、卡状态、学号〔二〕概念设计识别图书管理系统中的实体，识别实体的属性，识别实体的关键字，识别实体间的联系，利用实体关系图〔E—R图〕来描述图书管理相关实体、属性及关系，从而到达为图书管理系统建立良好的数据模型的目的。图12状态查询、借书、还书、注销业务E-R图图13新书入库E-R图图14图书搜索E-R图E——R全局图〔三〕逻辑结构设计将E-R图转换为关系模型，即将实体、实体的属性和实体之间的联系转化为关系模式实体定义学生：{学号，姓名，性别，年级，学院，专业，班级}图书：{条码号，书名，作者，出版社，定价，馆藏地编号，图书状态，借阅状态}馆藏地：{馆藏地编号，馆藏地名称}借书卡：{卡号，卡状态，学号}缺书：{书名，作者，出版社，定价，搜索频率}学生日常事务信息：{卡号，时间，欠书状态，超时罚款}1实体Student数据项名数据类型长度别名是否为空主外键Sno字符型10学号否主键Sname字符型10姓名Ssex字符型2性别Sgrade字符型10年级Sdept字符型10学院Smajor字符型10专业Sclass字符型10班级Book数据项名数据类型长度别名是否为空主外键Bno字符型10条码号否主键Bname字符型10书名否Bahthor字符型10作者否Bpress字符型10出版社否Bprice浮点型〔单精度〕4定价否Pno字符型10馆藏地编号否外键Bstate字符型10图书状态否Bbstate字符型10借阅状态否Jieyue数据项名数据类型长度别名是否为空主外键Cno字符型10卡号否外键主键Bno字符型10条码号否外键Bbno字符型10借书代号否Bbtime日期型10借书时间否Brtime日期型10应还时间否Btime日期型10还书时间注：〔应还时间—借书时间〕等于一个月Place数据项名数据类型长度别名是否为空主外键Pno字符型10馆藏地编号否主键Pname字符型10馆藏地名称否Rountin数据项名数据类型长度别名是否为空主外键Cno字符型10卡号否外键主键Rtime日期型10时间否Rstate字符型10欠书状态否Rfine浮点型〔单精度〕10超时罚款否Card数据项名数据类型长度别名是否为空主外键Cno字符型10卡号否主键Cstate字符型10卡状态否Sno字符型10学号否外键LinkBook数据项名数据类型长度别名是否为空主外键Qname字符型10书名否主键Qahthor字符型10作者否Qpress字符型10出版社否Qprice浮点型〔单精度〕4定价Qfrequence整型4搜索频率2，视图设计为学生进行图书查询建立视图：V-BookSelect数据项名数据类型长度别名是否为空Bname字符型10书名否Bahthor字符型10作者否Bpress字符型10出版社否Bprice浮点型〔单精度〕4定价否Pno字符型10馆藏地编号否Bstate字符型10图书状态否Bbstate字符型10借阅状态否建立学生的累计借书视图：V-AllBorrow数据项名数据类型长度别名是否为空Bno字符型10条码号否Bname字符型10书名否Bahthor字符型10作者否Bpress字符型10出版社否Bprice浮点型〔单精度〕4定价否Bbno字符型10借书代号否Bbtime日期型10借书时间否Btime日期型10还书时间建立缺书和图书丧失视图，为购书时提供信息：V-BuyBook1数据项名数据类型长度别名是否为空Qname字符型10书名否Qahthor字符型10作者否Qpress字符型10出版社否Qprice浮点型〔单精度〕4定价V-BuyBook2数据项名数据类型长度别名是否为空Bname字符型10书名否Bahthor字符型10作者否Bpress字符型10出版社否Bprice浮点型〔单精度〕4定价否〔四〕物理设计为上一阶段的关系模型选取一个最适合的应用要求的物理结构，即确定存取方法和存储结构。1索引存取方法为了提高检查图书借阅情况的速度，对借阅信息中的借书代号建立聚簇索引；为了提高检查图书位置的速度，对馆藏地编号建立聚簇索引。2，功能模块分析〔1〕办卡、挂失、注销学生在图书馆中必须持卡办理一切业务，新生必须首先办理借书卡，当借书卡丧失时需办理挂失业务，毕业生或中途退学者必须办理注销卡业务，以防止借书卡的流失。查询、借书、环书、注销〔2〕学生在图书馆中持卡可以进行以下业务：查询自己借书状态，借书，还书，当所借书籍丧失时需办理注销业务。〔3〕图书查询、缺书登记学生需要查询自己所需书籍时，假设馆中有那么直接借书；假设馆中没有，可以进行缺书登记〔五〕数据库实施根据关系模式建立根本表、视图和索引，并完成存取过程的设计。存取过程编号存取过程名作用1状态查询查询学生状态2借书完成借书过程3还书完成还书过程4注销完成丧失图书注销过程5办卡办理新借书卡6挂失完成借书卡挂失过程7用户注销离校学生办理注销过程8图书查询需要图书查询9缺书登记查询查看图书馆中没有书籍登记情况10追加缺书对馆中没有图书进行登记11修改缺书对已有缺书进行修改12累计借书查询查看个人累计借书情况创立根本表createtableStudent(SnoChar(10)NOTNULLunique,SnameChar(10),SsexChar(2),SgradeChar(10),SdeptChar(10),SmajorChar(10),SclassChar(10),primarykey(Sno))createtablePlace(Pnochar(10)notnullunique,Pnamechar(10)notnull,primarykey(Pno))createtableBook(Bnochar(10)notnullPRIMARYKEYNONCLUSTERED,Bnamechar(10)notnull,Bahthorchar(10)notnull,Bpresschar(10)notnull,Bpricenumeric(9)notnull,Pnochar(10)notnull,Bstatechar(10)notnull,Bbstatechar(10)notnull,foreignkey(Pno)referencesPlace(Pno))createindex书名onBook(Bname)createtableCard(Cnochar(10)notnullunique,Cstatechar(10)notnull,Snochar(10)notnull,primarykey(Cno),foreignkey(Sno)referencesStudent(Sno))createtableJieyue(Cnochar(10)notnull,Bnochar(10)notnull,Bbnochar(10)notnull,Bbtimedate(10)notnull,Brtimedate(10)notnull,Btimedate(10),primarykey(Cno,Bno,Bbno,Bbtime),foreignkey(Cno)referencesCard(Cno),foreignkey(Bno)referencesBook(Bno))createtableLinkBook(Qnamechar(10)notnull,Qahthorchar(10)notnull,Qpresschar(10)notnull,Qpricefloat(4),Qfrequencefloat(4),primarykey(Qname,Qahthor,Qpress))createtableRountin(Cnochar(10)notnull,Rtimedatetime,Rstateintnotnull,Rfinenumeric(9),primarykey(Cno,Rtime),foreignkey(Cno)referencesCard(Cno))创立视图createviewV_BookSelect(Bname,Bahthor,Bpress,Bprice,Pno,Bstate,Bbstate)asselectBname,Bahthor,Bpress,Bprice,Pno,Bstate,BbstatefromBookwhereBstate='在馆'createviewV_BuyBook1(Qname,Qahthor,Qpress,Qprice)asselectQname,Qahthor,Qpress,QpricefromLinkBookwhereQfrequence>10createviewV_BuyBook2(Bname,Bahthor,Bpress,Bprice)asselectBname,Bahthor,Bpress,BpricefromBookwhereBstate='丧失'createviewV_Fine(Cno,Sno,Rtime,Rfine)asselectCard.Cno,Sno,Rtime,RfinefromCard,RountincreateviewV_SBB1(Cno,Bno,Bname,Bahthor,Bpress,Bprice,Bstate,Bbstate,Bbno,Bbtime,Brtime,Btime,Rstate,Rfine)asselectJieyue.Cno,Book.Bno,Bname,Bahthor,Bpress,Bprice,Bstate,Bbstate,Bbno,Bbtime,Brtime,Btime,Rstate,RfinefromJieyue,Rountin,BookcreateviewV_SBB2(Cno,Bno,Bname,Bborrowno,Bahthor,Bpress,Bprice,Pno,Bbno,Bstate,Bbstate,Bbtime,Brtime,Btime,Rtime,Rstate,Rfine)asselectJieyue.Cno,Book.Bno,Bname,Bbno,Bahthor,Bpress,Bprice,Pno,Bbno,Bstate,Bbstate,Bbtime,Brtime,Btime,Rtime,Rstate,RfinefromJieyue,Rountin,BookwhereBtimeisnullcreateviewV_SBB3(Cno,Bno,Bname,Bahthor,Bpress,Bprice,Pno,Bstate,Bbstate,Brtime,Rstate,Rfine)asselectJieyue.Cno,Book.Bno,Bname,Bahthor,Bpress,Bprice,Pno,Bstate,Bbstate,Brtime,Rstate,RfinefromJiey