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

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

1 概念模型设计

1.1 实体设计

支行 (Branch)：支行名，城市，资产。

客户 (Customer)：身份证号，客户名，地址

员工 (Employee)：身份证号，姓名，电话，地址，入职日期。

帐户 (Account)：帐户ID，余额。

贷款 (Loan)：贷款ID，贷款金额。

部门(Department)：部门ID，部门名，部门类型，经理身份证号。

1.2 联系设计

序号 相关实体 联系 联系属性
1 支行：贷款 1:n
2 支行：员工 1:n
3 支行：账户 1:n
5 贷款：客户 m:n 发放金额，发放日期，发放号
6 员工：客户 m:n 身份
7 账户：客户 m:n 最近访问时间
8 账户：储蓄账户 继承
9 账户：支票账户 继承
10 部门：员工 1:n

1.3 ER图

2 概念模型到逻辑模型的转换

2.1 实体转换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
/*==============================================================*/
/* Table: Account                                               */
/*==============================================================*/
create table Account (
   Acount_ID            varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Balance              decimal(10,2)        null,
   constraint PK_ACCOUNT primary key nonclustered (Acount_ID)
)
go

/*==============================================================*/
/* Table: Branch                                                */
/*==============================================================*/
create table Branch (
   Branch_Name          varchar(255)         not null,
   City                 varchar(255)         null,
   Assets               decimal(20,2)        null,
   constraint PK_BRANCH primary key nonclustered (Branch_Name)
)
go

/*==============================================================*/
/* Table: Checking_Account                                      */
/*==============================================================*/
create table Checking_Account (
   Acount_ID            varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Balance              decimal(10,2)        null,
   Overdraft            decimal(10,2)        null,
   constraint PK_CHECKING_ACCOUNT primary key (Acount_ID)
)
go

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer (
   Customer_ID          varchar(50)          not null,
   Customer_Name        varchar(255)         null,
   Customer_Tel         int                  null,
   Customer_Addr        varchar(255)         null,
   constraint PK_CUSTOMER primary key nonclustered (Customer_ID)
)
go

/*==============================================================*/
/* Table: Department                                            */
/*==============================================================*/
create table Department (
   Department_ID        varchar(50)          not null,
   Department_Name      varchar(255)         null,
   Department_Type      varchar(255)         null,
   Manager_ID           varchar(50)          null,
   constraint PK_DEPARTMENT primary key nonclustered (Department_ID)
)
go

/*==============================================================*/
/* Table: Employee                                              */
/*==============================================================*/
create table Employee (
   Employee_ID          varchar(50)          not null,
   Emp_Employee_ID      varchar(50)          null,
   Department_ID        varchar(50)          null,
   Branch_Name          varchar(255)         null,
   Employee_Name        varchar(255)         null,
   Employee_Tel         int                  null,
   Employee_Addr        varchar(255)         null,
   HireDate             datetime             null,
   constraint PK_EMPLOYEE primary key nonclustered (Employee_ID)
)
go

/*==============================================================*/
/* Table: Loan                                                  */
/*==============================================================*/
create table Loan (
   Loan_ID              varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Loan_Amount          decimal(10,2)        null,
   constraint PK_LOAN primary key nonclustered (Loan_ID)
)
go

/*==============================================================*/
/* Table: Saving_Account                                        */
/*==============================================================*/
create table Saving_Account (
   Acount_ID            varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Balance              decimal(10,2)        null,
   Interest_Rate        decimal(10,2)        null,
   constraint PK_SAVING_ACCOUNT primary key (Acount_ID)
)
go

2.2 联系转换

  • 员工从属于部门，受雇于分行，新增部门ID，分行名
  • 贷款和客户，新增关系模式支付（支付金额，支付日期，支付号，客户号，贷款号）
  • 账户和客户，新增关系模式拥有（客户号，账户号，最近访问日期）
  • 员工和客户，新增关系模式联系（客户号，员工号，联系号）
  • 贷款由分行发放，新增分行名
  • 账户由银行创造，新增分行名

2.3 最终关系模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
/*==============================================================*/
/* Table: Account                                               */
/*==============================================================*/
create table Account (
   Acount_ID            varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Balance              decimal(10,2)        null,
   constraint PK_ACCOUNT primary key nonclustered (Acount_ID)
)
go

/*==============================================================*/
/* Index: Open_FK                                               */
/*==============================================================*/
create index Open_FK on Account (
Branch_Name ASC
)
go

/*==============================================================*/
/* Table: Branch                                                */
/*==============================================================*/
create table Branch (
   Branch_Name          varchar(255)         not null,
   City                 varchar(255)         null,
   Assets               decimal(20,2)        null,
   constraint PK_BRANCH primary key nonclustered (Branch_Name)
)
go

/*==============================================================*/
/* Table: Checking_Account                                      */
/*==============================================================*/
create table Checking_Account (
   Acount_ID            varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Balance              decimal(10,2)        null,
   Overdraft            decimal(10,2)        null,
   constraint PK_CHECKING_ACCOUNT primary key (Acount_ID)
)
go

/*==============================================================*/
/* Table: Contact                                               */
/*==============================================================*/
create table Contact (
   Customer_ID          varchar(50)          not null,
   Employee_ID          varchar(50)          not null,
   Contact_ID           varchar(50)          null,
   constraint PK_CONTACT primary key (Customer_ID, Employee_ID)
)
go

/*==============================================================*/
/* Index: Contact_FK                                            */
/*==============================================================*/
create index Contact_FK on Contact (
Customer_ID ASC
)
go

/*==============================================================*/
/* Index: Contact2_FK                                           */
/*==============================================================*/
create index Contact2_FK on Contact (
Employee_ID ASC
)
go

/*==============================================================*/
/* Table: Customer                                              */
/*==============================================================*/
create table Customer (
   Customer_ID          varchar(50)          not null,
   Customer_Name        varchar(255)         null,
   Customer_Tel         int                  null,
   Customer_Addr        varchar(255)         null,
   constraint PK_CUSTOMER primary key nonclustered (Customer_ID)
)
go

/*==============================================================*/
/* Table: Department                                            */
/*==============================================================*/
create table Department (
   Department_ID        varchar(50)          not null,
   Department_Name      varchar(255)         null,
   Department_Type      varchar(255)         null,
   Manager_ID           varchar(50)          null,
   constraint PK_DEPARTMENT primary key nonclustered (Department_ID)
)
go

/*==============================================================*/
/* Table: Employee                                              */
/*==============================================================*/
create table Employee (
   Employee_ID          varchar(50)          not null,
   Emp_Employee_ID      varchar(50)          null,
   Department_ID        varchar(50)          null,
   Branch_Name          varchar(255)         null,
   Employee_Name        varchar(255)         null,
   Employee_Tel         int                  null,
   Employee_Addr        varchar(255)         null,
   HireDate             datetime             null,
   constraint PK_EMPLOYEE primary key nonclustered (Employee_ID)
)
go

/*==============================================================*/
/* Index: Belongs_To_FK                                         */
/*==============================================================*/
create index Belongs_To_FK on Employee (
Department_ID ASC
)
go

/*==============================================================*/
/* Index: Manage_FK                                             */
/*==============================================================*/
create index Manage_FK on Employee (
Emp_Employee_ID ASC
)
go

/*==============================================================*/
/* Index: Employ_FK                                             */
/*==============================================================*/
create index Employ_FK on Employee (
Branch_Name ASC
)
go

/*==============================================================*/
/* Table: Loan                                                  */
/*==============================================================*/
create table Loan (
   Loan_ID              varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Loan_Amount          decimal(10,2)        null,
   constraint PK_LOAN primary key nonclustered (Loan_ID)
)
go

/*==============================================================*/
/* Index: Lend_FK                                               */
/*==============================================================*/
create index Lend_FK on Loan (
Branch_Name ASC
)
go

/*==============================================================*/
/* Table: Own                                                   */
/*==============================================================*/
create table Own (
   Acount_ID            varchar(50)          not null,
   Customer_ID          varchar(50)          not null,
   Visite_Time          datetime             null,
   constraint PK_OWN primary key (Acount_ID, Customer_ID)
)
go

/*==============================================================*/
/* Index: Own_FK                                                */
/*==============================================================*/
create index Own_FK on Own (
Acount_ID ASC
)
go

/*==============================================================*/
/* Index: Own2_FK                                               */
/*==============================================================*/
create index Own2_FK on Own (
Customer_ID ASC
)
go

/*==============================================================*/
/* Table: Pay                                                   */
/*==============================================================*/
create table Pay (
   Customer_ID          varchar(50)          not null,
   Loan_ID              varchar(50)          not null,
   Pay_ID               varchar(50)          null,
   Pay_Amount           decimal(10,2)        null,
   Pay_Date             datetime             null,
   constraint PK_PAY primary key (Customer_ID, Loan_ID)
)
go

/*==============================================================*/
/* Index: Pay_FK                                                */
/*==============================================================*/
create index Pay_FK on Pay (
Customer_ID ASC
)
go

/*==============================================================*/
/* Index: Pay2_FK                                               */
/*==============================================================*/
create index Pay2_FK on Pay (
Loan_ID ASC
)
go

/*==============================================================*/
/* Table: Saving_Account                                        */
/*==============================================================*/
create table Saving_Account (
   Acount_ID            varchar(50)          not null,
   Branch_Name          varchar(255)         null,
   Balance              decimal(10,2)        null,
   Interest_Rate        decimal(10,2)        null,
   constraint PK_SAVING_ACCOUNT primary key (Acount_ID)
)
go

alter table Account
   add constraint FK_ACCOUNT_OPEN_BRANCH foreign key (Branch_Name)
      references Branch (Branch_Name)
go

alter table Checking_Account
   add constraint FK_CHECKING_INHERITAN_ACCOUNT foreign key (Acount_ID)
      references Account (Acount_ID)
go

alter table Contact
   add constraint FK_CONTACT_CONTACT_CUSTOMER foreign key (Customer_ID)
      references Customer (Customer_ID)
go

alter table Contact
   add constraint FK_CONTACT_CONTACT2_EMPLOYEE foreign key (Employee_ID)
      references Employee (Employee_ID)
go

alter table Employee
   add constraint FK_EMPLOYEE_BELONGS_T_DEPARTME foreign key (Department_ID)
      references Department (Department_ID)
go

alter table Employee
   add constraint FK_EMPLOYEE_EMPLOY_BRANCH foreign key (Branch_Name)
      references Branch (Branch_Name)
go

alter table Employee
   add constraint FK_EMPLOYEE_MANAGE_EMPLOYEE foreign key (Emp_Employee_ID)
      references Employee (Employee_ID)
go

alter table Loan
   add constraint FK_LOAN_LEND_BRANCH foreign key (Branch_Name)
      references Branch (Branch_Name)
go

alter table Own
   add constraint FK_OWN_OWN_ACCOUNT foreign key (Acount_ID)
      references Account (Acount_ID)
go

alter table Own
   add constraint FK_OWN_OWN2_CUSTOMER foreign key (Customer_ID)
      references Customer (Customer_ID)
go

alter table Pay
   add constraint FK_PAY_PAY_CUSTOMER foreign key (Customer_ID)
      references Customer (Customer_ID)
go

alter table Pay
   add constraint FK_PAY_PAY2_LOAN foreign key (Loan_ID)
      references Loan (Loan_ID)
go

alter table Saving_Account
   add constraint FK_SAVING_A_INHERITAN_ACCOUNT foreign key (Acount_ID)
      references Account (Acount_ID)
go

3 MySQL数据库结构实现

3.1 Power Designer的PDM设计

3.2 数据库表定义

表一：部门表(Department)

列名 中文含义 类型 是否允许为空 主键 外键
Department_ID 部门号 varchar(50) Y
Department_Name 部门名 varchar(255)
Department_Type 部门类型 varchar(255) Y
Manager_ID 经理号 varchar(50) Y

表二：员工表(Employee)

列名 中文含义 类型 是否允许为空 主键 外键
Employee_ID 员工号 varchar(50) Y
Employee_Name 员工名 varchar(255)
Employee_Tel 员工电话 int Y
Employee_Addr 员工地址 varchar(255) Y
Hiredate 入职日期 date Y
Branch_Name 支行名 varchar(255) Y
Department_ID 部门名 varchar(50) Y

表三：客户表(Customer)

列名 中文含义 类型 是否允许为空 主键 外键
Customer_ID 客户号 varchar(50) Y
Customer_Name 客户名 varchar(255)
Customer_Tel 客户电话 int Y
Customer_Addr 客户地址 varchar(255) Y

表四：支行表(Bank)

列名 中文含义 类型 是否允许为空 主键 外键
Branch_Name 支行名 varchar(255) Y
City 支行所在城市 varchar(50)
Assets 支行资产 decimal(20,2)

表五：账户表(Account)

列名 中文含义 类型 是否允许为空 主键 外键
Account_ID 账户名 varchar(50) Y
Branch_Name 支行名 varchar(255) Y
Balance 账户余额 decimal(10,2) Y

表六：支票账户表(Checking_Account)

列名 中文含义 类型 是否允许为空 主键 外键
Account_ID 账户名 varchar(50) Y Y
Overdraft 透支额度 decimal(10,2) Y

表七：储蓄账户表(Saving_Account)

列名 中文含义 类型 是否允许为空 主键 外键
Account_ID 账户名 varchar(50) Y Y
Interest_Rate 利率 decimal(10,2) Y

表八：贷款表(Loan)

列名 中文含义 类型 是否允许为空 主键 外键
Loan_ID 贷款号 varchar(50) Y
Branch_Name 支行名 varchar(255) Y
Loan_Amount 贷款总额 decimal(10,2)

表九：联系表(Contact)

列名 中文含义 类型 是否允许为空 主键 外键
Customer_ID 客户号 varchar(50) Y Y
Employee_ID 员工号 varchar(50) Y Y
Contact_ID 联系号 varchar(50)

表十：账户从属表(Own)

列名 中文含义 类型 是否允许为空 主键 外键
Customer_ID 客户号 varchar(50) Y Y
Visite_Time 最近访问日期 date&time Y
Account_ID 账户号 varchar(50) Y Y

表十一：贷款支付表(Pay)

列名 中文含义 类型 是否允许为空 主键 外键
Customer_ID 客户号 varchar(50) Y Y
Loan_ID 贷款号 varchar(50) Y Y
Pay_ID 支付号 varchar(50) Y
Pay_Amount 支付金额 decimal(10,2) Y
Pay_Date 支付时间 date&time Y