【数据库课程设计】金融数据库设计与实现

最新推荐文章于 2024-09-08 12:07:57 发布
最新推荐文章于 2024-09-08 12:07:57 发布
阅读量8.1k 收藏 44
点赞数 9
分类专栏： 学习笔记 # 数据库系统原理 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-NC-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/blackingz/article/details/118556523
版权

数据库课程设计

文章目录

1.E-R图

在这里插入图片描述

2.E-R图转换为关系模式

样式说明：外码，主码

2.1 实体集的处理

普通实体集转换为关系模式：

  • 支行 (支行ID,支行名称,系统密码,城市,街道);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期);
  • 账户 (账号,金额)

一般化/特殊化实体集的处理:

由于所有一般化/特殊化实体集均为全部特殊化，且不相交，采取只将每个最低层实体集转化为关系模式

  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额);
  • 保险 (保险名称,保险编号,保险价格,适用人群,保险年限,保障项目);
  • 基金 (基金名称,基金编号,基金类型,基金价格,风险等级,基金管理者);

2.2 联系集的处理

一对多联系：

  • 客户-账户(账号,客户ID)；
  • 支行-理财经理 (支行ID,工号);
  • 支行-账户 (账号支行ID);
  • 支行-贷款 (贷款号,支行ID);
  • 客户-贷款 (贷款号,客户ID);
  • 理财经理-客户(工号,客户ID);
  • 个人客户- 信用卡(个人客户ID,信用卡号);
  • 企业客户-结算卡 (企业客户ID,结算卡号);

多对多联系：

  • 购买 (客户ID,产品编号,购买份额);

2.3 整理合并

  • 支行 (支行ID,支行名称,系统密码,城市,街道);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱，工作银行ID);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期,放贷银行ID,贷款客户ID);
  • 账户 (账号,金额,开户行ID,客户ID)
  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码,理财经理工号);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号,理财经理工号);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度,个人客户ID);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额,企业客户ID);
  • 保险 (保险名称,保险编号,保险金额,适用人群,保险年限,保障项目);
  • 基金 (基金名称,基金编号,基金类型,基金金额,风险等级,基金管理者);
  • 存取 (客户ID,账号,存取金额,存取日期,存/取);(注：客户ID多态关联)
  • 购买基金 (客户ID,基金产品编号,购买份额);
  • 购买保险 (客户ID,保险产品编号,购买数量);

3.数据模型

3.1对象及属性对应的编号

  • Branch (branch_ID, branch_name, system_password, city, street);
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
  • Loan (loan_no,loan_amount,loan_date, repayment_date, branch_ID, customer_ID);
  • Account (account_no, account_amount, branch_ID,Customer_ID);
  • PersonalCustomer(PersonalCustomer_ID,system_password,name, ID_number,email, telephone,work_number);
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
  • CreditCard (CreditCard_no, payment_password, count_limit, remain, PersonalCustomer_ID);
  • SettlementCard (SettlementCard_no, payment_password, count_limit ,remain ,EnterpriseCustomer_ID);
  • Fund(f_name,f_id，f_type，f_amount，risk_level，f_manager);
  • Insurance(i_name，i_id，i_amount，i_person，i_year，i_project);
  • Access (Customer_ID，account_no，count，access_date，state);
  • Buy_F (Customer_ID,f_id,count);
  • Buy_I (Customer_ID,i_id,count);

3.2 数据项说明

3.2.1 Branch(支行)表
  • Branch (branch_ID, branch_name, system_password, city, street);
  • 支行 (支行ID,支行名称,系统密码,城市,街道);
表3.1 Branch(支行)表
字段名称字段类型约束说明
branch_IDVARCHAR(20)PRIMARY KEY支行ID
branch_nameVARCHAR(100)UNIQUE支行名称
system_passwordVARCHAR(20)系统密码
cityVARCHAR(100)城市
streetVARCHAR(100)街道
3.2.2 Manager(理财经理)表
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱，工作银行ID);
字段名称字段类型约束说明
work_numberVARCHAR(20)PRIMARY KEY工号
nameVARCHAR(20)NOT NULL姓名
system_passwordVARCHAR(20)NOT NULL系统密码
ID_numberVARCHAR(20)UNIQUE身份证号
telephoneVARCHAR(20)电话
emailVARCHAR(50)邮箱
branch_IDVARCHAR(20)FOREIGN KEY工作银行ID
3.2.3 Loan(贷款)表
  • Loan (loan_no,loan_amount,loan_date, repayment_date, branch_ID, customer_ID);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期,放贷银行ID,贷款客户ID);
字段名称字段类型约束说明
loan_noVARCHAR(20)PRIMARY KEY贷款号
loan_amountDOUBLE PRECISIONNOT NULL贷款金额
loan_dateDATENOT NULL贷款日期
repayment_dateDATENOT NULL还款日期
branch_IDVARCHAR(20)FOREIGN KEY放贷银行ID
customer_IDVARCHAR(20)FOREIGN KEY贷款客户ID
3.2.4 Account(账户)表
  • Account (account_no, account_amount, branch_ID,Customer_ID);
  • 账户 (账号,金额,开户行ID,客户ID);
字段名称字段类型约束说明
account_noVARCHAR(20)PRIMARY KEY账号
account_amountDOUBLE PRECISIONNOT NULL金额
branch_IDVARCHAR(20)FOREIGN KEY开户行ID
Customer_IDVARCHAR(20)FOREIGN KEY客户ID
3.2.5 PersonalCustomer(个人客户)表

  • PersonalCustomer(PersonalCustomer_ID,system_password,name, ID_number,email, telephone,work_number);

  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码,理财经理工号);

字段名称字段类型约束说明
PersonalCustomer_IDVARCHAR(20)PRIMARY KEY个人客户ID
system_passwordVARCHAR(20)NOT NULL客户密码
nameVARCHAR(20)NOT NULL姓名
ID_numberVARCHAR(20)UNIQUE身份证号
emailVARCHAR(50)邮箱
telephoneVARCHAR(20)手机号码
work_numberVARCHAR(20)FOREIGN KEY理财经理工号
3.2.6 EnterpriseCustomer(企业客户)表
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号，理财经理工号);
字段名称字段类型约束说明
EnterpriseCustomer_IDVARCHAR(20)PRIMARY KEY企业客户ID
system_passwordVARCHAR(20)NOT NULL客户密码
nameVARCHAR(20)NOT NULL企业名称
legal_personVARCHAR(20)企业法人
addressVARCHAR(100)注册地址
business_numberVARCHAR(20)营业执照
work_numberVARCHAR(20)FOREIGN KEY理财经理工号
3.2.7 CreditCard(信用卡)表
  • CreditCard (CreditCard_no, payment_password, count_limit, remain, PersonalCustomer_ID);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度,个人客户ID);
字段名称字段类型约束说明
CreditCard_noVARCHAR(20)PRIMARY KEY信用卡号
payment_passwordVARCHAR(20)NOT NULL支付密码
count_limitDOUBLE PRECISIONCHECK透支额度
remainDOUBLE PRECISIONCHECK剩余额度
PersonalCustomer_IDVARCHAR(20)FOREIGN KEY个人客户ID
3.2.8 SettlementCard(结算卡)表
  • SettlementCard (SettlementCard_no, payment_password, count_limit ,remain ,EnterpriseCustomer_ID);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额,企业客户ID);
字段名称字段类型约束说明
SettlementCard_noVARCHAR(20)PRIMARY KEY结算卡号
payment_passwordVARCHAR(20)NOT NULL支付密码
count_limitDOUBLE PRECISIONCHECK上限金额
remainDOUBLE PRECISIONCHECK剩余金额
EnterpriseCustomer_IDVARCHAR(20)FOREIGN KEY企业客户ID
3.2.9 fund(基金)表
  • Fund(f_name,f_id，f_type，f_amount，risk_level，f_manager);
  • 基金 (基金名称,基金编号,基金类型,基金金额,风险等级,基金管理者);
字段名称字段类型约束说明
f_nameVARCHAR(100)NOT NULL基金名称
f_idINTEGERPRIMARY KEY基金编号
f_typeVARCHAR(20)基金类型
f_amountINTEGER基金金额
risk_levelVARCHAR(20)NOT NULL风险等级
f_managerINTEGERNOT NULL基金管理者
3.2.10 insurance(保险)表
  • 保险 (保险名称,保险编号,保险金额,适用人群,保险年限,保障项目);
  • Insurance(i_name，i_id，i_amount，i_person，i_year，i_project);
字段名称字段类型约束说明
i_nameVARCHAR(100)NOT NULL保险名称
i_idINTEGERPRIMARY KEY保险编号
i_amountINTEGER保险金额
i_personVARCHAR(20)适用人群
i_yearINTEGER保险年限
i_projectVARCHAR(200)保障项目
3.2.11 Buy_I(购买保险)
  • Buy_I (Customer_ID,i_id,count);
  • 购买保险 (客户ID,基保险产品编号,购买数量);
字段名称字段类型约束说明
Customer_IDVARCHAR(20)PRIMARY KEY客户ID
i_idVARCHAR(20)PRIMARY KEY保险产品编号
countINTERGERNOT NULL、CHECK购买数量
3.2.12 Buy_F(购买基金)
  • Buy_F (Customer_ID,f_id,count);
  • 购买基金 (客户ID,基金产品编号,购买份额);
字段名称字段类型约束说明
Customer_IDVARCHAR(20)PRIMARY KEY客户ID
f_idVARCHAR(20)PRIMARY KEY保险产品编号
countINTERGERNOT NULL、CHECK购买份额

4.创建数据表

4.1 创建数据库

4.1.1 登录数据库

切换到omm用户，以操作系统用户omm登录数据库主节点。

su - omm

启动数据库服务

gs_om -t start

使用gsql工具登陆数据库

gsql -d postgres -p 26000 -r

在这里插入图片描述

4.1.2 创建金融数据库

创建数据库finance

CREATE DATABASE finance ENCODING 'UTF8' template = template0;

连接finance数据库

\connect finance

创建名为finance的schema，并设置finance为当前的schema

CREATE SCHEMA finance;

将默认搜索路径设为finance

SET search_path TO finance;

在这里插入图片描述

4.2 信息表的创建

4.2.1 Branch(支行)表
  • Branch (branch_ID, branch_name, system_password, city, street);
  • 支行 (支行ID,支行名称,系统密码,城市,街道);

删除表Branch

DROP TABLE IF EXISTS finance.Branch;

创建表Branch

CREATE TABLE finance.Branch
(
	branch_ID VARCHAR(20) PRIMARY KEY ,
	branch_name VARCHAR(100) UNIQUE ,
	system_password VARCHAR(20) ,
	city VARCHAR(100) ,
	street VARCHAR(100)
);

在这里插入图片描述

使用命令查看Branch表详细信息： \d+ finance.Branch

                                    Table "finance.branch"
     Column      |          Type          | Modifiers | Storage  | Stats target | Description 
-----------------+------------------------+-----------+----------+--------------+-------------
 branch_id       | character varying(20)  | not null  | extended |              | 
 branch_name     | character varying(100) |           | extended |              | 
 system_password | character varying(20)  |           | extended |              | 
 city            | character varying(100) |           | extended |              | 
 street          | character varying(100) |           | extended |              | 
Indexes:
    "branch_pkey" PRIMARY KEY, btree (branch_id) TABLESPACE pg_default
    "branch_branch_name_key" UNIQUE CONSTRAINT, btree (branch_name) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
4.2.2 Manager(理财经理)表
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
  • 理财经理 (工号,姓名,系统密码,身份证号,电话,邮箱，工作银行ID);

删除表Manager

DROP TABLE IF EXISTS finance.Manager;

创建表Manager

CREATE TABLE finance.Manager
(
	work_number VARCHAR(20) PRIMARY KEY , 
	name VARCHAR(20) NOT NULL , 
	system_password VARCHAR(20) NOT NULL , 
	ID_number VARCHAR(20) UNIQUE , 
	telephone VARCHAR(20) , 
	email VARCHAR(50) , 
	branch_ID VARCHAR(20),
	FOREIGN KEY (branch_ID) REFERENCES finance.Branch (branch_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Manager表详细信息： \d+ finance.Manager

                                   Table "finance.manager"
     Column      |         Type          | Modifiers | Storage  | Stats target | Description 
-----------------+-----------------------+-----------+----------+--------------+-------------
 work_number     | character varying(20) | not null  | extended |              | 
 name            | character varying(20) | not null  | extended |              | 
 system_password | character varying(20) | not null  | extended |              | 
 id_number       | character varying(20) |           | extended |              | 
 telephone       | character varying(20) |           | extended |              | 
 email           | character varying(50) |           | extended |              | 
 branch_id       | character varying(20) |           | extended |              | 
Indexes:
    "manager_pkey" PRIMARY KEY, btree (work_number) TABLESPACE pg_default
    "manager_id_number_key" UNIQUE CONSTRAINT, btree (id_number) TABLESPACE pg_default
Foreign-key constraints:
    "manager_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.3 Account(账户)表
  • Account (account_no, account_amount, branch_ID,Customer_ID);
  • 账户 (账号,金额,开户行ID,客户ID);

删除表Account

DROP TABLE IF EXISTS finance.Account;

创建表Account

CREATE TABLE finance.Account
(
	account_no VARCHAR(20) PRIMARY KEY , 
	account_amount DOUBLE PRECISION NOT NULL , 
	branch_ID VARCHAR(20) , 
	Customer_ID VARCHAR(20),
	FOREIGN KEY (branch_ID) REFERENCES finance.Branch (branch_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Account表详细信息： \d+ finance.Account

                                  Table "finance.account"
     Column     |         Type          | Modifiers | Storage  | Stats target | Description 
----------------+-----------------------+-----------+----------+--------------+-------------
 account_no     | character varying(20) | not null  | extended |              | 
 account_amount | double precision      | not null  | plain    |              | 
 branch_id      | character varying(20) |           | extended |              | 
 customer_id    | character varying(20) |           | extended |              | 
Indexes:
    "account_pkey" PRIMARY KEY, btree (account_no) TABLESPACE pg_default
Foreign-key constraints:
    "account_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES finance.branch(branch_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.4 PersonalCustomer(个人客户)表

  • PersonalCustomer(PersonalCustomer_ID,system_password,name, ID_number,email, telephone,work_number);

  • 个人客户 (个人客户ID,客户密码,姓名,身份证号,邮箱,手机号码,理财经理工号);

删除表PersonalCustomer

DROP TABLE IF EXISTS finance.PersonalCustomer;

创建表PersonalCustomer

CREATE TABLE finance.PersonalCustomer
(
	PersonalCustomer_ID VARCHAR(20) PRIMARY KEY , 
	system_password VARCHAR(20) NOT NULL , 
	name VARCHAR(20) NOT NULL , 
	ID_number VARCHAR(20) UNIQUE , 
	email VARCHAR(50) , 
	telephone VARCHAR(20) , 
	work_number VARCHAR(20) , 
	FOREIGN KEY (work_number) REFERENCES finance.Manager (work_number) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看PersonalCustomer表详细信息： \d+ finance.PersonalCustomer

                                Table "finance.personalcustomer"
       Column        |         Type          | Modifiers | Storage  | Stats target | Description 
---------------------+-----------------------+-----------+----------+--------------+-------------
 personalcustomer_id | character varying(20) | not null  | extended |              | 
 system_password     | character varying(20) | not null  | extended |              | 
 name                | character varying(20) | not null  | extended |              | 
 id_number           | character varying(20) |           | extended |              | 
 email               | character varying(50) |           | extended |              | 
 telephone           | character varying(20) |           | extended |              | 
 work_number         | character varying(20) |           | extended |              | 
Indexes:
    "personalcustomer_pkey" PRIMARY KEY, btree (personalcustomer_id) TABLESPACE pg_default
    "personalcustomer_id_number_key" UNIQUE CONSTRAINT, btree (id_number) TABLESPACE pg_default
Foreign-key constraints:
    "personalcustomer_work_number_fkey" FOREIGN KEY (work_number) REFERENCES manager(work_number) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.5 EnterpriseCustomer(企业客户)表
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
  • 企业客户 (企业客户ID,客户密码,企业名称,企业法人,注册地址,营业执照号，理财经理工号);

删除表EnterpriseCustomer

DROP TABLE IF EXISTS finance.EnterpriseCustomer;

创建表EnterpriseCustomer

CREATE TABLE finance.EnterpriseCustomer
(
	EnterpriseCustomer_ID VARCHAR(20) PRIMARY KEY , 
	system_password VARCHAR(20)	NOT NULL , 
	name VARCHAR(20) NOT NULL , 
	legal_person VARCHAR(20) , 
	address VARCHAR(100) , 
	business_number VARCHAR(20) ,
	work_number VARCHAR(20) ,
	FOREIGN KEY (work_number) REFERENCES finance.Manager (work_number) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看EnterpriseCustomer表详细信息： \d+ finance.EnterpriseCustomer

                                 Table "finance.enterprisecustomer"
        Column         |          Type          | Modifiers | Storage  | Stats target | Description 
-----------------------+------------------------+-----------+----------+--------------+-------------
 enterprisecustomer_id | character varying(20)  | not null  | extended |              | 
 system_password       | character varying(20)  | not null  | extended |              | 
 name                  | character varying(20)  | not null  | extended |              | 
 legal_person          | character varying(20)  |           | extended |              | 
 address               | character varying(100) |           | extended |              | 
 business_number       | character varying(20)  |           | extended |              | 
 work_number           | character varying(20)  |           | extended |              | 
Indexes:
    "enterprisecustomer_pkey" PRIMARY KEY, btree (enterprisecustomer_id) TABLESPACE pg_default
Foreign-key constraints:
    "enterprisecustomer_work_number_fkey" FOREIGN KEY (work_number) REFERENCES manager(work_number) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.6 Loan(贷款)表
  • Loan (loan_no,loan_amount,loan_date, repayment_date, branch_ID, customer_ID);
  • 贷款 (贷款号,贷款金额,贷款日期,还款日期,放贷银行ID,贷款客户ID);

删除表Loan

DROP TABLE IF EXISTS finance.Loan;

创建表Loan

CREATE TABLE finance.Loan
(
	loan_no VARCHAR(20) PRIMARY KEY , 
	loan_amount DOUBLE PRECISION NOT NULL , 
	loan_date DATE NOT NULL , 
	repayment_date DATE NOT NULL , 
	branch_ID VARCHAR(20) , 
	customer_ID VARCHAR(20) ,
	FOREIGN KEY (branch_ID) REFERENCES finance.Branch (branch_ID)ON UPDATE CASCADE ON DELETE CASCADE 
);

在这里插入图片描述

使用命令查看Loan表详细信息： \d+ finance.Loan

                                        Table "finance.loan"
     Column     |              Type              | Modifiers | Storage  | Stats target | Description 
----------------+--------------------------------+-----------+----------+--------------+-------------
 loan_no        | character varying(20)          | not null  | extended |              | 
 loan_amount    | double precision               | not null  | plain    |              | 
 loan_date      | timestamp(0) without time zone | not null  | plain    |              | 
 repayment_date | timestamp(0) without time zone | not null  | plain    |              | 
 branch_id      | character varying(20)          |           | extended |              | 
 customer_id    | character varying(20)          |           | extended |              | 
Indexes:
    "loan_pkey" PRIMARY KEY, btree (loan_no) TABLESPACE pg_default
Foreign-key constraints:
    "loan_branch_id_fkey" FOREIGN KEY (branch_id) REFERENCES finance.branch(branch_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.7 CreditCard(信用卡)表
  • CreditCard (CreditCard_no, payment_password, count_limit, remain, PersonalCustomer_ID);
  • 信用卡 (信用卡号,支付密码,透支额度,剩余额度,个人客户ID);

删除表CreditCard

DROP TABLE IF EXISTS finance.CreditCard;

创建表CreditCard

CREATE TABLE finance.CreditCard
(
	CreditCard_no VARCHAR(20) PRIMARY KEY , 
	payment_password VARCHAR(20) NOT NULL , 
	count_limit DOUBLE PRECISION CHECK (count_limit>=0),
	remain DOUBLE PRECISION CHECK (remain>=0), 
	PersonalCustomer_ID VARCHAR(20) , 
	FOREIGN KEY (PersonalCustomer_ID) REFERENCES finance.PersonalCustomer (PersonalCustomer_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看CreditCard表详细信息： \d+ finance.CreditCard

                                   Table "finance.creditcard"
       Column        |         Type          | Modifiers | Storage  | Stats target | Description 
---------------------+-----------------------+-----------+----------+--------------+-------------
 creditcard_no       | character varying(20) | not null  | extended |              | 
 payment_password    | character varying(20) | not null  | extended |              | 
 count_limit         | double precision      |           | plain    |              | 
 remain              | double precision      |           | plain    |              | 
 personalcustomer_id | character varying(20) |           | extended |              | 
Indexes:
    "creditcard_pkey" PRIMARY KEY, btree (creditcard_no) TABLESPACE pg_default
Check constraints:
    "creditcard_count_limit_check" CHECK (count_limit >= 0::double precision)
    "creditcard_remain_check" CHECK (remain >= 0::double precision)
Foreign-key constraints:
    "creditcard_personalcustomer_id_fkey" FOREIGN KEY (personalcustomer_id) REFERENCES personalcustomer(personalcustomer_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.8 SettlementCard(结算卡)表
  • SettlementCard (SettlementCard_no, payment_password, count_limit ,remain ,EnterpriseCustomer_ID);
  • 结算卡 (结算卡号,支付密码,上限金额,剩余金额,企业客户ID);

删除表SettlementCard

DROP TABLE IF EXISTS finance.SettlementCard;

创建表SettlementCard

CREATE TABLE finance.SettlementCard
(
	SettlementCard_no VARCHAR(20) PRIMARY KEY , 
	payment_password VARCHAR(20) NOT NULL , 
	count_limit DOUBLE PRECISION CHECK (count_limit>=0), 
	remain DOUBLE PRECISION CHECK (remain>=0), 
	EnterpriseCustomer_ID VARCHAR(20) , 
	FOREIGN KEY (EnterpriseCustomer_ID) REFERENCES finance.EnterpriseCustomer (EnterpriseCustomer_ID) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看SettlementCard表详细信息： \d+ finance.SettlementCard

                                  Table "finance.settlementcard"
        Column         |         Type          | Modifiers | Storage  | Stats target | Description 
-----------------------+-----------------------+-----------+----------+--------------+-------------
 settlementcard_no     | character varying(20) | not null  | extended |              | 
 payment_password      | character varying(20) | not null  | extended |              | 
 count_limit           | double precision      |           | plain    |              | 
 remain                | double precision      |           | plain    |              | 
 enterprisecustomer_id | character varying(20) |           | extended |              | 
Indexes:
    "settlementcard_pkey" PRIMARY KEY, btree (settlementcard_no) TABLESPACE pg_default
Check constraints:
    "settlementcard_count_limit_check" CHECK (count_limit >= 0::double precision)
    "settlementcard_remain_check" CHECK (remain >= 0::double precision)
Foreign-key constraints:
    "settlementcard_enterprisecustomer_id_fkey" FOREIGN KEY (enterprisecustomer_id) REFERENCES enterprisecustomer(enterprisecustomer_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.9 fund(基金)表
  • Fund(f_name,f_id，f_type，f_amount，risk_level，f_manager);
  • 基金 (基金名称,基金编号,基金类型,基金金额,风险等级,基金管理者);

删除表fund

DROP TABLE IF EXISTS finance.fund;

创建表fund

CREATE TABLE finance.fund
(
	f_name VARCHAR(100) NOT NULL , 
	f_id INTEGER PRIMARY KEY , 
	f_type VARCHAR(20) , 
	f_amount INTEGER , 
	risk_level VARCHAR(20) NOT NULL , 
	f_manager INTEGER NOT NULL 
);

在这里插入图片描述

使用命令查看fund表详细信息： \d+ finance.fund

                                  Table "finance.fund"
   Column   |          Type          | Modifiers | Storage  | Stats target | Description 
------------+------------------------+-----------+----------+--------------+-------------
 f_name     | character varying(100) | not null  | extended |              | 
 f_id       | integer                | not null  | plain    |              | 
 f_type     | character varying(20)  |           | extended |              | 
 f_amount   | integer                |           | plain    |              | 
 risk_level | character varying(20)  | not null  | extended |              | 
 f_manager  | integer                | not null  | plain    |              | 
Indexes:
    "fund_pkey" PRIMARY KEY, btree (f_id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
4.2.10 insurance(保险)表
  • 保险 (保险名称,保险编号,保险金额,适用人群,保险年限,保障项目);
  • Insurance(i_name，i_id，i_amount，i_person，i_year，i_project);

删除表insurance

DROP TABLE IF EXISTS finance.insurance;

创建表insurance

CREATE TABLE finance.insurance
(
	i_name VARCHAR(100) NOT NULL , 
	i_id INTEGER PRIMARY KEY , 
	i_amount INTEGER , 
	i_person VARCHAR(20) , 
	i_year INTEGER , 
	i_project VARCHAR(200) 
);

在这里插入图片描述

使用命令查看insurance表详细信息： \d+ finance.insurance

                               Table "finance.insurance"
  Column   |          Type          | Modifiers | Storage  | Stats target | Description 
-----------+------------------------+-----------+----------+--------------+-------------
 i_name    | character varying(100) | not null  | extended |              | 
 i_id      | integer                | not null  | plain    |              | 
 i_amount  | integer                |           | plain    |              | 
 i_person  | character varying(20)  |           | extended |              | 
 i_year    | integer                |           | plain    |              | 
 i_project | character varying(200) |           | extended |              | 
Indexes:
    "insurance_pkey" PRIMARY KEY, btree (i_id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
4.2.11 Buy_I(购买保险)
  • Buy_I (Customer_ID,i_id,count);
  • 保险基金 (客户ID,保险产品编号,购买数量);

删除表Buy_I

DROP TABLE IF EXISTS finance.Buy_I;

创建表Buy_I

CREATE TABLE finance.Buy_I
(
	Customer_ID VARCHAR(20) , 
	i_id INT ,
	count INT NOT NULL,
	PRIMARY KEY (Customer_ID,i_id),
	FOREIGN KEY (i_id) REFERENCES finance.insurance (i_id) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Buy_I表详细信息： \d+ finance.Buy_I

                                  Table "finance.buy_i"
   Column    |         Type          | Modifiers | Storage  | Stats target | Description 
-------------+-----------------------+-----------+----------+--------------+-------------
 customer_id | character varying(20) | not null  | extended |              | 
 i_id        | integer               | not null  | plain    |              | 
 count       | integer               | not null  | plain    |              | 
Indexes:
    "buy_i_pkey" PRIMARY KEY, btree (customer_id, i_id) TABLESPACE pg_default
Foreign-key constraints:
    "buy_i_i_id_fkey" FOREIGN KEY (i_id) REFERENCES finance.insurance(i_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no
4.2.12 Buy_F(购买基金)
  • Buy_F (Customer_ID,f_id,count);
  • 购买基金 (客户ID,基金产品编号,购买份额);

删除表Buy_F

DROP TABLE IF EXISTS finance.Buy_F;

创建表Buy_F

CREATE TABLE finance.Buy_F
(
	Customer_ID VARCHAR(20) , 
	f_id INT , 
	count INT NOT NULL , 
	PRIMARY KEY (Customer_ID,f_id),
	FOREIGN KEY (f_id) REFERENCES finance.fund (f_id) ON UPDATE CASCADE ON DELETE CASCADE
);

在这里插入图片描述

使用命令查看Buy_F表详细信息： \d+ finance.Buy_F

                                  Table "finance.buy_f"
   Column    |         Type          | Modifiers | Storage  | Stats target | Description 
-------------+-----------------------+-----------+----------+--------------+-------------
 customer_id | character varying(20) | not null  | extended |              | 
 f_id        | integer               | not null  | plain    |              | 
 count       | integer               | not null  | plain    |              | 
Indexes:
    "buy_f_pkey" PRIMARY KEY, btree (customer_id, f_id) TABLESPACE pg_default
Foreign-key constraints:
    "buy_f_f_id_fkey" FOREIGN KEY (f_id) REFERENCES finance.fund(f_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no
Options: orientation=row, compression=no

4.3 表信息查看

列举表：

`finance=#`  \dt

                                List of relations
 Schema  |        Name        | Type  | Owner |             Storage              
---------+--------------------+-------+-------+----------------------------------
 finance | access             | table | omm   | {orientation=row,compression=no}
 finance | account            | table | omm   | {orientation=row,compression=no}
 finance | branch             | table | omm   | {orientation=row,compression=no}
 finance | buy_f              | table | omm   | {orientation=row,compression=no}
 finance | buy_i              | table | omm   | {orientation=row,compression=no}
 finance | creditcard         | table | omm   | {orientation=row,compression=no}
 finance | enterprisecustomer | table | omm   | {orientation=row,compression=no}
 finance | fund               | table | omm   | {orientation=row,compression=no}
 finance | insurance          | table | omm   | {orientation=row,compression=no}
 finance | loan               | table | omm   | {orientation=row,compression=no}
 finance | manager            | table | omm   | {orientation=row,compression=no}
 finance | personalcustomer   | table | omm   | {orientation=row,compression=no}
 finance | settlementcard     | table | omm   | {orientation=row,compression=no}
(13 rows)

5.数据库完整性

5.1 实体完整性约束

使用如下命令查询主码：

SELECT
     tc.constraint_name, tc.table_name, kcu.column_name, 
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name,
     tc.is_deferrable,tc.initially_deferred
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE constraint_type = 'PRIMARY KEY' ;

在这里插入图片描述

     constraint_name     |     table_name     |      column_name      | foreign_table_name |  foreign_column_name  | is_deferrable | initially_deferred 
-------------------------+--------------------+-----------------------+--------------------+-----------------------+---------------+--------------------
 branch_pkey             | branch             | branch_id             | branch             | branch_id             | NO            | NO
 manager_pkey            | manager            | work_number           | manager            | work_number           | NO            | NO
 personalcustomer_pkey   | personalcustomer   | personalcustomer_id   | personalcustomer   | personalcustomer_id   | NO            | NO
 creditcard_pkey         | creditcard         | creditcard_no         | creditcard         | creditcard_no         | NO            | NO
 settlementcard_pkey     | settlementcard     | settlementcard_no     | settlementcard     | settlementcard_no     | NO            | NO
 fund_pkey               | fund               | f_id                  | fund               | f_id                  | NO            | NO
 insurance_pkey          | insurance          | i_id                  | insurance          | i_id                  | NO            | NO
 loan_pkey               | loan               | loan_no               | loan               | loan_no               | NO            | NO
 account_pkey            | account            | account_no            | account            | account_no            | NO            | NO
 enterprisecustomer_pkey | enterprisecustomer | enterprisecustomer_id | enterprisecustomer | enterprisecustomer_id | NO            | NO
 buy_i_pkey              | buy_i              | i_id                  | buy_i              | customer_id           | NO            | NO
 buy_i_pkey              | buy_i              | customer_id           | buy_i              | customer_id           | NO            | NO
 buy_i_pkey              | buy_i              | i_id                  | buy_i              | i_id                  | NO            | NO
 buy_i_pkey              | buy_i              | customer_id           | buy_i              | i_id                  | NO            | NO
 buy_f_pkey              | buy_f              | f_id                  | buy_f              | customer_id           | NO            | NO
 buy_f_pkey              | buy_f              | customer_id           | buy_f              | customer_id           | NO            | NO
 buy_f_pkey              | buy_f              | f_id                  | buy_f              | f_id                  | NO            | NO
 buy_f_pkey              | buy_f              | customer_id           | buy_f              | f_id                  | NO            | NO
(18 rows)

5.2 参照完整性

CREATE TRIGGER Buy_IForeign
AFTER UPDATE OF Customer_ID ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE Buy_IForeign() ;

使用如下命令查询外码：

SELECT
     tc.constraint_name, tc.table_name, kcu.column_name, 
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name,
     tc.is_deferrable,tc.initially_deferred
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE constraint_type = 'FOREIGN KEY' ;

在这里插入图片描述

           constraint_name           |     table_name     |     column_name     | foreign_table_name | foreign_column_name | is_deferrable | initially_deferred 
-------------------------------------+--------------------+---------------------+--------------------+---------------------+---------------+--------------------
 manager_branch_id_fkey              | manager            | branch_id           | branch             | branch_id           | NO            | NO
 loan_branch_id_fkey                 | loan               | branch_id           | branch             | branch_id           | NO            | NO
 account_branch_id_fkey              | account            | branch_id           | branch             | branch_id           | NO            | NO
 personalcustomer_work_number_fkey   | personalcustomer   | work_number         | manager            | work_number         | NO            | NO
 enterprisecustomer_work_number_fkey | enterprisecustomer | work_number         | manager            | work_number         | NO            | NO
 creditcard_personalcustomer_id_fkey | creditcard         | personalcustomer_id | personalcustomer   | personalcustomer_id | NO            | NO
 buy_f_f_id_fkey                     | buy_f              | f_id                | fund               | f_id                | NO            | NO
 buy_i_i_id_fkey                     | buy_i              | i_id                | insurance          | i_id                | NO            | NO
(8 rows)

5.3 用户自定义完整性

查看自定义触发器

SELECT * FROM pg_trigger WHERE tgisinternal = 'f';

tgisinternal 表示触发器是否为内部生成的

在这里插入图片描述

 tgrelid |          tgname          | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgowner 
---------+--------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+---------
   17273 | deleteenterprisecustomer |  17359 |      9 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17224 | accountforeign           |  17246 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17193 | loanforeign              |  17250 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17304 | buy_iforeign             |  17350 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17314 | buy_fforeign             |  17352 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
   17008 | updatepersonalcustomer   |  17355 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17273 | updateenterprisecustomer |  17357 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17224 | updateaccount            |  17365 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 4      | \x     |        |      10
   17193 | updateloan               |  17368 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 6      | \x     |        |      10
   17304 | updatebuy_i              |  17370 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17314 | updatebuy_f              |  17372 |     19 | O         | f            |             0 |             0 |            0 | f            | f              |       0 | 1      | \x     |        |      10
   17008 | deletepersonalcustomer   |  17359 |      9 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |      10
(12 rows)
5.3.1AccountForeign触发器
DROP TRIGGER IF EXISTS AccountForeign on finance.account;

CREATE OR REPLACE FUNCTION AccountForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

DELETE FROM finance.Account
WHERE finance.Account.Customer_ID=NEW.Customer_ID;

CREATE TRIGGER AccountForeign
BEFORE INSERT ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE AccountForeign() ;
5.3.2 LoanForeign触发器
DROP TRIGGER IF EXISTS LoanForeign on finance.Loan;

CREATE OR REPLACE FUNCTION LoanForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER LoanForeign
BEFORE INSERT ON finance.Loan
FOR EACH ROW
EXECUTE PROCEDURE LoanForeign() ;
5.3.3 Buy_IForeign触发器
DROP TRIGGER IF EXISTS Buy_IForeign on finance.Buy_I;

CREATE OR REPLACE FUNCTION Buy_IForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER Buy_IForeign
BEFORE INSERT ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE Buy_IForeign() ;
5.3.4 Buy_FForeign触发器
DROP TRIGGER IF EXISTS Buy_FForeign on finance.Buy_F;

CREATE OR REPLACE FUNCTION Buy_FForeign() 
RETURNS TRIGGER AS $$ 
BEGIN
IF (NEW.Customer_ID NOT IN 
(
SELECT PersonalCustomer_ID
FROM finance.PersonalCustomer
UNION
SELECT EnterpriseCustomer_ID
FROM finance.EnterpriseCustomer
) )
THEN
RAISE NOTICE 'can not insert';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER Buy_FForeign
BEFORE INSERT ON finance.Buy_F
FOR EACH ROW
EXECUTE PROCEDURE Buy_FForeign() ;
5.3.5 UpdatePersonalCustomer触发器
DROP TRIGGER IF EXISTS UpdatePersonalCustomer on finance.PersonalCustomer;

CREATE OR REPLACE FUNCTION UpdatePersonalCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
UPDATE finance.Account SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
UPDATE finance.Loan SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
UPDATE finance.Buy_I SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
UPDATE finance.Buy_F SET Customer_ID = NEW.PersonalCustomer_ID
WHERE Customer_ID = OLD.PersonalCustomer_ID;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdatePersonalCustomer
AFTER UPDATE OF PersonalCustomer_ID ON finance.PersonalCustomer
FOR EACH ROW
EXECUTE PROCEDURE UpdatePersonalCustomer() ;

在这里插入图片描述

5.3.6 UpdateEnterpriseCustomer触发器
DROP TRIGGER IF EXISTS UpdateEnterpriseCustomer on finance.EnterpriseCustomer;

CREATE OR REPLACE FUNCTION UpdateEnterpriseCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
UPDATE finance.Account SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
UPDATE finance.Loan SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
UPDATE finance.Buy_I SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
UPDATE finance.Buy_F SET Customer_ID = NEW.EnterpriseCustomer_ID
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateEnterpriseCustomer
AFTER UPDATE OF EnterpriseCustomer_ID ON finance.EnterpriseCustomer
FOR EACH ROW
EXECUTE PROCEDURE UpdateEnterpriseCustomer() ;

在这里插入图片描述

5.3.7 DeletePersonalCustomer触发器
DROP TRIGGER IF EXISTS DeletePersonalCustomer on finance.PersonalCustomer;

CREATE OR REPLACE FUNCTION DeletePersonalCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
DELETE FROM finance.Account 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
DELETE FROM finance.Loan 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
DELETE FROM finance.Buy_I 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
DELETE FROM finance.Buy_F 
WHERE Customer_ID = OLD.PersonalCustomer_ID;
RETURN OLD;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER DeletePersonalCustomer
AFTER DELETE ON finance.PersonalCustomer
FOR EACH ROW
EXECUTE PROCEDURE DeletePersonalCustomer() ;
5.3.8 DeleteEnterpriseCustomer触发器
DROP TRIGGER IF EXISTS DeleteEnterpriseCustomer on finance.EnterpriseCustomer;

CREATE OR REPLACE FUNCTION DeleteEnterpriseCustomer() 
RETURNS TRIGGER AS $$ 
BEGIN
DELETE FROM finance.Account 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
DELETE FROM finance.Loan 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
DELETE FROM finance.Buy_I 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
DELETE FROM finance.Buy_F 
WHERE Customer_ID = OLD.EnterpriseCustomer_ID;
RETURN OLD;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER DeleteEnterpriseCustomer
AFTER DELETE ON finance.EnterpriseCustomer
FOR EACH ROW
EXECUTE PROCEDURE DeletePersonalCustomer() ;
5.3.9 UpdateAccount触发器
DROP TRIGGER IF EXISTS UpdateAccount on finance.account;

CREATE OR REPLACE FUNCTION UpdateAccount() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateAccount
BEFORE UPDATE OF Customer_ID ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE UpdateAccount() ;
5.3.10 UpdateLoan触发器
DROP TRIGGER IF EXISTS UpdateLoan on finance.Loan;

CREATE OR REPLACE FUNCTION UpdateLoan() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateLoan
BEFORE UPDATE OF Customer_ID ON finance.Loan
FOR EACH ROW
EXECUTE PROCEDURE UpdateLoan() ;
5.3.11 UpdateBuy_I触发器
DROP TRIGGER IF EXISTS UpdateBuy_I on finance.Buy_I;

CREATE OR REPLACE FUNCTION UpdateBuy_I() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateBuy_I
BEFORE UPDATE OF Customer_ID ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE UpdateBuy_I() ;
5.3.12 UpdateBuy_F触发器
DROP TRIGGER IF EXISTS UpdateBuy_F on finance.Buy_F;

CREATE OR REPLACE FUNCTION UpdateBuy_F() 
RETURNS TRIGGER AS $$ 
BEGIN
RAISE NOTICE 'can not update';
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER UpdateBuy_F
BEFORE UPDATE OF Customer_ID ON finance.Buy_F
FOR EACH ROW
EXECUTE PROCEDURE UpdateBuy_F() ;

6.物理结构设计

6.1 建立索引

6.1.1 Account表建立索引

银行-账户

CREATE INDEX branch_account
ON finance.Account (branch_id);

在这里插入图片描述

6.1.2 Manager表建立索引

银行-经理

CREATE INDEX branch_manager
ON finance.Manager (branch_id);

在这里插入图片描述

6.1.3 PersonalCustomer表建立索引

经理-个人客户

CREATE INDEX manager_PersonalCustomer
ON finance.PersonalCustomer (work_number);

在这里插入图片描述

6.1.4 EnterpriseCustomer表建立索引

经理-企业客户

CREATE INDEX manager_EnterpriseCustomer
ON finance.EnterpriseCustomer (work_number);

在这里插入图片描述

6.1.5 CreditCard表建立索引

个人客户-信用卡

CREATE INDEX PersonalCustomer_CreditCard
ON finance.CreditCard (PersonalCustomer_ID);

在这里插入图片描述

6.1.6 SettlementCard表建立索引

企业客户-结算卡

CREATE INDEX EnterpriseCustomer_SettlementCard
ON finance.SettlementCard (EnterpriseCustomer_ID);

在这里插入图片描述

6.2 查看索引

SELECT *
FROM pg_indexes
WHERE schemaname = 'finance'
ORDER BY tablename,indexname;

在这里插入图片描述

 schemaname |     tablename      |             indexname             | tablespace |                                                              indexdef                                                               
------------+--------------------+-----------------------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------
 finance    | account            | account_pkey                      |            | CREATE UNIQUE INDEX account_pkey ON finance.account USING btree (account_no) TABLESPACE pg_default
 finance    | account            | branch_account                    |            | CREATE INDEX branch_account ON finance.account USING btree (branch_id) TABLESPACE pg_default
 finance    | branch             | branch_branch_name_key            |            | CREATE UNIQUE INDEX branch_branch_name_key ON finance.branch USING btree (branch_name) TABLESPACE pg_default
 finance    | branch             | branch_pkey                       |            | CREATE UNIQUE INDEX branch_pkey ON finance.branch USING btree (branch_id) TABLESPACE pg_default
 finance    | buy_f              | buy_f_pkey                        |            | CREATE UNIQUE INDEX buy_f_pkey ON finance.buy_f USING btree (customer_id, f_id) TABLESPACE pg_default
 finance    | buy_i              | buy_i_pkey                        |            | CREATE UNIQUE INDEX buy_i_pkey ON finance.buy_i USING btree (customer_id, i_id) TABLESPACE pg_default
 finance    | creditcard         | creditcard_pkey                   |            | CREATE UNIQUE INDEX creditcard_pkey ON finance.creditcard USING btree (creditcard_no) TABLESPACE pg_default
 finance    | creditcard         | personalcustomer_creditcard       |            | CREATE INDEX personalcustomer_creditcard ON finance.creditcard USING btree (personalcustomer_id) TABLESPACE pg_default
 finance    | enterprisecustomer | enterprisecustomer_pkey           |            | CREATE UNIQUE INDEX enterprisecustomer_pkey ON finance.enterprisecustomer USING btree (enterprisecustomer_id) TABLESPACE pg_default
 finance    | enterprisecustomer | manager_enterprisecustomer        |            | CREATE INDEX manager_enterprisecustomer ON finance.enterprisecustomer USING btree (work_number) TABLESPACE pg_default
 finance    | fund               | fund_pkey                         |            | CREATE UNIQUE INDEX fund_pkey ON finance.fund USING btree (f_id) TABLESPACE pg_default
 finance    | insurance          | insurance_pkey                    |            | CREATE UNIQUE INDEX insurance_pkey ON finance.insurance USING btree (i_id) TABLESPACE pg_default
 finance    | loan               | loan_pkey                         |            | CREATE UNIQUE INDEX loan_pkey ON finance.loan USING btree (loan_no) TABLESPACE pg_default
 finance    | manager            | branch_manager                    |            | CREATE INDEX branch_manager ON finance.manager USING btree (branch_id) TABLESPACE pg_default
 finance    | manager            | manager_id_number_key             |            | CREATE UNIQUE INDEX manager_id_number_key ON finance.manager USING btree (id_number) TABLESPACE pg_default
 finance    | manager            | manager_pkey                      |            | CREATE UNIQUE INDEX manager_pkey ON finance.manager USING btree (work_number) TABLESPACE pg_default
 finance    | personalcustomer   | manager_personalcustomer          |            | CREATE INDEX manager_personalcustomer ON finance.personalcustomer USING btree (work_number) TABLESPACE pg_default
 finance    | personalcustomer   | personalcustomer_id_number_key    |            | CREATE UNIQUE INDEX personalcustomer_id_number_key ON finance.personalcustomer USING btree (id_number) TABLESPACE pg_default
 finance    | personalcustomer   | personalcustomer_pkey             |            | CREATE UNIQUE INDEX personalcustomer_pkey ON finance.personalcustomer USING btree (personalcustomer_id) TABLESPACE pg_default
 finance    | settlementcard     | enterprisecustomer_settlementcard |            | CREATE INDEX enterprisecustomer_settlementcard ON finance.settlementcard USING btree (enterprisecustomer_id) TABLESPACE pg_default
 finance    | settlementcard     | settlementcard_pkey               |            | CREATE UNIQUE INDEX settlementcard_pkey ON finance.settlementcard USING btree (settlementcard_no) TABLESPACE pg_default
(21 rows)

7.数据库安全性

7.1 视图

SELECT schemaname,viewname,viewowner
FROM   pg_views  
WHERE schemaname = 'finance';

 schemaname |       viewname        | viewowner 
------------+-----------------------+-----------
 finance    | newbranch             | omm
 finance    | newmanager            | omm
 finance    | newpersonalcustomer   | omm
 finance    | newenterprisecustomer | omm
(4 rows)

使用视图屏蔽掉密码

7.1.1 newBranch视图
CREATE VIEW newBranch AS
SELECT branch_ID,branch_name,city,street
FROM finance.Branch;

在这里插入图片描述

7.1.2 newManager视图
  • Manager (work_number, name, system_password, ID_number, telephone, email, branch_ID);
CREATE VIEW newManager AS
SELECT work_number, name, ID_number, telephone, email, branch_ID
FROM finance.Manager;

在这里插入图片描述

7.1.3 newPersonalCustomer视图
CREATE VIEW newPersonalCustomer AS
SELECT PersonalCustomer_ID,name, ID_number,email, telephone,work_number
FROM finance.PersonalCustomer;

在这里插入图片描述

7.1.4 newEnterpriseCustomer视图
  • EnterpriseCustomer (EnterpriseCustomer_ID,system_password, name, legal_person, address, business_number,work_number);
CREATE VIEW newEnterpriseCustomer AS
SELECT EnterpriseCustomer_ID, name, legal_person, address, business_number,work_number
FROM finance.EnterpriseCustomer;

在这里插入图片描述

7.2 角色及用户权限

7.2.1 客户系统权限

创建个人客户用户

CREATE USER pclient IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to pclient;

模式授权

GRANT ALL ON SCHEMA finance to pclient;

表授权

GRANT SELECT ON TABLE finance.Account to pclient;
GRANT SELECT ON TABLE finance.Loan to pclient;
GRANT SELECT ON TABLE finance.CreditCard to pclient;
GRANT SELECT ON TABLE finance.fund to pclient;
GRANT SELECT ON TABLE finance.insurance to pclient;
GRANT SELECT ON TABLE finance.Buy_I to pclient;
GRANT SELECT ON TABLE finance.Buy_F to pclient;

GRANT SELECT ON finance.newBranch to pclient;
GRANT SELECT ON finance.newManager to pclient;
GRANT SELECT ON finance.newPersonalCustomer to pclient;
GRANT SELECT ON finance.newEnterpriseCustomer to pclient;

创建企业客户用户

CREATE USER eclient IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to eclient;

模式授权

GRANT ALL ON SCHEMA finance to eclient;

表授权

GRANT SELECT ON TABLE finance.Account to eclient;
GRANT SELECT ON TABLE finance.EnterpriseCustomer to eclient;
GRANT SELECT ON TABLE finance.Loan to eclient;
GRANT SELECT ON TABLE finance.SettlementCard to eclient;
GRANT SELECT ON TABLE finance.fund to eclient;
GRANT SELECT ON TABLE finance.insurance to eclient;
GRANT SELECT ON TABLE finance.Buy_I to eclient;
GRANT SELECT ON TABLE finance.Buy_F to eclient;

视图授权

GRANT SELECT ON finance.newBranch to eclient;
GRANT SELECT ON finance.newManager to eclient;
GRANT SELECT ON finance.newPersonalCustomer to eclient;
GRANT SELECT ON finance.newEnterpriseCustomer to eclient;

创建客户用户

CREATE USER client IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to client;

模式授权

GRANT ALL ON SCHEMA finance to client;

表授权

GRANT SELECT ON TABLE finance.Branch to client;
GRANT SELECT ON TABLE finance.Manager to client;
GRANT ALL ON TABLE finance.Account to client;
GRANT ALL ON TABLE finance.PersonalCustomer to client;
GRANT ALL ON TABLE finance.EnterpriseCustomer to client;
GRANT ALL ON TABLE finance.Loan to client;
GRANT ALL ON TABLE finance.CreditCard to client;
GRANT ALL ON TABLE finance.SettlementCard to client;
GRANT SELECT ON TABLE finance.fund to client;
GRANT SELECT ON TABLE finance.insurance to client;
GRANT ALL ON TABLE finance.Buy_I to client;
GRANT ALL ON TABLE finance.Buy_F to client;

视图授权

GRANT SELECT ON finance.newBranch to client;
GRANT SELECT ON finance.newManager to client;
GRANT SELECT ON finance.newPersonalCustomer to client;
GRANT SELECT ON finance.newEnterpriseCustomer to client;

在这里插入图片描述

7.2.2 经理系统权限

创建经理用户

CREATE USER manager IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to manager;

模式授权

GRANT ALL ON SCHEMA finance to manager;

表授权

GRANT UPDATE ON TABLE finance.Manager to manager;
GRANT SELECT ON TABLE finance.Account to manager;
GRANT SELECT ON TABLE finance.Loan to manager;
GRANT SELECT ON TABLE finance.CreditCard to manager;
GRANT SELECT ON TABLE finance.SettlementCard to manager;
GRANT SELECT ON TABLE finance.fund to manager;
GRANT SELECT ON TABLE finance.insurance to manager;
GRANT SELECT ON TABLE finance.Buy_I to manager;
GRANT SELECT ON TABLE finance.Buy_F to manager;

视图授权

GRANT SELECT ON finance.newBranch to manager;
GRANT SELECT ON finance.newManager to manager;
GRANT SELECT ON finance.newPersonalCustomer to manager;
GRANT SELECT ON finance.newEnterpriseCustomer to manager;

在这里插入图片描述

7.2.3 管理员权限

创建管理员用户bk

CREATE USER bk IDENTIFIED BY 'iebokai@163.com';

数据库授权

GRANT ALL ON DATABASE finance to bk;

模式授权

GRANT ALL ON SCHEMA finance to bk;

表授权

GRANT ALL ON TABLE finance.Branch to bk;
GRANT ALL ON TABLE finance.Manager to bk;
GRANT ALL ON TABLE finance.Account to bk;
GRANT ALL ON TABLE finance.PersonalCustomer to bk;
GRANT ALL ON TABLE finance.EnterpriseCustomer to bk;
GRANT ALL ON TABLE finance.Loan to bk;
GRANT ALL ON TABLE finance.CreditCard to bk;
GRANT ALL ON TABLE finance.SettlementCard to bk;
GRANT ALL ON TABLE finance.fund to bk;
GRANT ALL ON TABLE finance.insurance to bk;
GRANT ALL ON TABLE finance.Buy_I to bk;
GRANT ALL ON TABLE finance.Buy_F to bk;

视图授权

GRANT ALL ON newBranch to bk;
GRANT ALL ON newManager to bk;
GRANT ALL ON newPersonalCustomer to bk;
GRANT ALL ON newEnterpriseCustomer to bk;

在这里插入图片描述

7.3 权限查看

7.3.1 查看所有角色
SELECT * FROM pg_roles;

 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil |  rolrespool  | rolparentid | roltabspace | rolconfig |  oid  | roluseft | rolkind | nodegroup | roltempspace | rolspillspace | rolmonitoradmin | roloperatoradmin | rolpolicyadmin 
---------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+---------------+--------------+-------------+-------------+-----------+-------+----------+---------+-----------+--------------+---------------+-----------------+------------------+----------------
 omm     | t        | t          | t             | t           | t            | t           | t              | t             | t              |           -1 | ********    |               |               | default_pool |           0 |             |           |    10 | t        | n       |           |              |               | t               | t                | t
 dbuser  | f        | t          | t             | t           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 16679 | f        | n       |           |              |               | f               | f                | f
 bk      | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 17374 | f        | n       |           |              |               | f               | f                | f
 manager | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 17378 | f        | n       |           |              |               | f               | f                | f
 client  | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |               | default_pool |           0 |             |           | 17382 | f        | n       |           |              |               | f               | f                | f
(5 rows)
7.3.2 查看角色权限

客户用户权限查看

SELECT * FROM INFORMATION_SCHEMA.role_table_grants 
where table_schema ='finance' AND grantee='client';

 grantor | grantee | table_catalog | table_schema |      table_name       | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+-----------------------+----------------+--------------+----------------
 omm     | client  | finance       | finance      | branch                | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | manager               | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | fund                  | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | creditcard            | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | creditcard            | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | DROP           | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | creditcard            | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | settlementcard        | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | DROP           | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | settlementcard        | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | personalcustomer      | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | DROP           | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | personalcustomer      | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | insurance             | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | account               | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | account               | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | account               | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | account               | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | account               | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | account               | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | account               | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | account               | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | account               | DROP           | NO           | NO
 omm     | client  | finance       | finance      | account               | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | account               | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | account               | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | enterprisecustomer    | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | DROP           | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | enterprisecustomer    | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | loan                  | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | loan                  | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | loan                  | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | loan                  | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | loan                  | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | loan                  | DROP           | NO           | NO
 omm     | client  | finance       | finance      | loan                  | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | loan                  | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | loan                  | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | buy_i                 | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | DROP           | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | buy_i                 | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | INSERT         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | buy_f                 | UPDATE         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | DELETE         | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | TRUNCATE       | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | REFERENCES     | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | TRIGGER        | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | ALTER          | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | DROP           | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | COMMENT        | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | INDEX          | NO           | NO
 omm     | client  | finance       | finance      | buy_f                 | VACUUM         | NO           | NO
 omm     | client  | finance       | finance      | newmanager            | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | newpersonalcustomer   | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | newenterprisecustomer | SELECT         | NO           | YES
 omm     | client  | finance       | finance      | newbranch             | SELECT         | NO           | YES
(104 rows)

经理用户权限查看

SELECT * FROM INFORMATION_SCHEMA.role_table_grants 
where table_schema ='finance' AND grantee='manager';

 grantor | grantee | table_catalog | table_schema |      table_name       | privilege_type | is_grantable | with_hierarchy 
---------+---------+---------------+--------------+-----------------------+----------------+--------------+----------------
 omm     | manager | finance       | finance      | manager               | UPDATE         | NO           | NO
 omm     | manager | finance       | finance      | fund                  | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | creditcard            | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | settlementcard        | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | insurance             | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | account               | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | loan                  | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | buy_i                 | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | buy_f                 | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newmanager            | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newpersonalcustomer   | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newenterprisecustomer | SELECT         | NO           | YES
 omm     | manager | finance       | finance      | newbranch             | SELECT         | NO           | YES
(13 rows)

7.4 审计技术

7.4.1 建立审计表

Branch(支行)表

CREATE TABLE log_Branch
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Manager(理财经理)表

CREATE TABLE log_Manager
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Loan(贷款)表

CREATE TABLE log_Loan
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Account(账户)表

CREATE TABLE log_Account
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

PersonalCustomer(个人客户)表

CREATE TABLE log_PersonalCustomer
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

EnterpriseCustomer(企业客户)表

CREATE TABLE log_EnterpriseCustomer
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

CreditCard(信用卡)表

CREATE TABLE log_CreditCard
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

SettlementCard(结算卡)表

CREATE TABLE log_SettlementCard
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

fund(基金)表

CREATE TABLE log_fund
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

insurance(保险)表

CREATE TABLE log_insurance
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Buy_I(购买保险)

CREATE TABLE log_Buy_I
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

Buy_F(购买基金)

CREATE TABLE log_Buy_F
(
update_time timestamp,
db_user varchar(40),
orp_tymp varchar(10)
);

在这里插入图片描述

7.4.2 建立审计触发器

Branch(支行)表

CREATE OR REPLACE FUNCTION log_Branch_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Branch VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Branch_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Branch
FOR EACH ROW
EXECUTE PROCEDURE log_Branch_trigger() ;

Manager(理财经理)表

CREATE OR REPLACE FUNCTION log_Manager_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Manager VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Manager_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Manager
FOR EACH ROW
EXECUTE PROCEDURE log_Manager_trigger() ;

Loan(贷款)表

CREATE OR REPLACE FUNCTION log_Loan_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Loan VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Loan_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Loan
FOR EACH ROW
EXECUTE PROCEDURE log_Loan_trigger() ;

Account(账户)表

CREATE OR REPLACE FUNCTION log_Account_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Account VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Account_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE log_Account_trigger() ;

PersonalCustomer(个人客户)表

CREATE OR REPLACE FUNCTION log_PersonalCustomer_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_PersonalCustomer VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_PersonalCustomer_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.PersonalCustomer
FOR EACH ROW
EXECUTE PROCEDURE log_PersonalCustomer_trigger() ;

EnterpriseCustomer(企业客户)表

CREATE OR REPLACE FUNCTION log_EnterpriseCustomer_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_EnterpriseCustomer VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_EnterpriseCustomer_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.EnterpriseCustomer
FOR EACH ROW
EXECUTE PROCEDURE log_EnterpriseCustomer_trigger() ;

CreditCard(信用卡)表

CREATE OR REPLACE FUNCTION log_CreditCard_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_CreditCard VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_CreditCard_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.CreditCard
FOR EACH ROW
EXECUTE PROCEDURE log_CreditCard_trigger() ;

SettlementCard(结算卡)表

CREATE OR REPLACE FUNCTION log_SettlementCard_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_SettlementCard VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_SettlementCard_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.SettlementCard
FOR EACH ROW
EXECUTE PROCEDURE log_SettlementCard_trigger() ;

fund(基金)表

CREATE OR REPLACE FUNCTION log_fund_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_fund VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_fund_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.fund
FOR EACH ROW
EXECUTE PROCEDURE log_fund_trigger() ;

insurance(保险)表

CREATE OR REPLACE FUNCTION log_insurance_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_insurance VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_insurance_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.insurance
FOR EACH ROW
EXECUTE PROCEDURE log_insurance_trigger() ;

Buy_I(购买保险)

CREATE OR REPLACE FUNCTION log_Buy_I_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Buy_I VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Buy_I_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Buy_I
FOR EACH ROW
EXECUTE PROCEDURE log_Buy_I_trigger() ;

Buy_F(购买基金)

CREATE OR REPLACE FUNCTION log_Buy_F_trigger() 
RETURNS TRIGGER AS $$ 
BEGIN
INSERT INTO finance.log_Buy_F VALUES (now(),user,TG_OP);
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER log_Buy_F_trigger
AFTER INSERT OR DELETE OR UPDATE ON finance.Buy_F
FOR EACH ROW
EXECUTE PROCEDURE log_Buy_F_trigger() ;

在这里插入图片描述

7.5 安全策略设置

为了保证帐户安全，如果用户输入密码次数超过一定次数（failed_login_attempts），系统将自动锁定该帐户，默认值为10。次数设置越小越安全，但是在使用过程中会带来不便。
当帐户被锁定时间超过设定值（password_lock_time），则当前帐户自动解锁，默认值为1天。时间设置越长越安全，但是在使用过程中会带来不便。

7.5.1 账户安全策略设置

启动服务器，再使用gsql客户端以管理员用户身份连接postgres数据库

gsql -d postgres -p 26000 -r

配置failed_login_attempts参数

gs_guc reload -D /gaussdb/data/dbnode -c "failed_login_attempts=10"

在这里插入图片描述
配置password_lock_time

gs_guc reload -N all -I all -c "password_lock_time=1"

在这里插入图片描述

7.5.2 密码安全策略设置

配置的加密算法

gs_guc reload -N all -I all -c "password_encryption_type=2"
  • 当参数password_encryption_type设置为0时，表示采用md5方式对密码加密。md5为不安全的加密算法，不建议使用。
  • 当参数password_encryption_type设置为1时，表示采用sha256和md5方式对密码加密。其中包含md5为不安全的加密算法，不建议使用。
  • 当参数password_encryption_type设置为2时，表示采用sha256方式对密码加密，为默认配置。

在这里插入图片描述
配置密码安全参数

gs_guc reload -N all -I all -c "password_policy=1"
  • 参数password_policy设置为1时表示采用密码复杂度校验，默认值；
  • 参数password_policy设置为0时表示不采用任何密码复杂度校验，设置为0会存在安全风险，不建议设置为0，即使需要设置也要将所有openGauss节点中的password_policy都设置为0才能生效。

在这里插入图片描述

8.日志

8.1 账号金额日志记录

针对Account中金额的更新建立日志

8.1.1 建立日志表

针对Account建立日志表

CREATE TABLE finance.AccountRecord
(
update_time timestamp,
account_no VARCHAR(20),
oldaccount_amount DOUBLE PRECISION,
newaccount_amount DOUBLE PRECISION,
orp_tymp varchar(10)
);

在这里插入图片描述

8.1.2 建立日志触发器
CREATE OR REPLACE FUNCTION AccountRecord() 
RETURNS TRIGGER AS $$ 
BEGIN
IF(TG_OP='UPDATE' OR TG_OP='update')
THEN
INSERT INTO finance.AccountRecord VALUES (now(),NEW.account_no,OLD.account_amount,NEW.account_amount,TG_OP);
END IF;
IF((TG_OP='INSERT' OR TG_OP='insert'))
THEN
INSERT INTO finance.AccountRecord VALUES (now(),NEW.account_no,NULL,NEW.account_amount,TG_OP);
END IF;
IF((TG_OP='DELETE' OR TG_OP='delete'))
THEN
INSERT INTO finance.AccountRecord VALUES (now(),NEW.account_no,OLD.account_amount,NULL,TG_OP);
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER AccountRecord
BEFORE INSERT OR DELETE OR UPDATE OF account_amount ON finance.Account
FOR EACH ROW
EXECUTE PROCEDURE AccountRecord() ;

在这里插入图片描述

8.2 openGauss日志管理

8.2.1系统日志

运行时日志

cd /var/log/gaussdb/omm/pg_log/dn_6001
ls

在这里插入图片描述

安装卸载时日志

 cd /var/log/gaussdb/omm/om
 ll -h

在这里插入图片描述

8.2.2 操作日志

操作日志是指数据库管理员使用工具操作数据库时以及工具被openGauss调用时产生的日志。如果openGauss发生故障，可以通过这些日志信息跟踪用户对数据库进行了哪些操作，重现故障场景。

cd /var/log/gaussdb/omm/bin

在这里插入图片描述

8.2.3 审计日志

连接数据库。

gsql -d postgres -p 26000 -r

选择日志维护方式

  • 配置审计文件占用磁盘空间的大小
    如下命令设置成默认值1024 MB。

    gs_guc reload -N all -I all -c "audit_space_limit=1024MB"

    在这里插入图片描述

  • 配置审计文件个数的最大值
    如下命令设置成默认值1048576。

    gs_guc reload -N all -I all -c "audit_file_remain_threshold=1048576"

    在这里插入图片描述

  • 备份审计文件
    获得审计文件所在目录

    SHOW audit_directory;

    在这里插入图片描述
    将审计目录整个拷贝出来进行保存。

    cp -r /var/log/gaussdb/omm/pg_audit/dn_6001 /var/log/gaussdb/omm/pg_audit/dn_6001_bak

    查看备份
    在这里插入图片描述

9 数据库备份

进行多种类型文件备份

9.1 生成备份文件

创建存储备份文件的文件夹

mkdir -p /home/omm/logical/finance_backup

执行gs_dump，导出的MPPDB_backup.sql文件格式为纯文本格式

gs_dump -U omm -W Bigdata@123 -f /home/omm/logical/finance_backup/MPPDB_backup.sql -p 26000 finance -F p

在这里插入图片描述

执行gs_dump，导出的MPPDB_backup.tar文件格式为tar格式

gs_dump -U omm -W Bigdata@123 -f  /home/omm/logical/finance_backup/MPPDB_backup.tar -p 26000 finance -F t

在这里插入图片描述

执行gs_dump，导出的MPPDB_backup.dmp文件格式为自定义归档格式

gs_dump -U omm -W Bigdata@123 -f  /home/omm/logical/finance_backup/MPPDB_backup.dmp -p 26000 finance -F c

在这里插入图片描述

执行gs_dump，导出的MPPDB_backup文件格式为目录格式

gs_dump -U omm -W Bigdata@123 -f /home/omm/logical/finance_backup/MPPDB_backup -p 26000  finance -F d

在这里插入图片描述

9.2 查看备份文件

ll /home/omm/logical/finance_backup/

在这里插入图片描述

BkbK-
关注
专栏目录
数据库设计原则（金融行业）
eagle89的专栏
07-19 5174
一个好的数据结构设计或者说数据库设计，不仅能应对复杂的业务变化，更可以应对未来海量的数据扩容，同时数据结构逻辑清晰更方便业务人员去使用数据。     适用场景：互联网高并发、高吞吐、海量数据、强一致性要求高、mysql数据库、互联网金融业务。      一、数据库设计框架思维     1. 数据库设计第一原则，领域设计 + 恒等式设计 + 数据流转设计     注：这三个思想后面会单独写...
金融业分布式数据库选型及HTAP场景实践
杨建荣的学习笔记
05-27 475
作为数据基础设施的重要组成部分，数据库在其中扮演着重要的角色。近些年来，数据库整体发展也呈现出较之以往很大的不同。其一、是开源数据库受到更为广泛的关注，从多家机构的最新报告来看，开源数据库无论从产品数量还是受关注程度都超过商业数据库。开源这一新模式，正成为未来数据库发展的主流。其二、是云计算成为未来主要资源供给方式得到普遍共识。已经有越来越多的企业选择在云上构建基础环境，...
数据库课程设计：基于 MySQL 的实现与实践
weixin_58606202的博客
09-08 923
数据库课程设计是计算机相关专业中一项重要的实践课程，通过设计与实现一个实际应用的数据库系统，学生可以深入理解数据库的设计原理与实际操作能力。应在设计阶段合理设置外键和约束。在数据库设计中，除了完成基本的表结构和数据操作外，性能和数据完整性也是关键考虑因素。使用 MySQL 语句创建数据库表，定义字段、数据类型、主键和外键，并实现数据库中的约束。在完成数据库表的创建之后，可以进行基本的数据操作：插入数据、查询数据、更新数据、删除数据等。根据数据库的设计，开发与之交互的应用程序或界面，最终构建一个完整的系统。
建库建表添加数据查询数据
qq_68040915的博客
06-08 191
创建数据库 create database financingSys character set utf8; create database 数据库名称 character set utf8;用库 use financingSys; use 库名称;创建数据表 create table financingProduct( 列名称 列类型 约束 id varchar(20) primary key, risk int not null comment ‘r1:1,r2:2,R3:3’, income varc
金融级分布式数据库架构设计要点
Enmotech的博客
02-03 1507
行业背景 银行业从最初的手工记账到会计电算化，到金融电子化，再到现在的金融科技，可以看到金融与科技的结合越来越紧密，人工智能、大数据、物联网、区块链等新兴技术改变了金融的交易方式，为金融行业的创新前行提供了源源不断的动力。同时互联网金融的兴起是一把双刃剑，带来了机遇的同时也带来了挑战。普惠金融使得金融的门槛降低，更多的普通大众参与到金融活动中，这让金融信息系统承受了越来越大的压力。于是我们可以...
头歌的数据库的第二次作业的答案
蒋劲豪的博客
10-13 3770
【代码】头歌的数据库的第二次作业的答案。
数据库课程设计jsp1159金融交易页面ssh.sql
09-07
数据库课程设计，毕业设计，数据库设计
分布式数据库课程设计报告.pdf
05-27
本课程设计报告主要探讨了分布式数据库在学生信息管理系统中的应用，以及分布式数据库的相关理论和设计方法。 一、课题研究的意义 分布式数据库在当今大数据时代具有重要的价值。学生信息管理系统是教育机构管理...
数据库课程设计ssm790基于ssm技术的金融支付终端管理系统设计与实现+jsp.sql
09-03
数据库课程设计ssm790基于ssm技术的金融支付终端管理系统设计与实现+jsp.sql
数据库课程设计宿舍管理信息系统样本.doc
11-21
"数据库课程设计宿舍管理信息系统样本.doc" 在当今时代，计算机技术的发展对广大顾客提供了更为周到和便捷的服务。当前各行各业广泛使用专用系统，其内容范畴跨越了教诲科研、文化事业、金融、商业、新闻出版、娱乐...
5396-微信小程序的小微金融企业的设计与实现（源码+数据库）.zip
最新发布
09-16
本系统主要针对计算机相关专业的正在做毕业设计的学生和需要项目实战练习的学习者，可作为毕业设计、课程设计、期末大作业。本系统主要针对计算机相关专业的正在做毕业设计的学生和需要项目实战练习的学习者，可作为...
Insurance-Management-System
04-16
保险管理系统 保险单数据管理系统是一个基于Web的项目，旨在跟踪保险单的详细信息，客户详细信息和公司详细信息。 该系列网页是一个在线保险分析和信息管理系统，可轻松访问有关保险人员和资源的信息。 登录到策略持有者模块时，用户可以查看自己的个人详细信息。 该项目对于任何类型的保险公司都可以通过在线管理保险明细，批准客户保险，处理保险单明细和各种保险流程非常有用。 对于需要管理其车辆，设备，建筑物和其他资源的保险的组织，保险管理系统是一个完整的解决方案。 该保险管理系统可以有效地管理公司，进行记录，提供即时访问权限，并且可以提高生产率。 它将显示有关保险及其类型的详细信息，还将显示有关与相应保险类型或保险单不同的工期计划的详细信息。 开发的系统的主要目的是允许管理员用户注册被保险人的姓名，出生日期，居住地址，病历以及保单详细信息。
SQL Server用触发器强制执行业务规则
03-03
Microsoft:registered: SQL Server:trade_mark: 2000 提供了两种主要机制来强制业务规则和数据完整性：约束和触发器。触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。唤醒调用触发 器以响应 INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表，并可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚。
某电商网站的数据库设计（6）——创建查询销售数据的视图
睿思达DBA
11-28 1875
电商数据库设计
7、MySQL视图
weixin_42749734的博客
11-14 245
1 什么是视图 视图是一种虚拟表。 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句. 视图向用户提供基表数据的另一种表现形式 2 视图的作用 权限控制时可以使用 比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用 简化复杂的多表查询 视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图就可以获取想要得到的信息(
为投资表property实现业务约束规则-根据投资类别分别引用不同表的主码
weixin_66477210的博客
04-25 1145
触发器是与某个表绑定的命名存储对象，与存储过程一样，它由一组语句组成，当这个表上发生某个操作(insert,delete,update)时，触发器被触发执行。评测程序将执行你的语句创建触发器，然后对相关表执行insert操作，来触发你定义的触发器，然后查看预期的结果有没有发生。如果执行insert语句后，发生的结果与预期结果一致即过关。与创建存储过程、函数一样，创建触发器时也要用delimiter语句重新指定触发器定义语句的界符(触发器内语句的分隔符仍为分号)，在触发器定义之后，再把界符更改回去。
案例题目.1
dr_abcd的博客
01-16 91
#include <stdio.h> int main() { printf("Hello world\n"); return 0; } dr_abcd
MySQL数据库—创建数据库与数据表
Junds0的博客
10-18 4137
关系数据库中，数据表是以行和列的形式组织而成的二维表，数据存在于行和列相交的单元格中，表结构中的一行表示一条唯一的记录，一列表示一个字段，唯一标识一行记录的属性或属性组称为主键。创建完数据库后需要创建数据表，并确定表中各个字段列的名称、数据类型、数据精度、是否为空等属性。当系统需求变更或设计之初考虑不周等情况发生时，就可能需要对数据表的结构进行修改，包括添加新的约束，修改及删除原有约束等。当系统需求变更或设计之初考虑不周等情况发生时，就可能需要对数据表的结构进行修改，包括修改数据表名及字段排列顺序等。
头歌（数据库实验八 视图）--第3关：根据视图建立视图
Zjyzzy123456789的博客
04-17 5015
为了完成本关任务，你需要掌握： 第一题： 根据xsdxx视图建立视图xsdhytj,显示会员号hyh，姓名name和实际付款sjfk的合计金额(命名为hjje) 按合计金额的降序排列。第二题： 根据xsdxx视图建立视图xsdgytj,显示雇员号gyh，姓名xm和实际付款sjfk的合计金额(命名为hjje) 按合计金额的降序排列。任务： 根据xsdxx视图建立视图。
数据库课程设计与实践要点详解
资源摘要信息:"数据库课程设计的过程.zip" 涉及到的知识点主要包括数据库的概念、数据库设计的步骤、以及数据库课程设计的具体实践过程。数据库是计算机系统中按照数据结构来组织、存储和管理数据的仓库。在信息时代...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

BkbK-

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值