(数据库课程设计)学生宿舍管理系统毕业设计论文(7)
RDepart char(20) not null,
RPerfect char(20) not null,
DorNo smallint not null,
DorCampus char(4) not null,
DorLocation char(4) not null,
primary key(RNo),
foreign key(DorNo,DorCampus,DorLocation) references
Dormitory(DorNo,DorCampus,DorLocation));
[来源:http://www.doc163.com]
create table Fitment(
FitName char(16) not null unique,
FitPrice float not null,
FitNum int not null,
DorNo smallint not null,
DorCampus char(4) not null,
DorLocation char(4) not null,
primary key(FitName),
foreign key(DorNo,DorCampus,DorLocation) references
Dormitory(DorNo,DorCampus,DorLocation));
create table Student(
StuNo char(9) not null unique,
DepName char(20) not null,
StuName char(10) not null,
StuSex char(2) not null,
StuHome char(10) not null,
StuBorth DATETIME not null,
StuETime DATETIME not null,
StuPerfect char(20) not null,
StuClass int not null,
RNo char(6) not null,
DorNo smallint not null,
DorCampus char(4) not null,
DorLocation char(4) not null,
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));
create table SafeGuard(
SGName char(15) not null unique,
SGWorNum int not null,
SGHeader char(10) not null,
SGPhone char(12) null,
primary key(SGName),
check(SGWorNum > 0));
create table ArticalInOut(
StuNo char(9) not null,
AIOArtical char(16) null,
AIOPrin char(5) not null,
AIODate datetime not null,
AIONo int not null unique,
primary key(AIONo,AIODate),
foreign key(StuNo) references Student(StuNo),
foreign key(AIOPrin) references Worker(WorNo),
check(AIONo > 0)); [资料来源:http://www.doc163.com]
create table FitmentDestruction(
FitName char(16) not null unique,
StuNo char(9) not null,
RNo char(6) not null,
FDFitNum int not null,
foreign key(FitName) references Fitment(FitName),
foreign key(StuNo) references Student(StuNo),
foreign key(RNo) references Room(RNo),
check(FDFitNum >= 0)); [资料来源:https://www.doc163.com]
create table FitmentCompensate(
FitName char(16) not null,
StuNo char(9) not null,
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)); [来源:http://Doc163.com]
create table Accident(
AcNo int not null unique,
AcType char(10) not null,
StuNo char(9) not null,
AcDate datetime not null,
AcArtical char(30) not null,
AcVerify char(5) not null,
SGName char(15) not null,
AcArNum int not null,
AcStuPh char(12) not null,
primary key(AcNo,AcDate),
foreign key(StuNo) references Student(StuNo),
foreign key(SGName) references SafeGuard(SGName),
check(AcArNum > 0)); [版权所有:http://DOC163.com]
create table AccidentResearch(
AcNo int not null,
ARName char(15) not null,
SGName char(15) not null,
ARResult char(5) not null,
primary key(SGName),
foreign key(AcNo) references Accident(AcNo),
foreign key(SGName) references SafeGuard(SGName));
create table AccidentCompensate(
AcNo int not null,
ACStu char(9) not null,
AcArtical char(30) not null,
AcDate Datetime not null,
SGName char(15) not null,
foreign key(AcNo) references Accident(AcNo),
foreign key(ACStu) references Student(StuNo),
foreign key(SGName) references SafeGuard(SGName));
create view WorView(编号,姓名,工作类型,工资,性别,联系方式,工作时间,宿舍楼编号,校区,区位)
as
select *
from Worker
with check option [版权所有:http://DOC163.com]
create view DormView (宿舍楼编号,校区,区位,楼管处电话,楼管)
as
select *
from dormitory
with check option
[资料来源:http://Doc163.com]
create view RoomView (宿舍编号,舍长,年级,学院,专业,宿舍楼编号, 校区,区位)
as
select *
from Room
with check option [资料来源:http://doc163.com]
create view FitView (物品名,价格,数量,宿舍楼编号, 校区,区位)
as
select *
from Fitment
with check option [来源:http://Doc163.com]
create view StuView (学号,学院,姓名,性别,祖籍,出生日期,入学时间,专业,班级,宿舍编号, 宿舍楼编号,校区,区位)
as
select *
from Student
with check option [资料来源:www.doc163.com]
create view SGView (名称,人员数目,负责人,电话)
as
select *
from SafeGuard
with check option
create view ArIOView (学号,出入物品,负责人,日期,序号)
as
select *
from ArticalInOut
with check option [来源:http://www.doc163.com]
create view FDView (物品名,学号,宿舍号,损坏数量)
as
select *
from FitmentDestruction
with check option [版权所有:http://DOC163.com]
create view FCView (物品名,学号,负责人,日期,赔偿数量)
as
select *
from FitmentCompensate
with check option
[版权所有:http://DOC163.com]
create view AccView (编号,事故类型,学号,日期,损失物品,属实,负责单位,损失数量,学生联系方式)
as
select *
from Accident
with check option
create view ARView (编号,调查名称,负责单位,调查结果)
as
select *
from AccidentResearch
with check option
[资料来源:www.doc163.com]
create view ACView (编号,学号,赔偿物品,日期,负责单位)
as
select *
from AccidentCompensate
with check option
create unique index StudentNo on Student(StuNo);
create unique index RoomNo on Room(RNo);
create unique index DormitoryNo_Campus_Location on Dormitory(DorNo desc,DorCampus,DorLocation);
create index StudentName on Student(StuName);
[资料来源:http://Doc163.com]
create unique index FitmentName on Fitment(FitName); [资料来源:http://Doc163.com]
create unique index SafeGuardName on SafeGuard(SGName);