Java – 自己動手之線上書店(2)

資料庫設計,我這裡用的PowerDesigner 15(破解版),需要下載的朋友可以去電驢搜尋一下.
我這裡只是簡單設計了一下,也沒有什麼深入考慮,畢竟整個項目就比較簡單 也不是什麼正規的.
一個:
 
PD設計好了以後,Ctrl+G產生指令檔:

drop table if exists tbBookAuthor;drop table if exists tbBookCategory;drop table if exists tbBookInfo;drop table if exists tbBookPublisher;drop table if exists tbManagerInfo;drop table if exists tbOrderInfo;drop table if exists tbOrderItem;drop table if exists tbStoreInfo;drop table if exists tbUserAddress;drop table if exists tbUserInfo;drop table if exists tbrBookAuthor;/*==============================================================*//* Table: tbBookAuthor                                          *//*==============================================================*/create table tbBookAuthor(   author_id            int not null auto_increment,   author_name          varchar(64) not null,   author_info          text,   author_memo          text,   primary key (author_id));alter table tbBookAuthor comment '圖書作者資訊表';/*==============================================================*//* Table: tbBookCategory                                        *//*==============================================================*/create table tbBookCategory(   category_id          int not null auto_increment,   category_name        varchar(32) not null,   category_description text,   category_memo        text,   primary key (category_id));alter table tbBookCategory comment '圖書分類表';/*==============================================================*//* Table: tbBookInfo                                            *//*==============================================================*/create table tbBookInfo(   book_id              int not null auto_increment,   category_id          int,   publisher_id         int,   book_name            varchar(64) not null,   book_price           float,   book_description     text,   book_memo            text,   book_nowprice        float,   book_storeno         int,   primary key (book_id));alter table tbBookInfo comment '圖書資訊表';/*==============================================================*//* Table: tbBookPublisher                                       *//*==============================================================*/create table tbBookPublisher(   publisher_id         int not null auto_increment,   publisher_name       varchar(32) not null,   publisher_addr       varchar(64),   publisher_phone      varchar(18),   publisher_email      varchar(32),   publisher_url        varchar(64),   publisher_description text,   publisher_memo       text,   primary key (publisher_id));alter table tbBookPublisher comment '圖書出版商資訊表';/*==============================================================*//* Table: tbManagerInfo                                         *//*==============================================================*/create table tbManagerInfo(   manager_id           int not null auto_increment,   manager_name         varchar(32) not null,   manager_password     varchar(32) not null,   primary key (manager_id));alter table tbManagerInfo comment '書店管理員資訊表';/*==============================================================*//* Table: tbOrderInfo                                           *//*==============================================================*/create table tbOrderInfo(   order_id             int not null auto_increment,   addr_id              int,   order_tel            varchar(18) not null,   order_time           datetime,   order_memo           text,   primary key (order_id));alter table tbOrderInfo comment '訂單資訊表';/*==============================================================*//* Table: tbOrderItem                                           *//*==============================================================*/create table tbOrderItem(   item_id              int not null auto_increment,   order_id             int,   book_id              int,   item_number          int,   item_memo            text,   primary key (item_id));alter table tbOrderItem comment '訂單中每一項資訊表';/*==============================================================*//* Table: tbStoreInfo                                           *//*==============================================================*/create table tbStoreInfo(   store_name           varchar(32) not null,   store_addr           varchar(64),   store_phone          varchar(18),   store_email          varchar(64),   store_descriptrion   text,   store_memo           text,   primary key (store_name));alter table tbStoreInfo comment '書店基礎資訊表,包含書店的名字,地址,電話等資訊';/*==============================================================*//* Table: tbUserAddress                                         *//*==============================================================*/create table tbUserAddress(   addr_id              int not null auto_increment,   user_id              int,   addr_addr            varchar(64) not null,   addr_memo            text,   primary key (addr_id));alter table tbUserAddress comment '使用者的收貨地址';/*==============================================================*//* Table: tbUserInfo                                            *//*==============================================================*/create table tbUserInfo(   user_id              int not null auto_increment,   user_name            varchar(32) not null,   user_password        varchar(32) not null,   user_email           varchar(64),   user_phone           varchar(18),   user_description     text,   user_memo            text,   primary key (user_id));alter table tbUserInfo comment '使用者表';/*==============================================================*//* Table: tbrBookAuthor                                         *//*==============================================================*/create table tbrBookAuthor(   rba_ID               int not null auto_increment,   book_id              int,   author_id            int,   primary key (rba_ID));alter table tbrBookAuthor comment '圖書和作者關係表,多對多';alter table tbBookInfo add constraint FK_BookCategory foreign key (category_id)      references tbBookCategory (category_id) on delete set null on update cascade;alter table tbBookInfo add constraint FK_BookPublisher foreign key (publisher_id)      references tbBookPublisher (publisher_id) on delete set null on update cascade;alter table tbOrderInfo add constraint FK_OrderAddress foreign key (addr_id)      references tbUserAddress (addr_id) on update cascade;alter table tbOrderItem add constraint FK_BookOrder foreign key (book_id)      references tbBookInfo (book_id) on update cascade;alter table tbOrderItem add constraint FK_OrderItem foreign key (order_id)      references tbOrderInfo (order_id) on update cascade;alter table tbUserAddress add constraint FK_UserAddress foreign key (user_id)      references tbUserInfo (user_id) on delete cascade on update cascade;alter table tbrBookAuthor add constraint FK_rAuthor foreign key (author_id)      references tbBookAuthor (author_id) on delete cascade on update cascade;alter table tbrBookAuthor add constraint FK_rBook foreign key (book_id)      references tbBookInfo (book_id) on delete restrict on update cascade;

整個檔案的