Functional Dependencies

A functional dependency X → Y says each value of X comes with exactly one value of Y; X is the determinant. A candidate key is a minimal set of columns that determines all the others. With the key (order_no, sku), the sheet holds:

order_no → status, email and sku → title, category: partial dependencies on part of the key;
email → cust_name, country: transitive, reaching the key only through email;
(order_no, sku) → qty, price: a full dependency on the whole key.

Business rules define dependencies, but data can refute one, by grouping on the determinant:

Refuting country → email, then MySQL's own dependency checkSQL

SELECT country, COUNT(DISTINCT email) AS emails FROM sheet GROUP BY country HAVING emails > 1;
SELECT order_no, cust_name, SUM(qty) AS items FROM sheet GROUP BY order_no;

Output

+---------+--------+
| country | emails |
+---------+--------+
| MY      |      2 |
+---------+--------+
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains
  nonaggregated column 'shop.sheet.cust_name' which is not functionally dependent on columns
    in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The same test finds no violation of email → cust_name, but passing proves nothing: sku → price passes too, yet a sale would break it, so order lines keep their own unit_price. ONLY_FULL_GROUP_BY accepts a non-grouped column only when a key or an equality makes it dependent. The sheet declares none; the same report on shop runs (INNER JOIN and the ON Clause).

MENU