基于SSM的租赁管理系统0.3_20161225_数据库设计

数据库设计

1. 概念模型

基于SSM的租赁管理系统0.3_20161225_数据库设计

2. 类模型

基于SSM的租赁管理系统0.3_20161225_数据库设计

3. 生成SQL

 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;
上一篇:[SQL] 外卖系统数据库设计


下一篇:使用PowerDesigner 设计SQL Server 数据库