SQL Lossy Decomposition
3 min readMay 2, 2020
Translate from : https://www.javatpoint.com/dbms-relational-decomposition
無損與有損分解, 主要用於檢測資料表設計後, 正規化的狀態。
無損分解 Lossless Decomposition
大資料表若正規化分解欄位後, 若能透過 JOIN, LEFT JOIN 連接返回原本的資料狀況, 資料並沒有任何損失, 稱之為 無損分解。
範例
-- origin table
+----------+--------------+---------+
| salesman | manufacturer | product |
+----------+--------------+---------+
| Jay | ACME | Dicer |
| Jay | ACME | Slicer |
| Jay | ACE | Cutter |
| Jay | ACE | Dicer |
| Sheila | ACME | Dicer |
| Sheila | ACME | Slicer |
+----------+--------------+---------+-- table saleman_manfacturer
+----------+--------------+
| salesman | manufacturer |
+----------+--------------+
| Jay | ACME |
| Jay | ACE |
| Sheila | ACME |
+----------+--------------+-- manufacturer_product
+--------------+---------+
| manufacturer | product |
+--------------+---------+
| ACME | Slicer |
| ACME | Dicer |
| ACE | Dicer |
| ACE | Cutter |
| AZ.Inc | Slicer |
| AZ.Inc | Cutter |
+--------------+---------+
Lossless Decomposition check
SELECT sm.salesman, sm.manufacturer, mp.product
FROM salesman_manufacturer sm
JOIN manufacturer_product mp ON mp.manufacturer = sm.manufacturer
