-- Grading scheme.
-- Each part has value 1. You get 0.5 for writing the correct relational
-- calculus expression and 0.5 for the correct translation of that expression.

-- The relational calculus and the translated relational algebra expressions
-- are in the attached pdf.

-------------------------------------------------------------------------------
-- 3.1
SELECT DISTINCT x.entity
FROM nell x, nell y, nell z
WHERE x.relation = 'concept:competeswith'
AND y.relation = 'concept:companyeconomicsector'
AND z.relation = 'concept:companyeconomicsector'
AND x.entity = y.entity
AND x.value = z.entity
AND y.value != z.value;
-- The above is correct for interpretation 1.

-- The following is correct for interpretation 2. The one following it is also
-- an equivalent alternate query.
SELECT DISTINCT a.entity
FROM (
	SELECT b.entity, c.value
	FROM competeswith b, companyeconomicsector c
	WHERE b.value = c.entity
	EXCEPT
	SELECT d.entity, d.value
	FROM companyeconomicsector d
) a;
SELECT DISTINCT c.entity
FROM competeswith c, companyeconomicsector f
WHERE c.value = f.entity
AND NOT EXISTS (
	SELECT *
	FROM companyeconomicsector e
	WHERE e.entity = c.entity
	AND e.value = f.value);
-- The differences in the two result sets are due to the following observations:
-- concept:website:microsoft has multiple economic sectors.
-- concept:automobilemaker:toyota has multiple economic sectors.
-- 43 companies belong to no sector.

-------------------------------------------------------------------------------
-- 3.2
SELECT DISTINCT x.value
FROM producesproduct x
WHERE
x.entity NOT IN
(SELECT entity
	FROM competeswith);

-------------------------------------------------------------------------------
-- 3.3
SELECT x.value
FROM nell x, nell y
WHERE x.relation = 'concept:producesproduct'
AND y.relation = 'concept:headquarteredin'
AND x.entity = y.entity AND y.value = 'concept:city:san_jose';

-------------------------------------------------------------------------------
-- 3.4
SELECT DISTINCT value
FROM ProducesProduct
EXCEPT
SELECT pp.value
FROM (
	SELECT DISTINCT cw.value AS entity, pp2.value AS value
	FROM ProducesProduct pp2, CompetesWith cw
	WHERE pp2.entity = cw.entity
	EXCEPT
	SELECT DISTINCT pp3.entity, pp3.value
	FROM ProducesProduct AS pp3
) AS pp;
-- We see entries here because of the fact that the CompetesWith view is not
-- symmetrical AND hence their are many products with manufacturers that have
-- no competitors (and hence all their competitors vacuously produce the
-- product).

-------------------------------------------------------------------------------
-- 3.5
SELECT DISTINCT value
FROM ProducesProduct
EXCEPT
SELECT n.value
FROM (
	SELECT entity, value
	FROM ProducesProduct
	EXCEPT
	SELECT c.entity, p.value
	FROM ProducesProduct p, CompetesWith c
	WHERE c.value = p.entity) AS n;
-- Note that this one results in zero results while the previous one had 54.
-- The reason is that in this case, products by manufactures that are a monpoly
-- will not be seen in the output while for the previous question, the
-- monopolies will be a part of the output.
