2023年7月7日发(作者：)

关系型数据库设计——银⾏业务管理系统⼀ 需求描述某银⾏准备开发⼀个银⾏业务管理系统，通过调查，得到以下的主要需求：银⾏有多个⽀⾏。各个⽀⾏位于某个城市，每个⽀⾏有唯⼀的名字。银⾏要监控每个⽀⾏的资产。银⾏的客户通过其⾝份证号来标识。银⾏存储每个客户的姓名及其居住的街道和城市。客户可以有帐户，并且可以贷款。客户可能和某个银⾏员⼯发⽣联系，该员⼯是此客户的贷款负责⼈或银⾏帐户负责⼈。银⾏员⼯也通过⾝份证号来标识。员⼯分为部门经理和普通员⼯，每个部门经理都负责领导其所在部门的员⼯，并且每个员⼯只允许在⼀个部门内⼯作。每个⽀⾏的管理机构存储每个员⼯的姓名、电话号码、家庭地址及其经理的⾝份证号。银⾏还需知道每个员⼯开始⼯作的⽇期，由此⽇期可以推知员⼯的雇佣期。 银⾏提供两类帐户——储蓄帐户和⽀票帐户。帐户可以由2个或2个以上客户所共有，⼀个客户也可有两个或两个以上的帐户。每个帐户被赋以唯⼀的帐户号。银⾏记录每个帐户的余额、开户的⽀⾏以及每个帐户所有者访问该帐户的最近⽇期。另外，每个储蓄帐户有其利率，且每个⽀票帐户有其透⽀额。 每笔贷款由某个分⽀机构发放，能被⼀个或多个客户所共有。每笔贷款⽤唯⼀的贷款号标识。银⾏需要知道每笔贷款所贷⾦额以及逐次⽀付的情况（银⾏将贷款分⼏次付给客户）。虽然贷款号不能唯⼀标识银⾏所有为贷款所付的款项，但可以唯⼀标识为某贷款所付的款项。对每次的付款需要记录⽇期和⾦额。⼆ E/R图实体、属性和联系确定

经分析可知实体及其属性如表⼀所⽰，说明如下：1、 总共包含8个实体2、 ⽀付为弱实体，依赖于强实体贷款。3、储蓄账户实体和⽀票账户实体继承于账户实体实体及属性基本信息表如下：序号12345678名称⽀⾏贷款⽀付员⼯客户账户储蓄账户⽀票账户基本属性名字、城市、资产贷款号、⾦额时间、⾦额⾝份证号、姓名、电话、地址⾝份证号、姓名、街道、城市账户号、余额利率透⽀额 E/R图联系确定

序号123456789相关实体⽀⾏:贷款⽀⾏:员⼯⽀⾏:账户贷款:⽀付贷款:客户员⼯:客户账户:客户账户: 储蓄账户账户: ⽀票账户联系1:n1:n1:n1:nm:nm:nm:n继承继承联系属性⽆开始⼯作时间⽆⽆⽆⾝份最近访问时间⽆⽆三 ⽤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, 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, 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)goalter table account add constraint FK_ACCOUNT_OPEN_BRANCH foreign key (branch_name) references branch (branch_name)goalter table checkAccount add constraint FK_CHECKACC_CAINHERIT_ACCOUNT foreign key (account_id) references account (account_id)goalter table custom add constraint FK_CUSTOM_APPLY_LOAN foreign key (loan_id) references loan (loan_id)goalter table have add constraint FK_HAVE_HAVE_ACCOUNT foreign key (account_id) references account (account_id)goalter table have add constraint FK_HAVE_HAVE2_CUSTOM foreign key (custom_id) references custom (custom_id)goalter table loan add constraint FK_LOAN_GRANT_BRANCH foreign key (branch_name) add constraint FK_LOAN_GRANT_BRANCH foreign key (branch_name) references branch (branch_name)goalter table payment add constraint "FK_PAYMENT_LOAN-PAY_LOAN" foreign key (loan_id) references loan (loan_id)goalter table responsible add constraint FK_RESPONSI_RESPONSIB_STAFF foreign key (staff_id) references staff (staff_id)goalter table responsible add constraint FK_RESPONSI_RESPONSIB_CUSTOM foreign key (custom_id) references custom (custom_id)goalter table savingAccount add constraint FK_SAVINGAC_SAINERITA_ACCOUNT foreign key (account_id) references account (account_id)goalter table staff add constraint FK_STAFF_LEAD_STAFF foreign key (sta_staff_id) references staff (staff_id)goalter table staff add constraint FK_STAFF_WORK_BRANCH foreign key (branch_name) references branch (branch_name)go