7.9

Given the database schema \(R(A,B,C)\), and a relation \(r\) on the schema \(R\), write an SQL query to test whether the functional dependency \(B \rightarrow C\) holds on relation \(r\). Also write an SQL assertion that enforces the functional dependency. Assume that no null values are present. (Although part of the SQL standard, such assertions are not supported by any database implementation currently.)

---

1. The query is given below. Its result is non-empty if and only if \(B \rightarrow C\) does not hold on \(r\).

SELECT B
FROM r
GROUP BY B
HAVING COUNT(DISTINCT C) > 1

CREATE ASSERTION b_to_c CHECK (
    NOT EXISTS ( 
        SELECT B
        FROM r
        GROUP BY B
        HAVING COUNT(DISTINCT C) > 1
    )
)