《数据库技术与应用》课件-项目12：数据库管理项目整体开发综合案例

数据库管理项目整体开发综合案例（一）课程简介2通过本课程，结合综合案例连贯地学习数据库管理项目的整体开发（一）。

主要学习内容：一．数据库管理项目整体开发流程二．开发综合案例数据库管理项目整体开发流程31.一个数据库管理项目的产生必须基于先对用户项目进行需求分析，做到项目既能包含用户所需要处理的所有数据，又支持用户提出的所有处理功能的实现。2.然后基于需求分析设计思路，完成数据库项目的概念结构设计、逻辑结构设计，整合出符合范式要求的数据库关系模型结构，大致确定数据库项目中关系表的基本雏型。数据库管理项目整体开发流程43.根据设计的关系模型结构选择能接受的现有数据库管理系统，如SQLServer2008。4.根据选好的数据库管理系统完成数据库的物理结构设计，确定需要的数据库、表结构、约束、表数据、视图、索引、函数、存储过程、触发器等内容。数据库管理项目整体开发流程55.在计算机上利用数据库管理系统，根据物理结构设计结果，实施创建数据库结构，并装入数据，创建约束等各项内容，进行试运行及评价，并随时调整修改维护。6.正式投入运行，同时进行日常维护，实施监测数据库、故障恢复、数据库安全性控制等工作。开发综合案例61.项目任务：某学校需要一个图书管理系统，需要实现管理员管理维护图书信息及用户借阅图书的功能，按需求进行数据库整体设计，再使用SQLServer2008数据库管理系统进行实施、运行与维护。开发综合案例72.数据库整体设计与实施1）设计与创建数据库：创建图书管理数据库BookManageDB并且使用这个数据库创建表CREATEDATABASE[BookManageDB]USE[BookManageDB]开发综合案例82）设计与创建数据表结构:图书管理系统，总共涉及到十三张表，（1）管理员表Admin：该表用于系统管理员登录，可以对图书等各种信息进行管理和维护，登录系统需要管理员账号、密码以及名称等进行区分。CREATETABLE[dbo].[Admin]( [AdminID][int]IDENTITY(1,1)primarykey, [Account][varchar](50)NOTNULL, [Password][varchar](50)NOTNULL, [PowerName][nvarchar](50)NULL)开发综合案例9（2）书架表BookCase：该表用于对图书信息进行分不同得书架进行管理，根据不同的书架规划不同图书存放的位置，涉及字段包括：书架编号、书架名称。CREATETABLE[dbo].[BookCase]( [BookCaseID][int]IDENTITY(1,1)primarykey, [BookCaseName][varchar](80)NOTNULL)开发综合案例10（3）图书信息表BookInfo：图书信息表作为整个图书管理系统的主表，用于图书信息的管理，规划图书的各种基本信息，包括图书编号、图书编码、图书名称、图书类型编号、图书书架编号、图书作者、图书价格、图书总数量、图书出版社编号以及详细内容等信息。CREATETABLE[dbo].[BookInfo](

[BookID][int]IDENTITY(1,1)primarykey,

[BookBarCode][varchar](256)NOTNULL,

[BookName][varchar](256)NOTNULL,

[BookTypeID][int]NOTNULL,

[BookCaseID][int]NOTNULL,

[BookAuthor][varchar](256)NULL,

[BookPrice][money]NULL,

[TotalSum][int]NULL,

[PublishingID][int]NOTNULL,

[Content][text]NULL)开发综合案例11（4）图书入库出库表BookInOutOrder：该表用于图书的出库入库管理，包含入库的图书信息以及损坏的图书信息，涉及字段包括图书库存编号、图书编号、入库出库数量、登记时间、入库出库类型。CREATETABLE[dbo].[BookInOutOrder]( [BookStockID][int]IDENTITY(1,1)primarykey, [BookID][int]NULL, [BookNum][int]NULL, [AddTime][datetime]NULL, [Type][int]NULL)开发综合案例12（5）图书类型表BookType：该表用于区分图书的类型，根据不同图书的分类进行分类图书信息，涉及字段包括图书类型编号、图书类型名称。CREATETABLE[dbo].[BookType]( [BookTypeID][int]IDENTITY(1,1)primarykey, [BookTypeName][varchar](50)NOTNULL)开发综合案例13（6）图书借阅表Borrow：该表用于存储图书的借阅登记信息，涉及字段包括图书借阅编号、图书编号、借阅时间、归还时间、用户编号、是否归还、归还天数以及罚款金额等，其中图书编号以及用户编号是外键，用于关联图书表和用户表。CREATETABLE[dbo].[Borrow]( [BorrowID][int]IDENTITY(1,1)primarykey, [BookID][int]NOTNULL, [BorrowTime][datetime]NULL, [ReturnTime][datetime]NULL, [UserID][int]NOTNULL, [IsReturn][bit]NULL, [ReturnDays][float]NULL, [FineMoney][float]NULL)开发综合案例14（7）用户系别表Dept：该表用于区分用户的专业系别等信息，涉及字段包括用户系别编号、专业系别名称。CREATETABLE[dbo].[Dept]( [DeptID][int]IDENTITY(1,1)primarykey, [DeptName][nvarchar](200)NULL)开发综合案例15（8）用户年级表Grade：该表用于存放用户的年级信息，涉及字段包括用户年级编号以及年级名称等。CREATETABLE[dbo].[Grade]( [GradeID][int]IDENTITY(1,1)primarykey, [GradeName][nvarchar](200)NULL)开发综合案例16（9）用户留言表Message：该表用于存放用户的留言信息，管理员可以查看用户留言信息并且给予回复，涉及字段包括：留言编号、用户编号、留言内容、留言时间、回复内容、回复时间，其中用户编号是外键，用于关联用户表。CREATETABLE[dbo].[Message]( [MessageID][int]IDENTITY(1,1)primarykey, [UserID][int]NOTNULL, [Content][varchar](2000)NULL, [AddTime][datetime]NULL, [ReplyContent][varchar](2000)NULL, [ReplyTime][datetime]NULL)开发综合案例17（10）消息通知表Notice：该表用于管理员进行公告通知，涉及字段包括：通知编号、消息标题以及消息内容。CREATETABLE[dbo].[Notice]( [NoticeID][int]IDENTITY(1,1)primarykey, [Title][varchar](200)NOTNULL, [Content][varchar](2000)NULL)开发综合案例18（11）出版社表Publishing：该表用于存放图书的出版社信息，涉及字段包括：出版社编号、出版社名称。CREATETABLE[dbo].[Publishing]( [PublishingID][int]IDENTITY(1,1)NOTNULL, [PublishingName][nvarchar](50)NULL)开发综合案例19（12）用户表Users：该表用于存放用户的各种基本信息，涉及字段包括：用户编号、用户姓名、用户密码、用户编码、用户性别、电话、邮箱、备注、是否使用、总金额、类型编号、系别编号、年级编号。CREATETABLE[dbo].[Users]( [UserID][int]IDENTITY(1001,1)NOTNULL, [UserName][nvarchar](50)NOTNULL, [UserPwd][nvarchar](50)NOTNULL, [UserCode][varchar](50)NOTNULL, [Sex][char](10)NULL, [Tel][varchar](50)NULL, [Email][varchar](50)NULL, [Content][varchar](500)NULL, [IsUse][nvarchar](50)NULL, [TotalMoeny][float]NULL, [UserTypeID][int]NULL, [DeptID][int]NULL, [GradeID][int]NULL)开发综合案例20（13）用户类型表UserType：该表用于存放用于的类型，可以针对不同的用户根据类型进行区分，涉及字段包括：用户类型编号、类型名称、可借阅天数。CREATETABLE[dbo].[UserType]( [UserTypeID][int]IDENTITY(1,1)NOTNULL, [UserTypeName][nvarchar](200)NULL, [BorrowDays][int]NULL)数据库管理项目整体开发综合案例（二）课程简介22通过本课程，结合综合案例连贯地学习数据库管理项目的整体开发（二）。

主要学习内容：一．数据库管理项目整体开发流程二．开发综合案例开发综合案例233）设计与创建表约束（1）确定主键约束为了保证数据表中的每一行数据在表中都是唯一的，必须在13张表中分别设定主键约束：管理员表Admin：AdminID（管理员账号）书架表BookCase：BookCaseID（书架编号）图书信息表BookInfo：BookID（图书编号）开发综合案例24图书入库出库表BookInOutOrder：BookStockID（图书库存编号）图书类型表BookType：BookTypeID（图书类型编号）图书借阅表Borrow：BorrowID（图书借阅编号）用户系别表Dept：DeptID（系别编号）用户年级表Grade：GradeID（年级编号）用户留言表Message：MessageID（留言编号）开发综合案例25消息通知表Notice：NoticeID（通知编号）出版社表Publishing：PublishingID（出版社编号）用户表Users：UserID（用户编号）用户类型表UserType：UserTypeID（用户类型编号）开发综合案例26（2）添加默认约束在图书信息表BookInfo中，添加图书的时候需要设置一个图书总数量字段TotalSum的默认值0，方便后续的图书入库报损等统计。ALTERTABLE[dbo].[BookInfo]ADDCONSTRAINT

[DF_books_borrowSum]DEFAULT((0))FOR[TotalSum]开发综合案例27对于添加图书借阅表Borrow数据时，需要对其中字段IsReturn是否归还设置一个默认值，其中0代表未归还，1代表已归还。ALTERTABLE[dbo].[Borrow]ADDCONSTRAINT[DF_bookBorrow_isReturn]DEFAULT((0))FOR[IsReturn]开发综合案例28对于用户表Users设置一个字段IsUse，用来表示这个用户是否在使用，其中设置默认值“是”，当用户没在使用会被设置成否。ALTERTABLE[dbo].[Users]ADDCONSTRAINT[DF_reader_IsUse]DEFAULT(N'是')FOR[IsUse]开发综合案例29对于用户表Users的用户总金额需要设置一个默认值，用来表示用户账户的总金额，其中默认值为0。ALTERTABLE[dbo].[Users]ADDCONSTRAINT[DF_reader_MoenyInfo]DEFAULT((0))FOR[TotalMoeny]开发综合案例30（3）添加外键约束图书信息表BookInfo中存有字段“图书书架的编号BookCaseID”，需要添加外键关联到书架表BookCase的主键BookCaseID。ALTERTABLE[dbo].[BookInfo]ADDCONSTRAINT[BookCaseID]FOREIGNKEY([BookCaseID])REFERENCES[dbo].[BookCase]([BookCaseID])开发综合案例31图书信息表BookInfo中存有字段“图书类型的编号BookTypeID”，需要添加外键关联到图书类型表BookType的主键BookTypeID。ALTERTABLE[dbo].[BookInfo]ADDCONSTRAINT[BookTypeID]FOREIGNKEY([BookTypeID])REFERENCES[dbo].[BookType]([BookTypeID])开发综合案例32图书信息表BookInfo中存有字段“图书出版社的编号PublishingID”，需要添加外键关联到图书出版社表Publishing的主键PublishingID。ALTERTABLE[dbo].[BookInfo]ADDCONSTRAINT[PublishingID]FOREIGNKEY([PublishingID])REFERENCES[dbo].[Publishing]([PublishingID])开发综合案例33图书入库出库信息表BookInOutOrder中存有字段“图书编号BookID”，需要添加外键关联到图书信息表BookInfo的主键。ALTERTABLE[dbo].[BookInOutOrder]WITHADDCONSTRAINT[BookStock_BookID]FOREIGNKEY([BookID])REFERENCES[dbo].[BookInfo]([BookID])开发综合案例34图书借阅信息表Borrow中存有字段“图书编号BookID”，需要添加外键关联到图书信息表BookInfo的主键BookID。ALTERTABLE[dbo].[Borrow]ADDCONSTRAINT[BookID]FOREIGNKEY([BookID])

REFERENCES[dbo].[BookInfo]([BookID])开发综合案例35图书借阅信息表Borrow中存有字段“用户编号UserID”，需要添加外键关联到用户表Users的主键UserID。ALTERTABLE[dbo].[Borrow]ADDCONSTRAINT[UserID]FOREIGNKEY([UserID])

REFERENCES[dbo].[Users]([UserID])开发综合案例36用户留言表Message中存有字段“用户编号UserID”，需要添加外键关联到用户表Users的主键UserID。ALTERTABLE[dbo].[Message]ADDCONSTRAINT[MessageUserID]FOREIGNKEY([UserID])REFERENCES[dbo].[Users]([UserID])开发综合案例37用户表Users中存有字段“系别编号DeptID”，需要添加外键关联到用户系别表Dept的主键DeptID。ALTERTABLE[dbo].[Users]WITHCHECKADDCONSTRAINT[Users_DeptID]FOREIGNKEY([DeptID])REFERENCES[dbo].[Dept]([DeptID])开发综合案例38用户表Users中存有字段“年级编号GradeID”，需要添加外键关联到用户年级表的主键GradeID。ALTERTABLE[dbo].[Users]WITHCHECKADDCONSTRAINT[Users_GradeID]FOREIGNKEY([GradeID])REFERENCES[dbo].[Grade]([GradeID])开发综合案例39用户表Users中存有字段“用户类型编号UserTypeID”，需要添加外键关联到用户类型表UserType的主键UserTypeID。ALTERTABLE[dbo].[Users]WITHCHECKADDCONSTRAINT[Users_UserTypeID]FOREIGNKEY([UserTypeID])REFERENCES[dbo].[UserType]([UserTypeID])数据库管理项目整体开发综合案例（三）课程简介41通过本课程，结合综合案例连贯地学习数据库管理项目的整体开发（三）。

主要学习内容：一．数据库管理项目整体开发流程二．开发综合案例开发综合案例424）添加表数据：按表结构设计情况，分别为各表添加数据（实施阶段的表数据内容会时刻在变化的）USE[BookManageDB]GO--添加系别信息INSERT[dbo].[Dept]([DeptName])VALUES('计算机系')GOINSERT[dbo].[Dept]([DeptName])VALUES('英语系')GO开发综合案例43-

-添加年级数据INSERT[dbo].[Grade]([GradeName])VALUES('一年级')GOINSERT[dbo].[Grade]([GradeName])VALUES('二年级')GOINSERT[dbo].[Grade]([GradeName])VALUES('三年级')GOINSERT[dbo].[Grade]([GradeName])VALUES('四年级')GO开发综合案例44--添加用户类型数据INSERT[dbo].[UserType]([UserTypeName],[BorrowDays])VALUES('学生',10)GOINSERT[dbo].[UserType]([UserTypeName],[BorrowDays])VALUES('教师',20)GO开发综合案例45--添加用户数据INSERT[dbo].[Users]([UserName],[UserPwd],[UserCode],[Sex],[Tel],[Email],[Content],[IsUse],[TotalMoeny],[UserTypeID],[DeptID],[GradeID])VALUES('张三','123456','C20220803001','男',NULL,'zhangsan@','xxx','0',100,1,1,1)GOINSERT[dbo].[Users]([UserName],[UserPwd],[UserCode],[Sex],[Tel],[Email],[Content],[IsUse],[TotalMoeny],[UserTypeID],[DeptID],[GradeID])VALUES('教师A','123456','T00001','女',,'jiaoshiA.','xxx','1',900,2,1,1)GO开发综合案例46--添加留言数据INSERT[dbo].[Message]([UserID],[Content],[AddTime],[ReplyContent],[ReplyTime])VALUES(1001,N'请问图书馆开放时间是啥时候呢？',CAST(0x0000AEE600000000ASDateTime),'早10：00-晚6：00',CAST(0x0000AEE600000000ASDateTime))GO开发综合案例47--添加出版社数据INSERT[dbo].[Publishing]([PublishingName])VALUES('人民教育出版社')GOINSERT[dbo].[Publishing]([PublishingName])VALUES('苏州教育出版社')GO开发综合案例48--添加图书类型数据INSERT[dbo].[BookType]([BookTypeID],[BookTypeName])VALUES('计算机')GOINSERT[dbo].[BookType]([BookTypeID],[BookTypeName])VALUES('外语')GOINSERT[dbo].[BookType]([BookTypeID],[BookTypeName])VALUES('文学')GO开发综合案例49--添加书架数据INSERT[dbo].[BookCase]([BookCaseID],[BookCaseName])VALUES('2-101')GOINSERT[dbo].[BookCase]([BookCaseID],[BookCaseName])VALUES('2-102')GOINSERT[dbo].[BookCase]([BookCaseID],[BookCaseName])VALUES('2-103')GO开发综合案例50--添加图书数据INSERT[dbo].[BookInfo]([BookID],[BookBarCode],[BookName],[BookTypeID],[BookCaseID],[BookAuthor],[BookPrice],[TotalSum],[PublishingID],[Content])VALUES('ISBN001','算法设计',1,1,'乔恩·克莱因伯格',59.9000,901,1,'本书围绕算法设计进行组织，对每种算法技术用多个典型范例进行分析，把算法的理论跟实际问题结合起来，具有很大的启发性')GO开发综合案例51--添加借阅数据INSERT[dbo].[Borrow]([BookID],[BorrowTime],[ReturnTime],[UserID],[IsReturn],[ReturnDays],[FineMoney])VALUES(3,CAST(0x0000AEE600000000ASDateTime),CAST(0x0000AF5801010FD0ASDateTime),1001,1,22,0)GOINSERT[dbo].[Borrow]([BookID],[BorrowTime],[ReturnTime],[UserID],[IsReturn],[ReturnDays],[FineMoney])VALUES(3,CAST(0x0000AF5800000000ASDateTime),NULL,1006,0,NULL,0)GO开发综合案例52--添加入库出库数据INSERT[dbo].[BookInOutOrder]([BookNum],[AddTime],[Type])VALUES(3,900,CAST(0x0000AEED00000000ASDateTime),1)GOINSERT[dbo].[BookInOutOrder]([BookNum],[AddTime],[Type])VALUES(6,1000,CAST(0x0000AEED00000000ASDateTime),1)GOINSERT[dbo].[BookInOutOrder]([BookNum],[AddTime],[Type])VALUES(3,-1,CAST(0x0000AEF500000000ASDateTime),0)GO开发综合案例53--添加管理员数据INSERT[dbo].[Admin]([AdminID],[Account],[Password],[PowerName])VALUES('admin','123','管理员')GO--添加通知信息数据INSERT[dbo].[Notice]([Title],[Content])VALUES('通知信息',N'图书馆每日开放时间为10：00-晚6：00')GO开发综合案例545）创建视图：根据查询使用的频率情况创建重要的满足需求的视图。（1）创建视图：根据图书的名称和类型分组查询图书的入库出库的数量。CREATEVIEW[dbo].[View_BookInOutOrder]ASselectb.BookName'图书名称',Sum(BookNum)'数量',casewhena.Type=1then'入库'whena.Type=0then'出库'end'类型'fromBookInOutOrderajoinBookInfobona.BookID=b.BookIDgroupbyb.BookName,a.TypeGO开发综合案例55（2）创建视图：通过图书表关联出版社表查询出版社名称为“苏州教育出版社”的图书信息。CREATEVIEW[dbo].[View_BookPublishing]ASSELECTa.BookID,a.BookName,b.PublishingNameFROMBookInfoajoinPublishingbona.PublishingID=b.PublishingIDWHEREPublishingName='苏州教育出版社'GO开发综合案例56（3）创建视图：通过用户表关联系别和年级表查询用户的编号、姓名、系别以及年龄等信息。CREATEVIEW[dbo].[View_UserDeptGrade]ASselecta.UserID,a.UserName,b.DeptName,c.GradeNamefromUsersajoinDeptbona.DeptID=b.DeptIDjoinGradecona.GradeID=c.GradeIDGO开发综合案例57（4）创建视图：通过用户表关联借阅表、用户类型表、图书信息表查询未归还图书的用户编号、姓名、用户类型、图书名称、借阅时间以及归还天数等数据。CREATEVIEW[dbo].[View_UsersBorrow]ASSELECTa.UserID,a.UserName,c.UserTypeName,d.BookName,--投影基表各列b.BorrowTime,b.ReturnDays--投影子查询的应还日--期函数值FROMUsersaINNERJOINBorrowbONa.UserID=b.UserID--子查询4表连接INNERJOINUserTypecONa.UserTypeID=c.UserTypeIDINNERJOINBookInfodONb.BookID=d.BookIDWHEREb.ReturnTimeISNotNULL--子查询尚未还书选择条件GO开发综合案例58（5）创建视图：通过用户表关联用户类型表，查询用户类型名称是学生的用户编号、姓名、类型名称。CREATEVIEW[dbo].[View_UsersType]ASSELECTdbo.Users.UserID,dbo.Users.UserName,dbo.UserType.UserTypeNameFROMdbo.UsersINNERJOINdbo.UserTypeONdbo.Users.UserTypeID=dbo.UserType.UserTypeIDWHERE(dbo.UserType.UserTypeName=N'学生')GO开发综合案例596）创建索引：根据索引使用原则，比如“主键和外键考虑创建索引”、“经常作为查找条件的字段，考虑创建索引”，依次为数据库“BookManageDB”中的各表创建适合表特性的各个索引。开发综合案例607）创建触发器：对于表中一些重要字段信息，为防止误更改，可以创建针对些字段的UPDATE触发器；为防止一些违规的表级联更改，可以定义对应的触发器，比如，创建DELETE触发器确保删除用户前先检查有无图书未还。但触发器性能较低，运行触发器时，系统会在参照其他表数据浪费时间。开发综合案例618）创建用户自定义函数：根据实际重复操作某种功能的频率情况，创建满足需求的自定义函数。开发综合案例62（1）创建函数：通过价格参数判断图书是否昂贵。CREATEFUNCTION[dbo].[fn_bookPriceLevel](@pricemoney)RETURNSnvarchar(10)ASBEGINDECLARE@returnstrnvarchar(10)IF@price>100SET@returnstr='该图书较为昂贵'ELSESET@returnstr='正常价格的图书'RETURN@returnstrEND开发综合案例63（2）创建函数：通过出版社参数查询图书的编号、名称、作者以及出版社等信息。CREATEFUNCTION[dbo].[fn_Publisher](@Publishernvarchar(28))--创建函数fn_PublisherRETURNStable--函数返回值为表值ASRETURN(SELECTa.BookID,a.BookName,a.BookAuthor,b.PublishingNameFROMBookInfoajoinPublishingbona.PublishingID=b.PublishingIDWHEREb.PublishingName=@Publisher)GO开发综合案例649）创建存储过程:根据实际重复操作某种功能的频率情况，创建满足需求的存储过程。开发综合案例65（1）创建带参数的存储过程：通过图书编号输出出版社的名称。CREATEPROCEDURE[dbo].[ProcSelectBoo