数据库原理与应用大作业

1、?数据库原理与应用?综合设计任务书前言?数据库原理与应用?课程的重点知识模块包括：1数据库设计、2用SQL实现建库、建表、查询、更新、和创立视图、3存储过程和触发器设计.针对这三个应用水平,用一个案例作为背景,布置三次大作业.在校大学生都能理解“图书治理系统的应用场合和业务流程.因此,以图书治理系统作为案例来布置作业,可以降低业务分析难度,让学生将主要精力放在知识消化与技术应用上.本文档包括四个局部.第一局部描述系统的需求,第二局部提出E-R模型设计和关系模型设计的任务；第三局部提出在SQLServer中,用SQL语句来建库、建表、查询、更新数据、创立视图的任务；第四局部,根据应用需求、平安需

2、求和数据完整性要求,提出设计存储过程和触发器的任务.每个任务之前,都给出了完成任务所需要掌握的关键知识点,学生可以在对这些知识点进行复习的根底上完成任务,每个任务是一次大作业.第一局部案例的需求描述本局部描述“图书治理系统的需求,学生通过阅读本局部内容,了解系统的功能要求、运行环境,对系统所需的数据有总体熟悉,作为三次作业的根底.1.1 需求故事洁是图书阅览室的治理员,她找到程序开发小组“枫,请他们帮助开发一套治理程序.他们围坐在一起,开始以下对话：洁：你们进来都看到了,那几排大柜子,全都放满了书,很多教师来借书,治理起来越来越难,希望帮我开发一个治理软件.枫：你希望软件提供哪些功能.洁：首先

3、是能登记和查看图书信息,借书和还书的时候要方便操作.对于我来说,看图书的状态很重要,就是说要知道哪些图书已经借出去了,哪些书已经过了借阅期,此外还要做一些统计表大概就是这些吧.枫：你怎么登记借书呢？规定的借阅期有多长？过了借阅期怎么办？洁：每个老师都有一个工号,我就计工号和图书的ISBN号,一个人最多能借2本书.不同类型的书借阅期限是不一样的,比方文学作品的借阅期规定是15天,教学参考书的借阅期限是半年.有的人不自觉,过期好久都不还,我现在是翻记录本一个个地找,然后打到他的部门,并且发催还邮件.枫：同一ISBN只有一本书吗？洁：是的,现在把书分成这样几类：教学参考书、文学作品、学术期刊、学术专

4、著.枫：你想统计一些什么数据洁：主要是分类统计图书册数和价值、图书借阅次数.我们想知道哪些图书最受欢送.枫：阅览室是你一个人在管么？洁：阅览室有两个人.我们俩都有一台计算机,最好两台计算机都可以用上这个软件.1.2 需求分析1功能需求教师信息治理：用于教师根本资料的增删改查.图书信息治理：用于图书根本信息的增删改查,分类统计图书册数和价值.借书登记：记录借书时间、所借图书、借书人、办理人.还书登记：记录还书时间、所还图书、还书人、办理人.催还：查询借阅逾期的借书信息,给借书人发电子邮件,给借书人的部门打.2运行环境要求图1-2:运行环境拓扑图系统采用C/S模式,有两台PC和一台效劳器,联成一个

5、局域网.PC上安装图书治理软件的客户端,效劳器上安装DBMS,效劳器也可由两台PC中的一台来代替.第二局部作业1E-R模型与关系模型设计总分值8分本局部的任务是：在需求分析的根底上,进行E-R图设计,然后将E-R模型转换为关系模型.任务：1） 根据需求描述,绘制E-R图.2） 将E-R图转换成关系模型,写出所有的关系模式,并写出每一个关系是根据什么规那么转换而成的.3） 在已经形成的关系模型下,举例说明连接运算、投影运算、选择运算.参考答案：属性写在了实体和联系图形内任务：1根据需求描述,分别为“图书治理系统的不同功能模块绘制局部E-R图.1部门部门名,教师信息治理：教师教师工号,姓名,性别,

6、Email图书根本信息治理:借还书登记:催还书登记:2整体E-R图3关系模式?类型表类型名,彳t阅期限,依据：实体转换为关系.?图书表BN,书名,作者,出版社,出版时间,单价,类型名,状态,依据：实体转换为关系,1:N的联系合并到N,在N方增加一个外键：类型名.注：状态属性为库存,借出?治理员表治理员名,性别,口令,依据：实体转换为关系.?教师表教师工号,姓名,性别,Email,部门名,依据：实体转换为关系,1:N的联系合并到N,在N方增加一个外键：部门名.?部门表部门名,依据：实体转换为关系.?借还表借还业务编号,ISBN,教师工号,借办理人,借出日期,还办理人,归还日期,依据,M:N联系转

7、换为一个关系,取联系的实体的键加联系属性作为其属性,可以增加一个列作为主键.?催还表催还业务编号,ISBN,教师工号,办理人,催还日期,依据,M:N联系转换为一个关系,取联系的实体的键加联系属性作为其属性,可以增加一个列作为主键.4在已经形成的关系模型下,举例说明连接运算、投影运算、选择运算.连接运算：查看类型的图书已经超过了有效借出期限.需要图书,类型,借还关系投影运算：查看图书的书名、作者.选择运算：查看单价高于50元的图书.第三局部作业2用SQL语句建库、查询、更新数据、创立视图总分值9分本局部的任务是：在关系模型的根底上,设计出数据库的逻辑结构,然后在SQLServer中用SQL语句创

8、立数据库、数据表、并输入一些原始数据,写SQL语句,进行数据增删改,创立视图.任务：1） 在第一次大作业的参考答案根底上,将每个关系转换为SQLServer支持的表结构,写出表结构.2） 使用SQL语句,创立数据库,数据库名为BookStore3） 使用SQL语句,在数据库中创立表,设定表的主键约束、外键约束、检查约束、非空约束.4） 写以下增删改查操作的SQL语句1、增加三条类型表数据2、增加三条教师表数据3、增加五条图书表数据4、某教师今天借了某书,涉及两个操作,一是图书表的状态改为“借出,二是增加一个借还表记录5、将某书的单价改为32元.5） 写出创立以下视图的SQL语句1 .查询某教师

9、的借还书记录2 .查询某本书的流转记录3 .借出但未归还的图书视图4 .借阅逾期视图5 .图书分类统计册数和总价值的视图参照完成：1表结构治理员表列名约束用户名Char(10)pk性别Char(2)NOTNULL,且默认为男口令Nchar(20)NOTNULL类型表列名约束类型名NChar(10)Pk借阅期限tinyintNOTNULL图书表列名约束ISBNChar(20)Pk书名Nchar(20)NOTNULL作者Nchar(20)NOTNULL出版社Nchar(20)NOTNULL出版时间DateTimeNOTNULL单价moneyNOTNULL类型名Nchar(10)FKreferenc

10、es类型表.图书类型状态Nchar(2)状态in库存,借出部门表列名约束部门名NChar(10)Pkchar(11)NOTNULL教师表列名约束工号Char(4)Pk姓名Nchar(4)NOTNULL性别Char(2)NOTNULL,且默认为男姓名Nchar(4)NOTNULLEmailChar(30)NOTNULL部门名Nchar(10)Fkreferences部门表部门名借还记录表列名约束业务编号intPPk自动标小ISBNchar(20)Fkreferences图.isbn工号Char(4)Fkreferences教师表.工号借办理人Char(10)NOTNULL借出日期datetime

11、NOTNULL还办理人Char(10)归还日期datetime催还记录表列名约束业务编号intPk自动标小ISBNchar(11)Fkreferences图.isbn工号Char(4)Fkreferences教师表.工号办理人Char(10)NOTNULL催还日期datetimeNOTNULL1) 使用SQL语句,创立数据库,数据库名为Bookstore语句：CreateDatabseBookstore2) 使用SQL语句,在数据库中创立表,设定表的主键约束、外键约束、检查约束、非空约束.语句：CREATETABLE治理员表(治理员名nchar(10)NOTNULL,性另ijChar(2)no

12、tnullDEFAULT'男,口令nchar(20)NOTNULL,CONSTRAINTPK_治理员PRIMARYKEYCLUSTERED(治理员名ASC)ONPRIMARYGOCREATETABLE类型表(类型名nchar(10)NOTNULL,借阅期限tinyintNOTNULL,CONSTRAINTPK_类型PRIMARYKEYCLUSTERED(类型名ASC)ONPRIMARYGOCREATETABLE图书表(ISBNchar(20)NOTNULL,书名nchar(20)NOTNULL,作者nchar(20)NOTNULL,出版社nchar(20)NOTNULL,出版时间dat

13、etimeNOTNULL,单价moneyNOTNULL,类型名nchar(10)NOTNULL,状态nchar(2)NOTNULLCHECK(状态in(库存,借出'),CONSTRAINTPK_图书PRIMARYKEYCLUSTERED(ISBNASC),CONSTRAINTFK_图书_类型FOREIGNKEY(类型名)REFERENCES类型表(类型名)ONUPDATECASCADEONDELETECASCADE)ONPRIMARYGOCREATETABLE部门表(部门名nchar(10)NOTNULL,char(11),CONSTRAINTPK_部门表PRIMARYKEYCLUST

14、ERED(部门名ASC)ONPRIMARYGOCREATETABLE教师表(工号char(4)NOTNULL,姓名nchar(4)NOTNULL,性别Char(2)notnullDEFAULT'男',Emailchar(30)NOTNULL,部门名nchar(10)NOTNULL,CONSTRAINTPK_教师表PRIMARYKEYCLUSTERED(工号ASC),CONSTRAINTFK_部门表教表FOREIGNKEY(部门名)REFERENCES部门表(部门名)ONUPDATECASCADEONDELETECASCADE)ONPRIMARYGOCREATETABLE借还记录

15、表(业务编号intidentity(0,1)NOTNULL,ISBNchar(20)NOTNULL,工号char(4)NOTNULL,代办理人char(10)NOTNULL,借出日期datetimeNOTNULL,还办理人char(10)NULL,归还日期datetimeNULL,CONSTRAINTPK_借还记录表PRIMARYKEYCLUSTERED(业务编号ASC),CONSTRAINTFK_借还t己录表_教师表FOREIGNKEY(工号)REFERENCES教表(工号)ONUPDATECASCADEONDELETECASCADE,CONSTRAINTFK_借还t己录表借还t己录表FOR

16、EIGNKEY(ISBN)REFERENCES图书表(ISBN)ONUPDATECASCADEONDELETECASCADE)ONPRIMARYGOCREATETABLE催还记录表(业务编号intidentity(0,1)NOTNULL,ISBNchar(20)NOTNULL,工号char(4)NOTNULL,办理人char(10)NOTNULL,催还日期datetime,CONSTRAINTPK_催还记录表PRIMARYKEYCLUSTERED(业务编号ASC),CONSTRAINTFK_催还t己录表_教币表FOREIGNKEY(工号)REFERENCES教表(工号)ONUPDATECASC

17、ADEONDELETECASCADE,CONSTRAINTFK_催还记录表图书FOREIGNKEY(ISBN)REFERENCES图书表(ISBN)ONUPDATECASCADEONDELETECASCADE)ONPRIMARYGO3) 写以下增删改查操作的SQL语句1 .增加三条类型表数据语句:INSERTINTO类型表类型名,借阅期限VALUES'计算机,150INSERTINTO类型表类型名,借阅期限VALUES'文学',180INSERTINTO类型表类型名,借阅期限VALUES'经济',30-delete类型表-select*from类型表2

18、.增加三条部门表数据语句：INSERTINTO部门表部门名,INSERTINTO部门表部门名,INSERTINTO部门表部门名,-select*from音6门表VALUES('网络系,'#39;VALUES('外语系#39;VALUES('治理系','#39;3 .增加三条教师表数据语句:INSERTINTO教师表工号,姓名,性另,Email','zs163','网络系'INSERTINTO教师表工号,姓名,性另,Email'

19、,'LS163','网络系'INSERTINTO教师表工号,姓名,性另,Email,部门名VALUES'J001','张三,女,部门名VALUES'J002',李四,男,部门名VALUES'J003','王五,男','ww163','夕卜语系')-select*from教师表4 .增加五条图书表数据语句：INSERTINTO图书表ISBN,书名,作者,出版社,出版时间,单价,类型名,状态VALUES'9789302218661','C#

20、','江红,清华大学出版社','2021.2.10',45,'计算机库存INSERTINTO图书表ISBN,书名,作者,出版社,出版时间,单价,类型名,状态VALUES'9787802229761','人生博弈密码','吕叔春','中国华侨出版社','2021.2.1',36.8,'文学','库存INSERTINTO图书表ISBN,书名,作者,出版社,出版时间,单价,类型名,状态VALUES'9787301150894',

21、9;经济学原理','梁小民,北京大学出版社','2021.4.1',54,经济,借出-select*from图书表5 .增加三条治理员表数据语句：INSERTINTO治理员表治理员名,性别,口令VALUES吴管,男,wg001'INSERTINTO治理员表治理员名,性别,口令VALUES'李管,女,Lg002'INSERTINTO治理员表治理员名,性别,口令VALUES徐管,男,Xg003'-select*from治理员表-select*from-select*from-select*from-select*from-se

22、lect*from教师表部门表类型表图书表治理员表6 .某教师今天借了某书,涉及两个操作,一是图书表的状态改为“借出,二是增加一个借还表记录语句：insert借还记录表(ISBN,工号,代办理人,借出日期,还办理人,归还日期)values('9787802229761','J001','wg001','2021.3.20','Lg002','')-select*from借还记录表-delete借还记录表update图书表set状态='借出whereISBN='978780222976

23、1'-select*from图书表7 .将某书的单价改为32元.语句：update图书表set单价=32whereISBN='9787802229761'-select*from图书表语句：-select*from教师表-select*from部门表-select*from类型表-select*from图书表-select*from治理员表-select*from借还记录表selecta.工号,姓名,部门名,书名from教师表ajoin借还记录表c8.查询某教师的借还书记录ona.join图书表bonc.ISBN=b.ISBNwherea.工号='J001

24、9;工号=c.工号go9 .查询某本书的流转记录语句：-select*from借还记录表selecta.工号,姓名,部门名,书名,借出日期,归还日期from教师表a,图书表b,借还记录表cwherea.工号=c.工号andb.ISBN=c.ISBNandb.ISBN='9787802229761'4) 写出创立以下视图的SQL语句1. 借出图书视图语句：-select*from借还记录表-select*from图书表Createview借出图书视图Asselect*from图书表where状态='借出'go2. 借阅逾期视图语句：-select*from图书表-

25、select*from借还记录表-select*from类型表CreateView借阅逾期视图AsSELECTa.ISBN,a.书名,b.工号,b.借出日期FROM图书表aJOIN借还记录表bONa.ISBN=b.ISBNJOIN类型表CONa.类型名=c.类型名WHEREa.状态='借出anddatediffday,b.借出日期,getdatec.借阅期限-select*from借阅逾期视图GO3. 图书分类统计册数视图语句：-select*from图书表CreateView图书统计视图AsSELECT类型名,count*各类图书册数,sum单彳各类图书总价FROM图书表GROUPB

26、y类型名-select*from图书统计视图第四局部作业3存储过程与触发器设计总分值8分任务：1为BookStore数据库增加一个用户oper1,该用户可以进行系统所需的应用操作,权限尽可能精简.请描述该用户的权限.2设计存储过程a数据初始化过程sp_Inito包含的操作有：所有的用于测试图书信息、教师信息、历史记录清空,输入图书分类、输入治理员信息、输入借书规定.b某教师在某时间借了某本书,由某治理员办理.c某教师在某时间还了某本书,由某治理员办理d查看某人的全部借还书记录e查看某本书的历史流转记录f某治理员在某时间给全部借阅过期者发催还邮件.g对书名进行模糊搜索搜索条件可以是多个模糊列值的组合,例如：搜索书名包含“网络两个字,并且是近3年出版的图书3设计触发器a拒绝超级用户sa对所有的数据的修改,但凡SA进行的修改全部回退.bOper1用户对图书状态进行修改时,在历史记录表中追加相应的数据.1为BookSto