2) 存储过程p3_Room_Insert功能的验证：
3) 存储过程p4_Fitment_Insert功能的验证：
(注：由于篇幅限制，这里仅给出了其中几个存储过程功能的验证)
-46-
附录4 所有的SQL运行语句
create database Student_Dormitory_Management;
create table Dormitory(
create table Worker(
WorNo char(5)
not null unique,
DorNo smallint
not null,
DorCampus char(4) not null, DorLocation char(4) not null, DorPhNo char(12) null, DorAdminist char(10) not null,
primary key(DorNo,DorCampus,DorLocation), check(DorNo>0 and DorNo<100));
WorName char(10) not null, WorType char(8) not null, WorWage int
not null,
WorSex char(2) not null, WorPhNo char(12) WorTime char(30) DorNo smallint
null, null,
not null,
DorCampus char(4) not null, DorLocation char(4) primary key(WorNo),
foreign key(DorNo,DorCampus,DorLocation) references
Dormitory(DorNo,DorCampus,DorLocation),
not null,
check(WorWage >= 0),
check(WorSex = '男' or WorSex = '女'));
create table Room(
RNo char(6)
not null unique,
null, not null,
RHeader char(10) RGrade char(4)
RDepart char(20) not null, RPerfect char(20) DorNo smallint
not null,
not null,
DorCampus char(4) not null, DorLocation char(4)
not null,
-47-
create table Fitment(
create table Student(
create table SafeGuard(
SGName char(15) SGWorNum int SGHeader char(10)
not null unique,
StuNo char(9)
not null unique, not null, not null, not null,
FitName char(16) FitPrice float FitNum int DorNo smallint
not null unique, not null, not null,
not null, not null, not null,
primary key(RNo),
foreign key(DorNo,DorCampus,DorLocation) references
Dormitory(DorNo,DorCampus,DorLocation));
DorCampus char(4)
DorLocation char(4) primary key(FitName),
foreign key(DorNo,DorCampus,DorLocation) references
Dormitory(DorNo,DorCampus,DorLocation));
DepName char(20) StuName char(10) StuSex char(2)
StuHome char(10) not null,
not null,
StuBorth DATETIME
StuETime DATETIME not null, StuPerfect char(20) StuClass int RNo char(6) DorNo smallint
not null, not null,
not null,
not null, not null, not null,
DorCampus char(4)
DorLocation char(4) primary key(StuNo),
foreign key(RNo) references Room(RNo),
foreign key(DorNo,DorCampus,DorLocation) references
Dormitory(DorNo,DorCampus,DorLocation),
check(StuClass>0 and StuClass<=10));
not null,
not null,
-48-
create table ArticalInOut(
create table FitmentDestruction(
create table FitmentCompensate(
create table Accident(
AcNo int
not null unique,
FitName char(16) StuNo char(9)
not null,
not null,
FitName char(16) StuNo char(9) RNo char(6) FDFitNum int
not null unique,
not null,
StuNo char(9)
not null, null,
SGPhone char(12) primary key(SGName), check(SGWorNum > 0));
null,
AIOArtical char(16) AIOPrin char(5) AIODate datetime AIONo int
not null,
not null,
not null unique,
primary key(AIONo,AIODate),
foreign key(StuNo) references Student(StuNo), foreign key(AIOPrin) references Worker(WorNo), check(AIONo > 0));
not null,
not null,
foreign key(FitName) references Fitment(FitName), foreign key(StuNo) references Student(StuNo), foreign key(RNo) references Room(RNo), check(FDFitNum >= 0));
FCPrin char(15) not null,
FCompDate Datetime not null, FCompNum int
not null,
foreign key(FitName) references Fitment(FitName), foreign key(StuNo) references Student(StuNo), foreign key(FCPrin) references SafeGuard(SGName), check(FCompNum >= 0));
AcType char(10) StuNo char(9)
not null,
not null,
-49-
create table AccidentResearch(
create table AccidentCompensate(
create view WorView(编号,姓名,工作类型,工资,性别,联系方式,工作时间,宿舍楼编号,校区,区位)
create view DormView (宿舍楼编号,校区,区位,楼管处电话,楼管)
as select * from dormitory
-50-
as select * from Worker with check option
AcNo int
not null, not null, not null,
AcNo int
not null, not null, not null,
AcDate datetime AcArtical char(30) AcVerify char(5) SGName char(15) AcArNum int
not null,
not null,
not null,
not null, not null, not null,
AcStuPh char(12)
primary key(AcNo,AcDate),
foreign key(StuNo) references Student(StuNo),
foreign key(SGName) references SafeGuard(SGName), check(AcArNum > 0));
ARName char(15) SGName char(15) ARResult char(5) primary key(SGName),
not null,
foreign key(AcNo) references Accident(AcNo),
foreign key(SGName) references SafeGuard(SGName));
ACStu char(9)
AcArtical char(30) AcDate Datetime SGName char(15)
not null,
not null,
foreign key(AcNo) references Accident(AcNo), foreign key(ACStu) references Student(StuNo),
foreign key(SGName) references SafeGuard(SGName));