Notes for FIT9132: Intro to DB

发表于 更新于

整体难度不大，tutor也很给面子（要是我自己给自己打分肯定更低

建议尽早摘抄知识点并且在Tut/Ass时候就用起来，别像我一样前面拍脑子考前总结才发现都在PPT里。

W1

回顾历史DB发展以及名人

W2 Conceptual Modelling

理论模型

因为是chow’s foot图，所以复合（1：1）、多值（1：N）属性记得加个实体来表述会更直观

理论模型设计

开发企业数据模型。

  • 对应于ANSI / SPARC体系结构的概念级别。
  • 独立于所有物理实施注意事项（要使用的数据库类型）。
  • 可以采用各种设计方法，例如UML，ER（实体关系）。
  • ER由实体之间的实体和关系组成

ENTITY具有属性（我们希望记录的东西），其中一个或多个属性将标识实体实例（称为KEY）

强弱实体

强实体

具有可以在不参考其他实体的情况下定义的Key。例如EMPLOYEE实体。

弱实体

具有要求存在一个或多个其他实体的Key。例如FAMILY实体-需要包括员工的KEY才能为家庭创建合适的密钥

数据库设计人员通常根据业务规则确定一个实体是否可被描述为弱实体，例如

客户每月支付帐款

Key：cust_no，date_paid

或者：payment_no（如果是凭空创造的surrogate Key？- 不属于理论模型）

理论模型不加surrogate Key！

显著Identifying 不显著Non-identifying
Identifier of A is part of identifier of B. Identifier of A is NOT part of identifier of B.
实线 虚线
ENROLMENT - STUDENT Enrolment key includes student id, which is an identifier of student.
CAR - CAR_COLOR
存在同样的key		 Department no (identifier of department) is not part of Employee’s identifier.

属性

类型: 简易，复合，单值，多值，派生

鱼尾纹chow’s foot表示法不支持多值属性。 值被列为单独的属性。

关联（或复合）实体

ERD

关系数据库模型，即ERM，构成ERD(实体关系图)的基础。
ERD代表最终用户查看的概念数据库。
ERD描述了数据库的主要组成部分：实体，属性和关系。
因为实体代表现实世界中的对象，所以实体和对象一词经常互换使用。

正好实体关系图也能在vuepress里用markdown+mermaid绘制，所以可以直接画着玩。由于还处于experimental，所以是只能表现实体和关系，并不能展示属性

在mermaid里，几对几这样的关系分成左右两部分描述。

左边 右边 含义
` o` `o
` `
}o o{ 零或多个 (无上限)
`} ` ` 
erDiagram
    CUSTOMER ||--o{ ORDER : places

表示一个CUSTOMER强对应0或多个ORDER，即一个顾客对应着0个(未点单)或多个其点过的订单

erDiagram
    ORDER ||--|{ ITEM : contains

表示一个ORDER强对应0或多个ITEM，即一份订单上存在一个或多个商品

erDiagram
    CUSTOMER }|..|{ ADDRESS : uses

表示0或多个CUSTOMER弱对应0或多个ADDRESS，即存在多个顾客且存在多个地址，之间存在弱对应关系

W3 Relational Model

Relational Model&Relational Algebra关系模型与代数

这个作业里是用SQL Developer从Logical里生成出来的，所以到SQL（DDL）部分才开始create。

这里主要讲的是

  • 用NAME(Attribs…)表示的实体，每个数据都是一个tuple
  • 其中的各种key: SuperKey(一或多个属性，独一无二), CK（一或多个属性）, PK（从CK中选的一个），AK（alternate keys。PK以外的CK）
  • 代数（select，project，join，其他union，diff，div啥的都不用）顺便为后面归一化做准备

功能性依赖Functional Dep

如果在任何时候属性（集）A的值都确定单属性B值（当且仅当），则属性B在A上是功能依赖的。

• order_no → order_date
• prod_no → prod_desc
• order_no, prod_no → ordered_qty

RM 特点：

  • 要有独一无二的名字
  • 每个属性要有独一无二名字（用prefix
  • 属性不能多值
  • 所有属性值应当来自同一个域
  • 必须有主键
  • relations 之间的 Logical (not physical) connections 通过primary/foreign keys表示

W4 Normalisation

归一化

  • 为了避免在插入，更新和删除数据时可能发生的异常，应规范化关系。

  • 规范化是一系列系统步骤，用于逐步完善数据模型。

  • 基于primary key（或候选 candidate keys）分析关系的正式方法 密钥）和功能依赖项。用作：“自下而上设计”的一种设计技术，以及作为一种验证通过“自上而下的设计”产生的结构的方式（将ER模型转换为逻辑模型-参见下周）

更新异常的类型包括：

•插入异常-仅在新员工被分配到项目时才插入

•删除异常-删除分配给项目的唯一员工？

​ –删除特定工作类别的唯一员工？

•修改（或 更新）异常

​ -更新作业类别的每小时费率 - 需要更新多行

归一化目标

  • 创建有效的关系，即每个关系都符合关系模型的属性。 特别是：–实体完整性–引用完整性–没有多对多关系–每个单元格都包含一个值（是原子的）。

  • 在RDBMS中实施时，实际上：–每个表代表一个主题 –没有数据项 将不必要地存储在多个表中。–可以建立表之间的关系（标识了PK和FK对）。–每个表都没有插入，更新和删除异常。

功能性依赖Functional Dep

参照前面

部分依赖Partial Dep（1->2）

缺少对多个属性键的完全依赖

  • ORDERNO, PRODNO ➔ PRODDESC, QTY_ORDERED
  • 尽管qty_ordered完全取决于orderno和prodno，但仅需prodno即可确定proddesc
  • proddesc部分依赖于orderno和prodno
  • 用人话说，就是现在看起来是主键是orderno，但是要是有个prodno也能决定qty ，那就把prodno和qty拎出去
  • 拎出去之后那个新实体的key在原位置保留且作为key！

传递依赖Transitive Dep（2->3）

  • 当Y取决于X，Z取决于Y时发生
  • 因此Z也取决于X，即。 X➔Y➔Z 并且Y不是候选键（或候选键的一部分）
  • ORDER_NO➔CUST_NO➔CUST_NAME: CUSTOMER(CUST_NO, CUST_NAME)
  • emp_no➔job_class➔chg_hour: JOB (job_class, chg_hour)
  • 拎出去之后那个新实体的key在原位置保留但是不作为key！

UNF->1NF

  1. 确定重复组的Key。

  2. 删除任何重复组以及主要关系的PK。

  3. 由于重复组的去除而导致的新关系的PK通常具有由主要关系的PK组成的复合PK 以及上面1.中选择的唯一标识符，但是必须进行检查。说人话就是拎出来的实体要带原来的Key，也要有自己的Key，就反正至少俩

最后写出所有的Partial dependencies

1NF -> 2NF

2NF下的关系

  • 继承前面的
  • 把所有Partial dependencies部分依赖拎出来

最后写出所有的Transitive dependencies

2NF -> 3NF

如果3NF，则存在关系

  • 继承前面的
  • 把所有Transitive dependencies传递依赖项都拎出来

最后要写出所有的Full Dependencies

W5 Logical Model

就是Assignment里让你画在SQL Developer里那个。按实际制作流程来看应该比relational model先讲。

在Conceptual Model正确情况下较简单，但是别像我一样拍脑子做。

逻辑设计（1b）

  • 开发针对特定数据库模型的数据模型（例如关系，分层，网络，面向对象，noSQL）。
  • 独立于任何特定供应商DBMS软件包的任何实施细节。
  • 归一化技术（请参阅w4）用于测试关系逻辑模型的正确性。

1. 映射常规（强）实体

先是把Conceptual Model里的Key转为PK或者FK，加UQ

2. 映射弱实体

3. 映射二元关系

1:M

记得加fk到M那侧

M:N

Conceptual里的M:N关系需要转为1:N和N:1

例如

转为

如图TEAM和EMPLOYEEE之间的leads关系

在强制侧的PK变成可选那侧的FK（如emp_no的到了team成了fk，虽然人家是1:M

如果都是可选的，那就加到null最少那侧

如果两边都强制那就考虑合并吧

4. 映射关联实体

就类似上面M:N的处理，不过关联实体往往有surrogate key，例如XXX_no，所以就这个no加P别的纯粹F

5. 映射一元关系

1:1，1:M 不变

MN：加实体

6. 映射三元关系

拆成三个二元

W6 DDL

SQL create

uq的constraint写inline

1
2
3
4
5
6
CREATE TABLE unit
(
unit_code CHAR(8) NOT NULL,
unit_name VARCHAR(50) CONSTRAINT uq_unit_name UNIQUE NOT NULL ,
CONSTRAINT pk_unit PRIMARY KEY (unit_code)
);

这里提一下前面W3里的内容，就是FK要么是其他的PK，要么就是NULL，所以就基本上不写NOT NULL。另外FK一般拎出来alter。ON DELETE CASCADE保证引用完整性（感觉一般不用

1
2
3
4
5
6
7
8
ALTER TABLE enrolment
ADD
(
CONSTRAINT fk_enrolment_student FOREIGN KEY (stu_nbr) REFERENCES student ( stu_nbr),

CONSTRAINT fk_enrolment_unit FOREIGN KEY (unit_code) REFERENCES unit (unit_code)
);

值的约束

1
2
3
4
5
ALTER TABLE student
ADD (stu_address varchar(200),
status char(1) DEFAULT 'C',
constraint status_chk CHECK (status in ('G','C'))
);

W7 SQL

主要讲的就是select和where

限定词：

  • =, !=, <>, <, >, >=, <=
  • between

NOTE: between aaa and bbb时候aaa和bbb都是被包括进去的

  • in (‘aaa’, ‘bbb’ …)
  • like: %表示0+个字符, _表示单字符
  • is null, is not null (判断null时候不能用=或!=)
  • any, all
  • exists

select执行后三种状态：True, False和Unknown（匹配到NULL）。是True才算被检索到。

select 检索时候做运算（如grade+1）则列名变成该算式（“grade+1”），反正就很难看，所以一般用as 重命名一下。当as的新名字有空格，用**" "**去包围（不是单引号！），列名始终用双引号。

NULL算啥都还是NULL，也可以用NVL(原列名, 值)来代替NULL做运算（如NVL(enrol_mark, 0)默认NULL为0）

Order by

  • NULLS LAST, NULLS FIRST

  • DESC （默认ASC故可省略）

SELECT DISTINCT 去重复行

JOIN

from aaa JOIN bbb ON aaa.a = bbb.b

from aaa JOIN bbb ON aaa.key = bbb.key

from aaa JOIN bbb USING (key)

from aaa NATURAL JOIN bbb

to_char(SYSDATE, ‘dd-Mon-yyyy hh:mi:ss PM’)

反过来就to_date(datetime, ‘dd-Mon-yyyy hh:mi:ss PM’ )

一些系统变量SYSDATE SYSDATETIME USER

另外数字的format里0和9区别: 9占位但不padding（除非小数，但用FM连小数也不），0占位还padding

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> select to_char(1, '999.999'),
  2         to_char(1, '000.000'),
  3         to_char(1, '900.009'),
  4         to_char(1, '999.000')
  5  from   dual;

1.000
001.000
01.000
1.000

select to_char(1, 'FM999.999'),
       to_char(1, 'FM000.000'),
       to_char(1, 'FM900.009'),
       to_char(1, 'FM999.000')
from   dual;

1.
001.000
01.00
1.000

W8 DML

Update, Delete and Transaction Management

记得每个insert/update 都要commit！

Lock

主要讲事务上的读写锁S（shared lock）和X（exclusive lock）

当一个新Tx，

  • 读SLock: 更新SLock到S(Tx)
  • 写SLock: 新Tx wait 上个老Tx
  • 读XLock: wait
  • 写XLock: wait

最后画个wait的图，判断是否deadlock（回环）

CheckPoint

rolled forward：REDO+after-images

rolled back：UNDO+before-images

checkpoint：就把在checkpoint后fail前commit的重做（REDO），未完成的rollback（UNDO），区间前面完成的也不理

如图

T1: 无需操作

T3,T5 roll back: 因为没commit

T2 roll forward: 因为在checkpoint点和fail点中间提交的

T4 roll forward: 因为在检查点后开始且fail前提交的

W9 Aggregate Functions

COUNT, MAX, MIN, AVG

COUNT，AVG ignore null rows

GROUP BY -> 合并keys相同的rows，对每个合并后的Aggregate使用Fn

HAVING -> 对Group BY的结果进行约束

IN

ANY, ALL

extract

1
extract(year from ofyear) as year

decode:

1
2
3
decode (cltype, 
  'L', 'Lecture',
  'T', 'Tutorial') as Classtype

lpad

1
2
3
lpad(
  extract(year from ofyear) || ' S' || semester,
  10,' ') as offering

W10 Advanced SQL

好家伙分数大头

CASE

  • case when expr then val end
  • case var then val end

Subquery

基本上哪里都能套

nested

xxx in (select)

correlated

1
2
3
4
5
6
select studid, unitcode, mark
from uni.enrolment e1
where mark = (select max(mark)
  from uni.enrolment e2
  where e1.unitcode = e2.unitcode)
order by unitcode, studid;

这个也能用在update，insert这些上（很好用

inline

1
2
3
4
5
6
7
8
select studid, e.unitcode, mark
from
(select unitcode, max(mark) as max_mark
  from uni. enrolment
  group by unitcode) max_table
join uni.enrolment e on e.unitcode =   max_table.unitcode and
e.mark = max_table.max_mark
order by unitcode, studid;

这里max_table就是select出来内容的alia

或者

1
2
3
4
5
6
7
8
9
10
11
SELECT
grade,
count(grade) as grade_count,
(SELECT 
  count(grade) from uni.enrolment) as total_rows,
  100*count(grade)/(SELECT count(grade) FROM uni.enrolment
) as percentage
FROM uni.enrolment
where grade is NOT NULL
GROUP BY grade
order by grade;

套中套

Views

基本不用 （因为ass不让

考试应该就可以

但是基本上功能和前面subquery差不多

Joins

natural join, self join, outer join

这个就没前面的natural join那样慈眉善目了，丢分巨头

self join

Show the name of the manager for each employee.

因为manager也是employee，所以就算self join，通过mgrno和empno来对应。

1
2
3
SELECT *
FROM emp.employee e1 JOIN emp.employee e2
ON e1.mgrno = e2.empno;

吓得我去看了眼考试SQL的模型，还好没一元关系

outer join

这个用的比较多

full outer join

两边对应不上的都保留

如图，左边的id=3没对应所以右边空null，右边4没对应所以左边空null

left/right outer join

left只保证左边的都有对应，因此只保留右边的null

right只保证右边的都有对应，因此只留左边的null。

另外左右是根据join关键词的左边右边决定的，比如上面的full就是id_name full outer join id_subj_mark

Set Operators

大前提：列能对的上

Union All 加法（不去重）

Union 加法（去重）

Intersect 重叠部分，即A \and B

Minus 减法，即A-(A \and B)

另外，union优先级比order by高

Oracle Functions

见文档

W11 BigData

纯介绍+NoSQL

JSON_OBJECT(‘key’ value val FORMAT JSON)

JSON_ARRAYAGG(JSON_OBJECT(), JSON_OBJECT())

MongoDB

insertOne(), insertMany([])

find({})或者find(匹配, 需要的列)，例如find({_id: 2}, {name: 1})只取id=2那列的name

op:

{$and: [{}, {}]}

{$or: [{}, {}]}

updateOne(匹配, {$set: {}})

$ 替代符 placeholder to update the first element that matches the query condition，例如 client.$.addr

deleteOne()

deleteMany()

W12

无Lec

错题

输出 Fri 01 February 2019 RM这样的date str用 lpad(rtrim(to_char(ca.carndate, 'Dy DD Month')) || ' ' || to_char(ca.carndate, 'YYYY'),23) AS carnival_date,（人裂开

Exam

Sample给的这么全面想想也不会考的很简单……

Sample

Relational Model 10

  1. 不同attrib（估计考试会换成不同的key）
  2. a. & b. 代数式（一定要记得最后再π一下去掉不必要的列

Database Design 20

根据文章徒手撸Logical Model（简易版，其实就和ASS1的Conceptual差不多了）

写纸上

Normalisation 10

写全过程（记得最后一定要跟上Full Dep

SQL 40

根据给出的Model写

  1. insert，alter还有update
  2. 简单select + 巨难的left outer join
  3. 又是left outer join再配合having count(distinct ) + select subquery

Big Data and No SQL 10

  1. json和mongodb

  2. 神tm名词解释Volume，Velocity，Variety。估计考试要求介绍NOSQL了

Transaction 10

  1. 锁
  2. checkpoint

Exam

题型的确和Sample一模一样

前四个除了第一个问答题以外不用多说，老内容了，而且感觉更简单？（也可能是我掉坑）。

第一个问答题改成了Relation里tuple的特性

就这部分内容

后面BigData部分，mongodb直接要求写find和insertOne，find有点难因为是在一个list/array里找匹配的object。问题没有根据预测考NoSQL，考的是Volume的内容

checkpoint也变名词解释Write Through和Deferred Write