酒业网站建设,中英文网站是咋做的,云兰装潢公司总部地址电话,网站域名跟谁买数据库系统原理复习提纲 参考文献#xff1a;A First Course in Database Systems | 3rd Edition Authors:Jennifer Widom,Jeffrey D Ullman 视频教程#xff1a;https://www.bilibili.com/video/BV13J411J7Vu 中国人民大学信息学院-数据库系统概论完整版(基础篇高级篇新技术…数据库系统原理复习提纲 参考文献A First Course in Database Systems | 3rd Edition Authors:Jennifer Widom,Jeffrey D Ullman 视频教程https://www.bilibili.com/video/BV13J411J7Vu 中国人民大学信息学院-数据库系统概论完整版(基础篇高级篇新技术篇) 期末复习用时间有限难免有疏漏还望各位批评指正主要是写一些对书里内容的理解。——©️Sylvan Ding ❤️ 文章目录数据库系统原理复习提纲关系数据模型关系模型的组成定义关系模式表创建数据类型约束条件修改/删除关系模式表修改/删除代数查询语言关系表达式关系表达式树查询优化关系数据库设计理论求属性集合的闭包求某关系的所有候选码/所有非平凡函数依赖求FD的最小基本集投影依赖的计算BCNF定义分解算法3NF定义分解算法高级数据库模型ER模型建立ER模型转关系模式ER模型设计原则ODL模型代数和逻辑查询语言包上的关系操作扩展操作符数据库语言SQL语法事务保证事务正确执行的ACID性质事务的隔离级别约束与触发器断言check约束和断言的对比触发器视图与索引视图更新索引计算最佳索引服务器环境下的SQL嵌入宿主语言的SQL存储过程关系数据库安全机制授权和销权授权图关系数据模型
关系模型的组成 关系数据结构 关系模型中的数据在逻辑上是一张二维表由行和列组成二维表、行列 关系操作 插入、删除、更新、查询增删改查 关系完整性约束 实体完整性约束属性A是关系R的主属性则A不能取空值主属性不能为空 参照完整性约束F是R的外码和S中的主键K相对应F必须为空或等于K中某个值下文必须在上文出现 用户定义的完整性对某一应用所涉及的数据必须满足语意要求根据实际自定义的约束
定义关系模式表创建
数据类型
类型说明char(n)定长n个字符不足位补空格效率最优varchar(n)变长最多n个字符实际存储长度1存字符长度空间最优text变长非unicode存储量很大nchar(n)定长unicodenvarchar(n)变长unicodentext变长unicodebinary/varbinary定长/变长 二进制image变长二进制用于保存图像bigint/int/smallint/tinyint整型bit模拟BOOLEANdecimal(n,d)n位有效位d位小数位real/float浮点datetime“YYYY-MM-DD HH:MM:SS”
约束条件
create table tb_name(attr_1 char(1) primary key, -- 主键约束attr_2 int references tb_2(att_name) -- 外键约束on update cascade -- 级联更新on delete set null, -- 置空值原则默认“缺省原则”拒绝更新attr_3 datetime unique not null default date 1900-12-12, -- 唯一、非空、默认值约束attr_4 real [constraint c_name] check(attr_410), -- 基于属性的check约束[约束命名]不等于用-- 也可以在末尾声明约束constraint c_name primary key(attr_1),foreign key(attr_2) references tb_2(att_name),unique(attr_3),check(attr_1 in (F,M))
);-- 删除约束
alter table tb_name drop constraint c_name;
-- 添加约束type_of_constraint[primary key|foreign key|unique...]
alter table tb_name add constraint c_name type_of_constraint(attr)...;修改/删除关系模式表修改/删除
-- 表删除
drop table t_name
-- 添加/删除列
alter table t_name [add|drop] column_name;
-- 添加/删除约束
alter table t_name [add|drop] constraint c_name;
-- 修改某列类型
alter table t_name alter column col_name type;代数查询语言
关系表达式
投影πA1,A2,⋯\pi _{A_1,A_2,\cdots}πA1,A2,⋯
选择σC\sigma _CσC
自然连接公共属性相同的行保留去掉重复列
θ\thetaθ 连接不去重复列比如用 R.A, S.A 表示根据条件保留行
重命名ρs(A1,A2,⋯)(R)\rho _s (A_1,A_2,\cdots) (R)ρs(A1,A2,⋯)(R) 将R重命名为S其属性重命名为A1, A2…也可省略S或A1, A2…
赋值R(a1,a2) : 表达式
关系表达式树
表达式-表达式树表达式由外及里表达式树自上到下
表达式树-表达式表达式树自下到上表达式由里及外
查询优化
顺序选择、投影、连接
注意先画连接关系简单理解为找输入-输出-连接确定连接个数是对直接连接再选择和投影的优化。在表达式中可以用交、并、差运算若题目中出现“没有”字样则用差运算。 Students(Sno,Sname,Ssex,Sage,Sdept) Courses(Cno, Cname, Cpno,Ccredit) SC(Sno,Cno,Grade) Q: 查询选修了以2号课为先修课的课程的学生的名字? 输入条件2号课 Courses.Cpno‘2’ 输出学生的名字 Students.Sname 连接Courses.Cno - SC.Cno, SC.Sno - Students.Sno 在连接前先做选择和投影即可。 关系数据库设计理论
求属性集合的闭包
输入A{A1,A2,…}, FD集合SA {A} 并上那些可以由 A 推导出来的属性
求某关系的所有候选码/所有非平凡函数依赖
构造关系的所有属性的子集闭包算法找出这些子集的所有非平凡依赖非平凡依赖的右边有全部属性的是候选码
求FD的最小基本集 FD右边分解为单一属性 去除能由其他FD推出的FD整体最小化 去除FD左边多余的属性局部最小化 两种方法判定是否多余计算闭包是不用像2那样除去FD 求左边属性的闭包闭包是否包含右边所有属性若包含则左边其余属性多余 去除左边某个属性观察新FD集和原FD集是否依然等价 去除左边那个属性后的新FD集是否能推出原FD集若等价则该属性多余 重复2-3直到不能再化简为止
投影依赖的计算
写出投影的所有属性子集的闭包得到所有非平凡函数依赖FD集最小化FD集得到投影的依赖集
BCNF
定义
当且仅当所有非平凡FD的左边均包含任意一个候选码或者说所有非平凡FD都是超码。
满足BCNF则一定满足NF-1、2、3即不存在非主属性对主属性的部分依赖、传递依赖主属性间不存在部分依赖、传递依赖。
注意任何二元关系都满足BCNF。
分解算法
假定输入的FD集已经为最简否则需要最小化FD集。 根据定义找BCNF违例 先确定所有候选码再找违例 对违例进行分解 如违例 X-YX 中不包含候选码那么求 X由X组成一个分组另一个分组由X和不在X的其他属性组成也就是说X要在两个分组中均存在以此作为分解后二者连接的依据。求X并组成一个分组也就是希望这个分组尽量满足BCNF分解到最后一定能满足BCNF 最小化两分组的FD集求投影FD集递归进行1-2分解直到所有分组满足BCNF R(ABCD), {AB-C,C-D,D-A} A{A},B{B},C{C,D,A},D{D,A} {AB}{ABCD},{AC}{A,C,D},{AD}{A,D} {BC}{B,C,D,A},{BD}{B,D,A,C},{CD}{C,D,A} {ABC}-,{ABD}-,{ACD}{C,D,A},{BCD}- {ABCD}不必考虑肯定是平凡依赖 注意{ABC}、{ABD}、{BCD}可由{AB}{ABCD}…等直接得出无需再计算 而且{ABC}、{ABD}、{BCD}是超码不算入候选码中所以结果用-表示 A{A}B{B}{AD}{A,D}{ACD}{C,D,A}是平凡依赖不考虑 候选码AB,BC,BD 除去上述超码、平凡依赖、候选码后剩余的 C{C,D,A},D{D,A} {AC}{A,C,D},{CD}{C,D,A} 此时BCNF违例为 C-D,C-A,D-A,AC-D,CD-A 选择一个违例进行分解应当经可能向右边添加属性以减少工作量 选择C-DA进行分解{C}{ACD} 则分解为R1{ACD},R2{BC} 计算R1投影的函数依赖 A{A},C{CDA},D{DA} {AC}-,{AD}{AD},{CD}- 候选码C BCNF违例D-A {D}{DA} 分解为R11{AD},R12{CD} 又知二元关系肯定满足BCNF所以分解结果{BC},{AD},{CD} 3NF
定义
BCNF放宽要求要么FD左边是超码要么FD右边全是主属性。
分解算法
求最小基本集对最小基本集中每一个FD X-Y 将 X U Y 作为一个分组进行分解修正删和添2步骤分解时没必要得到子集所以将子集去除 若没有一个分组包括原关系的候选键则添加一个分组分组为任一候选键一次确保无损连接性
注意该分解算法具有无损连接和依赖保持性质。 R(ABCDE), {AB-C,C-B,A-D} 最小化基本集 {A}{AD},{B}{B}所以AB不多余也没有FD是多余的所以已经最小化 那么{ABC},{BC},{AD}其中{BC}是{ABC}的子集所以删除 计算候选键 ABABCD,ACABCD, 显然ABE和ACE是候选键 添加候选键则分解结果为 {ABC}{AD},{ABE}或{ACE} 高级数据库模型
ER模型建立
注意一对多联系一方那段连线要画箭头。
多路联系箭头指向的那个实体集由其他实体集函数决定
多路联系转二元联系“联系中心转实体集再添加多个联系”
isa联系父类的主键一定在子类中出现
参照完整性约束A–)B说明A参照BA一定要在B文中出现而且是一对一或者多对一一方带箭头
弱实体集依赖一个实体集而存在本质上就是参照完整性约束也就是说A的主键一定在B的主键中出现过然后再为弱实体集设置一个主键即弱实体集的主键由对应“强”实体集的主键和弱实体集的主键共同构成对比外键的概念即可
注意isa联系和弱实体集的区别是弱实体集本质上是参照完整性约束需要增加属于弱实体集的主键部分而isa联系则不能改变父类的主键子类都要继承父类主键可以添加其他属性但属性不能成为子类键的一部分。
ER模型转关系模式 联系转关系 联系的属性是和该联系直接相连的实体的键。 多对一联系联系的键为多方实体集的键 多对多联系联系的键为联系的所有属性 一对一联系联系的键为任意一方实体集的键。 和“函数决定”理论相关 关系组合 上述联系转关系不一定是最优的比如“多对一”、“一对一”关系都可以合并 实体集转化为另一个实体集的属性要求改实体集是全码否则会冗余 处理弱实体集 支持联系无需转化为关系因为已经“关系组合”到弱实体集的关系属性中 子类结构处理 ER方法每个实体集都建一个关系子类包含父类的主键每个实体都有自己的关系面向对象枚举可能出现的子树包含根为每个子树单独建一个关系这个关系模式含有子树中所有实体的所有属性找子树空值法只创建一个关系模式包含所有属性空项用空值NULL填充所有属性塞一块 对比空值法占用空间最多ER法有信息重复面向对象法占空间最少且无重复。
ER模型设计原则 忠实性设计要反映现实情况 Stars和Movies之间定义联系Star-in应该是多对多联系; 不能把属性“气缸数”与stars联系却可以把它作为汽车的属性。 避免冗余一件事在模型中只出现一次 在电影和电影公司之间用了联系Owns。又把电影公司的名称studioName选作电影实体集的一个属性。 两次表示“所属的制片公司”这一同样的事实 简单性考虑模型元素越少越好 选择正确联系舍弃冗余、无关的联系 选择正确元素种类用属性还是用实体集用实体集还是用联系属性–实体–联系 属性代替实体集E的条件 E是一方实体集E的键是全码没有联系包含E多次
记忆装b剪连元一个装b的人剪断了连在一起的元宵 (装)忠实性(b)避免冗余(剪)简单性考虑(连)联系(元)元素种类
ODL模型
class Movie {attribute string title;attribute integer year;attribute enum Genres{drama,comedy...} genre; // enum声明枚举型属性genreGenres为枚举名称// relationship声明名为stars的联系// SetStar为一对多联系表示一部电影有多个影星// inverse Star::starredIn 说明stars联系为Star类中starredIn联系的反向联系relationship SetStar starsinverse Star::starredIn; relation Studio ownedByinverse Studio::owns;
};class Star {attribute string name;attribute Struct Addr{string street, string city} addr; // Struct声明结构型属性addrrelationship SetMovie inverse Movie::stars;
};class Studio {attribute string name;attribute Star::Addr address; // 引用Star域的Addr结构relationship SetMovie ownsinverse Movie::ownedBy;
};注意
类中元素有两种attribute和relationship其后接类型和元素名
[attribute|relationship] type ele_name;
比如 relationship SetMovie owns inverse Movie::ownedBy 表示Studio中有一组Movie和Movie的ownedBy这个Studio类互为反向关系。
class CMM (key(k1,k2)) extends M1:M2 {relationship Array... rs_name;// CMM继承了M1和M2的所有元素并添加了自己的属性 re_name// 用 key(k1,k2) 声明了CMM的键(K1,K2)
}代数和逻辑查询语言
包上的关系操作
注意包可以有重复
R 交 S元组t出现 min(m,n) 次R - S元组t出现 max(0,m-n) 次包连接先笛卡尔积再按条件筛选
扩展操作符
消重δ\deltaδ
聚集MIN、MAX、AVG、COUNT、SUM
分组聚集γA1,A2,agg(A3)→agg3,agg(A4)→agg4(R)\gamma _{A1,A2,agg(A3)\to agg3,agg(A4)\to agg4} (R)γA1,A2,agg(A3)→agg3,agg(A4)→agg4(R) 表示将R的A1和A2作为分组属性在A3和A4属性上做agg聚合聚合结果投影到agg3和agg4这两列上x→yx\to yx→y 表示将x重命名为y
投影扩展πA−B→C(R)\pi _{A-B \to C}(R)πA−B→C(R) 将R上A-B的结果重命名为C
排序τB,C\tau _{B,C}τB,C 从小到大排序按BC属性先后
外连悬浮元组指的是不能和另一张表配对配对指的是符合条件比如自然连接需要符合名相同的列值相等的条件theta连接需要符合特定条件的本表元组。解题方法先正常内连笛卡尔积筛选最后再加入各表的悬浮元组。左连和右连表示只保留左/右悬浮元组。注意如果是自然连接要去除重复列如果是theta连接则不去除重复列相同列名加前缀保留
数据库语言
SQL语法
字符串匹配通配符%任意字符串n0_单个字母[ ] 字符选择[^] 字符选择的否定两个’‘表示一个’
排序ORDER BY A1,A2 [DESC]默认升序DESC降序
FromFROM (table1) new_name 在table名后为table命新名
交、并、差INTERSECT、UNION、EXCEPT. 例如 (SELECT ...) INTERSECT [ALL] (SELECT ...), 注意三者默认消重若不希望消重则应加[ALL]
比较运算符用 ANY/ALL 对WHERE选择的结果进行聚合
theta连接SELECT...FROM ...JOIN...ON... ON 后为连接条件
自然连接NATURE [FULL/LEFT/RIGHT] [OUTER] JOIN [ON]...
消重SELECT DISTINCT ...
COUNT查不重复SELECT COUNT(DISTINCT ...) FROM ...
查询顺序select from where group by having order by
插入查询值insert into table_name (select...)
修改update ...set ......, ...... where ...
字符串拼接‘A’||‘B’
事务
保证事务正确执行的ACID性质 AAtomicity原子性 事务不可分割事务中的操作要么都发生要么都不发生不可分 CConsistency一致性 事务前后数据的完整性必须一致 IIsolation隔离性 并发事务间相互隔离不相互干扰 DDuration持久性 一旦事务被提交对数据的改变是永久性的
事务的隔离级别
脏读不可重复读幻读读未提交 read uncommitted✅✅✅读提交 read committed✅✅可重复读 repeatable read✅可串行化 serializable
约束与触发器
断言
本质上断言就是对数据库的全局的check约束。任何引起断言为假的数据库更新都将被阻止且断言的条件condition中不能有任何属性可以用自查询和EXISTS做判断。断言的条件可能暂时为假比如在事件的执行过程中在事件结束时再检查断言条件。
create assertion ass_name check (condition); -- 创建断言
drop assertion ass_name; -- 删除断言check约束和断言的对比
基于属性的check约束在某属性上更新时检查对子查询没有约束效力
基于表的check约束在某表的任何一个属性更新时检查对子查询没有约束效力
断言对引起断言条件变化的任何可能的更新检查对子查询有约束效力是基于数据库的约束。
触发器
create trigger tg_name -- 创建触发器
[after|before|instead of] [options] on tb_name -- options是增删改查其中update可以加of表示对某属性的更新
referencing -- 声明新旧元组/表old [row|table] as old_name -- 删除操纵new [row|table] as new_name -- 插入操作-- 二者均存在更新操作
for each [row|statement] -- 定义行/语句级触发器
-- 行级触发器每行触发一次而语句级触发器每条语句出发一次
when(condition) -- 触发条件可以没有比如可以是 old.valnew.val
begin -- 执行语句多条则用begin和end包括
...;
-- 比如 update tb_name
-- set col_namenew_name.col_name
-- where col_nameold_name.col_name;
end;视图与索引
视图创建create view view_name(newA1,newA2,...) as ... newA1、newA2给属性重命名
视图删除drop view...
视图更新
只能更新 行列子集视图由单表导出的含有其某些行列的视图。必须要含有所有主键因为主键不能为空。where子句不能查询自己。可以在视图上定义 instead of 触发器从而解决一些更新异常。
索引
create index index_name on tb_name(att1,att2...) -- 创建索引
drop index index_name -- 删除索引计算最佳索引 考虑上述员工表Staff上的索引假定Staffsidpay_rankservice_year 员工编号sid是主键给定一个sid值只可能查出1条元组只需调入1个磁盘页 每个索引需占用2个磁盘页 存储关系Staff的磁盘页为100如果要检查整个关系则代价为100 关系按照薪酬级别pay_rank聚合这样只需一次磁盘访问操作就可以找到给定薪酬级别的员工 每个service_year平均有5名员工且平均分散在5个磁盘页上 仅需一次磁盘访问就可读取用于容纳新元组的磁盘页。在该关系上有如下形式的查询 Q1: SELECT * FROM Staff WHERE sid s; Q2: SELECT * FROM Staff WHERE pay_rankp; Q3: SELECT * FROM Staff WHERE service_yeary; I: 插入新元组到表staff中。 计算上面4种操作在无索引、sid索引、pay_rank索引、service_year索引和全索引5种情况下的平均操作代价填入表1中。 无索引的情况下任何Q查询都需要访问所有100个磁盘页遍历所有数据页。 有索引时先读入索引消耗一次访存本题是两次因为每个索引需占用2个磁盘页再根据索引直接读对应的磁盘页一次索引Q1和Q2在有相应索引时是321。 而Q3每个service_year平均有5名员工且平均分散在5个磁盘页上所以要读入5个磁盘页725。 无索引插入读一页写回一页两次访存。有索引插入读写回索引2次每个索引2页4次加上数据读和写回2次共6次。全索引插入读写回索引2次每个索引2页3个索引12次14122. 无索引sid索引pay_rank 索引service_year索引全索引Q110031001003Q210010031003Q310010010077I266614服务器环境下的SQL
嵌入宿主语言的SQL
// **游标更新**
// 查看MovieExec的每个元组决定删除还是将其净资产翻倍
#define NO_MORE_TUPLE !(strcmp(SQLSTATE,02000)) // 宏定义游标取完的标识void changeWorth(){// 共享变量声明EXEC SQL BEGIN DECLARE SECTION;int certNo,worth; char SQLSTATE[6]; // SQLSTATE 返回SQL状态5个字节EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE execCursor CURSOR FOR query; // 声明游标declare cursor_name cursor for ...查询语句也可以直接是一个表名EXEC SQL OPEN execCursor; // 开启游标 open cursor_name// 进入循环while(1){// 取数据 fetch from cursor_name into [:share_variables]...;EXEC SQL FETCH FROM execCursor INTO :certNo, :worth;// SQL语句中使用共享变量要加:if(NO_MORE_TUPLE) break; // 跳出条件fetch查完了if(worth10000)EXEC SQL DELETE FROM MovieExec WHERE CURRENT OF CURSOR; // 使用游标where只能用CURRENT OF CURSORelseEXEC SQL UPDATE MovieExec SET netWorth2*netWorth WHERE CURRENT OF CURSOR;}// 关游标EXEC SQL CLOSE execCursor;
}存储过程
计算某电影公司制作的影片长度的平均值和方差
-- **方差\sum x_i^2/n-\bar{x}^2
-- loop循环结构需要写退出条件 delcare NOT_FOUND condition for sqlstate 02000
create procedure MeanVar( -- 存储过程创建in s char(15),out mean real,out variance real-- 声明参数输入/输出[in|out|inout] param_name type-- SQL中变量名在前类型在后
)
-- 局部变量声明
declare NOT_FOUND condition for sqlstate 02000; -- 循环退出条件声明
declare MV cursor for select lenth from movies where studioNames; -- 游标声明
declare MC integer; declare newlenth integer;
-- 注意在存储过程声明中创建的输出参数不需要重复声明
-- 程序体
begin-- 存储过程中赋值操作用setset mean0.0;set variance0.0;set MC0;set newlenth0;-- 开游标open MV;movieloop:loop-- loop循环loop_name:loop ~~~ end loop;-- 取数据fetch from MV into newlenth;-- 判退出if NOT_FOUND then leave movieloop end if;-- 数据处理set MCMC1;set meanmeannewlenth;set variancevariancenewlenth*newlenth;end loop;-- 关闭游标close MV;-- 输出set mean mean/MC;set variance variance/MC - mean*mean;
end;-- for 循环结构无需写退出条件
-- for loop name as cursor name cursor for query;
-- do statement list
-- end for;
-- 1. 创建存储过程
create procedure (-- 2. 填写参数输入输出in s char(15),out mean real,out variance real
)
-- 3. 局部变量声明因为是for循环所以不用声明退出条件变量
declare MC int;-- 开始语句记得加begin和end
begin-- 4. 变量赋初值包括输出变量set MC 0;set mean 0.0;set variance 0.0;-- 5. 创建for循环在for循环中创建游标注意for循环的尾部有个 ;for movieLoop as MV cursor for select lenth from movies where studioNames;-- 6. 定义数据处理do-- 注意for循环不用取数据了可直接使用上述select的属性set mean mean lenth;set variance variance lenth*lenth;set MC MC 1;end for;set mean mean/MC;set variance variance/MC mean*mean;
end;记忆一般模式
-- 1. 创建存储过程
create procedure(-- 2. 声明输入输出参数
)
-- 3. declare 声明局部变量
declare ...;
-- 4. 如果是loop那么要声明退出条件变量
declare ... condition for sqlstate 02000;-- 5. 写操作体
begin
-- 6. 初始化变量和参数
set ... ...;
-- 7. 开游标loop循环
-- 8. 写loop循环loop_name:loop-- 8.1 取数据fetch from cursor_name into ...;-- 8.2 判退出if NOT_FOUND then leave loop_name end if;-- 8.3 数据操作...;end loop;-- 8.4 关游标
-- 9. 写for循环for loop_name as cursor_name cursor for query;-- 9.1 写循环体do...;end for;
-- 10. 后续操作
end;关系数据库安全机制
授权和销权
权限ALL PRIVILEGES 表示所有权限
数据库元素除了表和视图以外其余数据库元素应当加上 类型即类型元素名称
-- 授权
grant 权限 on 数据库元素 to 用户1,用户2... [with grant option];
-- 销权
revoke 权限 on 数据库元素 from 用户1,用户2... [cascade];习题10.1.1解答 Chegg In SQL there are nine types of privileges, those are: SELECTINSERTDELETEUPDATEREFERENCES The statement Select-From-Where always requires the SELECT privileges on every table on which it queried or accessed. On other hand REFERENCES privileges on a relation , which it refers to that relation in an integrity constraint. Not only reference to relation but also it will consider some other form as referential integrity constraint or assertions or tuple based checks etc. 授权图
注意事项
两颗心心 ** 表示“属主”一颗心心 * 表示有权分发权限用cascade销权授权图去掉某边后“属主”达不到的点均要删除点内容自上而下分别是用户名 权限名 “权限表示”心心执行语句带或不带授权选项是不同的权限必须用两个不同的节点表示若某用户的一个权限比另一个权限更通用也要用两个不同的节点表示 ——©️Sylvan Ding ❤️
