Listing 6: Code to Return Properties of Relation R on V
WITH BaseProperties AS
(
SELECT
CASE
WHEN EXISTS
(SELECT v, v FROM dbo.V
EXCEPT
SELECT r1, r2 FROM dbo.R)
THEN 'no'
ELSE 'yes'
END AS reflexive,
CASE
WHEN EXISTS
(SELECT * FROM dbo.R
WHERE r1 = r2)
THEN 'no'
ELSE 'yes'
END AS irreflexive,
CASE
WHEN EXISTS
(SELECT r1, r2 FROM dbo.R
EXCEPT
SELECT r2, r1 FROM dbo.R)
THEN 'no'
ELSE 'yes'
END AS symmetric,
CASE
WHEN EXISTS
(SELECT r1, r2 FROM dbo.R WHERE r1 <> r2
INTERSECT
SELECT r2, r1 FROM dbo.R WHERE r1 <> r2)
THEN 'no'
ELSE 'yes'
END AS asymmetric,
CASE
WHEN EXISTS
(SELECT *
FROM dbo.R AS RA
INNER JOIN dbo.R AS RB
ON RA.r2 = RB.r1
LEFT OUTER JOIN dbo.R AS RC
ON RA.r1 = RC.r1 AND RB.r2 = RC.r2
WHERE RC.r1 IS NULL)
THEN 'no'
ELSE 'yes'
END AS transitive
)
SELECT *,
CASE
WHEN reflexive = 'yes'
AND symmetric = 'yes'
AND transitive = 'yes'
THEN 'yes'
ELSE 'no'
END AS equivalence
FROM BaseProperties;