• 基于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;
  • 相关阅读:
    【技术贴】解决Eclipse编译java源文件之后没有生成class文件|找不到class文件
    小米1s 正式为我服役,纪念一下。
    解决servlet的out输出流html中文乱码
    小米1s充电时屏幕一直亮着不关闭的解决办法
    今天遇到的问题分析
    java操作Excel(org.apache.poi.hssf.usermodel)
    HTML滚动文字代码 marquee标签
    MIUI小米 卸载金山安全服务
    Oracle DBA 逻辑备份试题
    Oracle DBA结构试题1
  • 原文地址:https://www.cnblogs.com/guanghe/p/6220287.html
Copyright © 2020-2023  润新知