use test;
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2016/12/25 19:51:49 */
/*==============================================================*/
drop table if exists cars;
drop table if exists checks;
drop table if exists clients;
drop table if exists items;
drop table if exists loginlog;
drop table if exists privileges;
drop table if exists rents;
drop table if exists role_privilege;
drop table if exists roles;
drop table if exists systemlog;
drop table if exists user_role;
drop table if exists users;
/*==============================================================*/
/* Table: cars */
/*==============================================================*/
create table cars
(
carid int not null,
carno varchar(40),
type varchar(30),
color varchar(30),
value decimal,
rentprice decimal,
deposit decimal,
isrented bool,
description varchar(500),
primary key (carid)
);
/*==============================================================*/
/* Table: checks */
/*==============================================================*/
create table checks
(
checkid int not null,
uid int,
rentid int,
checkdate datetime,
status varchar(30),
problem varchar(80),
indemnify decimal,
primary key (checkid)
);
/*==============================================================*/
/* Table: clients */
/*==============================================================*/
create table clients
(
cid int not null,
cname varchar(40),
cidentity varchar(40),
phone varchar(20),
address varchar(80),
sex varchar(10),
occupation varchar(40),
primary key (cid)
);
/*==============================================================*/
/* Table: items */
/*==============================================================*/
create table items
(
iid int not null,
iname varchar(40),
icon varchar(80),
primary key (iid)
);
/*==============================================================*/
/* Table: loginlog */
/*==============================================================*/
create table loginlog
(
lid int not null,
uid int,
ip varchar(40),
logintime datetime,
primary key (lid)
);
/*==============================================================*/
/* Table: privileges */
/*==============================================================*/
create table privileges
(
pid int not null,
iid int,
pname varchar(40),
purl varchar(80),
primary key (pid)
);
/*==============================================================*/
/* Table: rents */
/*==============================================================*/
create table rents
(
rentid int not null,
uid int,
cid int,
carid int,
imprest decimal,
price decimal,
realpay decimal,
begindate datetime,
enddate datetime,
realenddate datetime,
rentstatus varchar(20),
primary key (rentid)
);
/*==============================================================*/
/* Table: role_privilege */
/*==============================================================*/
create table role_privilege
(
rid int not null,
pid int not null,
primary key (rid, pid)
);
/*==============================================================*/
/* Table: roles */
/*==============================================================*/
create table roles
(
rid int not null,
rname varchar(40),
primary key (rid)
);
/*==============================================================*/
/* Table: systemlog */
/*==============================================================*/
create table systemlog
(
sid int not null,
uid int,
action varchar(50),
actiontime datetime,
primary key (sid)
);
/*==============================================================*/
/* Table: user_role */
/*==============================================================*/
create table user_role
(
uid int not null,
rid int not null,
primary key (uid, rid)
);
/*==============================================================*/
/* Table: users */
/*==============================================================*/
create table users
(
uid int not null,
username varchar(40),
password varchar(40),
uidentity varchar(40),
realname varchar(40),
sex varchar(10),
address varchar(80),
phone varchar(20),
position varchar(20),
enrolldate datetime,
primary key (uid)
);
alter table checks add constraint FK_check_rent foreign key (rentid)
references rents (rentid) on delete restrict on update restrict;
alter table checks add constraint FK_check_user foreign key (uid)
references users (uid) on delete restrict on update restrict;
alter table loginlog add constraint FK_loginlog_user foreign key (uid)
references users (uid) on delete restrict on update restrict;
alter table privileges add constraint FK_privilege_item foreign key (iid)
references items (iid) on delete restrict on update restrict;
alter table rents add constraint FK_rent_car foreign key (carid)
references cars (carid) on delete restrict on update restrict;
alter table rents add constraint FK_rent_client foreign key (cid)
references clients (cid) on delete restrict on update restrict;
alter table rents add constraint FK_rent_user foreign key (uid)
references users (uid) on delete restrict on update restrict;
alter table role_privilege add constraint FK_role_privilege foreign key (rid)
references roles (rid) on delete restrict on update restrict;
alter table role_privilege add constraint FK_role_privilege2 foreign key (pid)
references privileges (pid) on delete restrict on update restrict;
alter table systemlog add constraint FK_syslog_user foreign key (uid)
references users (uid) on delete restrict on update restrict;
alter table user_role add constraint FK_user_role foreign key (uid)
references users (uid) on delete restrict on update restrict;
alter table user_role add constraint FK_user_role2 foreign key (rid)
references roles (rid) on delete restrict on update restrict;