本次暑假實踐結束了,
和同學們一起做了一個學生宿捨管理系統的數據庫.
基於該數據庫開發學生宿捨管理系統軟件.
發現數據庫這東西一下子創建一個大概的結構其實是很簡單的,
但是你要將這個數據庫完整,並准確地運用到軟件程序開發中的確需要很大的心思.
更何況對於我這菜鳥,連數據庫的運用都不能達到十分熟悉的程度.
可想而知這次的數據庫的完整性有多高.........
由於數據庫編寫不完整,所以搞的後期編寫代碼麻煩死,錯誤時時發生.
數據庫啊數據庫,真是一樁麻煩事~
以下是數據庫代碼:
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 , -----這裡有個十分嚴重的問題,
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 ,
床位號 i
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 ,
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 &nb
學期 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 ,
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)
/*向各表中添加主鍵*/
use CS1
go
ALTER TABLE 宿捨表
/*向宿捨表中添加主鍵*/
ADD CONSTRAINT 宿捨表
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
