【资料库系统】 L4 中级资料库

资料库系统 L4-中级资料库

4-1 关联表达式

Joined Relations(结合关系)

• 连结操作(Join operations):取两个关系，将结果返回另一个关系

• 利用笛卡尔乘积，将两个元组匹配

• 通常在 from 後做子查询表达式

• 连接条件(Join condition):定义两个关系中哪个元组匹配，连接结果存在那些属性
  

• inner join(内结合)

  course inner join prereqon
  course.course_id= prereq.course_id
  

• Outer Join(外结合)
  连结操作的延展，避免资料的丢失
  • Left Outer Join(左外结合)

  course left outer join prereq on
  course.course_id= prereq.course_id
  

  
  • Right Outer Join(右外结合)
    
  • Full Outer Join(全外结合)
    
• 连接类型(Join type):定义如何处理与另一关系不匹配的元组
  

4-2 视图

• 限定资料库用户可浏览的虚拟关系

• 视图定义(View Definition)

    定义一张视图(View)
  

  create view v as < query expression >
  

    query expression为SQL表达式
    v为视图名称
  

• 范例

  • 对没有薪水的讲师的视图

  create view faculty as
  select ID, name, dept_name
  from instructor
  

  • 查找生物学的所有讲师

  select name
  from faculty
  where dept_name= ‘Biology’
  

  • 创建部门薪资总额视图

  create view departments_total_salary(dept_name, total_salary) as
    select dept_name, sum (salary)
    from instructor
    group by dept_name;
  

• 利用视图定义其他视图

  • 如果在 v1 视图中定义 v2,则视图 v1 依赖於视图 v2
  • 如果视图依赖本身则表示递回
  • 查看表达式的扩展重复以下替换步骤

  repeat
    Find any view relation vi in e1
    Replace the view relation vi by the expression defining vi
    until no more view relations are present in e1
  

• 视图的更新

  • 为我们定义的教师视图添加一个新的元组

    insert into faculty values (’30765’, ’Green’, ’Music’)
    

  • 允许简单的视图上更新
    • FROM 子句只有一个数据库关系
    • Select 子句仅包含关系的属性名称，并且没有任何表达式，聚合或不同的规范
    • 未在 SELECT 子句中列出的任何属性可以设置为 NUL
    • 查询没有组或具有子句的组

4-3 交易次数

• 完整性约束

  • 确保数据库资料的一致性，不会因为缺是数据导致损害

    1.客户必须有非空的电话号码
    2.银行薪资必须至少4美元
    

• 单一关系的限制

  • not null:不得为空

  name varchar(20) not null
  

  • unique( A1, A2, ..., Am):指出唯一的规格
  • check (P):确保资料的限定

  create table section (course_idvarchar (8),sec_idvarchar (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’));
  

• 参照完整性

  • 关联的两个表中，被关联的属性会互相影响，不可单独删除或增加

  • A 为一组属性。R 和 S 两个包含属性 A 的关系，其中 A 是 S 的主要键。如果对於所出现的任何值，这些值也会出现在 S 中的所有值也会出现。

  create table course (
    course_idchar(5) primary key,title             varchar(20),
    dept_name varchar(20) references department)
  

4-4 诚信约束(?)

• 检查条款

  check (time_slot_idin (select time_slot_idfrom time_slot))
  

4-5 SQL 数据与类型

• 资料类型
  • date: 日期，（4位数）年，月和日期

  date '2005-7-27'
  

  • time:：一天中的时间，以小时，分钟和秒

  time '09:00:30'        
  time '09:00:30.75'
  

  • timestamp:日期加上一天的时间

  timestamp '2005-7-  27 09:00:30.75'
  

  • interval:一段时间

  nterval  '1' day
  

• 用户类型定义(?)

create type Dollarsas numeric (12,2) final 

• 域(?)
  • 类型和域是相似的。域可以具有约束，例如指定不是null
• 大物件类型
  • 大物件:照片，视频，CAD文件等大型档案
  • blob:二进制的大物件
  • clob:大型字串数据
  • 查询大物件时，返回的是指标而非大物件本身

4-6 授权

• 资料库的部分授权形式
  • read:允许阅读，但不能修改资料
  • insert: 允许插入新资料，但不能修改现有数据
  • update:允许修改，但不能删除资料
  • delete:允许删除数据
• 修改数据库架构的授权形式
  • index:允许创建和删除索引
  • resources:允许创造新关联
  • alteration :允许在关联中添加或删除属性
  • drop:允许删除关联
• SQL的授权规范

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

    <user list>:user-id
* 视图的授权并不代表基础关联的任何授权

• SQL的特权

  • select:允许读取关联的访问，或使用视图查询的能力

      EX:予许U1~3的查询关联的授权
    

  grant select on instructor to U1, U2, U3
  

  • insert:插入元组的权限
  • update:使用UPDATE语句的更新功能
  • delete:删除元组的权限
  • all privileges:允许所有权限
• 在SQL中撤销授权
  • revoke on from

  revoke select on branch  from U1, U2, U
  

  • 可以全部撤消的所有权限。
  • 如果包含public，所有用户会失去权限
• 角色
  • 向角色授权

  grant select on takes to instructor;
  

  • 角色可授权其他角色或用户

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

• 授权视图

create view geo_instructor as
(select *
from instructor
where dept_name= 'Geology');

• 转让特权

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;