大数据库原理与指导应用大作业

《大数据库原理与指导应用大作业》由会员分享，可在线阅读，更多相关《大数据库原理与指导应用大作业（12页珍藏版）》请在装配图网上搜索。

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

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

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

4、这样几类：教学参考书、文学作品、学术期刊、学术专著。枫：你想统计一些什么数据洁：主要是分类统计图书册数和价值、图书借阅次数。我们想知道哪些图书最受欢迎。枫：阅览室是你一个人在管么？洁：阅览室有两个人。我们俩都有一台计算机，最好两台计算机都可以用上这个软件。1.2 需求分析1功能需求图1-1：功能需求示意图教师信息管理：用于教师根本资料的增删改查。图书信息管理：用于图书根本信息的增删改查，分类统计图书册数和价值。借书登记：记录借书时间、所借图书、借书人、办理人。还书登记：记录还书时间、所还图书、还书人、办理人。催还：查询借阅逾期的借书信息，给借书人发电子，给借书人的部门打。2运行环境要求图1-2

5、：运行环境拓扑图系统采用C/S模式，有两台PC和一台服务器，联成一个局域网。PC上安装图书管理软件的客户端，服务器上安装DBMS，服务器也可由两台PC中的一台来代替。第二局部 作业1E-R模型与关系模型设计总分为8分本局部的任务是：在需求分析的根底上，进展E-R图设计，然后将E-R模型转换为关系模型。任务：1） 根据需求描述，绘制E-R图。2） 将E-R图转换成关系模型，写出所有的关系模式，并写出每一个关系是按照什么规如此转换而成的。3） 在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。参考答案：属性写在了实体和联系图形任务：1） 根据需求描述，分别为“图书管理系统的不同功能模块

6、绘制局部E-R图。教师信息管理：教师教师工号，某某，性别，EmailN1属于部门部门名，图书根本信息管理：图书ISBN,书名，作者，出版时间，单价，状态归类类型类型名，借阅期限N1借还书登记：pM图书借/还借日期，还日期教师管理员管理员名，口令N催还书登记：pM图书催/还催还日期教师管理员用户名，口令N2） 整体E-R图1PPMM1图书ISBN,书名，作者，出版时间，单价，状态教师教师工号，某某，性别，Email催/还催还日期类型类型名，借阅期限归类部门部门名，属于1*N借/还借日期，还日期管理员管理员名，口令NNN3） 关系模式 类型表(类型名，借阅期限)，依据：实体转换为关系。 图书表IS

7、BN，书名，作者，出版时间，单价，类型名，状态，依据：实体转换为关系，1：N的联系合并到N，在N方增加一个外键:类型名。注：状态属性为库存，借出 管理员表(管理员名，性别，口令) ，依据：实体转换为关系。 教师表(教师工号，,性别，Email，部门名)， 依据：实体转换为关系，1：N的联系合并到N，在N方增加一个外键: 部门名。 部门表(部门名，)，依据：实体转换为关系。 借还表(借还业务编号，ISBN，教师工号，借办理人，借出日期，还办理人，归还日期)，依据，M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性，可以增加一个列作为主键。 催还表(催还业务编号，ISBN，教师工号，办

8、理人，催还日期)，依据，M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性，可以增加一个列作为主键。4） 在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。连接运算：查看类型的图书已经超过了有效借出期限。需要图书，类型，借还关系投影运算：查看图书的书名、作者。选择运算：查看单价高于50元的图书。第三局部 作业2用SQL语句建库、查询、更新数据、创建视图总分为9分本局部的任务是：在关系模型的根底上，设计出数据库的逻辑结构，然后在SQL Server中用SQL语句创建数据库、数据表、并输入一些原始数据，写SQL语句，进展数据增删改，创建视图。任务：1） 在第一次大作业的参考

9、答案根底上，将每个关系转换为SQL Server支持的表结构，写出表结构。2） 使用SQL语句，创建数据库，数据库名为BookStore3） 使用SQL语句，在数据库中创建表，设定表的主键约束、外键约束、检查约束、非空约束。4） 写以下增删改查操作的SQL语句1、 增加三条类型表数据2、 增加三条教师表数据3、 增加五条图书表数据4、 某教师今天借了某书，涉与两个操作，一是图书表的状态改为“借出，二是增加一个借还表记录5、 将某书的单价改为32元。5） 写出创建以下视图的SQL语句1. 查询某教师的借还书记录2. 查询某本书的流转记录3. 借出但未归还的图书视图4. 借阅逾期视图5. 图书分类

10、统计册数和总价值的视图参照完成：1） 表结构管理员表列名类型约束用户名Char(10)pk性别Char(2)NOT NULL,且默认为男口令Nchar(20) NOT NULL类型表列名类型约束类型名A 马克思主义、列宁主义、思想、理论B 哲学、某某C 社会科学总论D 政治、法律E 军事F 经济G 文化科学、教育、体育H 语言、文字I 文学J 艺术K 历史、地理N 自然科学总论O 数理科学和化学P 天文学、地球科学Q 生物科学R 医药、卫生S 农业科学T 工业技术U 交通运输V 航空、航天X 环境科学、安全科学Z 综合性图书NChar(10)pk借阅期限tinyint NOT NULL图书表列

11、名类型约束ISBNChar(20)pk书名Nchar(20) NOT NULL作者Nchar(20)NOT NULLNchar(20)NOT NULL出版时间DateTimeNOT NULL单价moneyNOT NULL类型名Nchar(10)状态Nchar(2)状态 in (库存，借出)部门表列名类型约束部门名NChar(10)pkchar(11) NOT NULL教师表列名类型约束工号Char(4)pkNchar(4) NOT NULL性别Char(2)NOT NULL,且默认为男Nchar(4) NOT NULLEmailChar(30)NOT NULL部门名Nchar(10)F借还记录

12、表列名类型约束业务编号intPk 自动标示ISBNchar(20) F工号Char(4)F借办理人Char(10)NOT NULL借出日期datetimeNOT NULL还办理人Char(10)归还日期datetime催还记录表列名类型约束业务编号intPk 自动标示ISBNchar(11) F工号Char(4)F办理人Char(10)NOT NULL催还日期datetimeNOT NULL1） 使用SQL语句，创建数据库，数据库名为BookStore语句：Create Databse Bookstore2） 使用SQL语句，在数据库中创建表，设定表的主键约束、外键约束、检查约束、非空约束。语

13、句：CREATETABLE管理员表(管理员名nchar(10)NOTNULL,性别Char(2)notnullDEFAULT男,口令nchar(20)NOTNULL,CONSTRAINT PK_管理员PRIMARYKEYCLUSTERED(管理员名ASC)ON PRIMARYGOCREATETABLE类型表(类型名nchar(10)NOTNULL,借阅期限tinyintNOTNULL,CONSTRAINT PK_类型PRIMARYKEYCLUSTERED(类型名ASC)ON PRIMARYGOCREATETABLE 图书表(ISBN char(20)NOTNULL,书名nchar(20)NOT

14、NULL,作者nchar(20)NOTNULL,nchar(20)NOTNULL,出版时间datetimeNOTNULL,单价moneyNOTNULL,类型名nchar(10)NOTNULL,状态nchar(2)NOTNULLCHECK(状态in(库存,借出),CONSTRAINT PK_图书PRIMARYKEYCLUSTERED(ISBN ASC),CONSTRAINT FK_图书_类型FOREIGNKEY(类型名)REFERENCES 类型表(类型名)ONUPDATECASCADEONDELETECASCADE)ON PRIMARYGOCREATETABLE部门表(部门名nchar(10)

15、NOTNULL,char(11),CONSTRAINT PK_部门表PRIMARYKEYCLUSTERED(部门名ASC)ON PRIMARYGOCREATETABLE教师表(工号char(4)NOTNULL,nchar(4)NOTNULL,性别Char(2)notnullDEFAULT男,Email char(30)NOTNULL,部门名nchar(10)NOTNULL,CONSTRAINT PK_教师表PRIMARYKEYCLUSTERED(工号ASC),CONSTRAINT FK_部门表_教师表FOREIGNKEY(部门名)REFERENCES 部门表(部门名)ONUPDATECASCA

16、DEONDELETECASCADE,)ON PRIMARYGOCREATETABLE借还记录表(业务编号intidentity(0,1)NOTNULL,ISBN char(20)NOTNULL,工号char(4)NOTNULL,代办理人char(10)NOTNULL,借出日期datetimeNOTNULL,还办理人char(10)NULL,归还日期datetimeNULL,CONSTRAINT PK_借还记录表PRIMARYKEYCLUSTERED(业务编号ASC),CONSTRAINT FK_借还记录表_教师表FOREIGNKEY(工号)REFERENCES 教师表(工号)ONUPDATEC

17、ASCADEONDELETECASCADE,CONSTRAINT FK_借还记录表_借还记录表FOREIGNKEY(ISBN)REFERENCES 图书表(ISBN)ONUPDATECASCADEONDELETECASCADE)ON PRIMARYGOCREATETABLE催还记录表(业务编号intidentity(0,1)NOTNULL,ISBN char(20)NOTNULL,工号char(4)NOTNULL,办理人char(10)NOTNULL,催还日期datetime,CONSTRAINT PK_催还记录表PRIMARYKEYCLUSTERED(业务编号ASC),CONSTRAINT

18、FK_催还记录表_教师表FOREIGNKEY(工号)REFERENCES 教师表(工号)ONUPDATECASCADEONDELETECASCADE,CONSTRAINT FK_催还记录表_图书FOREIGNKEY(ISBN)REFERENCES 图书表(ISBN)ONUPDATECASCADEONDELETECASCADE)ON PRIMARYGO3） 写以下增删改查操作的SQL语句1. 增加三条类型表数据语句：INSERTINTO 类型表(类型名,借阅期限)VALUES(计算机,150)INSERTINTO 类型表(类型名,借阅期限)VALUES(文学,180)INSERTINTO 类型表

19、(类型名,借阅期限)VALUES(经济,30)-delete 类型表-select * from 类型表2. 增加三条部门表数据语句：INSERTINTO 部门表(部门名,)VALUES(网络系,)INSERTINTO 部门表(部门名,)VALUES(外语系,)INSERTINTO 部门表(部门名,)VALUES(管理系,)-select * from 部门表3. 增加三条教师表数据语句：INSERTINTO 教师表(工号,性别,Email ,部门名)VALUES(J001,三,女,zs163.,网络系)INSERTINTO 教师表(工号,性别,Email ,部门名)VALUES(J002,四

20、,男,LS163.,网络系)INSERTINTO 教师表(工号,性别,Email ,部门名)VALUES(J003,王五,男,ww163.,外语系)-select * from 教师表4. 增加五条图书表数据语句：INSERTINTO 图书表(ISBN,书名,作者,出版时间,单价,类型名,状态)VALUES(61,C#,江红,清华大学,2012.2.10,45,计算机,库存)INSERTINTO 图书表(ISBN,书名,作者,出版时间,单价,类型名,状态)VALUES(61,人生博弈密码,吕叔春,中侨,2009.2.1,文学,库存)INSERTINTO 图书表(ISBN,书名,作者,出版时间,

21、单价,类型名,状态)VALUES(94,经济学原理,梁小民,大学,2009.4.1,54,经济,借出)-select * from 图书表5. 增加三条管理员表数据语句：INSERTINTO 管理员表(管理员名,性别,口令)VALUES(吴管,男,wg001)INSERTINTO 管理员表(管理员名,性别,口令)VALUES(管,女,Lg002)INSERTINTO 管理员表(管理员名,性别,口令)VALUES(徐管,男,Xg003)-select * from 管理员表-select * from 教师表-select * from 部门表-select * from 类型表-select

22、* from 图书表-select * from 管理员表6. 某教师今天借了某书，涉与两个操作，一是图书表的状态改为“借出，二是增加一个借还表记录语句：insert 借还记录表(ISBN ,工号,代办理人,借出日期,还办理人,归还日期)values(61,J001,wg001,2012.3.20,Lg002,)-select * from 借还记录表-delete 借还记录表update 图书表set 状态=借出where ISBN=61-select * from 图书表7. 将某书的单价改为32元。语句：update 图书表set 单价=32where ISBN=61-select *

23、from 图书表8. 查询某教师的借还书记录语句：-select * from 教师表-select * from 部门表-select * from 类型表-select * from 图书表-select * from 管理员表-select * from 借还记录表select a.工号,部门名,书名from 教师表 a join 借还记录表 c on a.工号=c.工号 join 图书表 bwhere a.工号=J001go9. 查询某本书的流转记录语句：-select * from 借还记录表select a.工号,部门名,书名,借出日期,归还日期from 教师表 a,图书表 b,借

24、还记录表 cwhere a.工号=c.工号 and b.ISBN=c.ISBN and b.ISBN=614） 写出创建以下视图的SQL语句1. 借出图书视图语句：-select * from 借还记录表-select * from 图书表Create view 借出图书视图Asselect*from 图书表where 状态=借出go2. 借阅逾期视图语句：-select * from 图书表-select * from 借还记录表-select * from 类型表CreateView 借阅逾期视图AsSELECT a.ISBN, a.书名, b.工号, b.借出日期FROM 图书表 a J

25、OIN 借还记录表b ON a.ISBN=b.ISBN JOIN 类型表 C ON a.类型名=c. 类型名WHERE a.状态=借出anddatediff(day, b.借出日期,getdate()c.借阅期限-select * from 借阅逾期视图GO3. 图书分类统计册数视图语句：-select * from 图书表CreateView 图书统计视图AsSELECT 类型名,count(*) 各类图书册数,sum(单价) 各类图书总价FROM 图书表GROUPBy 类型名-select * from 图书统计视图第四局部 作业3存储过程与触发器设计总分为8分任务：1） 为BookSto

26、re数据库增加一个用户oper1，该用户可以进展系统所需的应用操作，权限尽可能精简。请描述该用户的权限。2） 设计存储过程a) 数据初始化过程sp_Init。包含的操作有：所有的用于测试图书信息、教师信息、历史记录清空，输入图书分类、输入管理员信息、输入借书规定。b) 某教师在某时间借了某本书，由某管理员办理。c) 某教师在某时间还了某本书，由某管理员办理d) 查看某人的全部借还书记录e) 查看某本书的历史流转记录f) 某管理员在某时间给全部借阅过期者发催还。 g) 对书名进展模糊搜索搜索条件可以是多个模糊列值的组合，例如：搜索书名包含“网络两个字，并且是近3年出版的图书3） 设计触发器a)

27、拒绝超级用户sa对所有的数据的修改，但凡SA进展的修改全部回退。b) Oper1用户对图书状态进展修改时，在历史记录表中追加相应的数据。1） 为BookStore数据库增加一个用户oper1，该用户可以进展系统所需的应用操作，权限尽可能精简。请描述该用户的权限。use bookstoregoexecsp_addloginoper1,123456,bookstore-sl, 123456,bookstore:依次为用户名，密码，默认数据库goEXEC sp_addrolemember db_datareader, oper1授权oper1用户对所有表具有查询权GRANTUPDATEON图书表TO

28、oper1 -允许用户oper1对数据表直接更新go2） 设计存储过程a) 略b) createproc proc_lendbookjsID char(4),time datetime,shuISBN char(20),gly nchar(20)asselect a.,借出日期,d.书名,b.管理员名from教师表a join借还记录表 con a.工号=c.工号join管理员表 bon b.口令=c.代办理人join图书表 don c.ISBN=d.ISBNwhere a.工号= jsID and c.借出日期=time and c.ISBN=shuISBN and c.代办理人=glygoEXEC proc_lendbook J001,2012-3-20,61,WG001c) 略d) 略e) 略f) 略g) 对书名进展模糊搜索搜索条件可以是多个模糊列值的组合，例如：搜索书名包含“网络两个字，并且是近3年出版的图书Create proc getbooktsname nchar(20),nx tinyintAsSelect * from 图书表Where 图书名 LIKE Coalesce(%,tsname,%) and datediff(year,出版时间,getdate()nxGoExec getbook 网络,3略12 / 12