学生宿舍管理系统oracle数据库设计 - 图文(5)

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));

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库学生宿舍管理系统oracle数据库设计 - 图文(5)在线全文阅读。