暑假实践做了一个学生宿舍管理系统的数据库

本次暑假实践结束了,

和同学们一起做了一个学生宿舍管理系统的数据库.

基于该数据库开发学生宿舍管理系统软件.

发现数据库这东西一下子创建一个大概的结构其实是很简单的,

但是你要将这个数据库完整,并准确地运用到软件程序开发中的确需要很大的心思.

更何况对于我这菜鸟,连数据库的运用都不能达到十分熟悉的程度.

可想而知这次的数据库的完整性有多高.........

由于数据库编写不完整,所以搞的后期编写代码麻烦死,错误时时发生.

数据库啊数据库,真是一桩麻烦事~

以下是数据库代码:

USE master
GO

IF EXISTS
(SELECT name
FROM master.dbo.sysdatabases
WHERE name = 'CS1')
 DROP DATABASE CS1
GO     

-------------------------------------------------------------------------------------------------------------------------------                                              

create database  CS1   ---新建一个名为CS1的数据库
on
( name = CS1_dat,
  filename = 'D:/CS1.mdf',
  size = 10,
 maxsize = 50,
 filegrowth = 5)                  -------本人创建数据库的位置就是为了贪方便,直接把数据库文件丢到D盘里去 - -!

log on
(name = 'CS1_log',
 filename = 'D:/CS1.ldf',
  size = 5mb,
 maxsize = 25mb,
 filegrowth = 5mb)

go

--------------------------------------------------------------------------------------------------------------------------

use CS1
go

IF OBJECT_ID('系统管理员表')IS NOT NULL
DROP TABLE 系统管理员表
GO

IF OBJECT_ID('宿舍表')IS NOT NULL
DROP TABLE 宿舍表
GO

IF OBJECT_ID('宿舍调整表')IS NOT NULL
DROP TABLE 宿舍调整表
GO
IF OBJECT_ID('学生表')IS NOT NULL
DROP TABLE 学生表
GO
IF OBJECT_ID('违纪记录表')IS NOT NULL
DROP TABLE 违纪记录表
GO
IF OBJECT_ID('访客表')IS NOT NULL
DROP TABLE 访客表
GO

IF OBJECT_ID('访问表 ')IS NOT NULL
DROP TABLE 访问表
GO

IF OBJECT_ID('卫生评比表')IS NOT NULL
DROP TABLE 卫生评比表
GO

IF OBJECT_ID('报修表')IS NOT NULL
DROP TABLE 报修表
GO
IF OBJECT_ID('水电费表')IS NOT NULL
DROP TABLE 水电费表
GO

IF OBJECT_ID('宿舍管理员表')IS NOT NULL
DROP TABLE 宿舍管理员表
GO

IF OBJECT_ID('学院表')IS NOT NULL
DROP TABLE 学院表
GO
IF OBJECT_ID('班级表')IS NOT NULL
DROP TABLE 班级表
GO

create table 系统管理员表
(
 user_id  int identity(1,1),--key
 user_name nchar(30) not null,
 user_password nchar(30) null,
 flag  int default  0
)
GO

create table 宿舍表  ---新建宿舍表,并在表中添加约束
(
  宿舍ID varchar(8)     not null ,                              -----宿舍表里,其实我觉得宿舍ID这东西应该由楼号,
  楼号  varchar(5)     not null ,                                 ----单元号以及宿舍号一同做为主键,这样宿舍ID的
  房间号码 char(3)        not null ,                            -----唯一性及功能性就会更丰富了
  基本设施      varchar(50)    not null ,
  规格          varchar(20)    not null ,
  可住人数      int            not null ,                            
  实住人数      int            not null ,
  入住性别      char(2)        not null ,
  所属学院      varchar(20)    not null ,
  入住情况      varchar(10)    not null ,                   -----这里有个十分严重的问题,就是入住情况竟然没有
  电话          varchar(15)    not null ,                      -----约束,那么这里有人入住也可以写成没有人入住了, 汗~~

  check (入住性别 ='男' OR 入住性别='女'),
  check(可住人数>=实住人数)
)
go

create table 宿舍调整表  ---新建一个宿舍调整表,并在表中添加约束
(
  调整记录号     int            identity(1,1) ,                -----这个 调整记录号用IDENTITY,个人觉得有点太过简化了,
  学号           char(11)       not null ,                        -----合理性究竟怎样,没主意了.毕竟不是自己一个人建的,
  调整时间       datetime       not null ,
  原宿舍ID varchar(8)     not null ,
  现宿舍ID varchar(8)     not null ,
  原床位号       int            not null ,
  现床位号       int            not null ,
  调整类型  varchar(10) not null ,
  备注说明       varchar(100)    ,

  check(原床位号>=0)    ,
  check(现床位号>=0) 
)
go

create table 学生表  ---新建一个学生表,并在表中添加约束
(
  宿舍ID varchar(8)     not null,
  学号          char(11)       not null ,
  姓名          varchar(20)    not null ,
  性别          char(2)        not null ,
  床位号        int            not null ,
  班级号        varchar(4)     not null ,
  学院号 varchar(4)      not null ,
  家庭住址      varchar(40)    not null ,  
  联系电话      varchar(15)    not null ,
  备注说明 varchar(100)            ,

  check (性别 ='男' OR 性别='女')       ,
  check (床位号>=0)   
)
go

 

create table 学院表  ---新建一个学院表,并在表中添加约束
(
  学院号    varchar(4)       not null ,
  学院名       varchar(30)      not null ,
  备注说明    varchar(100)              
)
go

create table 班级表  ---新建一个班级表,并在表中添加约束
(
  班级号    varchar(4)       not null ,
  班级名           varchar(30)      not null ,
  备注说明    varchar(100)              
)
go

 

 

create table 违纪记录表  ---新建一个违纪记录表,并在表中添加约束
(
  违纪记录号    int            identity(1,1) ,
  宿舍ID varchar(8)     not null ,
  学号          char(11)       not null ,
  姓名          varchar(20)    not null ,
  违纪事项      varchar(50)    not null ,
  违纪时间  varchar(20)       not null ,
  处理方法  varchar(50)    not null ,
  处理时间      varchar(20)       not null ,
  备注说明 varchar(100)    ,

  check(处理时间>=违纪时间)
)
go

create table 访客表  ---新建一个访客表,并在表中添加约束
(
  访客号         int            not null ,
  姓名           varchar(20)    not null ,
  性别           char(2)        not null ,
  与被访人关系   varchar(10)    not null ,
  备注说明  varchar(100)    ,

  check (性别 ='男' OR 性别='女')
)
go

create table 访问表  ---新建一个访问表,并在表中添加约束
(
  记录号         int              identity(1,1) ,
  访客号         int              not null,
  宿舍ID  varchar(8)       not null ,
  学号       varchar(11)      not null ,
  访问时间       datetime         not null ,
  离开时间       datetime         not null ,
 
  事宜   varchar(100)             ,

  check(离开时间>=访问时间)  
)
go 

 

create table 卫生评比表  ---新建一个卫生评比表,并在表中添加约束
(
  卫生评比号     int                 identity(1,1) ,
  宿舍ID  varchar(8)          not null ,
  年份           varchar(10)         not null ,
  学期           varchar(10)         not null ,
  周次           int                 not null ,
  分数          int           not null ,

  check( 分数>=0 AND 分数<=100)        ,
  check( 周次>=0) 
)
go 

 

create table 报修表  ---新建一个报修表,并在表中添加约束
(
  报修号         int      identity(1,1) ,
  宿舍ID    varchar(8)       not null ,
  学号             char(11)         not null ,
  报修时间         datetime         not null ,
  维修时间         datetime          ,
  报修事项         varchar(20)      not null ,
  处理结果         varchar(20)      ,
  员工号           char(4)       not null ,
  备注说明         varchar(100)              ,

  check(报修时间<维修时间)
)
go 

 

create table 水电费表  ---新建一个水费表,并在表中添加约束
(
  抄表时间         datetime         not null ,
  宿舍ID    varchar(8)       not null ,
  给定水量         int              not null ,
  起抄水量         int              not null ,
  本月水量         int              not null ,
  水单价           smallmoney       not null ,
  给定电量         int              not null ,
  起抄电量         int              not null ,
  本月电量         int              not null ,
  电单价           smallmoney       not null ,
  备注说明         varchar(100)              ,

  check(本月水量>=起抄水量)                  ,
  check(本月电量>=起抄电量)
)
go  

 

create table 宿舍管理员表  ---新建一个宿舍管理员表,并在表中添加约束
(
  员工号    char(4)          not null ,
  楼号     varchar(5)       not null ,
  姓名             varchar(20)      not null ,
  性别             char(2)          not null ,
  家庭住址         varchar(40)      not null ,  
  联系电话         varchar(15)      not null ,
  备注说明    varchar(100)              ,
  check (性别 ='男' OR 性别='女')
)
go 

 

/*向各表中添加主键*/

use CS1
go

ALTER TABLE 宿舍表                                                     
/*向宿舍表中添加主键*/
ADD CONSTRAINT 宿舍表_pk PRIMARY KEY (宿舍ID)
go

ALTER TABLE 宿舍调整表                                                     
/*向宿舍表中添加主键*/
ADD CONSTRAINT 宿舍调整表_pk PRIMARY KEY (调整记录号)
go

ALTER TABLE 学生表                                                     
/*向学生表中添加主键*/
ADD CONSTRAINT 学生表_pk PRIMARY KEY (学号)
go

ALTER TABLE 违纪记录表                                                     
/*向违纪记录表中添加主键*/
ADD CONSTRAINT 违纪记录表_pk PRIMARY KEY (违纪记录号)
go

ALTER TABLE 访客表                                                     
/*向访客表中添加主键*/
ADD CONSTRAINT 访客表_pk PRIMARY KEY (访客号)
go

ALTER TABLE 访问表                                                     
/*向访问表中添加主键*/
ADD CONSTRAINT 访问表_pk PRIMARY KEY (记录号)
go

ALTER TABLE 卫生评比表                                                     
/*向卫生评比表中添加主键*/
ADD CONSTRAINT 卫生评比表_pk PRIMARY KEY (卫生评比号)
go

ALTER TABLE 报修表                                                     
/*向报修表中添加主键*/
ADD CONSTRAINT 报修表_pk PRIMARY KEY (报修号)
go

ALTER TABLE 水电费表                                                     
/*向水电费表中添加主键*/
ADD CONSTRAINT 水费表_pk PRIMARY KEY (抄表时间,宿舍ID)
go

ALTER TABLE 宿舍管理员表                                                     
/*向宿舍管理员表中添加主键*/
ADD CONSTRAINT 宿舍管理员表_pk PRIMARY KEY (员工号)
go

ALTER TABLE 学院表                                                     
/*向学院表中添加主键*/
ADD CONSTRAINT 学院表_pk PRIMARY KEY (学院号)
go

ALTER TABLE 班级表                                                     
/*向班级表中添加主键*/
ADD CONSTRAINT 班级表_pk PRIMARY KEY (班级号)
go

 

/*约束*/
alter table 学生表  add constraint 学号要以N开头 check (学号 like 'N[0-9]%[0-9]')
alter table 宿舍表 add constraint 电话号码错误 check (电话 like '[0-9][0-9][0-9][0-9][0-9][0-9]%[0-9]');

/*外键*/

alter table 宿舍调整表 add constraint 不存在此类型编号2_fk foreign key (原宿舍ID) references 宿舍表 (宿舍ID);

alter table 宿舍调整表 add constraint 不存在此类型编号3_fk foreign key (现宿舍ID) references 宿舍表 (宿舍ID);

 

alter table 违纪记录表 add constraint 不存在此类型编号4_fk foreign key (宿舍ID) references 宿舍表 (宿舍ID);

alter table 学生表 add constraint 不存在此类型编号7_fk foreign key (宿舍ID) references 宿舍表 (宿舍ID);

 

alter table 访问表 add constraint 不存在此类型编号10_fk foreign key (访客号) references 访客表(访客号);

alter table 访问表 add constraint 不存在此类型编号11_fk foreign key (宿舍ID) references 宿舍表(宿舍ID);

alter table 报修表 add constraint 不存在此学号12_fk foreign key (学号) references 学生表(学号);
alter table 报修表 add constraint 不存在此宿舍ID14_fk foreign key (宿舍ID) references 宿舍表(宿舍ID);
alter table 报修表 add constraint 不存在此员工号15_fk foreign key (员工号) references 宿舍管理员表(员工号);
alter table 水电费表 add constraint 不存在此类型编号16_fk foreign key (宿舍ID) references 宿舍表(宿舍ID);

--=======================存储过程与触发器====================================================================

if Exists (select * from sysobjects where type = 'P' and name = 'CheckLoginGetFlag_SP')
    drop Procedure CheckLoginGetFlag_SP
go

CREATE PROCEDURE CheckLoginGetFlag_SP
(@Name nchar (30),
@Pass char (30))
 AS
   Declare @Flag int
   select @Flag = -1
    if exists
              (select flag from 系统管理员表 where [user_name] = @Name and user_password = @Pass)
    select @Flag =(select  flag from 系统管理员表 where [user_name] = @Name and user_password = @Pass)
 Return @Flag
GO

--=======================默认值====================================================================

CREATE DEFAULT def_默认宿舍 AS '0-0-000'
GO

CREATE DEFAULT def_默认数值 AS 0
GO

sp_bindefault 'def_默认宿舍' , '宿舍调整表.原宿舍ID'
GO

sp_bindefault 'def_默认宿舍' , '宿舍调整表.现宿舍ID'
GO

sp_bindefault 'def_默认数值' , '卫生评比表.分数'
GO

sp_bindefault 'def_默认数值' , '卫生评比表.周次'
GO

sp_bindefault 'def_默认数值' , '水电费表.给定水量'
GO

sp_bindefault 'def_默认数值' , '水电费表.给定电量'
GO

sp_bindefault 'def_默认数值' , '水电费表.水单价'
GO

sp_bindefault 'def_默认数值' , '水电费表.电单价'
GO

sp_bindefault 'def_默认数值' , '学生表.床位号'
GO

sp_bindefault 'def_默认数值' , '宿舍调整表.原床位号'
GO

sp_bindefault 'def_默认数值' , '宿舍调整表.现床位号'
GO

/*增加维修登记视图*/
create view 维修登记
AS
select  楼号,房间号码,学号,报修时间,维修时间,报修事项,处理结果,员工号,备注说明 from  报修表 , 宿舍表
where 报修表.宿舍ID=宿舍表.宿舍ID
with check option
GO

create view 学生居住信息
AS
select  楼号,房间号码,学号,姓名,性别,床位号, 班级名, 学院名, 家庭住址, 联系电话
FROM 学生表 ,宿舍表,班级表, 学院表
where 学生表.宿舍ID=宿舍表.宿舍ID AND
 学生表.班级号=班级表.班级号 AND
 学生表.学院号=学院表.学院号
with check option
GO

create view 学生居住变动信息
AS
SELECT 学生表.学号, 姓名, 性别, 班级名, 学院名, 联系电话,家庭住址,调整时间,原宿舍ID,现宿舍ID,原床位号, 现床位号,调整类型,宿舍调整表.备注说明
FROM 学生表 INNER JOIN
      宿舍调整表 ON 学生表.学号 = 宿舍调整表.学号 INNER JOIN
      班级表 ON 学生表.班级号 = 班级表.班级号 INNER JOIN
      学院表 ON 学生表.学院号 = 学院表.学院号
with check option
GO