關係型資料庫設計/檢索資料

關係代數

關係代數是一種用於表達資料庫或關係操作的正式語言。最重要的操作是

σ（小寫西格瑪） - 選擇符合條件的值）
Π（大寫派） - 投影，僅顯示命名的屬性
X 或笛卡爾積（顯示一個關係中每一行與另一個關係中每一行的組合），
（基於條件連線，等值連線，自然連線（基於兩個關係的公共欄位的等值連線））。

此外，還有並集、交集、差集的集合操作： $A\cup B$ , $A\cap B$ , $A-B$ （其中 x 不是 A 與 B 交集的元素）。

還有一種除法運算，它類似於自然連線，其中第二個關係的屬性是第一個關係的屬性的一個子集，並且只選擇第一個關係中具有第二個關係中對應屬性值的那些行，並投影非公共屬性。

A 除以 B，可能更好地說是，給定 B 的 A，或 A 中所有將顯示 B 中所有行的行。

關係演算

SQL

SQL 語句分為兩種型別，資料宣告語句 (DDL) 和資料操作語句 (DML)（L 代表語言）。

資料操作 SQL

這是迄今為止 SQL 最主要的用途，因為一旦資料庫設計完成，SQL 資料操作通常靈活到足以彌補糟糕的設計，並且允許空值，因此可以繞過設計不當的規範化，方法是將欄位留空。因此，瞭解資料操作語句比了解資料宣告語句更有用，因為這樣你才能知道如何處理糟糕的設計，這可能比精通提取、轉換和載入，同時又精通最佳規範化資料庫設計的專家更可能成為招聘的原因。

檢索資料

使用 SQL 從資料庫檢索資料是使用 *SELECT* 關鍵字實現的，範圍從使用非常簡單的語句檢索表的單個列，到使用條件連線、分組、排序、限制等實現的極其複雜的語句。

最基本的 SELECT 查詢採用以下形式

SELECT <columns> 
  FROM <table>;

多個列名用逗號分隔，星號字元可用作萬用字元來檢索表中的所有列。

從表中檢索給定列或列的另一種方法是使用以下語法

SELECT table.column ...

當然，能夠根據給定的條件僅從表中返回某些行非常重要。此操作的基本語法如下

SELECT <columns>
  FROM <table>
 WHERE <condition>;

到目前為止，基於這些關於相關 SQL 語法基本總結的語句，可以讓你從符合 SQL 標準的資料庫中檢索資料，但不能以關係的方式檢索。以這種方式檢索資料的關鍵是 *JOIN* 關鍵字。最基本的 JOIN 語法是

SELECT <columns>
  FROM <table1>
  JOIN <table2> ON <join_condition>
 WHERE <condition>;

以這種方式使用 `JOIN` 關鍵字可以讓你根據條件從多個表中檢索列到單個結果集中。這使你能夠在檢索時將已經規範化為多個表的資料“重新連線”成一致的資料。

但是，規範化的原因之一是，第二個表中可能並不總是有與第一個表中任何資料相對應的資料，反之亦然。在這種情況下，需要決定如何處理未“連線”在一起的資料。通常，需要決定是忽略第二個表中與第一個表中的任何資料不匹配的資料，還是忽略第一個表中與第二個表中的任何資料不匹配的資料；在 SQL 中，這可以透過分別使用 `LEFT JOIN` 或 `RIGHT JOIN` 來實現。

資料宣告 SQL

這些語句為你構建一個數據庫。層次結構是資料庫包含一個或多個模式，模式包含一個或多個表，表包含一個或多個列。

資料宣告還包含用於建立使用者或角色的語句，這些語句允許使用許可權分配進行訪問控制：資料庫將定義某些型別的操作，如 SELECT、REFERENCE、UPDATE、DELETE、USAGE、CREATION，並允許向用戶或角色授予這些操作，以不同的粒度，例如，授予資料庫、模式、表或表的某個部分。

以下是使用 DML 構建資料庫的一個示例，沒有任何規劃。資料庫設計的一種方法可能是將這種駭客行為放在一個帶有網路駭客行為的前端，並邀請人們免費玩虛擬商業模擬，看看會出現什麼問題。

CREATE ROLE CUSTDB_ADMIN;

GRANT CUSTDB_ADMIN CREATE DATABASE;

CREATE DATABASE CUSTDB OWNER CUSTDB_ADMIN;

CREATE SCHEMA CUST;

CREATE TABLE CUST.CUSTOMER (
  CUST_ID INTEGER PRIMARY KEY,
  FIRSTNAME TEXT,
  SURNAME TEXT,
  ADDRESS TEXT,
  PHONE TEXT
);

CREATE SCHEMA STOCK;

CREATE TABLE STOCK.PRODUCT (
  PROD_ID INTEGER PRIMARY KEY,
  REFERENCE_NO NUMERIC(15),
  NAME TEXT, DESCRIPTION TEXT
);

CREATE TABLE STOCK.INVENTORY (
  INVENT_ID INTEGER PRIMARY KEY,
  PROD_ID INTEGER REFERENCES STOCK.PRODUCT, 
  QTY INTEGER
);

CREATE TABLE STOCK.INVENTORY_CHANGE (
  INV_CHANGE_ID INTEGER PRIMARY KEY,
  INVENT_ID INTEGER REFERENCES STOCK.INVENTORY,
  QTY INTEGER, WHEN TIMESTAMP
);

CREATE TABLE STOCK.PRICING (
  PRICING_ID INTEGER PRIMARY KEY,
  PRODUCT_ID INTEGER REFERENCES PRODUCT,
  PRICE NUMERIC(10,2)
);

CREATE TABLE STOCK.COST (
  COST_ID INTEGER PRIMARY KEY,
  PRODUCT_ID INTEGER REFERENCES PRODUCT,
  COST NUMERIC(10,2)
);

CREATE SCHEMA SALES;

CREATE TABLE SALES.PRICE (
  PRICE_ID INTEGER PRIMARY KEY,
  PROD_ID INTEGER REFERENCES STOCK.PRODUCT,
  PRICING_ID REFERENCES STOCK.PRICING,
  ALTERATION NUMERIC(10,2)
);

CREATE TABLE CUST.ORDER (
  ORDER_ID INTEGER PRIMARY KEY,
  CUST_ID INTEGER REFERENCES CUST.CUSTOMER,
  WHEN TIMESTAMP
);

CREATE TABLE CUST.ORDER_ITEM (
  ORDER_ITEM_ID INTEGER PRIMARY KEY,
  ORDER_ID INTEGER REFERENCES CUST.ORDER,
  PROD_ID INTEGER REFERENCES STOCK.PRODUCT,
  QTY INTEGER,
  PRICE_ID INTEGER REFERENCES SALES.PRICE
);

請注意，每個表都有一個主鍵約束，並且許多表都有一個外部索引鍵約束，其中關鍵字 REFERENCES 可以在列宣告之後應用（其中列的型別必須與主鍵的型別匹配），或者在表的末尾應用，其中 FOREIGN KEY CONSTRAINT 欄位 REFERENCES other_table 是字首。

將資料宣告 SQL 關聯到使用規範化的關係設計

在這些表中，沒有複合鍵，如果這些表處於 3NF，那麼根據 Date/Fagin 的說法，它們也處於 5NF，如上所述。這些表是否符合 3NF 的定義 - 對於函式依賴 X->Y，X 是一個超鍵或 Y 是候選鍵的一部分？

對於客戶：FD 是 CUST_ID -> FIRSTNAME、LASTNAME、ADDRESS、PHONE - 3NF（也是 5NF）

對於產品：FD 是 PROD_ID -> REFERENCE_NO -1、REFERENCE_NO -> NAME、DESCRIPTION -2

FD1 X 是一個超鍵，FD2 Y 是 NAME、DESCRIPTION，可能是一個候選鍵，因此 3NF 成立。但是，可能有人爭辯說 NAME 是一個候選鍵，而 TEXT 是 NAME 的依賴項，即 NAME -> DESCRIPTION。在這裡，NAME 對於每個產品行可能都是唯一的，因此它也是一個候選鍵，因此 X 是一個超鍵，並且 3NF 對於 NAME->DESCRIPTION 仍然成立。

PROD_ID 和 REFERENCE_NO 之間似乎存在明顯的問題，並且可能應該將產品分解為 (PROD_ID, REFERENCE_NO)、(PROD_ID, NAME, DESCRIPTION)。

但 2NF 成立（沒有非主屬性依賴於最小超鍵或候選鍵的一部分而不是全部，這些鍵是 PROD_ID 或 REFERENCE_NO），3NF 成立（X->A，X 是一個超鍵，或者 A 是候選鍵的一部分），甚至 BCNF 也成立（REFERENCE_NO->NAME、DESCRIPTION，並且 REFERENCE_NO 是一個備用候選鍵）。因此，分解似乎沒有必要。

對於庫存表，INVENT_ID -> PROD_ID, QTY 是主鍵，但 PROD_ID -> QTY 也可能成立，所以 QTY 可以放到產品表中。庫存表可以刪除，庫存變更表應該引用產品表。

對於庫存變更表，原來的 invent_id 現在是 prod_id，而 prod_id 不是候選鍵，因為同一個 prod_id 可能會有多個庫存變更。因此，該表的 FD 為 inv_change_id -> prod_id, qty, when。符合 3NF（也符合 BCNF）。

對於定價表，同一個產品可能有多個不同的價格，應該有一個類似定價期間和定價原因的資訊，可能需要單獨的表。該表的 FD 為 pricing_id -> price, prod_id （符合 3NF，BCNF）。

對於訂單表，Order_id -> customer, when。

對於訂單項表，order_item_id -> Order_id, prod_id, qty, pricing_id，但 pricing_id -> prod_id, price，所以在構建最小覆蓋 FD 集時，order_item_id 的 FD 可能變成 order_item_id -> order_id, pricing_id, qty，這樣就消除了 prod_id。

由於 pricing_id -> prod_id，這個 FD 中 X 不是 order_item 關係的超鍵，違反了 BCNF，但不違反 3NF，因為 pricing_id 是候選鍵 order_id, pricing_id, qty 的一部分。

最好先嚐試達到 BCNF，所以應該從訂單項表中移除 prod_id。

因此，我們使用正規化化來檢查一個由增量設計過程產生的有機資料庫。ER 圖可能會顯示訂單項、定價和訂單之間冗餘關係，例如：

訂單項 - 關於 - 產品是一個冗餘關係。

另外，檢查後發現所有表都只有一個屬性的主鍵，並且都符合 3NF，因此也符合 5NF，儘管看起來只有多值依賴被正確分解了；例如，訂單 ->-> 訂單項，產品 ->-> 定價。