資料庫系統 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;