关系型数据库设计——银行业务管理系统

一 需求描述

某银行准备开发一个银行业务管理系统，通过调查，得到以下的主要需求：银行有多个支行。各个支行位于某个城市，每个支行有唯一的名字。银行要监控每个支行的资产。银行的客户通过其身份证号来标识。银行存储每个客户的姓名及其居住的街道和城市。客户可以有帐户，并且可以贷款。客户可能和某个银行员工发生联系，该员工是此客户的贷款负责人或银行帐户负责人。银行员工也通过身份证号来标识。员工分为部门经理和普通员工，每个部门经理都负责领导其所在部门的员工，并且每个员工只允许在一个部门内工作。每个支行的管理机构存储每个员工的姓名、电话号码、家庭地址及其经理的身份证号。银行还需知道每个员工开始工作的日期，由此日期可以推知员工的雇佣期。 银行提供两类帐户——储蓄帐户和支票帐户。帐户可以由2个或2个以上客户所共有，一个客户也可有两个或两个以上的帐户。每个帐户被赋以唯一的帐户号。银行记录每个帐户的余额、开户的支行以及每个帐户所有者访问该帐户的最近日期。另外，每个储蓄帐户有其利率，且每个支票帐户有其透支额。 每笔贷款由某个分支机构发放，能被一个或多个客户所共有。每笔贷款用唯一的贷款号标识。银行需要知道每笔贷款所贷金额以及逐次支付的情况（银行将贷款分几次付给客户）。虽然贷款号不能唯一标识银行所有为贷款所付的款项，但可以唯一标识为某贷款所付的款项。对每次的付款需要记录日期和金额。

二 E/R图实体、属性和联系确定

 

 

经分析可知实体及其属性如表一所示，说明如下：

1、  总共包含8个实体

2、  支付为弱实体，依赖于强实体贷款。

3、储蓄账户实体和支票账户实体继承于账户实体

实体及属性基本信息表如下：

序号	名称	基本属性
1	支行	名字、城市、资产
2	贷款	贷款号、金额
3	支付	时间、金额
4	员工	身份证号、姓名、电话、地址
5	客户	身份证号、姓名、街道、城市
6	账户	账户号、余额
7	储蓄账户	利率
8	支票账户	透支额

 

E/R图联系确定

 

 

序号	相关实体	联系	联系属性
1	支行:贷款	1:n	无
2	支行:员工	1:n	开始工作时间
3	支行:账户	1:n	无
4	贷款:支付	1:n	无
5	贷款:客户	m:n	无
6	员工:客户	m:n	身份
7	账户:客户	m:n	最近访问时间
8	账户: 储蓄账户	继承	无
9	账户: 支票账户	继承	无

三 用Visio绘制E/R草图

 

 

四 用powerdesigner绘制E/R图

 

 

 

五 将逻辑模型导出为物理模型

 

 

 

五 将物理模型生成SQL脚本

 

 

/*==============================================================*/
/* Table: account                                               */
/*==============================================================*/
create table account (
   account_id           varchar(20)          not null,
   branch_name          varchar(50)          not null,
   account_balance      money                null,
   constraint PK_ACCOUNT primary key nonclustered (account_id)
)
go

/*==============================================================*/
/* Index: open_FK                                               */
/*==============================================================*/
create index open_FK on account (
branch_name ASC
)
go

/*==============================================================*/
/* Table: branch                                                */
/*==============================================================*/
create table branch (
   branch_name          varchar(50)          not null,
   branch_city          varchar(50)          null,
   branch_assets        money                null,
   constraint PK_BRANCH primary key nonclustered (branch_name)
)
go

/*==============================================================*/
/* Table: checkAccount                                          */
/*==============================================================*/
create table checkAccount (
   account_id           varchar(20)          not null,
   branch_name          varchar(50)          null,
   account_balance      money                null,
   overdraft            money                null,
   constraint PK_CHECKACCOUNT primary key (account_id)
)
go

/*==============================================================*/
/* Table: custom                                                */
/*==============================================================*/
create table custom (
   custom_id            char(18)             not null,
   loan_id              varchar(20)          null,
   custom_name          varchar(20)          null,
   custom_street        varchar(50)          null,
   custom_city          varchar(50)          null,
   constraint PK_CUSTOM primary key nonclustered (custom_id)
)
go

/*==============================================================*/
/* Index: apply_FK                                              */
/*==============================================================*/
create index apply_FK on custom (
loan_id ASC
)
go

/*==============================================================*/
/* Table: have                                                  */
/*==============================================================*/
create table have (
   account_id           varchar(20)          not null,
   custom_id            char(18)             not null,
   recent_time          datetime             null,
   constraint PK_HAVE primary key (account_id, custom_id)
)
go

/*==============================================================*/
/* Index: have_FK                                               */
/*==============================================================*/
create index have_FK on have (
account_id ASC
)
go

/*==============================================================*/
/* Index: have2_FK                                              */
/*==============================================================*/
create index have2_FK on have (
custom_id ASC
)
go

/*==============================================================*/
/* Table: loan                                                  */
/*==============================================================*/
create table loan (
   loan_id              varchar(20)          not null,
   branch_name          varchar(50)          not null,
   loan_sum             money                null,
   constraint PK_LOAN primary key nonclustered (loan_id)
)
go

/*==============================================================*/
/* Index: grant_FK                                              */
/*==============================================================*/
create index grant_FK on loan (
branch_name ASC
)
go

/*==============================================================*/
/* Table: payment                                               */
/*==============================================================*/
create table payment (
   loan_id              varchar(20)          not null,
   pay_time             datetime             not null,
   pay_sum              money                null,
   constraint PK_PAYMENT primary key nonclustered (loan_id, pay_time)
)
go

/*==============================================================*/
/* Index: "loan-pay_FK"                                         */
/*==============================================================*/
create index "loan-pay_FK" on payment (
loan_id ASC
)
go

/*==============================================================*/
/* Table: responsible                                           */
/*==============================================================*/
create table responsible (
   staff_id             char(18)             not null,
   custom_id            char(18)             not null,
   "identity"           "identity"           null,
   constraint PK_RESPONSIBLE primary key (staff_id, custom_id)
)
go

/*==============================================================*/
/* Index: responsible_FK                                        */
/*==============================================================*/
create index responsible_FK on responsible (
staff_id ASC
)
go

/*==============================================================*/
/* Index: responsible2_FK                                       */
/*==============================================================*/
create index responsible2_FK on responsible (
custom_id ASC
)
go

/*==============================================================*/
/* Table: savingAccount                                         */
/*==============================================================*/
create table savingAccount (
   account_id           varchar(20)          not null,
   branch_name          varchar(50)          null,
   account_balance      money                null,
   rate                 decimal(8,3)         null,
   constraint PK_SAVINGACCOUNT primary key (account_id)
)
go

/*==============================================================*/
/* Table: staff                                                 */
/*==============================================================*/
create table staff (
   staff_id             char(18)             not null,
   sta_staff_id         char(18)             null,
   branch_name          varchar(50)          not null,
   staff_name           varchar(20)          null,
   staff_tel            varchar(20)          null,
   staff_addr           varchar(50)          null,
   start_time           datetime             null,
   constraint PK_STAFF primary key nonclustered (staff_id)
)
go

/*==============================================================*/
/* Index: work_FK                                               */
/*==============================================================*/
create index work_FK on staff (
branch_name ASC
)
go

/*==============================================================*/
/* Index: lead_FK                                               */
/*==============================================================*/
create index lead_FK on staff (
sta_staff_id ASC
)
go

alter table account
   add constraint FK_ACCOUNT_OPEN_BRANCH foreign key (branch_name)
      references branch (branch_name)
go

alter table checkAccount
   add constraint FK_CHECKACC_CAINHERIT_ACCOUNT foreign key (account_id)
      references account (account_id)
go

alter table custom
   add constraint FK_CUSTOM_APPLY_LOAN foreign key (loan_id)
      references loan (loan_id)
go

alter table have
   add constraint FK_HAVE_HAVE_ACCOUNT foreign key (account_id)
      references account (account_id)
go

alter table have
   add constraint FK_HAVE_HAVE2_CUSTOM foreign key (custom_id)
      references custom (custom_id)
go

alter table loan
   add constraint FK_LOAN_GRANT_BRANCH foreign key (branch_name)
      references branch (branch_name)
go

alter table payment
   add constraint "FK_PAYMENT_LOAN-PAY_LOAN" foreign key (loan_id)
      references loan (loan_id)
go

alter table responsible
   add constraint FK_RESPONSI_RESPONSIB_STAFF foreign key (staff_id)
      references staff (staff_id)
go

alter table responsible
   add constraint FK_RESPONSI_RESPONSIB_CUSTOM foreign key (custom_id)
      references custom (custom_id)
go

alter table savingAccount
   add constraint FK_SAVINGAC_SAINERITA_ACCOUNT foreign key (account_id)
      references account (account_id)
go

alter table staff
   add constraint FK_STAFF_LEAD_STAFF foreign key (sta_staff_id)
      references staff (staff_id)
go

alter table staff
   add constraint FK_STAFF_WORK_BRANCH foreign key (branch_name)
      references branch (branch_name)
go