(数据库课程设计)学生宿舍管理系统毕业设计论文(7)

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

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

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

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

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

create view DormView (宿舍楼编号,校区,区位,楼管处电话,楼管)
  as
  select *
  from dormitory
  with check option

create view RoomView (宿舍编号,舍长,年级,学院,专业,宿舍楼编号, 校区,区位)
  as
  select *
  from  Room
  with check option

create view FitView (物品名,价格,数量,宿舍楼编号, 校区,区位)
  as
  select *
  from Fitment
  with check option

create view StuView (学号,学院,姓名,性别,祖籍,出生日期,入学时间,专业,班级,宿舍编号, 宿舍楼编号,校区,区位)
  as
  select *
  from Student
  with check option

create view SGView (名称,人员数目,负责人,电话)
  as
  select *
  from SafeGuard
  with check option

create view ArIOView (学号,出入物品,负责人,日期,序号)
  as
  select *
  from ArticalInOut
  with check option

create view FDView (物品名,学号,宿舍号,损坏数量)
  as
  select *
  from FitmentDestruction
  with check option

create view FCView (物品名,学号,负责人,日期,赔偿数量)
  as
  select *
  from FitmentCompensate
  with check option

create view AccView (编号,事故类型,学号,日期,损失物品,属实,负责单位,损失数量,学生联系方式)
  as
  select *
  from Accident
  with check option

create view ARView (编号,调查名称,负责单位,调查结果)
  as
  select *
  from AccidentResearch
  with check option

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

create unique index FitmentName on Fitment(FitName);

create unique index SafeGuardName on SafeGuard(SGName);