[大二春夏] 数据库系统

第四章

Abstract

• Join
• View
• Index
• Transaction
• Integrity Constraint
• Data Types and Schemas
• Authorization

联结 Join

• 联结的种类：inner、left outer、right outer、full outer
• 联结的条件：natural、on、using

自然联结：匹配公共属性相同的元组

注意下面两个的不同：

from student natural join takes natural join course

from student natural join takes, course
where takes.course_id = course.course_id

为防止上面的情况，一般使用 using 指定联结列：

from (student natural join takes) natural join course using (course_id)

使用 on 指定联结条件：

from student join takes on student.ID = takes.ID

外部联结

可以理解为：如果没有匹配的元组，就用 NULL 填充其他属性。保留所有信息。

有 left、right、full 三种。关系代数符号：⟕、⟖、⟗。

内部联结

使用自然联结时，会自动去掉重复的（联结所使用的）列。内部联结则保留。

视图 View

create view v as <query expression>

相当于存储了一个表达式。

Materialized view：存储了结果，而不是表达式。

允许简单视图的更新。

insert into faculty values ('30765', 'Green', 'Music');

索引 Index

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

事务 Transaction

作为一个整体执行的一组操作。

事务的结尾必须是 commit 或 rollback。

完整性约束 Integrity Constraint

常见：

not null
primary key
unique(a1, a2, ...)
check(predicate)

• unique 指明了 superkey，其中 candidate key 可以是空的，primary key 不可以。
• check 可以含有查询，比如

check(time_slot_id in (select time_slot_id from time_slot))

引用完整性 Referential Integrity

默认拒绝。

cascade 级联操作

foreign key (a1, a2, ...) references r(b1, b2, ...)
    on delete cascade
    on update cascade

断言 Assertion

数据库始终满足的条件。

create assertion <assertion-name> check (predicate)

触发器 Triggers

对数据库进行修改时的副作用。

ECA：Event-Condition-Action

• 事件：insert、delete、update
  • 发生前后：after update of salary on instructor
  • 引用发生前后的值：new.salary、old.salary

create trigger setnull_trigger before update of takes
    referencing new row as nrow
    for each row
        when (nrow.grade= ' ')
        begin atomic
            set nrow.grade = null;
end;

数据类型和模式 Data Types and Schemas

• date、time、timestamp、interval
• Large Object: BLOB (binary)、CLOB (character)，查询时返回定位器 pointer 而不是实际数据
• 自定义：
  • create type Dollars as numeric (12,2) final
  • create domain person_name char(20) not null

授权 Authorization

• 权限 privilege：read、insert、update、delete
  • index、resources、alteration、drop

grant <privilege list> on <relation or view> to <user list>
grant select on  department to Amit,Satoshi
revoke <privilege list> on <relation or view> from <user list>
create role <name>
grant <role> to <users>

第五章

Abstract

• SQL 编程
• 函数和过程

JDBC

• Open connection
• Create statement
• Execute query
• Fetch result
• Handle Errors

SQL Injection

不使用字符串拼接，而是使用参数化查询。

PreparedStatement pstmt = conn.prepareStatement("select * from student where ID = ?");
pstmt.setString(1, "12345");
ResultSet rs = pstmt.executeQuery();

这里的 ? 就是参数。

setString
setInt
setDate

结果

while (rs.next()) {
    System.out.println(rs.getString("ID"));
}

元数据 .getMetaData()：

ResultSet rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();
for (int i = 1; i <= numCols; i++) {
    System.out.print(rsmd.getColumnName(i) + "\t");
    System.out.print(rsmd.getColumnTypeName(i) + "\t");
}

事务

conn.setAutoCommit(false);
conn.commit();
conn.rollback();

函数与过程

create function f (a1 type1, a2 type2, ...) returns type
    begin
    declare v1 type1;
        select ... into v1
        from ...
        where ...;
    return v1;
end;

select ...
from ...
where f(a1, a2, ...) = ...;

表函数：

create function f (a1 type1, a2 type2, ...) returns table (c1 type1, c2 type2, ...)
    begin
    ...
end;

过程：

create procedure p (in a1 type1, out a2 type2, inout a3 type3, ...)
    begin
    ...
end;

declare a2 type2;
call p(a1, a2, a3, ...);

常用语句：

while ... do
    ...
end while;

repeat 
    ...
until ...
end repeat;

declare n interger default 0;
for r as 
    select ... from ...
    where ...
do
    set n = n + r.budget
end for;

if ... then
    ...
elseif ... then
    ...
else
    ...
end if;

第六章 E-R 模型

设计数据库的两种模式：ER 模型、范式理论。

• Entity：由 attributes 描述，如 instructor=(ID, name, dept_name, salary)
• Relationship：多个 entities 之间的联系。Relationshop Sets 表示为：\(\{(e_1, e_2, ..., e_n)|e_i \in E_i\}\)
• 整体用 ER 图表示
• 基本概念：entity sets、relationship sets、attributes

Relationship Set

• 可以有 attributes
• Degree：relationship 的元素个数。一般都是二元关系 binary relationship。

Attribute

• Simple/Composite
• Single-Valued/Multi-Valued
• Derived
• Domain：属性的取值范围

Example:

Book(bid, title, author) // primary key: bid
Reader(name, age, profession) // primary key: name
Read(name, bid) // primary key: (name, bid)
Publisher(pid, pname, location) // primary key: pid
PublishedBy(bid, pid) // primary key: bid

Make necessary formalizations of the relational schema above to get a minimum number of relations schemas.

Answer:

```sql create table Book ( bid char(10) primary key, title char(50), author char(50), pid char(10) references Publisher

);