金融场景化实验
文章目录
金融场景化实验一、金融数据模型1.E-R图2.关系模式3.物理模型Client（客户）表bank_card（银行卡）表finances_product（理财产品）信息表insurance（保险）表fund（基金）表property（资本）表4.创建数据表创建金融数据库finance客户信息表的创建银行卡信息表的创建理财产品信息表的创建保险信息表的创建基金信息表的创建资产信息表的创建5.插入表数据client表进行数据初始化bank_card表进行数据初始化finances_product表进行数据初始化insurance表进行数据初始化fund表进行数据初始化property表进行数据初始化6.添加约束7.新用户的创建和授权二、使用JDBC连接数据库一、金融数据模型
假设A市C银行为了方便对银行数据的管理和操作，引入了华为openGauss数据库。针对C银行的业务，本实验主要将对象分为客户、银行卡、理财产品、保险和基金。因此，针对这些数据库对象，本实验假设C银行的金融数据库存在着以下关系：客户可以办理银行卡，同时客户可以购买不同的银行产品，如理财产品，基金和保险。那么，根据C银行的对象关系，本实验给出了相应的关系模式和ER图，并对其进行较为复杂的数据库操作。
1.E-R图
2.关系模式
对于C银行中的5个对象，分别建立属于每个对象的属性集合，具体属性描述如下：
客户（客户编号、客户名称、客户邮箱，客户身份证，客户手机号，客户登录密码）银行卡（银行卡号，银行卡类型）理财产品（产品名称，产品编号，产品描述，购买金额，理财年限）保险（保险名称，保险编号，保险金额，适用人群，保险年限，保障项目）基金（基金名称，基金编号，基金类型，基金金额，风险等级，基金管理者）
对象之间的关系：
一个客户可以办理多张银行卡一个客户可以购买多个理财产品，同一类理财产品可由多个客户购买一个客户可以购买多个基金，同一类基金可由多个客户购买一个客户可以购买多个保险，同一类保险可由多个客户购买
根据关系分析，设计关系模式如下：
说明：
由于一个客户可以办理多张银行卡，所以银行卡表引用客户表的客户编号作为外键。由于一个客户可以购买多个理财产品，同一类理财产品可由多个客户购买。所以生成关系表——资产表。资产表引用客户表的商品编号作为外键，引用理财产品表的产品编号作为外键，并且添加商品状态、商品数量、商品收益和购买时间等属性。客户和保险、客户和基金的关系同理，所以资产表同样作为生成的关系表，修改资产表的理财产品编号为商品编号，商品编号引用自理财产品表、保险和基金表的编号。
3.物理模型
对象及属性对应的编号为：
Client(c_id，c_name，c_mail，c_id_card，c_phone，c_password)bank_card(b_number，b_type，b_c_id)finances_product(p_name，p_id，p_description，p_amount，p_year)insurance(i_name，i_id，i_amount，i_person，i_year，i_project)fund(f_name，f_id，f_type，f_amount，risk_level，f_manager)property(pro_id,pro_c_id，pro_pif_id，pro_type，pro_status，pro_quantity，pro_income，pro_purchase_time)
说明：
资产表（property）中由于商品编号(pro_pif_id)字段引用自理财产品表、保险和基金表的id字段，为了防止三商品的id字段相互冲突，添加商品类型(pro_type)字段区分三种商品。并且资产表添加资产编号(pro_id)字段作为主键。
Client（客户）表
bank_card（银行卡）表
finances_product（理财产品）信息表
insurance（保险）表
fund（基金）表
property（资本）表
4.创建数据表
创建金融数据库finance
切换到omm用户，以操作系统用户omm登录数据库主节点。
su - omm
启动数据库服务
gs_om -t start
使用gsql工具登陆数据库
gsql -d postgres -p 26000 -r
创建数据库finance
CREATE DATABASE finance ENCODING 'UTF8' template = template0;
连接finance数据库
\connect finance
创建名为finance的schema，并设置finance为当前的schema
CREATE SCHEMA finance;
将默认搜索路径设为finance
SET search_path TO finance;
客户信息表的创建
在SQL编辑框中输入如下语句，创建客户信息表client。
删除表client
DROP TABLE IF EXISTS finance.client;
创建表client
CREATE TABLE finance.client(c_id INT PRIMARY KEY,c_name VARCHAR(100) NOT NULL,c_mail CHAR(30) UNIQUE,c_id_card CHAR(20) UNIQUE NOT NULL,c_phone CHAR(20) UNIQUE NOT NULL,c_password CHAR(20) NOT NULL);
银行卡信息表的创建
在SQL编辑框中输入如下语句，创建银行卡信息表bank_card。
删除表bank_card
DROP TABLE IF EXISTS finance.bank_card;
创建表bank_card
CREATE TABLE finance.bank_card(b_number CHAR(30) PRIMARY KEY,b_type CHAR(20),b_c_id INT NOT NULL);
理财产品信息表的创建
创建理财产品信息表finances_product。
删除表finances_product
DROP TABLE IF EXISTS finance.finances_product;
CREATE TABLE finance.finances_product(p_name VARCHAR(100) NOT NULL,p_id INT PRIMARY KEY,p_description VARCHAR(4000),p_amount INT,p_year INT);
保险信息表的创建
在SQL编辑框中输入如下语句，创建保险信息表insurance。
删除表insurance
DROP TABLE IF EXISTS finance.insurance;
创建表insurance
CREATE TABLE finance.insurance(i_name VARCHAR(100) NOT NULL,i_id INT PRIMARY KEY,i_amount INT,i_person CHAR(20),i_year INT,i_project VARCHAR(200));
基金信息表的创建
在SQL编辑框中输入如下语句，创建保险信息表fund。
删除表fund
DROP TABLE IF EXISTS finance.fund;
创建表fund
CREATE TABLE finance.fund(f_name VARCHAR(100) NOT NULL,f_id INT PRIMARY KEY,f_type CHAR(20),f_amount INT,risk_level CHAR(20) NOT NULL,f_manager INT NOT NULL);
资产信息表的创建
在SQL编辑框中输入如下语句，创建资产信息表property。
删除表property。
DROP TABLE IF EXISTS finance.property;
创建表property
CREATE TABLE finance.property(pro_id INT PRIMARY KEY,pro_c_id INT NOT NULL,pro_pif_id INT NOT NULL,pro_type INT NOT NULL,pro_status CHAR(20),pro_quantity INT,pro_income INT,pro_purchase_time DATE);
5.插入表数据
为了实现对表数据的相关操作，本实验需要以执行SQL语句的方式对金融数据库的相关表插入部分数据。
client表进行数据初始化
执行insert操作
INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (1,'张一','zhangyi@','340211199301010001','18815650001','gaussdb_001');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (2,'张二','zhanger@','340211199301010002','18815650002','gaussdb_002');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (3,'张三','zhangsan@','340211199301010003','18815650003','gaussdb_003');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (4,'张四','zhangsi@','340211199301010004','18815650004','gaussdb_004');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (5,'张五','zhangwu@','340211199301010005','18815650005','gaussdb_005');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (6,'张六','zhangliu@','340211199301010006','18815650006','gaussdb_006');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (7,'张七','zhangqi@','340211199301010007','18815650007','gaussdb_007');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (8,'张八','zhangba@','340211199301010008','18815650008','gaussdb_008');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (9,'张九','zhangjiu@','340211199301010009','18815650009','gaussdb_009');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (10,'李一','liyi@','340211199301010010','18815650010','gaussdb_010');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (11,'李二','lier@','340211199301010011','18815650011','gaussdb_011');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (12,'李三','lisan@','340211199301010012','18815650012','gaussdb_012');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (13,'李四','lisi@','340211199301010013','18815650013','gaussdb_013');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (14,'李五','liwu@','340211199301010014','18815650014','gaussdb_014');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (15,'李六','liliu@','340211199301010015','18815650015','gaussdb_015');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (16,'李七','liqi@','340211199301010016','18815650016','gaussdb_016');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (17,'李八','liba@','340211199301010017','18815650017','gaussdb_017');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (18,'李九','lijiu@','340211199301010018','18815650018','gaussdb_018');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (19,'王一','wangyi@','340211199301010019','18815650019','gaussdb_019');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (20,'王二','wanger@','340211199301010020','18815650020','gaussdb_020');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (21,'王三','wangsan@','340211199301010021','18815650021','gaussdb_021');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (22,'王四','wangsi@','340211199301010022','18815650022','gaussdb_022');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (23,'王五','wangwu@','340211199301010023','18815650023','gaussdb_023');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (24,'王六','wangliu@','340211199301010024','18815650024','gaussdb_024');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (25,'王七','wangqi@','340211199301010025','18815650025','gaussdb_025');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (26,'王八','wangba@','340211199301010026','18815650026','gaussdb_026');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (27,'王九','wangjiu@','340211199301010027','18815650027','gaussdb_027');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (28,'钱一','qianyi@','340211199301010028','18815650028','gaussdb_028');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (29,'钱二','qianer@','340211199301010029','18815650029','gaussdb_029');INSERT INTO finance.client(c_id,c_name,c_mail,c_id_card,c_phone,c_password) VALUES (30,'钱三','qiansan@','340211199301010030','18815650030','gaussdb_030');
查询插入结果
select count(*) from finance.client;
select * from finance.client;
bank_card表进行数据初始化
执行insert操作
INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000001','信用卡',1);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000002','信用卡',3);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000003','信用卡',5);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000004','信用卡',7);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000005','信用卡',9);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000006','信用卡',10);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000007','信用卡',12);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000008','信用卡',14);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000009','信用卡',16);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000010','信用卡',18);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000011','储蓄卡',19);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000012','储蓄卡',21);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000013','储蓄卡',7);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000014','储蓄卡',23);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000015','储蓄卡',24);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000016','储蓄卡',3);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000017','储蓄卡',26);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000018','储蓄卡',27);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000019','储蓄卡',12);INSERT INTO finance.bank_card(b_number,b_type,b_c_id) VALUES ('62230000020','储蓄卡',29);
查询插入结果
select count(*) from finance.bank_card;
finances_product表进行数据初始化
执行insert操作
INSERT INTO finance.finances_product(p_name,p_id,p_description,p_amount,p_year) VALUES ('债券',1,'以国债、金融债、央行票据、企业债为主要投资方向的银行理财产品。',50000,6);INSERT INTO finance.finances_product(p_name,p_id,p_description,p_amount,p_year) VALUES ('信贷资产',2,'一般指银行作为委托人将通过发行理财产品募集资金委托给信托公司，信托公司作为受托人成立信托计划，将信托资产购买理财产品发售银行或第三方信贷资产。',50000,6);INSERT INTO finance.finances_product(p_name,p_id,p_description,p_amount,p_year) VALUES ('股票',3,'与股票挂钩的理财产品。目前市场上主要以港股挂钩居多',50000,6);INSERT INTO finance.finances_product(p_name,p_id,p_description,p_amount,p_year) VALUES ('大宗商品',4,'与大宗商品期货挂钩的理财产品。目前市场上主要以挂钩黄金、石油、农产品的理财产品居多。',50000,6);
查询插入结果。
select count(*) from finance.finances_product;
select * from finance.finances_product;
insurance表进行数据初始化
执行insert操作
INSERT INTO finance.insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES ('健康保险',1,2000,'老人',30,'平安保险');INSERT INTO finance.insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES ('人寿保险',2,3000,'老人',30,'平安保险');INSERT INTO finance.insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES ('意外保险',3,5000,'所有人',30,'平安保险');INSERT INTO finance.insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES ('医疗保险',4,2000,'所有人',30,'平安保险');INSERT INTO finance.insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES ('财产损失保险',5,1500,'中年人',30,'平安保险');
查询插入结果
select count(*) from finance.insurance;
select * from finance.insurance;
fund表进行数据初始化
执行insert操作
INSERT INTO finance.fund(f_name,f_id,f_type,f_amount,risk_level,f_manager) VALUES ('股票',1,'股票型',10000,'高',1);INSERT INTO finance.fund(f_name,f_id,f_type,f_amount,risk_level,f_manager) VALUES ('投资',2,'债券型',10000,'中',2);INSERT INTO finance.fund(f_name,f_id,f_type,f_amount,risk_level,f_manager) VALUES ('国债',3,'货币型',10000,'低',3);INSERT INTO finance.fund(f_name,f_id,f_type,f_amount,risk_level,f_manager) VALUES ('沪深300指数',4,'指数型',10000,'中',4);
查询插入结果
select count(*) from finance.fund;
select * from finance.fund;
property表进行数据初始化
执行insert操作
INSERT INTO finance.property(pro_id,pro_c_id,pro_pif_id,pro_type,pro_status,pro_quantity,pro_income,pro_purchase_time) VALUES (1,5,1,1,'可用',4,8000,'-07-01');INSERT INTO finance.property(pro_id,pro_c_id,pro_pif_id,pro_type,pro_status,pro_quantity,pro_income,pro_purchase_time) VALUES (2,10,2,2,'可用',4,8000,'-07-01');INSERT INTO finance.property(pro_id,pro_c_id,pro_pif_id,pro_type,pro_status,pro_quantity,pro_income,pro_purchase_time) VALUES (3,15,3,3,'可用',4,8000,'-07-01');INSERT INTO finance.property(pro_id,pro_c_id,pro_pif_id,pro_type,pro_status,pro_quantity,pro_income,pro_purchase_time) VALUES (4,20,4,1,'冻结',4,8000,'-07-01');
查询插入结果。
select count(*) from finance.property;
select * from finance.property;
6.添加约束
步骤1:对表添加外键约束
在银行信息表和资产信息表中，都存在每个银行卡必须有一个持卡者、每份资产必须都有一个资产拥有者这样的对应关系。因此针对这种对应关系，创建外键约束。
给表bank_card添加外键约束
ALTER TABLE finance.bank_card ADD CONSTRAINT fk_c_id FOREIGN KEY (b_c_id) REFERENCES finance.client(c_id) ON DELETE CASCADE;
ALTER TABLE finance.property ADD CONSTRAINT fk_pro_c_id FOREIGN KEY (pro_c_id) REFERENCES finance.client(c_id) ON DELETE CASCADE;
备注：
银行卡信息表中的b_c_id与客户信息表中的c_id一致，且每个银行卡都必须有一个持卡者。在进行表删除时，需要先删除bank_card表，再删除client表，因为两个表存在约束。资产信息表中的pro_c_id与客户信息表中的c_id一致，且每一份资产都必须有一个资产拥有者。
在进行表删除时，需要先删除property表，再删除client表，因为两个表存在约束。
步骤2:增加大于0的约束条件
在理财产品表、保险信息表和基金信息表中，都存在金额这个属性，在现实生活中，金额不会存在负数。因此针对表中金额的属性，增加大于0的约束条件。
为finances_product表的p_amount列添加大于等于0的约束
ALTER table finance.finances_product ADD CONSTRAINT c_p_mount CHECK (p_amount >=0);
为fund表的f_amount列添加大于等于0的约束。
ALTER table finance.fund ADD CONSTRAINT c_f_mount CHECK (f_amount >=0);
为insurance表的i_amount列添加大于等于0的约束。
ALTER table finance.insurance ADD CONSTRAINT c_i_mount CHECK (i_amount >=0);
7.新用户的创建和授权
假设C银行的某新员工想要在自己的用户下去访问sys用户下的金融数据库，则该员工需要向sys申请添加相关权限，具体操作如下：
创建用户dbuser，密码为Gauss#3demo。
CREATE USER dbuser IDENTIFIED BY 'Gauss#3demo';
给用户dbuser授予finance数据库下bank_card表的查询和插入权限
GRANT SELECT,INSERT ON finance.bank_card TO dbuser;
将SCHEMA的权限也授予dbuser用户
GRANT ALL ON SCHEMA finance to dbuser;
二、使用JDBC连接数据库
【数据库原理实验(openGauss)】 使用JDBC连接数据库
