数据库学习笔记

发表于 更新于 分类于 阅读次数： 本文字数： 6.4k 阅读时长 ≈ 6 分钟

数据库其实不难学，就是头有点凉。

概念逻辑转化规律——week5~8

重点——relationship

1.*

S-S

主关键字做了外部关键字

1的主关键字移向了n

有属性

如果有属性，属性去掉后基数交换，再依照上述规则处理

概念模型建模的时候注意：先画概念模型的1*

S-W

弱实体没有主关键字，不能移向强实体

1：* 二元 S——W 1——>* 主关键字——>主关键字+外部关键字

from our teacher

有属性

括号表示preference是弱实体。

此时生成逻辑模型出错

把关系的属性移入依赖的弱实体

(这里的Relationship_2应是state，而preference中的state项应为Date项)

再依照之前的规律转换

1.1

S-S

双方强制参与会报警，根据业务将其中一方改为0-1。

被支配方会出现支配方外部关键字。

此时为了外部关键字移入右边，应该将右边改为0-1。

同理，对于1-1和0-1，也等同与将左边设为支配方的情况。

对于双方都是0-1的情况，被支配的对象是人更少的一方。

S-W

弱实体将强实体作为主关键字和外部关键字。

双方强制参与会报警，有两种解决方式。一是将被支配方改为0-1，二是将弱实体与强实体直接合并。

有属性

1-1,0-1：

此时可能出现新的双向联系警告，按照无属性的方法处理即可。

我们将Manages和Staff合并，此时支配者在Branch（图略），生成物理模型：

1-1,1-1：

双向关联限制，修改1-1,0-1，等同于此种情况的处理方式。

0-1,0-1：

哪边更接近1-1（人少），将那边和新实体合并，原实体作为支配者。

*.*

强实体无属性

一个:变成了两个1：*

生成了新实体，弱实体

两边原实体的主关键字——>新实体：主关键字+外部关键字（1的主关键字移向了n成为外部关键字）

强实体有属性

比无属性多了关联本身的属性，其他没什么区别。

S-W设不起。

解决warning的办法：

  1. 无属性，将其中一个*改成1。
  2. 有属性，将一个联系与弱实体合并，变成1.*关系。

递归

1.*

逻辑模型多了实体本身的外部关键字，也可以理解为1移到多。

0-1,1-*没有问题。0-1,0-*没有问题。

**1-1,1-*会有自反强制warning。**

1.1

注意设置支配角色，其余规律与1.*相同。

只有0-1,0-1不会warning。

*.*

与二元*.*情况类似，注意重命名。

各类*.*不会warning。

多元联系

注意基数必须是n，参与性对其没什么影响。

如果基数是1会有warning：

子类合并的四种情况

设A是父类，BC是子类。

强制不相交

没有必要建立A，将A的属性继承给B和C即可。

可选不相交

B和C不能与A建立联系（保留超子类关系）。将A与D建立联系；将B、C和D建立联系，或者各自与其他实体分别建立联系。

强制非不相交

将BC合并，再与A合并。

可选非不相交

将BC合并，B+C不能和A建立联系（保留超子类关系）。将A与D建立联系，将B+C与D或者与其他实体建立联系。

超子类

子类继承了父类的属性。四种强制/相交关系均无区别。

多值属性

建立新实体，强实体，分离出多值属性，和原实体建立1.*关系。

规范化——week9~10

云上课太™酸爽了。

以这个表作为例子：

首先做一堆假设导出关系

  • 员工的编号对应唯一姓名: ⑥ -> ⑦
  • 房产编号对应唯一地址: ① -> ④
  • 一名员工一天只可用一辆车: ②⑥ -> ⑧
  • 一套房子一天只能被看一次: ①② -> ③⑤⑥
  • 同日期、时间，一辆车只可被一名员工用: ②③⑧ -> ⑥
  • 同日期、时间，一名员工只可看一处房产: ②③⑥ -> ①⑤

然后开始组合

可得①② -> 全部

可得②③⑧ -> 全部

可得②③⑥ -> 全部

综上得到：

去除依赖

后三行可以导出所有信息，因此这三组都是候选关键字。同时将①②作为主关键字。

①② -> ④且① -> ④，因此① -> ④是部分依赖。

②③⑧ -> ⑥且②③⑧ -> ⑦，因此⑥ -> ⑦，是传递依赖（同时也是部分依赖）。

②⑥ -> ⑧是决定方不是关键字的依赖（同时也是部分依赖）。

导出最终表

①④为一个表，⑥⑦为一个表，②⑥⑧为一个表，剩下①②③⑤⑥为一个表。

数据库系统与sql——week10~13

创建

创建数据类型

1
2
3
create type BranchNumber
   from varchar(20)
go

创建默认值

1
2
3
create default D_B001
    as 'B001'
go

将默认值与列绑定

1
2
execute sp_bindefault D_B001, BranchNumber
go

创建规则

1
2
3
create rule R_SexType as
      @column in ('F','M')
go

将规则与列绑定

1
2
execute sp_bindrule R_SexType, SexType
go

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
create table Staff (
   staffNo              StaffNumber          not null,
   branchNo             BranchNumber         not null,
   Sup_staffNo          varchar(5)           null,
   fNameOfStaff         FirstName            not null,
   lNameOfStaff         LastName             not null,
   Position             varchar(10)          not null,
   sex                  SexType              not null           D_M            R_SexType,
   DOB                  datetime             null,
   salary               money                not null,
   constraint PK_STAFF primary key nonclustered (staffNo)
)# column name          datatype             null/not null      default val    rule  
go

查询

结果去重

1
2
select DISTINCT propertyNo
from Viewing

结果去重会影响基数、平均值与总数，对最小最大值无影响。

范围问题

1
2
3
4
select *
from Staff
where salary between 5000 and 9000
# 值为5000或者9000也会列入查询结果中

通配符查找

1
2
3
4
select *
from PrivateOwner
where address like '%St%'
# %类似于正则表达式的通配符'*'，查找包含'St'的字符串

排序

1
2
3
select *
from PropertyForRent
order by type, rent desc

先优先按照字母序对type排序，然后type相同，按从大到小排列rent。

分组

1
2
3
4
5
6
7
select branchNo,
COUNT(staffNo) as myCount,
SUM(salary) as mySum
from Staff
group by branchNo
order by AVG(salary)
# 求各个分公司的人数和总收入，并按照平均工资排序

此时删去group by branchNo列报错。应作以下修改：

1
2
3
4
5
select 
COUNT(staffNo) as myCount,
SUM(salary) as mySum
from Staff
# 求整个公司的人数和总收入

相关子查询

1
2
3
4
select *
from Staff
where salary>(select AVG(salary) from Staff)
# 查询拉高平均工资的人，不能写成 salary>AVG(salary)
1
2
3
4
5
select *
from Staff S1
where salary>(select AVG(salary) from Staff S2 
where S1.branchNo=S2.branchNo)
# 查询每个分公司中，拉高平均工资的人

内、左、右、全连接 Inner/Left/Right/Full join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select *
from Staff,Branch
where Staff.branchNo=Branch.branchNo
and street='163 Main St'
# 两表的数据合并到了一个结果，以相同的分公司信息和特定的街道连接。

select *
from Branch b left join PropertyForRent p
on p.city=b.city
# 保留左表数据，右边对应不上的填null

select *
from Branch b right join PropertyForRent p
on p.city=b.city
# 保留右表数据，左边对应不上的填null

select *
from Branch b full join PropertyForRent p
on p.city=b.city
# 两边都保留，两边对应不上的分别填null

表间查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select *
from Registers
where staffNo in
	(select staffNo
	 from Staff
	 where branchNo = 
		(select branchNo
		 from Branch
		 where street = '163 Main St'))
# 查询分公司在这个街道的所有职员的注册信息

select distinct clientNo
from Viewing v1
where NOT EXISTS
    (select *
 	from PropertyForRent P
 	where rooms=3 and NOT EXISTS
 		(select * 
 		from Viewing v2
		where v2.clientNo=v1.clientNo and v2.propertyNo=p.propertyNo))
# 好家伙 （不存在套3的房子是他没看的，即找看完所有套3的人）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select distinct clientNo
from Viewing v1
where EXISTS
    (select *
 	from PropertyForRent P
 	where rooms=3 and EXISTS
 		(select * 
 		from Viewing v2
		where v2.clientNo=v1.clientNo and v2.propertyNo=p.propertyNo))
# 找看过任何套三的人

select distinct clientNo
from Viewing v1
where propertyNo IN
    (select propertyNo
 	from PropertyForRent
 	where rooms=3)
# 使用IN语句的写法，与上述等同

集合操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
(select * from Branch)
 union
(select * from PropertyForRent)
# 并

(select * from Branch)
 intersect
(select * from PropertyForRent)
# 交

(select * from Branch)
 except
(select * from PropertyForRent)
# 差

select distinct clientNo
from Viewing v1
where NOT EXISTS
    ((select propertyNo
 	from PropertyForRent P
 	where rooms=3)
 	except
 	(select propertyNo
 	from Viewing v2
	where v2.clientNo=v1.clientNo))
# 找看完所有套三的人的另一种写法

some/any/all

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select *
from Staff
where salary > any
	(select salary
	 from Staff
	 where branchNo = 'B003')
# 此处填some/any效果相同，select salary和select MIN(salary)效果也相同

select *
from Staff
where salary > all
	(select max(salary)
	 from Staff
	 where branchNo = 'B003')
# 此处select salary和select MAX(salary)效果也相同

视图——week13

创建视图

1
2
3
4
5
6
create view StaffPropCnt(branchNo,staffNo,cnt)
as 
select s.branchNo,s.staffNo,COUNT(*)
from Staff s, PropertyForRent p
where s.staffNo = p.staffNo
group by s.branchNo,s.staffNo

查询视图

1
2
3
4
5
6
7
8
9
10
11
# 视图可以当成一张表进行进一步查询
select staffNo, cnt
From StaffPropCnt
where branchNo='B003'
order by staffNo

# 等同于
select s.staffNo as staffNo, COUNT(*) as cnt
from Staff s, PropertyForRent p
where s.staffNo = p.staffNo and s.branchNo = 'B003'
group by s.branchNo,s.staffNo

视图修改

1
2
3
insert into StaffPropCnt
values('B003','SG5',2)
# 报错，更改视图不能更改视图来源的表（信息量过小）。

创建角色

1
create role Manager

授予权限

1
2
3
4
grant select, insert, update, delete, references, alter
on Staff
to Manager
with grant option

撤回权限

1
2
3
4
revoke references 
on Staff
from Manager 
cascade

事务并发——week14

串行化

对不同线程的同一事务的读写和写写会有冲突。

把每一个线程作为一个点，对同一事务不同线程的读写和写写进行加有向边，如果生成图无环，代表并行调度冲突可串行化。

对视图的调度属于NP完全问题，不可解。

加锁

读锁/共享锁/read_lock：自己别人可以读，自己别人不能改。

写锁/互斥锁/write_lock：自己可以读和改，别人不能读和改。

如果别人加了共享锁，你只能加共享锁；如果别人加了互斥锁，你什么也不能加。

通过二段锁可以避免丢失更新、未提交依赖、不一致分析问题。

缺点有：

  • 级联回滚——解决方法有在事务的结尾释放锁，或者在事务的结尾释放互斥锁。
  • 死锁——解决方法有锁超时、wait-die & wound-wait、时间戳排序

托马斯写规则：

A先写，然后B再读，A回滚，时间戳移到B之后。

A先写，然后B再写，A的写就废除。

锁释放之前要确保加好所有的锁。

乐观检查机制

只对事务结束的时候进行冲突检查，可提高并发性。

数据粒度

粒度越细，并发度越高，但是加锁的信息也越多，反之亦然。

意向锁？

不管。

安全——week16

这个得靠自己。

风险原因

  • 硬件故障
  • DBMS安全机制失效
  • 数据库超出授权的范围、修改
  • 数据库管理员安全策略或过程不完备
  • 程序员制造“陷阱门”，程序变更或者员工安全方面训练不足
  • 用户用他人身份访问泄露数据，黑客侵入敲诈勒索，病毒入侵等

数据库防护措施

  • 授权访问控制
  • 视图
  • 备份恢复
  • 完整性
  • 加密（对称、非对称）
  • RAID技术

DBMS和网络安全防护措施

  • 代理服务器
  • 防火墙
  • Kerberos
  • 报文摘要算法和数字签名
  • 数字证书
  • 安全电子交易和安全交易技术
  • 安全套接字层和安全HTTP
  • Java安全
  • ActiveX安全