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

数据库设计

1. 概念模型

 

2. 类模型

3. 生成SQL

  1 use test;
  2 /*==============================================================*/
  3 /* DBMS name:      MySQL 5.0                                    */
  4 /* Created on:     2016/12/25 19:51:49                          */
  5 /*==============================================================*/
  6 
  7 
  8 drop table if exists cars;
  9 
 10 drop table if exists checks;
 11 
 12 drop table if exists clients;
 13 
 14 drop table if exists items;
 15 
 16 drop table if exists loginlog;
 17 
 18 drop table if exists privileges;
 19 
 20 drop table if exists rents;
 21 
 22 drop table if exists role_privilege;
 23 
 24 drop table if exists roles;
 25 
 26 drop table if exists systemlog;
 27 
 28 drop table if exists user_role;
 29 
 30 drop table if exists users;
 31 
 32 /*==============================================================*/
 33 /* Table: cars                                                  */
 34 /*==============================================================*/
 35 create table cars
 36 (
 37    carid                int not null,
 38    carno                varchar(40),
 39    type                 varchar(30),
 40    color                varchar(30),
 41    value                decimal,
 42    rentprice            decimal,
 43    deposit              decimal,
 44    isrented             bool,
 45    description          varchar(500),
 46    primary key (carid)
 47 );
 48 
 49 /*==============================================================*/
 50 /* Table: checks                                                */
 51 /*==============================================================*/
 52 create table checks
 53 (
 54    checkid              int not null,
 55    uid                  int,
 56    rentid               int,
 57    checkdate            datetime,
 58    status               varchar(30),
 59    problem              varchar(80),
 60    indemnify            decimal,
 61    primary key (checkid)
 62 );
 63 
 64 /*==============================================================*/
 65 /* Table: clients                                               */
 66 /*==============================================================*/
 67 create table clients
 68 (
 69    cid                  int not null,
 70    cname                varchar(40),
 71    cidentity            varchar(40),
 72    phone                varchar(20),
 73    address              varchar(80),
 74    sex                  varchar(10),
 75    occupation           varchar(40),
 76    primary key (cid)
 77 );
 78 
 79 /*==============================================================*/
 80 /* Table: items                                                 */
 81 /*==============================================================*/
 82 create table items
 83 (
 84    iid                  int not null,
 85    iname                varchar(40),
 86    icon                 varchar(80),
 87    primary key (iid)
 88 );
 89 
 90 /*==============================================================*/
 91 /* Table: loginlog                                              */
 92 /*==============================================================*/
 93 create table loginlog
 94 (
 95    lid                  int not null,
 96    uid                  int,
 97    ip                   varchar(40),
 98    logintime            datetime,
 99    primary key (lid)
100 );
101 
102 /*==============================================================*/
103 /* Table: privileges                                            */
104 /*==============================================================*/
105 create table privileges
106 (
107    pid                  int not null,
108    iid                  int,
109    pname                varchar(40),
110    purl                 varchar(80),
111    primary key (pid)
112 );
113 
114 /*==============================================================*/
115 /* Table: rents                                                 */
116 /*==============================================================*/
117 create table rents
118 (
119    rentid               int not null,
120    uid                  int,
121    cid                  int,
122    carid                int,
123    imprest              decimal,
124    price                decimal,
125    realpay              decimal,
126    begindate            datetime,
127    enddate              datetime,
128    realenddate          datetime,
129    rentstatus           varchar(20),
130    primary key (rentid)
131 );
132 
133 /*==============================================================*/
134 /* Table: role_privilege                                        */
135 /*==============================================================*/
136 create table role_privilege
137 (
138    rid                  int not null,
139    pid                  int not null,
140    primary key (rid, pid)
141 );
142 
143 /*==============================================================*/
144 /* Table: roles                                                 */
145 /*==============================================================*/
146 create table roles
147 (
148    rid                  int not null,
149    rname                varchar(40),
150    primary key (rid)
151 );
152 
153 /*==============================================================*/
154 /* Table: systemlog                                             */
155 /*==============================================================*/
156 create table systemlog
157 (
158    sid                  int not null,
159    uid                  int,
160    action               varchar(50),
161    actiontime           datetime,
162    primary key (sid)
163 );
164 
165 /*==============================================================*/
166 /* Table: user_role                                             */
167 /*==============================================================*/
168 create table user_role
169 (
170    uid                  int not null,
171    rid                  int not null,
172    primary key (uid, rid)
173 );
174 
175 /*==============================================================*/
176 /* Table: users                                                 */
177 /*==============================================================*/
178 create table users
179 (
180    uid                  int not null,
181    username             varchar(40),
182    password             varchar(40),
183    uidentity            varchar(40),
184    realname             varchar(40),
185    sex                  varchar(10),
186    address              varchar(80),
187    phone                varchar(20),
188    position             varchar(20),
189    enrolldate           datetime,
190    primary key (uid)
191 );
192 
193 alter table checks add constraint FK_check_rent foreign key (rentid)
194       references rents (rentid) on delete restrict on update restrict;
195 
196 alter table checks add constraint FK_check_user foreign key (uid)
197       references users (uid) on delete restrict on update restrict;
198 
199 alter table loginlog add constraint FK_loginlog_user foreign key (uid)
200       references users (uid) on delete restrict on update restrict;
201 
202 alter table privileges add constraint FK_privilege_item foreign key (iid)
203       references items (iid) on delete restrict on update restrict;
204 
205 alter table rents add constraint FK_rent_car foreign key (carid)
206       references cars (carid) on delete restrict on update restrict;
207 
208 alter table rents add constraint FK_rent_client foreign key (cid)
209       references clients (cid) on delete restrict on update restrict;
210 
211 alter table rents add constraint FK_rent_user foreign key (uid)
212       references users (uid) on delete restrict on update restrict;
213 
214 alter table role_privilege add constraint FK_role_privilege foreign key (rid)
215       references roles (rid) on delete restrict on update restrict;
216 
217 alter table role_privilege add constraint FK_role_privilege2 foreign key (pid)
218       references privileges (pid) on delete restrict on update restrict;
219 
220 alter table systemlog add constraint FK_syslog_user foreign key (uid)
221       references users (uid) on delete restrict on update restrict;
222 
223 alter table user_role add constraint FK_user_role foreign key (uid)
224       references users (uid) on delete restrict on update restrict;
225 
226 alter table user_role add constraint FK_user_role2 foreign key (rid)
227       references roles (rid) on delete restrict on update restrict;

 

posted @ 2016-12-25 19:41  光何  阅读(783)  评论(0编辑  收藏