9.1 数据库完整性概述

数据库完整性–数据的正确性,有效性,西安共性

列级约束

对属性的数据类型,等的定义

元祖约束

元组中各个属性之间的约束关系

实现完整性约束的方法

1. 提供定义机制
2. 提供检查方法
3. 违约处理

9.4用户定义的完整性

9.5触发器

一类用户定义在关系数据表上的一类由事件驱动的特殊过程,用编程的方法实现复杂的业务规则

是一种特殊类型的存储过程

可用触发器完成很多数据库完整性保护的过程

1. 复杂的业务规则
2. 比较数据修改前后状态

SQL:CREATE TRIGER命令

事务

原子性:不可分割

一致性:事务执行结果必须从一个状态转换到另一个状态时保持一致,即若中间出现错误,则将所有操作撤销

事务应当具有ACID性质，
A是原子性(atomic)：事务中包含的各项操作必须全部成功执行或者全部不执行。任何一项操作失败，将导致整个事务失败，其他已经执行的任务所作的数据操作都将被撤销，只有所有的操作全部成功，整个事务才算是成功完成。
C是一致性(consistent)：保证了当事务结束后，系统状态是一致的。那么什么是一致的系统状态？例如，如果银行始终遵循着”银行账号必须保持正态平衡”的原则，那么银行系统的状态就是一致的。上面的转账例子中，在取钱的过程中，账户会出现负态平衡，在事务结束之后，系统又回到一致的状态。这样，系统的状态对于客户来说，始终是一致的。
I是隔离性(isolated)：使得并发执行的事务，彼此无法看到对方的中间状态。保证了并发执行的事务顺序执行，而不会导致系统状态不一致。
D是持久性(durable)：保证了事务完成后所作的改动都会被持久化，即使是发生灾难性的失败。可恢复性资源保存了一份事务日志，如果资源发生故障，可以通过日志来将数据重建起来。

事务应处在下列状态之一

活动状态

失败状态:意味着要回滚

中止状态:数据库已经回滚完毕,恢复到提交前状态

提交状态:当数据更改完全写入磁盘时,进入提交状态

10.3.1事务故障的恢复

1.反向扫描日文件日志,查找该事务的更新操作

2.对该事务的更新操作执行你操作

3.继续反向扫描,直到读到此事务的开始操作

10.3.2系统故障恢复

1.正向扫描日志文件,列出Redo队列,Undo队列

日志

<u>登记两条原则 </u>

<u>1.登记次序严格按并行事物执行顺序 </u>

<u>2.先写日志文件,后写数据库 </u>

1. 分析系统功能

   1. ER图

      

   2. 关系模型

      1. 对象的属性描述

         客户(<u>客户编号</u>,电子邮箱,登录密码,身份证,联系方式,姓名)
         银行卡(<u>卡号</u>,类型,客户编号)
         理财产品(<u>理财产品编号</u>,理财年限,购买金额,产品名称,产品描述)
         基金(<u>基金编号</u>,基金名称,基金类型,风险等级,基金管理者,基金金额)
         保险(<u>保险编号</u>,保险名称,适用人群,保险项目,保险金额,保险年限)
         资产(<u>客户编号</u>,<u>资产编号</u>,状态,购买数量,收益,购买时间)

      2. 对象之间关系描述

         一个客户可以购买多种理财产品
         可以办理多张银行卡
         一个客户可以购买多种保险
         一个客户可以购买多种基金
         

2. 物理设计与数据字典

   1. 表及其属性

      client(c_id,c_name,c_mail,c_id_card,c_phone,c_password)
      bank_card(b_id,b_type,b_c_id)
      property(p_id,p_c_id,p_i_id,p_status,p_amount,p_get_time,p_income,p_type)
      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)
      f_product(fp_id,fp_name，fp_description，fp_amount，fp_year)

   2. 数据字典

      1. 表1-1 client表

         字段名称	字段类型	约束	说明
         c_id	INTEGER	PRIMARY KEY	客户编码
         c_name	VARCHAR(100)	NOT NULL	客户名称
         c_mail	CHAR(30)	UNIQUE	客户邮箱
         c_id_card	CHAR(20)	UNIQUE NOT NULL	客户身份证
         c_phone	CHAR(20)	UNIQUE NOT NULL	客户手机号
         c_password	CHAR(20)	NOT NULL	客户登录密码

      2. 表1-2bank_card表

         字段名称	字段类型	约束	说明
         b_id	INTEGER	PRIMARY KEY	银行卡号
         b_type	CHAR(20)	NOT NULL	银行卡类型
         b_c_id	INTERGER	NOT NULL FOREIGNKEY	客户编号

      3. 表1-3 property资产表

         字段名称	字段类型	约束	说明
         p_id	INTEGER	PRIMARY KEY	资产编号
         p_c_id	INTEGER	NOT NULL FOREIGN KEY	客户编号 外键,对应client中的c_id
         p_i_id	INTEGER	NOT NULL FOREIGN KEY	商品编号 ,对应三种产品的id
         p_status	CHAR(20)	NOT NULL	状态
         p_amount	INTERGER	NOT NULL	购买数量
         p_get_time	DATE	NOT NULL	购买时间
         p_income	INTERGER		收益
         p_type	INTERGER	NOT NULL	1代表是理财产品,2代表保险,3代表基金

      4. 表1-4 f_products 理财产品表

         字段名称	字段类型	约束	说明
         fp_id	INTEGER	PRIMARY KEY	理财产品编号
         fp_year	INTEGER		理财年限
         fp_amount	INTERGER		购买金额
         fp_name	VARCHAR(200)	NOT NULL	产品名称
         fp_description	VARCHAR(2000)	NOT NULL	产品描述

      5. 表1-5 insurance 保险表

         字段名称	字段类型	约束	说明
         i_id	INTEGER	PRIMARY KEY	保险编号
         i_name	VARCHAR(100)	NOT NULL	保险名称
         i_person	CHAR(30)		适用人群
         i_project	VARCHAR(200)		保险项目
         i_amount	INTERGER		保险金额
         i_year	CHAR(20)	NOT NULL	保险年限

      6. 表1-6 fund基金表

         字段名称	字段类型	约束	说明
         f_id	INTEGER	PRIMARY KEY	基金编号
         f_name	VARCHAR(100)	NOT NULL	基金名称
         f_type	CHAR(30)		基金类型
         f_risk	INTERGER		风险等级
         f_manager	INTERGER	NOT NULL	基金管理者
         f_amount	INTERGER	NOT NULL	基金金额

3. 创建数据库表

   1. 创建finance数据库作为项目数据库，数据库编码为UTF-8。（2分）

      1. 切换到omm用户
         
      2. 启动数据库服务
         
      3. 登入postres数据库(为了在里面创建新的数据库)
         
      4. 创建finance数据库,设置编码为UTF-8
         

   2. 连接finance数据库，创建名为finance的schema，并设置finance为当前的schema。（2分）

      1. 连接finance数据库
         
      2. 创建名为finance的schema，并设置finance为当前的schema
         

   3. 在finance模式下完成金融管理系统中所有数据库对象（数据表）的创建，并完成数据的填充。其中客户数据不少于20条，银行卡数据不少于10条，其他数据不少于5条；（6分）

      1. 数据表创建

         1. 客户表创建

            

            

         2. 银行卡表创建
            
            

         3. 理财产品表创建
            

            

         4. 保险信息表创建

            
            

         5. 基金信息表创建

            

            

         6. 资产信息表创建

            

            

      2. 添加数据

         1. 客户信息添加
            

         2. 银行卡数据填充
            

         3. 理财产品填充

            INSERT INTO finance.f_products

            (fp_id ,fp_year ,fp_amount ,fp_name ,fp_description ) VALUES

            (1 ,2 ,4000000 ,’储蓄’ ,’储蓄理财产品’ ),

            (2 ,1 ,3000 ,’债券’ ,’债券是政府、企业、银行等债务人为筹集资金,按照法定程序发行并向债权人承诺于指定日期还本付息的有价证券’),

            (3 ,1 ,200000 ,’股票’ ,’股份公司为筹集资金而发行给各个股东作为持股凭证并借以取得股息和红利的一种有价证券’),

            (4 ,3 ,2000 ,’国债’ ,’国家以其信用为基础，按照债的一般原则，通过向社会筹集资金所形成的债权债务关系’),

            (5 ,2 ,10000 ,’大宗商品’ ,’与大宗商品期货挂钩的理财产品。目前市场上主要以挂钩黄金、石油、农产品的理财产品居多’)

         4. 保险产品填充

            INSERT INTO finance.insurance(i_name,i_id,i_amount,i_person,i_year,i_project) VALUES

            (‘意外保险’,3,5000,’所有人’,20,’平安保险’),

            (‘医疗保险’,4,2000,’所有人’,20,’平安保险’),

            (‘健康保险’,1,2000,’老年人’,10,’平安保险’),

            (‘人寿保险’,2,3000,’老年人’,10,’平安保险’),

            (‘财产损失保险’,5,1500,’中年人’,3,’平安保险’);
            

         5. 基金表格填充

            INSERT INTO finance.fund (f_id,fp_name,f_type,f_risk,f_manager,f_amount) VALUES

            (1,’股票基金’,’股票型’,3,1,200000),

            (2,’债券基金’,’债券型’,2,2,3000),

            (3,’投资基金’,’债券型’,3,2,1000),

            (4,’国债’,’货币型’,1,4,2000),

            (5,’期货’,’期货型’,2,5,1500);

            

         6. 资产表格填充

            INSERT INTO finance.property

            (p_id , p_c_id ,p_i_id , p_status , p_amount ,p_get_time ,p_income ,p_type ) VALUES

            (1 ,1 ,1 ,’可用’ ,100 ,’2021-11-20’ ,2000 ,1 ),

            (2 ,1 ,2 ,’可用’ ,100 ,’2021-11-21’ ,3020 ,1 ),

            (3 ,1 ,1 ,’冻结’ ,100 ,’2021-11-20’ ,2000 ,2 ),

            (4 ,1 ,4 ,’可用’ ,101 ,’2021-11-10’ ,3000 ,3 ),

            (5 ,2 ,5 ,’可用’ ,100 ,’2021-11-20’ ,2000 ,2 )

            

   4. 对表添加外键约束，在银行信息表和资产信息表中，都存在每个银行卡必须有一个持卡者、每份资产必须都有一个资产拥有者这样的对应关系。因此针对这种对应关系，创建外键约束。（4分）

      添加外键约束

      1. 信用卡的外键
         ALTER TABLE finance.bank_card ADD CONSTRAINT fk_c_id FOREIGN KEY (b_c_id) REFERENCES finance.client(c_id) ON DELETE CASCADE;
         
      2. 资产的外键
         ALTER TABLE finance.property ADD CONSTRAINT fk_pro_c_id FOREIGN KEY (p_c_id) REFERENCES finance.client(c_id) ON DELETE CASCADE;
         

   5. 在理财产品表、保险信息表和基金信息表中，都存在金额这个属性，在现实生活中，金额不会存在负数。因此针对表中金额的属性，增加大于0的约束条件。（4分）

      增加大于0的约束

      1. f_products表
         ALTER table finance.f_products ADD CONSTRAINT c_p_mount CHECK (fp_amount >=0);
         

      2. fund表

         ALTER table finance.fund ADD CONSTRAINT c_f_mount CHECK (f_amount >=0);
         

      3. insurance表
         ALTER table finance.insurance ADD CONSTRAINT c_i_mount CHECK (i_amount >=0);
         

   6. 输出：

      1. 输出查询所有表的数据字典的查询语句和结果，可以截图或者复制黏贴代码。(基本都在之前有展示,这里对所有的表及schema进行了一次查询)
         select * from pg_tables;
         

      2. 输出查询所有表的数据的查询语句和结果，可以截图或者复制黏贴代码。

         1. client表
            select * from finance.client;

            

         2. bank_card表
            select * from finance.bank_card;
            

         3. fund表
            select * from finance.fund;
            

         4. f_products表
            select * from finance.f_products;
            

         5. insurance表
            select * from finance.insurance;
            

         6. property表
            select * from finance.property;
            

      3. 输出所有创建约束的语句和创建结果，可以截图或者复制黏贴代码(这在之前已经放上了)

4. 对表中的数据进行查询操作

   1. l 至少完成1条单表查询和1条表查询。（4分）
      单表查询–查询客户表中所有的客户id

      select c_id from finance.client;
      

      表查询
      select c_name from finance.client;
      

   2. l 至少完成两条聚合查询，例如查询用户表中有多少个用户；查询保险信息表中，保险金额的平均值等。（4分）
      查询property表的income平均值
      select avg(p_income) from finance.property
      
      查询用户表中用户总数
      select count(*) from finance.client;
      

   3. l 至少完成3条连接查询，例如：半连接、反连接、多表查询和子查询。（6分）

      1. 等值连接查询,查询每个用户及其拥有的资产
         select finance.client.*,finance.property. *
         from finance.client,finance.property
         where finance.client.c_id=finance.property.p_c_id;
         
      2. 自身连接,在fund表中对每一个基金项,查询manager 和自身id相同的基金
         select first.*,second.f_manager
         from finance.fund first,finance.fund second
         where first.f_id = second.f_manager
         
      3. 子查询,查询和p_id=3的资产处于相同状态的资产(然后发现就它自己冻结着)
         select * from finance.property
         where p_status=
         (select p_status from finance.property where p_id=3);
         

   4. 至少完成1条ORDER BY查询。（2分）
      将资产按照收益逆序输出
      select * from finance.property order by p_income desc;
      

   5. 至少完成1条GROUP BY……HAVING查询。（4分）

   6. 查询收益在2000及以上的资产及其平均收益,将结果按p_id分组显示,且只显示平均收益大于2500的组
      select *,avg(p_income) from finance.property
      where p_income>=2000

      group by p_id
      having avg(p_income)>=2500;
      

   7. 查询基金,将其按f_id分组,且只显示平均数量大于等于2000的组
      select *,avg(f_amount) from finance.fund
      group by f_id
      having avg(f_amount)>=2000;
      

4.5 创建视图和索引

1. 创建一个视图

   查看client中属性,除了c_mail属性
   create view see_client
   as
   select c_id,c_name,c_id_card,c_phone,c_password
   from finance.client
   

2. 修改视图

   修改视图所属schema
   alter view see_client set schema finance;
   

3. 使用视图进行查询
   

4. 重命名视图
   alter view finance.see_client
   rename to oh_my_god;
   

5. 删除视图
   

6. 创建索引
   为客户电话号码创建索引
   CREATE INDEX index_phone on finance.client(c_phone) ;
   

7. 重建索引
   对整个客户表重建索引
   

8. 重命名索引
   alter index finance.index_phone rename to ind_phone;
   

9. 删除索引
   

1.4.6 数据修改和删除（5分）

任务：

l 修改数据：至少修改2个不同的数据。（3分）

1. 将client表中第1个元素的值c_name改为’年没怀念’
   之前的表
   
   修改语句:
   update finance.client
   set c_name=’年没怀念’
   where c_id=1;
   

   修改后表情况
   

2. 将property中p_get_time小于2021-11-11 00:00:00的值修改为2021-11-19 00:00:00

   修改前状态
   
   修改
   update finance.property
   set p_get_time=’2021-11-19’
   where p_get_time<’2021-11-11’;
   
   修改后状态
   

l 删除指定数据：至少删除2个不同数据表的数据。（2分）

1. 将finance.property中p_amount=101的数据删除

   删除前表情况

   

   删除
   delete from finance.property where p_amount=101;

   

   删除后表情况

2. 删掉c_id>20的用户信息

   删除前表情况

   

   删除操作

   delete from finance.client where c_id>20;
   

   删除后表情况

1.4.7 创建新用户（6分）

任务：

1. 新用户的创建和授权：创建用户dbuser，密码为Gauss#3demo；给用户dbuser授予finance数据库下银行卡信息表的查询和插入权限，并将finance模式的权限也授予dbuser用户。（3分）

   创建用户
   

   给用户dbuser授予finance数据库下银行卡信息表的查询和插入权限，并将finance模式的权限也授予dbuser用户
   

2. 新用户连接数据库：使用新用户连接finance数据库；访问finance数据库的银行卡信息表。（2分）

   新用户连接

   
   新用户访问finance数据库的银行卡信息表

   

3. 删除finance模式。（1分）
   

1.4.8 使用jdbc连接openGauss数据库

1. 创建测试数据库demo；
   

2. 创建名为demo的schema，并设置demo为当前的schema
   
   设置搜索路径为demo

   SET search_path TO demo;

3. 创建测试表websites（id,name,url），数据为（’1’, ‘openGauss’, ‘https://opengauss.org/zh/')，(‘2’, ‘华为云’, ‘https://www.huaweicloud.com/'), (‘3’, ‘openEuler’, ‘https://openeuler.org/zh/'), (‘4’, ‘华为support中心’, ‘https://support.huaweicloud.com/')。（3分）
   create table websites
   (
   id Integer primary key,
   name varchar(200),
   url varchar(200)

   );
   
   插入数据

   INSERT INTO websites (id,name,url) VALUES

   (‘1’, ‘openGauss’, ‘https://opengauss.org/zh/'),

   (‘2’, ‘华为云’, ‘https://www.huaweicloud.com/'),

   (‘3’, ‘openEuler’, ‘https://openeuler.org/zh/'),

   (‘4’, ‘华为support中心’, ‘https://support.huaweicloud.com/')

   

4. 查看入站规则是否包括tcp26000端口
   

5. 查看java版本是否为8
   

6. 创建项目,引入postgres.jar包
   

7. 写下测试程序如下
   
   

8. 结果
   

本实验完成