数据库课程设计学生宿舍管理系统.docVIP

目录一、需求分析.2【处理对象】.2【处理功能要求】.2【安全性和完整性要求】.2二、概要设计.3【功能结构图】.3【E-R图】.4【建立数据库、数据表、插入数据】.5【导入数据】.6【数据库的建立】.10三、详细设计.13四、小结.18五、参考文献.19宿舍信息管理系统1、需求分析1.1处理对象系统要处理的对象包括学生基本信息、宿舍基本信息、宿舍楼基本信息、楼栋管理员基本信息、来访人员出入基本信息等五个方面，各个对象包括信息如下所示（详细的数据见于数据字典）：系统要处理的对象包括宿舍楼基本信息、学生基本信息、宿舍基本信息、楼道工作人员基本信息、宿舍保卫处基本信息、宿舍事故基本信息、物品出入基本信息等七个方面，各个对象包括信息如下所示（详细的数据见于数据字典）：1宿舍楼基本信息(Dor_info)：包括 宿舍楼编号、宿舍楼楼管员信息等方面，这样可以方便管理者对宿舍楼的管理，提高查询效率；2学生基本信息(Student)：包括 学生编号、学生所在学院信息、学生姓名、学生性别、学生来自省份、学生出生日期、学生入学时间、学生所学专业、所在班级等方面的信息，可以方便学信息的查询和更新；3宿舍基本信息()：宿舍基本信息包括； 4来访人员出入信息(Visit)：访问的宿舍楼编号、房间号、来访人的姓名、访问日期、离开日期。1.2处理功能要求系统主要完成以下几个功能：1宿舍楼基本信息查询与修改；2学生基本信息查询与更新以及插入；3每一幢宿舍楼中宿舍信息的查询与信息更新；4宿舍楼人员访问出入审批及记录；1.3安全性和完整性要求安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供系统数据一定程度上的安全性，再通过用户授权机制，欲用户登陆来识别用户级别，根据这个级别来分配用户权限，达到数据更高层次的安全保密功能。完整性要求用于描述宿舍楼基本信息、学生基本信息、宿舍基本信息、楼栋管理员基本信息、访问出人员入基本信息中数据项能否为null，以及一些用户自定义完整性（符合实际要求）。2、概要设计2.1功能结构图管理员宿舍楼基本信息宿舍基本信息宿舍居住信息学生基本信息访问人员出入信息插入查询删除查询删除进入界面插入插入显示全部删除插入查询删除插入查询删除2.2 E-R图学生宿舍楼管宿舍楼入住n1组成m1管理1n性别分类宿舍编号学号姓名性别管理员编号2.3建立数据库、数据表、插入数据CREATE DATABASE Dor_ManON (NAME=Dor_Man_Data,FILENAME=E:宿舍管理系统Dor_Man_Data.mdf,SIZE=100,MAXSIZE=500,FILEGROWTH=10%)LOG ON(NAME=Dor_Man_Log,FILENAME=E:宿舍管理系统Dor_Man_Log.ldf,SIZE=50,MAXSIZE=100,FILEGROWTH=2)GOCREATE TABLE Dor_info(Dor_no VARCHAR(10) NOT NULL PRIMARY KEY,Dorvol INT CHECK(Dorvol=0 AND Dorvol=0 AND invol=6) NULL,supd VARCHAR(12) NULL,Dor_tel CHAR(20) NULL,PRIMARY KEY (Dor_no,Room_no)GOCREATE TABLE Check_In_info(number CHAR(12) NOT NULL PRIMARY KEY,name VARCHAR(24) NOT NULL ,Dor_no VARCHAR(10) NOT NULL,Room_no VARCHAR(10) NOT NULL,intime SMALLDATETIME NOT NULL)GOCREATE TABLE Visitor_info(vit_name CHAR(12) NOT NULL,sta_time DATETIME NOT NULL,end_time DATETIME NULL,InDor_no VARCHAR(10) NOT NULL,InRoom_no VARCHAR(10) NOT NULL)GOINSERT INTO Dor_info(Dor_no,Dorvol,Dor_sex,Admi)VALUES(#11,439,男,楼妈号);INSERT INTO Dor_info(Dor_no,Dorvol,Dor_sex,Admi)VALUES(#12,500,男,楼妈号);INSERT INTO Dor_info(Dor_no,Dorvol,Dor_sex,Admi)VALUES(#13,458,女,楼妈号);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163001,李雪,女,1993-11-7,甘肃天水财经,经管院,2,2012-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163002,王刚,男,1992-1-17,甘肃金昌农经,经管院,3,2011-9-2);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163003,纳兰嫣然,女,1993-5-8,甘肃平凉财经,经管院,1,2013-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163004,萧炎,男,1992-4-7,甘肃武威农经,经管院,2,2012-9-2);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163005,叶凡,男,1992-1-25,甘肃敦煌财经,经管院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163006,萧薰儿,女,1992-11-25,甘肃敦煌计算机,信科院,2,2012-9-3);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163007,姬紫月,女,1993-1-12,甘肃天水计算机,信科院,1,2013-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163008,方寒,男,1992-6-25,甘肃武威电信,信科院,2,2012-9-3);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163009,烟水天,女,1991-8-2,甘肃平凉电信,信科院,1,2013-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163010,李琦,男,1991-3-25,甘肃定西信管,信科院,2,2012-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163011,叶瞳,男,1992-9-25,甘肃定西信管,信科院,1,2013-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163012,叶星辰,男,1992-11-3,甘肃敦煌水土保湿,林学院,2,2012-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163013,杨柳,女,1993-8-7,甘肃敦煌水土保湿,林学院,2,2012-9-3);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163014,王璞,男,1992-1-25,甘肃平凉水土保湿,林学院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163015,邹天琪,男,1992-5-25,甘肃定西水利水电工程,工学院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163016,王海强,男,1993-5-15,甘肃庆阳土木工程专业,工学院,2,2012-9-2);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163017,王明祥,男,1990-11-25,甘肃兰州水利水电工程,工学院,3,2011-9-2);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163018,林月如,女,1990-7-25,甘肃兰州土木工程专业,工学院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163019,李逍遥,男,1993-4-22,甘肃庆阳水利水电工程,工学院,1,2013-9-2);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163020,赵灵儿,女,1992-6-26,甘肃敦煌园艺专业,农学院,2,2012-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163021,独孤剑,男,1992-12-24,甘肃金昌中草药栽培与鉴定,农学院,2,2012-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163022,徐正宇,男,1991-12-2,甘肃天水园艺专业,农学院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163023,周幼琳,女,1992-12-12,甘肃武威中草药栽培与鉴定,农学院,1,2012-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163024,林凡,男,1992-7-13,甘肃平凉设施农业科学与工程,农学院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163025,杨林,男,1992-11-25,甘肃兰州区域发展专业,农学院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163026,徐紫萱,女,1990-1-25,甘肃定西设施农业科学与工程,农学院,1,2013-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163027,周建行,男,1990-11-5,甘肃敦煌水利水电工程,工学院,1,2013-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163028,周雪茹,女,1992-8-7,甘肃天水信管,信科院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163029,路飞,男,1992-8-6,甘肃武威计算机,信科院,3,2011-9-1);INSERT INTO Stu_info(number,name,sex,birthday,native,tel,major,dept,grade,entime)VALUES(622701163030,杨过,男,1992-6-30,甘肃兰州区域发展专业,农学院,3,2011-9-1);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163001,李雪,#13,333,9/1/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163002,王刚,#12,222,9/2/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163003,纳兰嫣然,#13,127,9/1/2013);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163004,萧炎,#11,111,9/2/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163005,叶凡,#11,111,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163006,萧薰儿,#13,333,9/3/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163007,姬紫月,#13,333,9/1/2013);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163008,方寒,#11,111,9/3/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163009,烟水天,#13,333,9/1/2013);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163010,李琦,#11,111,9/1/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163011,叶瞳,#11,111,9/1/2013);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163012,叶星辰,#11,111,9/1/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163013,杨柳,#13,333,9/3/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163014,王璞,#12,222,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163015,邹天琪,#12,222,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163016,王海强,#12,222,9/2/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163017,王明祥,#12,222,9/2/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163019,李逍遥,#12,222,9/2/2013);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163018,林月如,#13,113,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163020,赵灵儿,#13,113,9/1/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163023,周幼琳,#13,113,9/1/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163026,徐紫萱,#13,113,9/1/2013);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163021,独孤剑,#11,128,9/1/2012);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163022,徐正宇,#11,128,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163024,林凡,#11,128,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163025,杨林,#11,128,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163027,周建行,#12,126,9/1/2013);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163029,路飞,#12,126,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163030,杨过,#12,126,9/1/2011);INSERT INTO Check_In_info(number,name,Dor_no,Room_no,intime)VALUES(622701163028,周雪茹,#13,127,9/1/2011);INSERT INTO Room_info(Dor_no,Room_no,invol,supd,Dor_tel)VALUES(#11,111,6,萧炎,11-596387);INSERT INTO Room_info(Dor_no,Room_no,invol,supd,Dor_tel)VALUES(#11,128,4,独孤剑,11-578387);INSERT INTO Room_info(Dor_no,Room_no,invol,supd,Dor_tel)VALUES(#12,222,6,李逍遥,12-634387);INSERT INTO Room_info(Dor_no,Room_no,invol,supd,Dor_tel)VALUES(#12,126,3,路飞,12-616387);INSERT INTO Room_info(Dor_no,Room_no,invol,supd,Dor_tel)VALUES(#13,333,5,姬紫月,13-785127);INSERT INTO Room_info(Dor_no,Room_no,invol,supd,Dor_tel)VALUES(#13,127,2,纳兰嫣然,13-746369);INSERT INTO Room_info(Dor_no,Room_no,invol,supd,Dor_tel)VALUES(#13,113,4,林月如,13-731369);3、详细设计3.1新生入住宿舍业务流程图：学生未批准入住楼管处批准入住并登记信息宿舍3.2查询业务流程图（查询宿舍学生信息、楼栋管理员信息、宿舍楼信息等）：查询宿舍学生信息表查询宿舍信息表查询楼栋管理员信息表楼管处查询结果外来人员4.数据字典（a）数据项：系统涉及的数据项有71项表1.1 数据项列表数据项编号数据项名数据项含义与其它数据项的关系存储结构别名DI-1number学生编号varchar(12)学号DI-2dept学生所在学院varchar(24)学院DI-3name学生姓名varchar(12)姓名DI-4sex学生性别char(2)性别DI-5native学生来自省份varchar(40)祖籍DI-6birt