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

数据库课程设计

文章目录

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/

在这里插入图片描述

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

打赏作者

BkbK-

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

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

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

打赏作者

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

抵扣说明:

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

余额充值