最新数据库实验--学生宿舍管理系统设计大作业

目录

一、需求分析…………………………………………………………

【信息需求】…………………………………………………………

【处理功能要求】……………………………………………………

【安全性和完整性要求】……………………………………………

【业务流程图】……………………………………………………….【数据字典】……………………………………………………….

二、概念设计………………………………………………………….【E-R图】…………………………………………………………….三．数据库实现……………………………………………………………【建立数据库、数据表、插入数据】……………………………….【数据库的建立】…………………………………………………….四、小结……………………………………………………………….

1. 系统需求分析阶段

1.1需求分析阶段

（1）信息要求：

系统要处理的对象包括宿舍楼基本信息、学生基本信息、宿舍基本信息、宿舍楼管理人员基本信息、外来人员登记基本信息等5个方面，各个对象包括信息如下所示（详细的数据见于数据字典）：

1．宿舍楼基本信息：包括宿舍编号、每一幢宿舍楼号、楼管处的电话，这样可以方便管理者对宿舍楼的管理，提高查询效率；

2．学生基本信息：包括学号、学院、学生姓名、学生性别、原籍所在地、联系方式、学生出生日期、民族、学生所学专业、所在班级等方面的信息，可以方便学信息的查询和更新；

3．宿舍基本信息：包括宿舍号、可住人数、已住人数、学生专业、学生班级、学生学院。

4．宿舍管理人员基本信息：包括人员编号、人员姓名、性别、联系方式、出生年月、入职年月、管理楼号等数据项，可以方便管理人员对宿舍楼道工人的任用、信息查询及更改；

5．外来人员登记基本信息：包括（维修人员、学生工作人员、来访人员）的姓名、性别、登记时间、离开时间、原由、被访人员姓名。

（2）处理功能要求

系统主要完成一下几个功能：

1．宿舍楼基本信息查询与修改；

2．学生基本信息查询与更新；

3．每一幢宿舍楼中宿舍信息的查询与信息更新；

4．外来人员登记信息查询与修改；

5. 宿舍楼管理人员信息查询与修改；

（3）安全性和完整性要求

安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供系统数据一定程度上的安全性。

完整性要求用于描述宿舍楼基本信息、学生基本信息、宿舍基本信息、楼道管理人员基本信息、外来人员登记基本信息中数据项能否为null，以及一些用户自定义完整性（符

合实际要求），详细完整性要求见于系统的逻辑设计阶段。（4)学生宿舍管理系统业务流程图

(5)数字字典

数据项：

学生数据项

管理人员数据项

宿舍数据项

宿舍楼数字项

外来人员数据项

1.5.2数据结构

1.5.3数据流

1.5.4数据存储

1.5.5处理过程

3．数据库实施阶段

3.1建立数据库、数据表、视图、索引

1.建立数据库。

create database Student Dormitory management

2 建立数据表

/*建立学生信息表*/

CREATE Table Student

(StuNo Varchar(20) Not null Unique ,

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)

);

/*建立公寓楼基本信息表*/

CREATE 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 null,

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) Not 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 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','jilin','','science' ,'massage','082','20080907');

insert into student values('08052202','lucy','woman','21','19890205','liaoning','','scie nce','massage','082','20080907');

insert into student values('08052203','jim','man','20','19900206','hunan','','computer', 'software','084','20080907');

insert into student values('08052204','Tom','man','22','19880205','hubei','','economy',' sale','082','20080907');

insert into student values('08052205','lily','woman','20','19900405','jilin','','science ','massage','084','20080907');

insert into student values('08052206','anne','woman','21','19890208','beijing','','scien ce','math','081','20080907');

insert into student values('08052207','Tim','man','20','19900206','henan','','computer',

'software','083','20080907');

insert into student values('08052208','harry','woman','20','19900205','liaoning','','sci ence','massage','082','20080907');

insert into student values('08052209','rose','woman','20','19900511','shanghai','','scie nce','math','082','20080907');

insert into student values('08052210','jack','man','20','19900212','nanjing','','science ','massage','081','20080907');

insert into student values('08052211','arce','woman','19','19910205','hubei','','science ','massage','081','20080907');

insert into student values('08052212','roon','man','20','19900205','jilin','','science', 'massage','082','20080907');

insert into student values('08052213','hong','woman','21','19900225','zhejiang','','scie nce','math','082','20080907');

insert into student values('08052214','wen','man','20','19901105','shengyang','','langua ge','news','083','20080907');

insert into student values('08052215','li','man','20','19900305','heilongjiang','','scie nce','massage','081','20080907');

insert into student values('08052216','Dmdn','woman','20','19900508','henan','','compute

r','software','083','20080907');

insert into student values('08052217','jiao','woman','20','19900423','liaoning','','scie nce','math','083','20080907');

insert into student values('08052218','jili','man','19','19910205','jilin','','science', 'massage','081','20080907');

insert into student values('08052219','lan','woman','20','19900805','shanxi','','science ','math','083','20080907');

insert into student values('08052220','grey','man','20','19900315','jilin','','science', 'massage','082','20080907');

/*导入公寓楼基本信息*/

insert into Dormitory values('1','woman','Aunt wang','88648256','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('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','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 Live 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','20080907');

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','woman','','cleanner','1500','1',);

insert into Worker values('zhayue','0202','woman','','manage','1500','1');

insert into Worker values('liran','0203','woman','','cleanner','1500','2');

insert into Worker values('huhg','0204','woman','','manage','1500','3');

insert into Worker values('jilan','0205','woman','','cleanner','1500','3');

insert into Worker values('liyue','0206','woman','','manage','1500','2');

insert into Worker values('wangqiang','0207','man','','manage','1500','6');

insert into Worker values('qianzhan','0208','man','','cleanner','1500','6');

insert into Worker values('zhaobin','0209','man','','manage','1500','4');

insert into Worker values('sunwen','0212','man','','cleanner','1500','4');

insert into Worker values('zhangli','0214','man','','manage','1500','5');

insert into Worker values('qianqian','0215','man','','cleanner','1500','4');

/*导入访问基本信息*/

insert into Visit values('1','613','wen','anne','525','525');

insert into Visit values('2','314','li','arce','525','526');

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');