create table floor (
  fid  varchar(2) not null ,  
  flevel  varchar(2) not null ,  
  froom  int not null ,  
  fexcept  int not null ,  
  freal  int not null ,  
  primary key(fid)  
);

create table dorm (
  fid  varchar(2) not null ,
  did  varchar(4) not null ,
  dexcept  int not null ,
  dreal  int not null ,
  dsex  varchar(2) not null ,
  dmoney  double not null ,
  primary key (fid, did),
  foreign key (fid) references floor (fid)
);

create table ele (
  fid  varchar(2) not null , 
  did  varchar(4) not null ,
  year  varchar(4) not null ,
  month  varchar(2) not null ,
  electric  varchar(4) not null ,
  fare  double not null ,
  primary key (fid,did, year, month),
  foreign key (fid, did) references dorm (fid, did);
);

create table stu (
  stuid  varchar(13) not null ,
  name  varchar(10) not null ,
  sex  varchar(2) not null ,
  age  int not null ,
  depart  varchar(20) not null ,
  major  varchar(20) not null ,
  phone  varchar(11) not null ,
  fid  varchar(2) not null ,
  did  varchar(4) not null ,
  primary key (stuid)
);  

create trigger tri1 
after inster on stu
for each row 
begin
update floor 
set freal=(freal+1) where fid=new.fid;
update dorm
set dreal=(dreal+1) where fid=new.fid and did=new.did; 
end;

create table user (
  stuid  varchar(13) not null , 
  password  varchar(20) not null ,
  primary key (stuid)
);

create table root (
  rid  varchar(20) not null ,
  rname  varchar(10) not null ,
  rpassword  varchar(20) not null ,
  primary key (rid)
);