《数据库实现》-- 第一章 数据库基本概念

Posted on In , Views: Symbols count in article: 2.7k Reading time ≈ 10 mins.
《数据库实现》-- 第一章 数据库基本概念

第一章 – 数据库基本概念

数据库语言

DML(data manipulation language)

对数据进行增删改查

DDL (data definition language)

数据模式有一些定义来说明， 这些定义由DDL 来表达。ddl 以一些指令为输入， 输出放在数据字典中。

一致性约束

  • 域约束（domain constraint）：一个所有取值构成的约束。 是完整性约束的基本形式。 整数型就表示只能取值整数。
  • 参照完整性（referential integrity）： 一个关系中某个属性的值必须在另外一个关系某个属性中出现。其实就是满足相关性。
  • 断言（assertion）：数据库某个时刻必须满足的某个条件。 域约束和参照完整性是断言的子集。
  • 授权（authorization）： 读权限， 插入权限，更新权限，删除权限

模型设计

E-R 模型

模型设计避免2个问题：

  1. 冗余， 这个理念类似 范式概念；比如， 一个课程中， 有老师列， 在老师列中，可以只要老师id或老师姓名， 而不需要把老师的所有信息，比如系，性别 等信息带进去。
  2. 不完整， 不要漏掉一些信息，导致无法建模； 比如整个设计只为 带课程的老师进行设计，但实际上还有未带课程的老师；

实体-联系模型

  • 实体/实体集
  • 联系/联系集 : {(e1, e2, e3, … en)| e1∈E1, e2∈E2, ….}
  • 属性
    • 简单/复合， 复合表示属性可以拆解或细化更细粒度， 在不同场景下，会对细度有不同的要求
    • 单值/多值， 当多值时，需要{}, 比如一个老师有多个电话号码{phone_number}
    • 派生， 派生属性不存储，当需要时再计算， 比如一个老师记录了出生日期， 这个时候，可以直接计算出他的生日。
  • 映射基数： mapping cardinality, 基数比率， 一个实体通过一个联系集关联实体的个数
    • 1v1： one-to-one
    • 1vn: one-to-many
    • nv1: many-to-one
    • nvn: many-to-many
  • 参与约束， 有可能一个实体集全部参与联系集中，也可能部分。

关系模型

范式

  • 第一范式， 属性是原子不能细分的
  • BCNF Boyce-Codd Normal Form: 消除利用函数依赖发现的冗余，
    • 函数依赖， 对于所有元组t1和t2， 如果t1[a] = t2[a], 则t1[b] = t2[b]

基本概念

结构

关系relation用表table来指代， 元组（tuple）指代行， 属性attribute指代列。对于每个属性，都存在一个取值范围， 它被称为该属性的域domain。 如果域中元素不可再分， 则域是原子的atomic。

键

  • 超键(superkey)： 一个或多个属性的集合， 这个集合的组合可以唯一标识一个元组。

  • 候选键(candidate key)：最小的超键。 如果id 能唯一表示一个tuple， 那么含id的超集都可以是超键，但候选键表示满足唯一表示的最小子集（即id）。

  • 主键(primary key)： 被设计者选中的候选键。 主键很少改动

  • 外键（foreign key）：一个关系（r1）中属性集（a1）为另外一个关系（r2）的主键，a1 被称为r2的外键； r1 称为外键依赖的referencing relation， r2 称为外键的referenced relation。

索引

为了高效找到索引属性上给定值的元组，而不是扫描所有的元组，可以对某属性简历索引。 默认主键会创建索引。
create index studentid_index on student(ID)

大对象

大对象数据类型clob， 二进制大对象 blob。 通常查询语言不会直接获取大对象， 而是获取大对象定位器， 然后利用大对象定位器来一点点读取数据（有点类似文件系统read）

自定义类型

  • distinct type， 本节介绍

  • structured data type

distinct type

1
2
3
4
5
6
7
create type dollars as numeric(12, 2) final;
create type pounds as numeric(12, 2) final;

drop type
alert type

create table department (budget dollars);

因此不能直接对dollars和pounds 进行加减直接操作，尽管他们都是numeric(12, 2)， 也不可用 dollars + 20， 这个适合需要cast 转到另外一个域
cast (department.budget to numeric(12,  2))

另外域和自定义类型相似但有区别
create domain ddollars as numeric(12, 2) constraint dollar_test check (value > 29000.00);

  1. 域可以加声明约束， 比如not null 或default 或 constraint， 而自定义类型不可以

  2. 域不是强类型， 可以一个类型转另外一个类型， 只要它们基本类型相同。

关系运算

  • 自然连接， 其实就是inner join

  • 笛卡尔积， 2个关系的所有对，不关注属性值匹配

关系代数

符号 使用例子 解释
select: σ 选择，类似于SQL中的where。注意，和SQL中的select不一样。
project: Π 投影，类似于SQL中的select
rename: ρ 重命名，类似于SQL中的as
assignment：← 赋值。
union: ∪ 集合并，类似于SQL中的union
set difference: – 集合差，sql中except
intersection: ∩ 集合交，SQL 中intersect/intersect all
Cartesian product: x 笛卡尔积，类似于SQL中不带on条件的inner join
natural join: ⋈ 自然连接，类似于SQL中的inner join， join… on..,  join … using…, natural join

SQL

  • 类型：

    • DDL Data definition languation

      • 视图定义 view definition : 定义视图的命令

    • DML data manipulation languation

  • 属性：

    • 完整性（integrity）： 完整性约束

    • 嵌入式SQL/动态sql embeded sql/dynamic sql： 如何潜入到其他编程语言中

    • 授权

外连接

  • 左外连接 left outer join

  • 右外连接 right outer join

  • 全外连接 full outer join

外连接中 on 和where 是不一样的， on 不会过滤器元组， 但where 会过滤

1
2
select * from student left outer join takes on true
where student.id = takes.id;

视图

为什么需要视图

  1. 处于安全考虑， 不让某些用户看到所有的字段。

视图更新

一般不允许对视图进行更新操作， 但如果定义视图的ddl 满足：

  • 只从一个table进行from

  • select 只包含属性名，没有额外的表达式，如聚集或distinct

  • 任何没有出现在select子句中的属性可以取空值

  • 不包含group by 或having

物化视图

数据库允许存储视图关系， 如果用于定义视图的实际关系改变， 视图也跟着改变。

完整性约束

alert table xxx add tttt
ttt 即为限制 constraint

单个关系上的约束

  • not null

  • unique： unique(a1, a2, a3, ….), 只能从属性集中取值， 也可以为null， 除非限制了not null

  • check : check （semester in (‘fall’, “winter”, “spring”, “summer”)）， check (time_slot_id in select time_slot_id from time_slot),  后一个开销可能有点大， 因为更新time_slot 或section 时都需要做检查。

参照完整性

一个关系中某个属性的取值必须在另外一个关系的某个属性的取值集内. referential-intergrity constraint/subset dependency.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table department
(dept_name varchar(20),
building varchar(15),
budget   numveric(12,2),
primary key (dept_name)
);

create table course
(
course_id varchar(7),
title varchar(50),
dept_name varchar(20),
credits numeric(2, 0)m
primary key (course_id),
foreign key (dep_name) references department.
);

foreign key (dept_name) references department.

还有一种简单方式  

1
2
3
4
5
6
7
8
create table course
(
course_id varchar(7),
title varchar(50),
dept_name varchar(20) references department.
credits numeric(2, 0)m
primary key (course_id)
);

course 中dept_name 为参照关系department中dept_name的外键。
参照性约束不要求dept_name为主键， 但sql中，如果使用references , 则属性列必须是被参照关系（department）的候选键， 要么是primary key， 要么使用unique 约束。
违法完整性约束时， 通常会拒绝导致完整性被破坏的操作（更新操作的事务会被回滚）
foreign key 则可以高级操作， 如果被参照关系上（department）上进行删除或修改时， 破坏了约束， 可以采取一些措施来修改参照关系（course）中元组来恢复完整性约束， 而不是简单拒绝操作。

1
2
3
4
5
6
7
8
9
10
11
create table course
(
course_id varchar(7),
title varchar(50),
dept_name varchar(20),
credits numeric(2, 0)m
primary key (course_id),
foreign key (dep_name) references department.
        on delete cascade
        on update cascade,
);

延迟约束检查
比如 一张表内， name 为主键， spouse（配偶） 是person的一个外键（依赖name）， 加速mary和john 是夫妻， 无论是第一次插入john还是mary， 都会违反完整性约束， 直到2个人都插入时， 才能满足完整性约束。

通过增加initially deferred加入到约束语句中或者’set constraints xxxx deferred’做为事务的一部分， 在一个事务中， 约束性检查会延迟到事务提交时再进行。

断言

断言就是一个谓词， 域约束和参照完整性都是断言的子集。

1
2
3
4
5
6
7
8
9
10
create assertion credit_earned_constraint check
(
    not exists (
        select id from student
        where tot_cred <> (
                            select sum(credits) from takes natural join course
                            where students.id = takes.id and grade is not null 
                            )
    ) 
);

事务

acd：

  • atomicity : 事务要么成功， 要么失败， 没有中间状态， 成功了就提交了所有修改， 失败了就没有任何修改
  • consistency：所有操作符合预期
  • isolation： 隔离性， 事务的隔离级别
  • durability： 一旦事务提交成功后， 修改的数据就持久化存储下去， 不可逆的修改

事务将隔离性分为几个级别：

  1. read uncommitted, 一个事物可以读到另外一个事物还未提交的数据， 隔离级别最低
  2. read committed， 只有在数据被提交后，他的更新结果才能被别人读取
  3. repeatable committed， 在一个事物中， 对该数据始终读取都是相同的数据， 无论在这个事物执行的过程中， 其他并行事物是否已经提交或未提交
  4. serializable， 隔离级别最高，就是串行处理所有事物。

因为不同的隔离级别，会带来不同的数据读取问题：

  1. dirty read
  2. nonrepeatable read
  3. phantom read

dirty read：事务1 修改了数据， 但还没有提交， 事务2 读取了事务1 修改的结果， 但随后 事务1 进行了回滚， 因此， 事务2的读取行为，就被称为 脏读。

nonrepeatable read： 事务1 读取了数据a， 但随后 事务2 修改了数据a 并进行了提交， 随后， 事务1 再次读取数据a ，发现2次数据读取不一致， 这种称为nonrepeatable read。

phantom read， 事务1 读取了一批数据， 事务2 插入了一条新数据并提交了事务， 事务1 再次进行读取，发现多了这条新数据， 这条新数据就像 幻象一样存在 phantom