數據庫課程設計學生宿舍管理系統(tǒng)

1、目錄一、需求分析.2【處理對象】.2【處理功能要求】.2【安全性和完整性要求】.2二、概要設計.3【功能結構圖】.3【E-R圖】.4【建立數據庫、數據表、插入數據】.5【導入數據】.6【數據庫的建立】.10三、詳細設計.13四、小結.18五、參考文獻.19宿舍信息管理系統(tǒng)1、需求分析1.1處理對象系統(tǒng)要處理的對象包括學生基本信息、宿舍基本信息、宿舍樓基本信息、樓棟管理員基本信息、來訪人員出入基本信息等五個方面，各個對象包括信息如下所示（詳細的數據見于數據字典）：1學生基本信息(Student)：包括 學生學號、學生姓名、學生性別、學生年齡、學生電話、學生所在學院、學生所學專業(yè)、所在班級、入學時

2、間等方面的信息，可以方便學生信息的查詢和更新；2宿舍基本信息(Live)： 包括學生學號、學生姓名、樓棟號、宿舍房間、入住日期；3宿舍樓基本信息(Dormitory)：包括 宿舍樓編號、宿舍樓性別分類信息、管理員姓名、每一幢宿舍樓樓管處的電話、已入住人數、最大容量等方面，這樣可以方便管理者對宿舍樓的管理，提高查詢效率；4樓棟管理員基本信息(Worker)：包括 工作人員編號、工作人員姓名、工作類型、工資、性別、工作地點、電話可以方便管理人員對宿舍樓道工人的任用、信息查詢及更改；6來訪人員出入信息(Visit)：訪問的宿舍樓編號、房間號、來訪人的姓名、被訪問人的姓名、訪問日期、離開日期。1.2處

3、理功能要求系統(tǒng)主要完成以下幾個功能：1宿舍樓基本信息查詢與修改；2學生基本信息查詢與更新以及插入；3每一幢宿舍樓中宿舍信息的查詢與信息更新；（1）宿舍信息管理系統(tǒng)實現對學生，管理員信息的全部讀?。粚崿F對某個學生或管理員特定信息的讀??；（2）實現對某個人員信息的刪除；4宿舍樓人員訪問出入審批及記錄；1.3安全性和完整性要求安全性先通過視圖機制，不同的用戶只能訪問系統(tǒng)授權的視圖，這樣可提供系統(tǒng)數據一定程度上的安全性，再通過用戶授權機制，欲用戶登陸來識別用戶級別，根據這個級別來分配用戶權限，達到數據更高層次的安全保密功能。完整性要求用于描述宿舍樓基本信息、學生基本信息、宿舍基本信息、樓棟管理員基本信

4、息、訪問出人員入基本信息中數據項能否為null，以及一些用戶自定義完整性（符合實際要求）。2、概要設計2.1功能結構圖用戶宿舍樓基本信息樓棟管理員信息宿舍居住信息學生基本信息訪問人員出入信息插入顯示全部信息刪除顯示全部信息刪除進入界面插入插入顯示全部刪除插入顯示全部刪除插入顯示全部刪除2.2 E-R圖學生宿舍樓管宿舍樓入住n1組成m1管理1n性別分類宿舍編號學號姓名性別管理員編號2.3建立數據庫、數據表、插入數據create database StuRoom;/*建立學生信息表*/CREATE Table Student (StuNo Varchar(20) Not null Unique ,

5、 StuName Varchar(20) Not null, Stusex Varchar(20) Check(sex In (man,woman), Stuage Varchar(20) Not null, Stuphone Varchar(20) Not null, School Varchar(20) Not null, Major Varchar(20) Not null, Class Varchar(20) Not null, StuATime Varchar(20) Not null, primary key(StuNo,StuName) );/*建立公寓樓基本信息表*/CREAT

6、E Table Dormitory (DorNo varchar(10) not null primary key, DorSex varchar(10) not null, DorMag varchar(50) not null, DorTel varchar(20) not null, DorNum varchar(20) not null, DorMax varchar(200) not null );/*建立住宿登記表*/CREATE Table Live (StuNo varchar(8) not null primary key, StuName varchar(50) not n

7、ull, DorNo varchar(20) not null, RNo varchar(60) not null, InTime varchar(8) not null, foreign key (StuNo) references Student(StuNo), foreign key (DorNo) references Dormitory(DorNo) );/*建立樓棟工作人員基本信息表*/CREATE Table Worker( WorName Varchar(20) Not null, WorNo Varchar(20) Not null, WorSex Varchar(20) N

8、ot null, WorPhNo Varchar(20) Not null, WorType Varchar(20) Not null, WorWage Varchar(20) Not null, DorNo Varchar(20) Not null, Primary key(WorNo), Foreign key(DorNo) References Dormitory (DorNo),Check(WorSex In(man,woman);/*建立來訪記錄信息表*/CREATE Table Visit ( DorNo varchar(20) not null primary key, RNo

9、varchar(20) not null, InName varchar(20) not null, StuName varchar(20) not null, CTime varchar(20) not null, GTime varchar(20) not null,Foreign key(DorNo) References Dormitory (DorNo);2.3.1 導入數據/*導入學生基本信息*/insert into student values(08052201,David,man,20,19900205,jilinscience,massage,08

10、2,20080907);insert into student values(08052202,lucy,woman,21,19890205,liaoningscience,massage,082,20080907);insert into student values(08052203,jim,man,20,19900206,hunancomputer,software,084,20080907);insert into student values(08052204,Tom,man,22,19880205,hubei,1580425475

11、3,economy,sale,082,20080907);insert into student values(08052205,lily,woman,20,19900405,jilinscience,massage,084,20080907);insert into student values(08052206,anne,woman,21,19890208,beijingscience,math,081,20080907);insert into student values(08052207,Tim,man,20,19900206,he

12、nancomputer,software,083,20080907);insert into student values(08052208,harry,woman,20,19900205,liaoningscience,massage,082,20080907);insert into student values(08052209,rose,woman,20,19900511,shanghaiscience,math,082,20080907);insert into student values(0805221

13、0,jack,man,20,19900212,nanjingscience,massage,081,20080907);insert into student values(08052211,arce,woman,19,19910205,hubeiscience,massage,081,20080907);insert into student values(08052212,roon,man,20,19900205,jilinscience,massage,082,20080907);insert into stu

14、dent values(08052213,hong,woman,21,19900225,zhejiangscience,math,082,20080907);insert into student values(08052214,wen,man,20,19901105,shengyanglanguage,news,083,20080907);insert into student values(08052215,li,man,20,19900305,heilongjiangscience,massage,081,20

15、080907);insert into student values(08052216,Dmdn,woman,20,19900508,henancomputer,software,083,20080907);insert into student values(08052217,jiao,woman,20,19900423,liaoningscience,math,083,20080907);insert into student values(08052218,jili,man,19,19910205,jilins

16、cience,massage,081,20080907);insert into student values(08052219,lan,woman,20,19900805,shanxiscience,math,083,20080907);insert into student values(08052220,grey,man,20,19900315,jilinscience,massage,082,20080907);/*導入公寓樓基本信息*/insert into Dormitory values(1,woman,Aunt wang,88

17、648256,79,100);insert into Dormitory values(2,woman,Aunt zhang,88648252,70,100);insert into Dormitory values(3,woman,Aunt li,88648259,69,100);insert into Dormitory values(4,woman,uncle wang,88648245,83,100);insert into Dormitory values(5,woman,Aunt zhao,88648246,68,100);insert into Dormitory values(

18、6,woman,Aunt qian,88648248,76,100);/*導入宿舍基本信息*/insert into Live values(08052201,David,2,314,20080907);insert into Live values(08052202,lucy,5,202,20080907);insert into Live values(08052203,jim,2,216,20080907);insert into Live values(08052204,Tom,4,236,20080907);insert into Live values(08052205,lily,

19、6,432,20080907);insert into Live values(08052206,anne,1,613,20080907);insert into Live values(08052207,Tim,2,231,20080907);insert into Live values(08052208,jili,5,112,20080907);insert into Live values(08052209,harry,1,213,20080907);insert into Live values(08052210,rose,4,202,20080907);insert into Li

20、ve values(08052211,arce,2,314,20080907);insert into Live values(08052212,roon,5,202,20080907);insert into Live values(08052213,hong,2,216,20080907);insert into Live values(08052214,wen,4,236,20080907);insert into Live values(08052215,li,6,432,20080907);insert into Live values(08052216,Dmdn,1,613,200

21、80907);insert into Live values(08052217,jiao,2,231,20080907);insert into Live values(08052218,jili,5,112,20080907);insert into Live values(08052219,lan,1,213,20080907);insert into Live values(08052220,grey,4,202,20080907);/*導入工作人員基本信息*/Insert into Worker values(wahua,0201,womancleanner,

22、1500,1,);insert into Worker values(zhayue,0202,womanmanage,1500,1);insert into Worker values(liran,0203,womancleanner,1500,2);insert into Worker values(huhg,0204,womanmanage,1500,3);insert into Worker values(jilan,0205,womancleanner,1500,3);insert

23、into Worker values(liyue,0206,womanmanage,1500,2);insert into Worker values(wangqiang,0207,manmanage,1500,6);insert into Worker values(qianzhan,0208,mancleanner,1500,6);insert into Worker values(zhaobin,0209,manmanage,1500,4);insert into Worker val

24、ues(sunwen,0212,mancleanner,1500,4);insert into Worker values(zhangli,0214,manmanage,1500,5);insert into Worker values(qianqian,0215,mancleanner,1500,4);/*導入訪問基本信息*/insert into Visit values(1,613,wen,anne,525,525);insert into Visit values(2,314,li,arce,525,526)

25、;insert into Visit values(4,202,yue,rose,524,525);insert into Visit values(6,432,zhang,li,525,527);insert into Visit values(1,613,wang,Dmdn,525,525);2.3.2數據庫的建立（1）student表（2）dormitory表（3）live表（4）worker表（5）visit表3、詳細設計3.1新生入住宿舍業(yè)務流程圖：學生未批準入住樓管處批準入住并登記信息宿舍3.2查詢業(yè)務流程圖（查詢宿舍學生信息、樓棟管理員信息、宿舍樓信息等）：查詢宿舍學生信息表查詢宿舍信息表查詢樓棟管理員信息表樓管處查詢結果外來人員3.3系統(tǒng)界面操作1.1查詢宿舍信息