鍵與超鍵

SQL 關聯式資料庫入門

Timo Grossenbacher

Data Journalist

目前的資料庫模型

含主鍵的資料庫模型

什麼是鍵？

可唯一識別紀錄的屬性（群）
只要還能移除屬性：超鍵（superkey）
不能再移除屬性：極小超鍵或鍵（key）

     license_no     | serial_no |    make    |  model  | year
 -------------------+-----------+------------+---------+------
 Texas ABC-739      | A69352    | Ford       | Mustang |    2
 Florida TVP-347    | B43696    | Oldsmobile | Cutlass |    5
 New York MPO-22    | X83554    | Oldsmobile | Delta   |    1
 California 432-TFY | C43742    | Mercedes   | 190-D   |   99
 California RSK-629 | Y82935    | Toyota     | Camry   |    4
 Texas RSK-629      | U028365   | Jaguar     | XJS     |    4

SK1 = {license_no, serial_no, make, model, year}

SK2 = {license_no, serial_no, make, model}

SK3 = {make, model, year}, SK4 = {license_no, serial_no}, SKi, ..., SKn

改編自 Elmasri、Navathe（2011）：Fundamentals of Database Systems，6th Ed.，Pearson

     license_no     | serial_no |    make    |  model  | year
 -------------------+-----------+------------+---------+------
 Texas ABC-739      | A69352    | Ford       | Mustang |    2
 Florida TVP-347    | B43696    | Oldsmobile | Cutlass |    5
 New York MPO-22    | X83554    | Oldsmobile | Delta   |    1
 California 432-TFY | C43742    | Mercedes   | 190-D   |   99
 California RSK-629 | Y82935    | Toyota     | Camry   |    4
 Texas RSK-629      | U028365   | Jaguar     | XJS     |    4

K1 = {license_no}; K2 = {serial_no}; K3 = {model}; K4 = {make, year}

K1～K3 皆為單一屬性
從 K4 移除「make」或「year」任一者都會產生重複
只會從候選鍵中選出一個作為「選定」的鍵

一起來練習吧！

SQL 關聯式資料庫入門