数据库系统概论(个人笔记)(第四部分)

2024-06-18 04:36

本文主要是介绍数据库系统概论(个人笔记)(第四部分),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

数据库系统概论(个人笔记)

文章目录

  • 数据库系统概论(个人笔记)
  • 4、中间的SQL
    • 4.1 连接表达式
    • 4.2 视图
    • 4.3 事务
    • 4.4 完整性约束
    • 4.5 SQL数据类型和模式
    • 4.6 SQL中的索引定义
    • 4.7 授权

4、中间的SQL


4.1 连接表达式

Join Expressions


Joined Relations

连接操作 Join operations 接受两个关系并返回另一个关系作为结果。

连接操作是笛卡尔积,它要求两个关系中的元组匹配(在某些条件下)。它还指定了出现在连接结果中的属性

连接操作通常用作 from 子句中的子查询表达式

连接条件 Join condition ——定义两个关系中的哪些元组匹配,以及在连接结果中出现哪些属性

连接类型 Join type ——定义如何处理每个关系中与其他关系中任何元组不匹配的元组(基于连接条件)

在这里插入图片描述


Natural Join in SQL

自然连接 Natural Join 为所有公共属性匹配具有相同值的元组,并且只保留每个公共列的一个副本。

列出每个学生所修的课程:

  • select name, course_id
    from  students, takes
    where student.ID = takes.ID;
    

在SQL中使用“自然连接”构造进行相同的查询:

  • select name, course_id
    from student natural join takes;
    

from 子句可以有多个使用自然连接组合的关系:

select  A1, A2, … An
from  r1  natural join r2 natural join .. natural join rn
where  P ;

Student Relation

在这里插入图片描述


Takes Relation

在这里插入图片描述


student natural join takes

在这里插入图片描述


Dangerous in Natural Join

谨防不相关的相同名称的属性被错误地等同

Example——列出学生的名字以及他们所修课程的名称

  • 不正确的版本

    select name, title
    from student natural join takes natural join course;
    
    student( ID, name dept_name, tot_cred)
    course(course_id, title, dept_name, credits))
    takes(ID, course_id, sec_id, semester, year, grade)
  • 正确的版本

    select name, title
    from student natural join takes, course
    where takes.course_id = course.course_id;
    
    • 该查询省略了学生在该学生所在系以外的系中学习课程的所有(学生姓名、课程名称)对。
    • 正确的版本(如上),正确输出这样的匹配。

Natural Join with Using Clause

为了避免错误地等同属性的危险,我们可以使用“using”构造,它允许我们精确地指定应该等同哪些列。

查询示例

select name, title
from  (student natural join takes)  join course using (course_id)

Join Condition

on 条件允许在连接的关系上使用一般谓词

该谓词的编写与 where 子句谓词类似,只是使用了关键字 on

查询示例

select *
from  student join takes on student_ID  = takes_ID
  • 上面的 on 条件指定了一个来自 student 的元组与来自 take 的元组匹配,如果它们的 ID 值相等。

等价于:

select *
from  student , takes
where  student_ID  = takes_ID

Outer Join

连接操作的扩展,可避免信息丢失。

计算连接,然后将一个关系中不匹配另一个关系中的元组的元组添加到连接的结果。

使用空值 null。

外连接 Outer Join 的三种形式:

  • 左外连接 left outer join
  • 右外连接 right outer join
  • 全外连接 full outer join

Outer Join Examples

在这里插入图片描述


Left Outer Join

在这里插入图片描述


Right Outer Join

在这里插入图片描述


Full Outer Join

在这里插入图片描述


Joined Types and Conditions

连接操作 Join operations 接受两个关系并返回另一个关系作为结果。

这些额外的操作通常用作 from 子句中的子查询表达式。

连接条件 Join condition ——定义两个关系中哪个元组匹配。

连接类型 Join type ——定义如何处理每个关系中与其他关系中任何元组不匹配的元组(基于连接条件)。

在这里插入图片描述


Joined Relations – Examples

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述


4.2 视图

Views

在某些情况下,不希望所有用户都看到整个逻辑模型(即存储在数据库中的所有实际关系)。

考虑一个人需要知道导师的名字和部门,但不知道薪水。这个人应该看到 SQL 中描述的关系:

 select ID, name, dept_namefrom instructor

视图 view 提供了一种机制,可以在某些用户的视图中隐藏某些数据。

任何不属于概念模型但作为“虚拟关系”对用户可见的关系都称为视图 view


View Definition

视图是使用 create view 语句定义的,该语句具有如下形式:

create view v as <query expression>

其中 是任何合法的SQL表达式。视图名用 v 表示。

一旦定义了视图,就可以使用视图名称来引用视图生成的虚拟关系。

视图定义与通过计算查询表达式来创建新关系不同

  • 相反,视图定义会导致表达式的保存;该表达式被替换到使用视图的查询中。

View Definition and Use

教师没有他们工资的一个视图:

create view faculty asselect ID, name, dept_namefrom instructor

找到生物系的所有老师:

select name
from faculty
where dept_name = 'Biology'

创建部门工资总额视图:

create view departments_total_salary(dept_name, total_salary) asselect dept_name, sum (salary)from instructorgroup by dept_name;

Views Defined Using Other Views

一个视图可以在定义另一个视图的表达式中使用

如果在定义 v1 的表达式中使用了 v2 ,则认为视图关系 v1 直接依赖于 depend directly on 视图关系 v2

如果视图关系 v1 直接依赖于 v2 ,或者存在从 v1 到 v2 的依赖路径,则称视图关系 v1 依赖于 depend on 视图关系 v2

如果视图关系 v 依赖于自身,则称其为递归 recursive

create view physics_fall_2017 asselect course.course_id, sec_id, building, room_numberfrom course, sectionwhere course.course_id = section.course_idand course.dept_name = 'Physics'and section.semester = 'Fall'and section.year = '2017;
create view physics_fall_2017_watson asselect course_id, room_numberfrom physics_fall_2017where building= 'Watson';

View Expansion

展开视图(将上述两个结合起来):

create view physics_fall_2017_watson asselect course_id, room_numberfrom (select course.course_id, building, room_numberfrom course, sectionwhere course.course_id = section.course_idand course.dept_name = 'Physics'and section.semester = 'Fall'and section.year = '2017')where building= 'Watson';

定义根据其他视图定义的视图的含义的一种方法。

让视图 v1 由表达式 e1 定义,表达式 e1 本身可以包含视图关系的用法。

表达式的视图展开重复以下替换步骤:

repeat

​ 查找e1中任何视图关系vi

​ 用定义vi的表达式替换视图关系vi,

until e1中不再存在视图关系

只要视图定义不是递归的,这个循环就会终止


Materialized Views

某些数据库系统允许对视图关系进行物理存储。

  • 定义视图时创建的物理副本。
  • 这样的视图被称为物化视图 Materialized Views

如果更新了查询中使用的关系,则物化视图结果将过期

  • 需要通过在底层关系更新时更新视图来维护 maintain视图。

Update of a View

向前面定义的教员 faculty 视图添加一个新的元组

insert into facultyvalues ('30765', 'Green', 'Music');

这个插入必须通过插入到教员关系中来表示

  • 必须有工资的价值

两种方法

  • 拒绝插入
  • 将元组(‘30765’, ‘Green’, ‘Music’, null)插入到讲师关系中

Some Updates Cannot be Translated Uniquely

create view instructor_info asselect ID, name, buildingfrom instructor, departmentwhere instructor.dept_name= department.dept_name;
insert into instructor_info values ('69987', 'White', 'Taylor');

问题:

  • 如果 Taylor 有多个部门,那是哪个部门?
  • 如果 Taylor 没有部门呢?

And Some Not at All

create view history_instructors asselect *from instructorwhere dept_name= 'History';

如果我们将 (‘25566’, ‘Brown’, ‘Biology’, 100000) 插入history_instructors 会发生什么?


View Updates in SQL

大多数SQL实现只允许对简单视图进行更新

  • from 子句只有一个数据库关系。
  • select 子句只包含关系的属性名,没有任何表达式、聚合或不同的 distinct 规范。
  • 任何未在 select 子句中列出的属性都可以设置为null。
  • 查询没有 group byhaving 子句。

4.3 事务

Transactions

事务 Transaction 由一系列查询和/或更新语句组成,是一个“工作单元”

SQL 标准指定在执行 SQL 语句时隐式地开始事务。

交易必须以下列语句之一结束:

  • 提交工作 Commit work。事务执行的更新将永久保存在数据库中。
  • 回滚的工作 Rollback work。事务中 SQL 语句执行的所有更新都将撤消。

原子事务 Atomic transaction

  • 要么完全执行,要么回滚,就像从未发生过一样

隔离并发事务 Isolation from concurrent transactions


4.4 完整性约束

Integrity Constraints

完整性约束通过确保对数据库的授权更改不会导致数据一致性丢失,从而防止对数据库的意外损坏。

  • 支票账户的余额必须大于10,000美元
  • 银行职员的工资必须至少是每小时4美元
  • 客户必须有一个(非空)电话号码

Constraints on a Single Relation

Constraints on a Single Relation 单个关系上的约束

  • not null
  • primary key
  • unique
  • check (P), where P is a predicate P是谓词

Not Null Constraints

not null

  • 声明名称和预算不为空 not null

    name varchar(20) not null
    budget numeric(12,2) not null
    

Unique Constraints

unique(A1, A2, …, Am)

  • 唯一规范声明属性 A1,A2,…,Am 构成一个候选键。
  • 候选键允许为空(与主键相反)。

The check clause

check(P)子句指定一个谓词P,关系中的每个元组都必须满足该谓词P。

Example:确保这个学期是秋季、冬季、春季或夏季之一

create table section (course_id varchar (8),sec_id varchar (8),semester varchar (6),year numeric (4,0),building varchar (15),room_number varchar (7),time slot id varchar (4),primary key (course_id, sec_id, semester, year),check (semester in ('Fall', 'Winter', 'Spring', 'Summer')))

Referential Integrity

Referential Integrity 参照完整性

确保在一个关系中出现的值对于给定的一组属性也出现在另一个关系中的一组属性中。

  • Example:如果“生物学”是出现在教员关系中某个元组中的院系名,则在院系关系中存在“生物学”的元组。

设 A 是一组属性。设 R 和 S 是两个包含属性A的关系,其中 A 是 S 的主键。如果对于出现在 R 中的 A 的任何值,这些值也出现在 S 中,则称 A 是 R 的外键 foreign key

外键可以指定为 SQL create table 语句的一部分

foreign key (dept_name) references department

默认情况下,外键引用被引用表的主键属性。

SQL 允许显式地指定引用关系的属性列表。

 foreign key (dept_name) references department (dept_name)

Cascading Actions in Referential Integrity

Cascading Actions in Referential Integrity 引用完整性中的级联操作

当引用完整性约束被违反时,正常的过程是拒绝导致违反的操作。

在删除或更新的情况下,另一种选择是级联:

create table course ((...dept_name varchar(20),foreign key (dept_name) references departmenton delete cascadeon update cascade,...) )

我们可以使用以下命令代替 cascade :

  • set null
  • set default

Integrity Constraint Violation During Transactions

Integrity Constraint Violation During Transactions 在事务期间违反完整性约束

考虑如下情况:

create table person (ID char(10),name char(40),mother char(10),father char(10),primary key ID,foreign key father references person,foreign key mother references person)

如何插入元组而不造成约束违反?

  • 在插入 person 之前插入 person 的父亲和母亲
  • 或者,将父属性和母属性初始设置为空,在插入所有人员后更新(如果声明父属性和母属性不为空 not null,则不可能)
  • 或者推迟约束检查

Complex Check Conditions

Complex Check Conditions 复杂检查条件

检查子句中的谓词可以是任意谓词,可以包含子查询。

check (time_slot_id in (select time_slot_id from time_slot))

检查条件声明 section 关系中每个元组中的 time_slot_id 实际上是 time_slot 关系中一个时隙的标识符。

  • 不仅在 section 中插入或修改元组时必须检查条件,而且在关系 time_slot 发生变化时也必须检查条件

Assertions

断言 assertion 是表示我们希望数据库始终满足的条件的谓词。

以下约束可以用断言来表示:

对于学生关系中的每个元组,属性 tot_cred 的值必须等于该学生成功完成的课程的学分总和。

一个教师不能在一个学期的同一时间段在两个不同的教室授课

SQL中的断言采用以下形式:

create assertion <assertion-name> check (<predicate>);

4.5 SQL数据类型和模式

SQL Data Types and Schemas


Built-in Data Types in SQL

date: 日期,包含(4位)年、月和日期

  • Example:date ‘2005-7-27’

time: 一天中的时间,以小时、分钟和秒为单位。

  • Example:time ‘09:00:30’ time ‘09:00:30.75’

timestamp: 日期加上时间

  • Examlpe:timestamp ‘2005-7-27 09:00:30.75’

interval: 时间段

  • Example:interval ‘1’ day
  • 从一个日期/时间/时间戳值 date/time/timestamp 减去另一个值得到一个间隔值
  • 间隔值可以添加到日期/时间/时间戳 date/time/timestamp 值中

Large-Object Types

大对象 Large objects (照片、视频、CAD文件等)作为大对象存储:

  • blob:二进制大对象——对象是未解释的二进制数据的大集合(其解释留给数据库系统外的应用程序)
  • clob:字符大对象——对象是字符数据的大集合

当查询返回一个大对象时,返回的是指针而不是大对象本身。


User-Defined Types

SQL中的 create type 构造创建用户定义的类型:

create type Dollars as numeric (12,2) final

Example:

create table department
(dept_name varchar (20),building varchar (15),budget Dollars);

Domains

SQL-92 中的 create domain construct 创建用户定义的域类型:

create domain person_name char(20) not null

类型和域是相似的。域上可以指定约束,比如不为空。

Example:

create domain degree_level varchar(10)constraint degree_level_testcheck (value in ('Bachelors', 'Masters', 'Doctorate'));

4.6 SQL中的索引定义

Index Definition in SQL


Index Creation

许多查询只引用表中一小部分记录。

对于系统来说,读取每条记录来查找具有特定值的记录是低效的

关系属性上的索引 index 是一种数据结构,它允许数据库系统在关系中有效地找到具有该属性指定值的元组,而无需扫描关系的所有元组。

我们使用 create index 命令创建索引:

create index <name> on <relation-name> (attribute);

我们还可以删除带有索引名称的索引:

drop index <name>;

Index Creation Example

create table student
(ID varchar (5),name varchar (20) not null,dept_name varchar (20),tot_cred numeric (3,0) default 0,primary key (ID))
create index studentID_index on student(ID)

(以下的语句)可以通过使用索引查找所需的记录来执行查询,而不必查看 student 的所有记录:

select *
from student
where ID = '12345'

Basic Concepts

用于加快对所需数据访问的索引机制。

  • 例如,图书馆的作者目录

搜索键属性 Search Key:用于查找文件中记录的一组属性。

索引文件 index file 由以下形式的记录(称为索引条目 index entries )组成

search-keypointer

索引文件通常比原始文件小得多

两种基本指标:

  • 有序索引 Ordered indices:搜索键按排序顺序存储
  • 哈希索引 Hash indices:使用“ hash function ”在“ buckets ”中均匀分布搜索键。

Example of Hash Index

在这里插入图片描述


Example of B+ - Tree

在这里插入图片描述


Type of Index

不同数据类型和条件类型的索引

查询类型索引类型
Point queries(score=90)Hash、B±tree
Range queries(score>60)B±tree
Multi-condition queries (gender=male & rating=5)Bitmap
Spatial range queries (115.7°<lag<117.4°& 39.4°<long<41.6°)Nearest neighbor queries(KNN)Multidimensional index (R-tree、Quadtree、KD-tree)

是否支持持久化和快速恢复

  • 内存索引和磁盘索引

4.7 授权

Authorization

我们可以对数据库的某些部分为用户分配几种形式的授权。

  • 读取 Read ——允许读取,但不允许修改数据。
  • 插入 Insert ——允许插入新数据,但不修改现有数据。
  • 更新 Update ——允许修改,但不删除数据。
  • 删除 Delete ——允许删除数据。

这些授权类型中的每一种都称为特权 privilege。我们可以对数据库的指定部分(如关系或视图)授予用户全部、无或这些类型的特权的组合。

修改数据库模式的授权形式

  • 索引 Index ——允许创建和删除索引。
  • 资源 Resources ——允许创建新的关系。
  • 修改 Alteration ——允许在关系中添加或删除属性。
  • 删除 Drop ——允许删除关系。

Authorization Specification in SQL

grant 语句用于授予授权

grant <privilege list> on <relation or view > to <user list>

为:

  • a user-id
  • Public,它允许所有有效用户获得授予的特权
  • A role

Example:

  • grant  select on  department to Amit,  Satoshi
    

在视图上授予特权并不意味着在底层关系上授予任何特权。

特权授予者必须已经拥有对指定项的特权(或者是数据库管理员)。


Privileges in SQL

选择 select:允许对关系进行读访问,或者使用视图进行查询

  • Example:授权用户U1、U2 和 U3 选择 select 教员关系:

    grant select on instructor to U1, U2, U3
    

插入 insert:插入元组的能力

更新 update:使用SQL update 语句进行更新的能力

删除 delete:删除元组的能力

所有特权 all privileges:用作所有允许的特权的缩写形式


Revoking Authorization in SQL

revoke 语句用于撤销授权:

revoke <privilege list> on <relation or view> from <user list>

Example:

revoke select on student from U1, U2, U3

可以是 all,用来撤销被撤销者可能拥有的所有特权。

如果<revoke -list>包含 public,则除了显式授予的权限外,所有用户都将失去权限。

如果同一用户被不同的授予者授予了两次相同的权限,那么该用户在被撤销后可以保留该权限。

依赖于被撤销特权的所有特权也将被撤销。


Roles

角色 role 是区分不同用户的一种方法,这些用户可以访问/更新数据库中的内容。

要创建一个角色,我们使用:

create a role <name>

Example:

  •  create role instructor
    

创建角色后,我们可以使用以下命令为角色分配“users”:

  • grant  <role> to <users>
    

Roles Example

create role instructor;
grant instructor to Amit;

权限可以授予角色:

  • grant select on takes to instructor
    

角色可以授予用户,也可以授予其他角色

  • create role teaching_assistant
    
  • grant teaching_assistant to instructor
    
    • 讲师继承助教的所有特权

角色链

  • create role dean;
    
  • grant instructor to dean;
    
  • grant dean to Satoshi;
    

Authorization on Views

create view geo_instructor as
(select *from instructorwhere dept_name = 'Geology');
grant select on geo_instructor to geo_staff

假设一个geo_staff成员出了问题

  • select * from geo_instructor
    

如果

  • geo_staffinstructor 没有权限?
  • 视图创建者对 instructor 没有某些权限?

Other Authorization Features

引用 refrences 特权创建外键

  • grant reference (dept_name) on department to Mariano;
    
  • 为什么需要这样做?

转让特权

  • grant select on department to Amit with grant option;
    
  • revoke select on department from Amit, Satoshi cascade;
    
  • revoke select on department from Amit, Satoshi restrict;
    
  • And more!

在这里插入图片描述

这篇关于数据库系统概论(个人笔记)(第四部分)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1071320

相关文章

【学习笔记】 陈强-机器学习-Python-Ch15 人工神经网络(1)sklearn

系列文章目录 监督学习:参数方法 【学习笔记】 陈强-机器学习-Python-Ch4 线性回归 【学习笔记】 陈强-机器学习-Python-Ch5 逻辑回归 【课后题练习】 陈强-机器学习-Python-Ch5 逻辑回归(SAheart.csv) 【学习笔记】 陈强-机器学习-Python-Ch6 多项逻辑回归 【学习笔记 及 课后题练习】 陈强-机器学习-Python-Ch7 判别分析 【学

系统架构师考试学习笔记第三篇——架构设计高级知识(20)通信系统架构设计理论与实践

本章知识考点:         第20课时主要学习通信系统架构设计的理论和工作中的实践。根据新版考试大纲,本课时知识点会涉及案例分析题(25分),而在历年考试中,案例题对该部分内容的考查并不多,虽在综合知识选择题目中经常考查,但分值也不高。本课时内容侧重于对知识点的记忆和理解,按照以往的出题规律,通信系统架构设计基础知识点多来源于教材内的基础网络设备、网络架构和教材外最新时事热点技术。本课时知识

poj 2976 分数规划二分贪心(部分对总体的贡献度) poj 3111

poj 2976: 题意: 在n场考试中,每场考试共有b题,答对的题目有a题。 允许去掉k场考试,求能达到的最高正确率是多少。 解析: 假设已知准确率为x,则每场考试对于准确率的贡献值为: a - b * x,将贡献值大的排序排在前面舍弃掉后k个。 然后二分x就行了。 代码: #include <iostream>#include <cstdio>#incl

论文阅读笔记: Segment Anything

文章目录 Segment Anything摘要引言任务模型数据引擎数据集负责任的人工智能 Segment Anything Model图像编码器提示编码器mask解码器解决歧义损失和训练 Segment Anything 论文地址: https://arxiv.org/abs/2304.02643 代码地址:https://github.com/facebookresear

数学建模笔记—— 非线性规划

数学建模笔记—— 非线性规划 非线性规划1. 模型原理1.1 非线性规划的标准型1.2 非线性规划求解的Matlab函数 2. 典型例题3. matlab代码求解3.1 例1 一个简单示例3.2 例2 选址问题1. 第一问 线性规划2. 第二问 非线性规划 非线性规划 非线性规划是一种求解目标函数或约束条件中有一个或几个非线性函数的最优化问题的方法。运筹学的一个重要分支。2

【C++学习笔记 20】C++中的智能指针

智能指针的功能 在上一篇笔记提到了在栈和堆上创建变量的区别,使用new关键字创建变量时,需要搭配delete关键字销毁变量。而智能指针的作用就是调用new分配内存时,不必自己去调用delete,甚至不用调用new。 智能指针实际上就是对原始指针的包装。 unique_ptr 最简单的智能指针,是一种作用域指针,意思是当指针超出该作用域时,会自动调用delete。它名为unique的原因是这个

查看提交历史 —— Git 学习笔记 11

查看提交历史 查看提交历史 不带任何选项的git log-p选项--stat 选项--pretty=oneline选项--pretty=format选项git log常用选项列表参考资料 在提交了若干更新,又或者克隆了某个项目之后,你也许想回顾下提交历史。 完成这个任务最简单而又有效的 工具是 git log 命令。 接下来的例子会用一个用于演示的 simplegit

记录每次更新到仓库 —— Git 学习笔记 10

记录每次更新到仓库 文章目录 文件的状态三个区域检查当前文件状态跟踪新文件取消跟踪(un-tracking)文件重新跟踪(re-tracking)文件暂存已修改文件忽略某些文件查看已暂存和未暂存的修改提交更新跳过暂存区删除文件移动文件参考资料 咱们接着很多天以前的 取得Git仓库 这篇文章继续说。 文件的状态 不管是通过哪种方法,现在我们已经有了一个仓库,并从这个仓

忽略某些文件 —— Git 学习笔记 05

忽略某些文件 忽略某些文件 通过.gitignore文件其他规则源如何选择规则源参考资料 对于某些文件,我们不希望把它们纳入 Git 的管理,也不希望它们总出现在未跟踪文件列表。通常它们都是些自动生成的文件,比如日志文件、编译过程中创建的临时文件等。 通过.gitignore文件 假设我们要忽略 lib.a 文件,那我们可以在 lib.a 所在目录下创建一个名为 .gi

取得 Git 仓库 —— Git 学习笔记 04

取得 Git 仓库 —— Git 学习笔记 04 我认为, Git 的学习分为两大块:一是工作区、索引、本地版本库之间的交互;二是本地版本库和远程版本库之间的交互。第一块是基础,第二块是难点。 下面,我们就围绕着第一部分内容来学习,先不考虑远程仓库,只考虑本地仓库。 怎样取得项目的 Git 仓库? 有两种取得 Git 项目仓库的方法。第一种是在本地创建一个新的仓库,第二种是把其他地方的某个