本次暑假实践结束了,
和同学们一起做了一个学生宿舍管理系统的数据库.
基于该数据库开发学生宿舍管理系统软件.
发现数据库这东西一下子创建一个大概的结构其实是很简单的,
但是你要将这个数据库完整,并准确地运用到软件程序开发中的确需要很大的心思.
更何况对于我这菜鸟,连数据库的运用都不能达到十分熟悉的程度.
可想而知这次的数据库的完整性有多高.........
由于数据库编写不完整,所以搞的后期编写代码麻烦死,错误时时发生.
数据库啊数据库,真是一桩麻烦事~
以下是数据库代码:
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