数据库系统实验报告4(简单图书馆管理数据库的实现).doc

数据库系统 课程实验报告 "实验名称 "实验四 简单图书馆管理数据库的实现 " "班级 " "学号 " "姓名 " " "指导教师 " " "（一、一、实验目的 " "学习数据库的设计，了解存储过程、触发器MS Sql Server的使用。 " "二、实验内容 " "【数据库设计】 " "简单图书管理系统应包括读者和图书的管理，借、还管理等功能。为此我们设计三张" "表分别记录读者、图书和借阅信息。同时为了降低复杂度，我们假定每本书只有一本" "，每个读者借阅时间为一个月并且针对借阅权限不做限制。由此数据库设计如下： " "表：图书信息表 " "字段名 " "数据类型 " "长度 " "允许空 " " " "书号 " "varchar " "10 " "否 " " " "书名 " "varchar " "15 " "否 " " " "作者 " "varchar " "15 " "是 " " " "状态 " "bit " "1 " "否 " " " "说明：书号是书的唯一标识即主键；状态表示是否在库可借。 " " " "表：读者信息表 " "字段名 " "数据类型 " "长度 " "允许空 " " " "借书证号 " "varchar " "10 " "否 " " " "姓名 " "varchar " "15 " "否 " " " "住址 " "varchar " "20 " "是 " " " "说明：借书证号是读者的唯一标识即主键。 " " " "表：借阅信息表 " "字段名 " "数据类型 " "长度 " "允许空 " " " "编号 " "int " "4 " "否 " " " "书号 " "varchar " "10 " "否 " " " "借书证号 " "varchar " "10 " "否 " " " "借书时间 " "datetime " "8 " "否 " " " "应还时间 " "datetime " "8 " "否 " " " "说明：编号是借阅记录的标识，主键，自动增长；书号 " "图4-1为ER图： " "图4-1 " " " "【实验过程】 " "建数据库、数据表 " "按前述分析，利用企业管理器或者查询分析器（推荐）建"图书馆管理"数据库和数据" "表。 " "数据的基本操作 " "增加记录： " " " "图书信息表： " "书号 " "书名 " "作者 " "状态 " " " "1 " "sql语言 " "sql " "1 " " " "2 " "c＋＋语言 " "c＋＋ " "1 " " " "3 " "sql server " "ms " "1 " " " "4 " "java教程 " "java " "1 " " " "5 " "c＃教程 " "c＃ " "1 " " " "6 " "计算机网络 " "net " "1 " " " " " "读者信息表： " "借书证号 " "姓名 " "住址 " " " "1 " "张三 " "学三 " " " "2 " "李四 " "学四 " " " "3 " "王五 " "学五 " " " "并对这两个表进行删除、更新、和查询等操作 " " " "创建触发器、和存储过程进行借、还书操作 " "针对借阅信息表的插入操作创建触发器，当借阅信息表进行插入操作时，自动根据书" "号，将图书信息表相应记录的"状态"字段改为0。 " "针对借阅信息表的删除操作创建触发器，当借阅信息表进行删除操作时，自动根据书" "号，将图书信息表相应记录的"状态"字段改为1。 " "创建存储过程：sf_borrow，实现借书功能即：向借阅信息表进行插入操作，将书号 " "、借书证号，借书时间作为参数，并自动的将还书时间设置为借书时间后一个月。 " "创建存储过程：sf_return，现实还书功能即：根据书号对借阅信息表进行删除操作 " "，将书号作为参数。 " "利用sf_borrow，sf_return进行借、还操作，并注意数据库中三个表的数据变化情况" "。 " "三、实验结果及分析 " "(一)创建数据库和数据表： " "选择用企业管理器创建数据库、数据表 " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " "(二)数据相关操作： " "1.查询： " " " " " " " " " " " " " " " " " " " " " " " " " " " "2.删除： " " " " " "删除后： " " " " " "