SQL statement to prove that A->B in a R(ABCD)

How do I write a SQL statement that proves the functional dependency A → B holds given开发者_JAVA百科 a relation with attributes ABCD knowing that no record has NULL values?

SELECT * from R r1, R r2 where r1.A=r2.A and r1.B <> r2.B

This should return an empty set if the FD holds.

Starting at the top, this shouldn't necessarily be an SQL query. The question of functional dependence is addressed by proper database normalization, especially when considering second and third normal form. If you're attempting to discover functional dependencies within a single entity, you may want to decompose its attributes and revise your schema.

If you'd still like to prove direct functional dependence on the fly, construct a query that satisfies the definition of functional dependence:

In a given table, an attribute Y is said to have a functional dependency on a set of attributes X (written X → Y) if and only if each X value is associated with precisely one Y value. For example, in an "Employee" table that includes the attributes "Employee ID" and "Employee Date of Birth", the functional dependency {Employee ID} → {Employee Date of Birth} would hold.

You may prove weak functional dependence by selecting for A and B, then determining if one repeats relative to the other.

To do so, construct the query: SELECT UNIQUE A,B FROM ABCD;. Repeating elements in the unique set {A,B} disprove functional dependence A → B if and only if A repeats within the set. If A does not repeat relative to B within a sufficiently large data set, B may be said to be weakly functionally dependent on A. In other words, "we haven't disproven functional dependency, and we have a compelling statistical argument that it might be the case directly or transitively."

To do better than this is to determine additional information about the entity being inspected. If you can do that, consult my original advice about normalization above.

You could also group by the functional dependency you want to check (in this case A->B), and count if there are distinct B´s for each A.

SELECT *  
FROM R 
GROUP BY A 
HAVING COUNT (DISTINCT B) > 1;

If you would like to check if A -> B,C you could expand the query:

SELECT *  
FROM R 
GROUP BY A 
HAVING COUNT (DISTINCT B) > 1 OR COUNT (DISTINCT C)>1;