create database 宿舍管理系统;
use 宿舍管理系统
create table 宿舍
(
Dno varchar(10) primary key,
Dphone varchar(15) not null
);
use 宿舍管理系统
create table 学生
(
Sno varchar(20),
Sname varchar(20),
Ssex varchar(4) not null,
Sdept varchar(40),
Dno varchar(10),
Checkin datetime,
primary key(Sno,Sname),
foreign key(Dno) references 宿舍(Dno)
);
use 宿舍管理系统
create table 邮件
(
Sname varchar(20) primary key,
Dno varchar(10),
Arrival datetime,
Receipt datetime,
Dphone varchar(15),
foreign key(Dno) references 宿舍(Dno)
);
use 宿舍管理系统
create table 报修
(
Dno varchar(10),
Rname varchar(10),
Rappointment datetime,
Rsolve datetime,
Rreason varchar(50),
Result varchar(50),
Dphone varchar(15),
primary key(Dno,Rname),
foreign key(Dno) references 宿舍(Dno)
);
use 宿舍管理系统
create table 晚归
(
Sno varchar(20),
Sname varchar(20),
Btime datetime,
Lreason varchar(50),
primary key(Sno,Sname,Btime),
foreign key(Sno,Sname) references 学生(Sno,Sname)
);
use 宿舍管理系统
create table 离返校
(
Sno varchar(20),
Sname varchar(20),
Dno varchar(10),
Ltime datetime,
Lreturn datetime,
primary key(Sno,Sname),
foreign key(Sno,Sname) references 学生(Sno,Sname),
foreign key(Dno) references 宿舍(Dno)