1、数据库设计实验报告银行储蓄系统银行储蓄系统Xx1 用户需求分析,建立E-R图,模型生成,数据库实施和修改;Xx2 用户需求分析,建立E-R图,数据库运行和维护;设计过程相互探讨,相互学习,达到共同进步之目的。一、需求描述银行储蓄系统,实现储户开户登记,办理定期存款帐,办理定期取款手续,办理活期存款帐,办理活期取款手续,同时实现各银行及储蓄所间的业务代理和转换。1、信息处理需求总行:包括国家编号(唯一),总行长(唯一),总行长编号(唯一),地址,联系电话,包括多个分行分行:包括分行编号(唯一),分行长(唯一),分行长编号(唯一),地址,联系电话,各分行之间为并列关系,分行雇佣业务员,审核账目清单
2、,每个分行有一个营业厅受理各项业务营业厅:包括营业厅编号(唯一),地址,联系电话,营业窗口号,一个营业厅负责受理一个分行的业务,一个营业厅由多个储蓄所组成并有多个业务员受理各项业务储蓄所:包括储蓄所编号(唯一),储蓄所名称,储蓄所长,地址,联系电话,储蓄柜台号,储蓄金额,并有多个职员办理各项业务业务员:包括姓名(唯一),性别,编号(唯一),年龄,职务,办理储蓄类别(活期,定期),每个业务员在一个营业窗口工作职员:包括姓名(唯一),性别,编号(唯一),年龄,职务,办理(包括储蓄类别-活期/定期;登记,核对),每个职员在一个储蓄柜台工作,计算存、取额及利息储户:包括姓名(唯一),身份证号(唯一),
3、性别,储蓄类别,年龄,开户金额,输入(密码,密码确认,储蓄金额,取款额),储户可选择多个储蓄所存、取款,并可拥有多个储蓄账号账户:包括储蓄账号编码(唯一),储蓄账号(唯一),储蓄类别,开户时间,开户金额,总金额,密码 2、功能需求交互:各分行及储蓄所之间进行业务交互,包括转账,代办提交:各级银行进行由下至上的清单、业务信息的交接审核:上级银行对所属银行的业务信息和账户清单进行审查、核准计算:主要是工作人员对总账出入和利息的合计办理:职员对储户信息和账户资料的登记和核准,实现储户开户登记,办理定期存款帐,办理定期取款手续,办理活期存款帐,办理活期取款手续管理:主指上级银行业务员对下级银行、人员的
4、管控和调动数据输入:输入储户和账户的基本信息3、系统性能该系统的精度要求较高、时间比较快、应变能力快。4、数据字典(1) 数据项 数据项名称及属性编码数据类型长度储蓄所名称savings bank_nameVA2020储蓄所编号savings bank_numberN2020储蓄所长managerVA2020储蓄柜台号counter numberN2020储蓄类别typeA2020储蓄账号accountA2020储蓄账号编码account numberN88储蓄金额moneyMN2020储蓄金额2money2N2020分行编号branch b_numberLVA2020分行长branch b_
5、chiefVA2020分行长编号branch b_chief numberVA2020取款额money1MN2020唯一unique国家编号no.LVA地址addressVA3030姓名name1VA2020姓名name2VA2020姓名nameVA2020定期dingqi密码secretVA1212密码确认assuredVA1212年龄ageN33并列apposeVA1010开户时间timeDT开户金额account moneyMN2020性别sexA22总行长cheifVA2020总行长编号chief numberVA1010总金额all moneyMN2020方式mannerVA2020
6、核对check活期huoqi登记register编号number3A1010编号number2N1010编号number1N1010职务positionVA1010联系电话telephoneN1515营业厅编号numberN2020营业窗口号window numberN1010身份证号IDA1818(2)数据结构总行包括多个分行,各分行之间为并列关系,分行雇佣业务员,审核账目清单,每个分行有一个营业厅受理各项业务;一个营业厅负责受理一个分行的业务,一个营业厅由多个储蓄所组成;并有多个业务员受理各项业务,一个储蓄所有多个职员,每个职员在一个储蓄柜台工作;每个业务员在一个营业窗口工作,储户可选择多
7、个储蓄所存、取款,并可拥有多个储蓄账号二、概念结构设计ER图:PowerDesigner的概念模型图三、逻辑结构设计根据规则转换成关系模式总行(国家编号,总行长,总行长编号,地址,联系电话,分行)分行(分行编号,分行长,分行长编号,地址,联系电话,营业厅,业务员)营业厅(营业厅编号,地址,联系电话,营业窗口号,储蓄所,业务员)储蓄所(储蓄所编号,储蓄所名称,储蓄所长,地址,联系电话,储蓄柜台号,储蓄金额,职员)业务员(姓名,性别,编号,年龄,职务,营业窗口)职员(姓名,性别,编号,年龄,职务,储蓄柜台)储户(姓名,身份证号,性别,储蓄类别,年龄,开户金额,储蓄所,储蓄账号)账户(储蓄账号编码,
8、储蓄账号,储蓄类别,开户时间,开户金额,总金额,密码)PowerDesigner的物理模型图四、数据库物理实施数据库定义SQL文本如下:/*=*/* DBMS name: ORACLE Version 10g */* Created on: 2010-11-22 20:41:13 */*=*/*=*/* Table: account */*=*/create table account ( type CHAR(20), account CHAR(20), time DATE, account money NUMBER(20), all money NUMBER(20) not null, se
9、cret VARCHAR2(12), account number NUMBER(8) not null, constraint PK_ACCOUNT primary key (account number);/*=*/* Table: alternation */*=*/create table alternation ( bra_branch b_c2 VARCHAR2(20) not null, bra_branch b_c3 VARCHAR2(20) not null, bra_branch b_n2 CLOB not null, branch b_chief VARCHAR2(20)
10、 not null, branch b_chief VARCHAR2(20) not null, branch b_number CLOB not null, bra_branch b_ch VARCHAR2(20) not null, bra_branch b_c4 VARCHAR2(20) not null, bra_branch b_nu CLOB not null, bra_branch b_c5 VARCHAR2(20) not null, bra_branch b_c6 VARCHAR2(20) not null, bra_branch b_n3 CLOB not null, ma
11、nner VARCHAR2(20), constraint PK_ALTERNATION primary key (bra_branch b_c2, bra_branch b_c3, bra_branch b_n2, branch b_chief, branch b_chief, branch b_number, bra_branch b_ch, bra_branch b_c4, bra_branch b_nu, bra_branch b_c5, bra_branch b_c6, bra_branch b_n3);/*=*/* Index: alternation_FK */*=*/creat
12、e index alternation_FK on alternation ( bra_branch b_c2 ASC, bra_branch b_c3 ASC, bra_branch b_n2 ASC);/*=*/* Table: bank clerk */*=*/create table bank clerk ( name2 VARCHAR2(20) not null, sex CHAR(2), number2 NUMBER(10) not null, age NUMBER(3), position VARCHAR2(10), type CHAR(20), window number NU
13、MBER(10), constraint PK_BANK CLERK primary key (name2, number2);/*=*/* Table: bank department */*=*/create table bank department ( number NUMBER(20) not null, name2 VARCHAR2(20) not null, number2 NUMBER(10) not null, savings bank_nu NUMBER(20) not null, ban_name2 VARCHAR2(20) not null, ban_number2 N
14、UMBER(10) not null, ban_name3 VARCHAR2(20) not null, ban_number3 NUMBER(10) not null, name VARCHAR2(20) not null, number1 NUMBER(10) not null, sav_savings ban NUMBER(20) not null, ban_name4 VARCHAR2(20) not null, ban_number4 NUMBER(10) not null, sav_savings ba2 NUMBER(20) not null, cle_name VARCHAR2
15、(20) not null, cle_number1 NUMBER(10) not null, sav_savings ba3 NUMBER(20) not null, address VARCHAR2(30), telephone NUMBER(15), window number NUMBER(10), appose VARCHAR2(10), appose2 VARCHAR2(10), constraint PK_BANK DEPARTM primary key (number);/*=*/* Table: branch bank */*=*/create table branch ba
16、nk ( branch b_number CLOB not null, branch b_chief VARCHAR2(20) not null, branch b_chief VARCHAR2(20) not null, savings bank_nu NUMBER(20) not null, name VARCHAR2(20) not null, number1 NUMBER(10) not null, sav_savings ban NUMBER(20) not null, cle_name VARCHAR2(20) not null, cle_number1 NUMBER(10) no
17、t null, address VARCHAR2(30), telephone NUMBER(15), appose VARCHAR2(10), appose2 VARCHAR2(10), constraint PK_BRANCH BANK primary key (branch b_chief, branch b_chief, branch b_number);/*=*/* Index: engage_FK */*=*/create index engage_FK on branch bank ( cle_name ASC, cle_number1 ASC);/*=*/* Index: ma
18、ke up_FK */*=*/create index make up_FK on branch bank ( savings bank_nu ASC);/*=*/* Index: make up4_FK */*=*/create index make up4_FK on branch bank ( sav_savings ban ASC);/*=*/* Index: engage2_FK */*=*/create index engage2_FK on branch bank ( name ASC, number1 ASC);/*=*/* Table: chief bank */*=*/cr
19、eate table chief bank ( no. CLOB not null, cheif VARCHAR2(20) not null, chief number VARCHAR2(10) not null, branch b_chief VARCHAR2(20) not null, branch b_chief VARCHAR2(20) not null, branch b_number CLOB not null, bra_branch b_ch VARCHAR2(20) not null, bra_branch b_c2 VARCHAR2(20) not null, bra_bra
20、nch b_nu CLOB not null, address VARCHAR2(30), telephone NUMBER(15), constraint PK_CHIEF BANK primary key (no., cheif, chief number);/*=*/* Table: clerk */*=*/create table clerk ( name VARCHAR2(20) not null, sex CHAR(2), number1 NUMBER(10) not null, age NUMBER(3), position VARCHAR2(10), type CHAR(20)
21、, counter number NUMBER(20), constraint PK_CLERK primary key (name, number1);/*=*/* Table: depositor */*=*/create table depositor ( name1 VARCHAR2(20) not null, ID CHAR(18) not null, account number NUMBER(8) not null, acc_account num NUMBER(8) not null, sex CHAR(2), type CHAR(20), age NUMBER(3), acc
22、ount CHAR(20), savings bank_na VARCHAR2(20), money NUMBER(20), account money NUMBER(20), secret VARCHAR2(12), assured VARCHAR2(12), money2 NUMBER(20), money1 NUMBER(20), secret2 VARCHAR2(12), assured2 VARCHAR2(12), money3 NUMBER(20), money4 NUMBER(20), constraint PK_DEPOSITOR primary key (name1, ID);/*=*/* Table: savings bank */*=*/create table savings bank ( savings bank_nu NUMBER(20) not null, name VARCHAR2(20), number1 NUMBER(10), cle_name VARCHAR2(20), cle_number1 NUMBER(10), manager VARCHAR2(20), address VARCHAR2(30), telephone NUMBER(15), counter number N
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1