SQL Server数据库宿舍入住迁出管理 报告.doc

SQL Server数据库综合实验说明书学 院： 信息科学与工程学院 班 级： 计算机科学与技术2011-3 完 成 人：姓 名： 学 号： 姓 名： 学 号： 指导教师： 山 东 科 技 大 学2013年10月23日综合实验任务书一、综合实验题目： 宿舍入住迁出管理 二、综合实验解决的主要问题、实现的主要功能：（1）创建一个宿舍入住迁出管理系统，实现对学生入住信息的管理。 （2）创建相关数据表， 实现对宿舍入住学生信息的查询、增加、修改、删除。（3）创建触发器，实现对多个数据表的级联操作。 （4）创建视图、索引，方便用户快速获取所需信息。 （5）自定义函数方便宿舍分配及宿舍信息查询。 （6）创建存储过程和游标，实现新生入学信息插入与毕业生宿舍迁出等 。 小组分工说明小组编号 11 题 目： 宿舍入住迁出管理 小组分工情况：触发器、存储过程、自定义函数、游标、控制流语句、查询、数据插入、数据库设计、课程设计报告：创建表、创建数据库、完整性约束、数据插入、查询、数据库设计、课程设计报告、建索引、建视图组长签字： 2013年 10 月 25 日指导教师对综合实验的评价学生姓名： 成绩： 学生姓名： 成绩： 指导教师签字： 年 月 日 目录一需求分析1 1.1数据表的建立1 1.2不同用户的需求1 1.3系统开发的目标1二.数据库概念结构设计2 2.1分析e-r图2 2.1系统整体e-r图5三.数据库逻辑结构设计5 3.1逻辑的设计与结构5 3.2概念模型转换关系模型6四.数据库实施阶段8 4.1创建数据库和表8 4.2视图的创建10 4.3索引的创建10 4.4触发器的创建10 4.5数据的查询12五.T-SQL 程序设计17 5.1创建储存过程和游标17 5.2执行储存过程20 5.3自定义函数定义20 5.4自定义函数执行23 六.总结25 4一需求分析：1.1数据表的建立 用户的需求具体体现在各种信息的提供、保存、更新和查询上，这就要求数据库结构能够满足各种信息的输入与输出。在对宿舍入住迁出管理进行分析的基础上，本系统所涉及的主体共有7个数据表。学生表，管理员表，楼房表，房间表，宿舍分配表，注册表，毕业生表1.2不同用户的需求1）：管理员 学生信息的添加个人信息的查询和修改 添加 修改 学生信息 查看 添加 修改 删除 楼房和宿舍信息 2 ）：个人信息的查询 宿舍入住信息的查询 添加 修改 删除 学生信息的查询 宿舍入住情况的操作 3 ）： 学生 宿舍入住情况的查看个人信息的查看 1.3系统开发的目标（1） 对宿舍分配表的设计，要考虑到不同学院不同年级、不同专业学生，要做到最尽量的合理分配。（2） 对注册表里的内容应该可以呈现给每位管理员，并且可以统计所有的房间和学生信息。（3） 学生只能入住房间表里有空床位的宿舍，否则禁止在宿舍分配表中插入新增学生信息。二、数据库概念结构设计2.1分析e-r图管理员表：性别姓名电话职务管理员表管理员编号身份证号出生日期民族学生表：民族姓名性别出生日期电话学院学生表班级学号注册表：楼号寝室号入学日期注册表入住状态学号楼房表：层数楼号应住人数楼名管理员编号楼房表性别房间数房间表：楼号应住人数寝室号房间表已住人数室长学号宿舍分配表：学院寝室号宿舍分配表性别楼号毕业生表：民族姓名性别出生日期学院毕业生表班级学号2.2系统整体e-r图管理员表：编号，姓名，性别，职务，电话，民族，身份证号，出生日期2.学生表：学号，姓名，性别，电话，学院，班级，民族，出生日期3.注册表：学号，入学日期，楼号，寝室号，入住状态4.楼房表：楼号，楼名，层数，房间数，应住人数，管理员编号，性别5.房间表：楼号，寝室号，室长学号，已住人数，应住人数6.宿舍分配表：楼号，寝室号，学院，性别7.毕业生表：学号，姓名，性别，学院，班级，民族，出生日期，入学日期三、数据库逻辑结构设计逻辑结构设计是将抽象的概念结构转化为所选用的DBMS支持的数据模型，并对其进行优化。3.1 逻辑的设计与结构将概念结构设计中的全局E-R图转换为某个具体的DBMS所支持的数据模型相符合的符合的逻辑结构。这些模式在功能上、完整性和一致性约束及数据库的可扩充性等方面均应满足用户的需求。步骤为：（1）.转换数据模型，设计数据库模型（2）.设计子模式（3）.优化模型（4）.评价模型3.2 概念模型转换关系模型将用E-R图描述的概念模型转换成关系模型：一是如何将实体集和实体间的联系转换为关系模式；二是如何确定这些关系模式的属性和键。关系模型：管理员表（编号，姓名，性别，职务，电话，民族，身份证号，出生日期）3NF字段名称数据类型约束编号char(10)Primary key姓名char(10)Not null性别char(4)Not null职务char(10)Not null电话char(11)Not null民族char(10)Not null身份证号char(18)Not null出生日期datetimeNot null学生表（学号，姓名，性别，电话，学院，班级，民族，出生日期）3NF字段名称数据类型约束学号char(10)Primary key姓名char(10)Not null性别char(4)Not null学院char(10)Not null电话char(11)Not null民族char(10)Not null出生日期datetimeNot null注册表（学号，入学日期，楼号，寝室号，入住状态）3NF字段名称数据类型约束学号char(10)Foreign key/Primary key入学日期datetimeNot null楼号char(10)Foreign key寝室号char(10)Foreign key入住状态char(4)Not null楼房表（楼号，楼名，层数，房间数，应住人数，管理员编号，性别）3NF字段名称数据类型约束楼号char(10)Primary key楼名char(10)Not null层数char(10Not null房间数char(10)Not null应住人数char(10)Not null管理员编号char(10)Foreign key性别char(4)Not null房间表（楼号，寝室号，室长学号，已住人数，应住人数）3NF字段名称数据类型约束楼号char(10)Foreign key寝室号char(10)Primary key室长学号char(10Not null已住人数char(10)Not null应住人数char(10)Not null毕业生表（学号，姓名，性别，学院，班级，民族，出生日期，入学日期）3NF字段名称数据类型约束学号char(10)Primary key姓名char(10)Not null性别char(4)Not null学院char(10)Not null入学日期datetimeNot null民族char(10)Not null出生日期datetimeNot null宿舍分配表（楼号，寝室号，学院，性别）字段名称数据类型约束楼号char(10)Primary key寝室号char(10)Primary key学院char(10Not null性别char(4)Not null4、 数据库实施阶段4.1创建数据库和表 （1）数据库的建立：CREATE DATEBASE 宿舍入住迁出系统ONPRIMARY( NAME=宿舍入住迁出系统, FILENAME=G:study（课内）SQL ServerSQL SERVER课程设计宿舍入住迁出系统, SIZE=50, MAXSIZE=500, FILEGROWTH=1) LOG ON( NAME=宿舍入住迁出系统, FILENAME=G:study（课内）SQL ServerSQL SERVER课程设计宿舍入住迁出系统, SIZE=25, MAXSIZE=250, FILEGROWTH=1)（2）宿舍管理员表的建立：CREATE TABLE 管理员表( 编号 CHAR(10) PRIMARY KEY, 姓名 CHAR(10) NOT NULL, 性别 CHAR(4) NOT NULL, 职务 CHAR(10) , 电话 CHAR(11) UNIQUE, 民族 CHAR(10) , 身份证号 CHAR(18) NOT NULL UNIQUE, 出生日期 DATETIME NOT NULL)（3）学生表的建立CREATE TABLE 学生表( 学号 CHAR(10) PRIMARY KEY, 姓名 CHAR(10) NOT NULL, 性别 CHAR(4) NOT NULL, 电话 CHAR(11) NOT NULL UNIQUE, 学院 CHAR(10) NOT NULL, 班级 CHAR(10) NOT NULL, 民族 CHAR(10), 出生日期 DATETIME NOT NULL )（4）楼房表的建立CREATE TABLE 楼房表( 楼号 CHAR(10) PRIMARY KEY, 楼名 CHAR(10) NOT NULL, 层数 INT NOT NULL, 房间数 INT NOT NULL, 应住人数 INT , 管理员编号 CHAR(10) NOT NULL, 性别 CHAR(4) NOT NULL, FOREIGN KEY (管理员编号) REFERENCES 管理员表 (编号) ) （5）房间表的建立CREATE TABLE 房间表( 楼号 CHAR(10) NOT NULL , 寝室号 INT NOT NULL, 室长学号 CHAR(10), 已住人数 INT NOT NULL, 应住人数 INT NOT NULL, FOREIGN KEY (楼号) REFERENCES 楼房表 (楼号), PRIMARY KEY (楼号,寝室号)（6）注册表的建立CREATE TABLE 注册表( 学号 CHAR(10) PRIMARY KEY , 入学日期 DATETIME NOT NULL, 楼号 CHAR(10) NOT NULL , 寝室号 INT NOT NULL , 入住状态 CHAR(4) NOT NULL, FOREIGN KEY (学号) REFERENCES 学生表 (学号), FOREIGN KEY (楼号,寝室号) REFERENCES 房间表 (楼号,寝室号) )（7）宿舍分配表的建立CREATE TABLE 宿舍分配表( 楼号 CHAR(10) NOT NULL , 寝室号 INT NOT NULL , 学院 CHAR(10) NOT NULL, 性别 CHAR(4) NOT NULL, FOREIGN KEY (楼号,寝室号) REFERENCES 房间表 (楼号,寝室号), PRIMARY KEY (楼号,寝室号) （8）毕业生表的建立CREATE TABLE 毕业生表( 学号 CHAR(10) PRIMARY KEY, 姓名 CHAR(10) NOT NULL, 性别 CHAR(4) NOT NULL, 学院 CHAR(10) NOT NULL, 班级 CHAR(10) NOT NULL, 民族 CHAR(10), 出生日期 DATETIME NOT NULL, 入学日期 DATETIME NOT NULL)4.2 创建视图（1）用于查询学生的基本宿舍信息的视图如下：CREATE VIEW 学生基本宿舍信息ASSELECT 学生表.学号,姓名,学院,楼号,寝室号,入住状态FROM 学生表,注册表WHERE 学生表.学号=注册表.学号WITH CHECK OPTION（2）用于查询管理员管理宿舍信息的视图如下：CREATE VIEW 管理员管理宿舍信息ASSELECT 编号,姓名,管理员表.性别,电话,民族,身份证号,出生日期,楼号,楼名FROM 楼房表,管理员表WHERE 楼房表.管理员编号=管理员表.编号 AND 职务=宿舍管理员WITH CHECK OPTION（3）用于查询学生在校全部信息的视图如下：CREATE VIEW 学生全部信息ASSELECT 学生表.学号,姓名,性别,电话,学院,班级,民族,出生日期,楼号,寝室号, 入学日期,入住状态FROM 学生表,注册表WHERE 学生表.学号=注册表.学号WITH CHECK OPTION4.3 创建索引（1）为房间表的楼号,寝室号创建名为idx_宿舍号的非聚集索引USE 宿舍入住迁出系统IF EXISTS(SELECT NAME FROM SYSINDEXES WHERE NAME=IDX_宿 舍号)DROP INDEX 房间表.IDX_宿舍号GOCREATE NONCLUSTERED INDEX IDX_宿舍号 ON 房间表(楼号,寝室号)（2）为宿舍分配表的系别创建名为IDX_系别的非聚集索引USE 宿舍入住迁出系统IF EXISTS(SELECT NAME FROM SYSINDEXES WHERE NAME=IDX_学院)DROP INDEX 宿舍分配表.IDX_学院GOCREATE NONCLUSTERED INDEX IDX_学院 ON 宿舍分配表(学院)4.4 创建触发器（1）在注册表上创建DELETE类型的触发器CREATE TRIGGER TRIG_DEL ON 注册表 FOR DELETEAS DECLARE SNO CHAR(10),LOU CHAR(10),ROOM INT SET SNO=(SELECT 学号 FROM DELETED) SET LOU=(SELECT 楼号 FROM DELETED) SET ROOM=(SELECT 寝室号 FROM DELETED) DELETE 学生表 WHERE 学生表.学号=SNO UPDATE 房间表 SET 已住人数=已住人数-1 WHERE 房间表.楼号=LOU AND 房间表.寝室号=ROOM IF(SELECT 已住人数 FROM 房间表 WHERE 房间表.楼号=LOU AND 房间表.寝室号=ROOM) (SELECT B.出生日期 FROM 学生全部信息 B WHERE B.学号=2012302201)（9）新生入学信息插入，并触发分配宿舍，相应房间表已住人数加一SELECT * FROM 学生基本宿舍信息 WHERE 学号=2013702204 OR 学号=2013402203 OR 学号=2013301104INSERT INTO 学生表 VALUES(2013702204,赵倩,女外国语学院,日语-2,汉族,1995-10-04) INSERT INTO 学生表 VALUES(2013402203,王巧,男材料学院,金属-2,满族,1994-08-03)INSERT INTO 学生表 VALUES(2013301104,陈树骏,男信电学院,电气-1,汉族,1993-01-04)SELECT * FROM 学生基本宿舍信息 WHERE 学号=2013702204 OR 学号=2013402203 OR 学号=20133011045 T-SQL 程序设计5.1创建存储过程和游标（1） 创建存储过程maxderee，输出学校所有剩余未分配床的个数SET ANSI_NULLS ON -用于和NULL的比较，如：NULL=NULL在OFF时会返回 TRUE,在ON 时为FALSE SET QUOTED_IDENTIFIER ON -GOCREATE PROCEDURE REMNUMROOMSAS BEGIN SET NOCOUNT ON - 使返回的结果中不包含受TRANSACT-SQL语句影响的行数的信息。 SELECT SUM(应住人数-已住人数) AS 剩余床位个数 FROM 房间表ENDGO（2） 创建存储过程change_dorm，实现更换宿舍USE 宿舍入住迁出系统IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = CHANGE_DORM AND TYPE = P) DROP PROCEDURE CHANGE_DORMGOUSE 宿舍入住迁出系统GOCREATE PROCEDURE CHANGE_DORM SNO CHAR(10)AS DECLARE SNAME CHAR(10),SDEPT CHAR(10),CLASS CHAR(10),LOU CHAR(10),NLOU CHAR(10) DECLARE ROOM INT,NROOM INT DECLARE CHG_CURSOR CURSOR FOR SELECT 姓名,学院,班级,楼号,寝室号 FROM 注册表,学生表 WHERE 注册表.学号=学生表.学号 AND 注册表.学号=SNO/*打开游标*/OPEN CHG_CURSOR/*提取第一行数据*/FETCH NEXT FROM CHG_CURSOR INTO SNAME,SDEPT,CLASS,LOU,ROOM/*为学生分配宿舍*/SET NLOU=DBO.DISTRILOU(SNO,SDEPT)SET NROOM=DBO.DISTRROOM(SNO,SDEPT)BEGIN/*显示更换宿舍结果*/ SELECT SNO AS 学号,SNAME AS 姓名,SDEPT AS 学院,CLASS AS 班级,LOU AS 原楼号,ROOM AS 原寝室号,NLOU AS 新楼号,NROOM AS 新寝室号 UPDATE 房间表 SET 已住人数=已住人数+1 WHERE 楼号=NLOU AND 寝室号=NROOM UPDATE 房间表 SET 已住人数=已住人数-1 WHERE 楼号=LOU AND 寝室号=ROOM UPDATE 注册表 SET 楼号=NLOU WHERE 学号=SNO UPDATE 注册表 SET 寝室号=NROOM WHERE 学号=SNO ENDCLOSE CHG_CURSORDEALLOCATE CHG_CURSORGO（3）创建存储过程delete_dorm ，实现毕业生迁出USE 宿舍入住迁出系统IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = DELETE_DORM AND TYPE = P) DROP PROCEDURE DELETE_DORMGOUSE 宿舍入住迁出系统GOCREATE PROCEDURE DELETE_DORM AS DECLARE SNO CHAR(10),SNAME CHAR(10),SDEPT CHAR(10),CLASS CHAR(10),RTIME DATETIME,LOU CHAR(10),ROOM INTDECLARE SEX CHAR(4),BIR DATETIME,NATION CHAR(10)DECLARE RI_CURSOR CURSOR FOR SELECT 注册表.学号,姓名,性别,学院,班级,民族,出生日期,入学日期,楼号,寝室号 FROM 注册表,学生表 WHERE 注册表.学号=学生表.学号 AND DATEDIFF ( MONTH,入学日期,GETDATE()45/*打开游标*/OPEN RI_CURSOR/*提取第一行数据*/FETCH NEXT FROM RI_CURSOR INTO SNO,SNAME,SEX,SDEPT,CLASS,NATION,BIR,RTIME,LOU,ROOM-WHILE FETCH_STATUS = 0BEGIN /*把毕业生的信息插入毕业生表*/ INSERT INTO 毕业生表 VALUES(SNO,SNAME,SEX,SDEPT,CLASS,NATION,BIR,RTIME) /*删除毕业学生住宿记录*/ DELETE FROM 注册表 WHERE 学号=SNO /*提取下一行数据*/ FETCH NEXT FROM RI_CURSOR INTO SNO,SNAME,SEX,SDEPT,CLASS,NATION,BIR,RTIME,LOU,ROOMENDCLOSE RI_CURSORDEALLOCATE RI_CURSORGO5.2执行存储过程（1）执行存储过程REMNUMROOMS，输出学校所有剩余未分配床的个数EXECUTE REMNUMROOMS （10）执行存储过程 CHANGE_DORM实现为学号为2010102201的学生更换宿舍EXECUTE CHANGE_DORM 2010102201（11）执行存储过程 DELETE_DORM，实现毕业生表迁出宿舍SELECT * FROM 毕业生表EXECUTE DELETE_DORMSELECT * FROM 毕业生表5.3自定义函数定义（1）统计宿舍已住人数USE 宿舍入住迁出系统GOCREATE FUNCTION HAVLIVENUM (LOUID CHAR(10),ROOMID INT) RETURNS INTAS BEGIN DECLARE NUM INT SELECT NUM= ( SELECT 已住人数 FROM 房间表 WHERE 寝室号=ROOMID AND 楼号=LOUID) RETURN NUM ENDGO（2）统计某校共有房间数USE 宿舍入住迁出系统GOCREATE FUNCTION ALLROOMNUM () RETURNS INTAS BEGIN DECLARE NUM INT SELECT NUM= ( SELECT SUM(房间数) FROM 楼房表 ) RETURN NUM ENDGO（3）检查某房间是否可住人USE 宿舍入住迁出系统GOCREATE FUNCTION CHECKIFLIVE (LOUID CHAR(10),ROOMID INT) RETURNS CHAR(10)AS BEGIN DECLARE NUM INT,YNUM INT SELECT NUM=DBO.HAVLIVENUM(LOUID,ROOMID) SET YNUM= (SELECT 应住人数 FROM 房间表 WHERE 寝室号=ROOMID AND 楼号=LOUID) IF(YNUM-NUM0) RETURN 是 RETURN 否 ENDGO（4）查询学院SDEPT剩余未分配床位数USE 宿舍入住迁出系统GOCREATE FUNCTION YVROOM (SDEPT CHAR(10) RETURNS INTAS BEGIN DECLARE NUM INT SET NUM= (SELECT SUM(应住人数-已住人数) FROM 房间表,宿舍分配表 WHERE 宿舍分配表.学院=SDEPT AND 宿舍分配表.楼号=房间表.楼号 AND 宿舍分配表.寝室号=房间表.寝室号) RETURN NUM ENDGO（5）给学号为SNO的学生分配宿舍,返回分配的楼号USE 宿舍入住迁出系统GOCREATE FUNCTION DISTRILOU (SNO CHAR(10),SDEPT CHAR(10) RETURNS CHAR(10)AS BEGIN DECLARE LOU CHAR(10) DECLARE SEX CHAR(4) SET SEX=(SELECT 性别 FROM 学生表 WHERE 学号=SNO)SET LOU=(SELECT TOP 1 房间表.楼号 FROM 房间表,宿舍分配表 WHERE 宿舍分配表.楼号=