SELECT [SCity='Houston'] (Supplier);

PROJECT [SNAME] (Supplier); 

Supplier times Supply;

Supplier times Supply

natural join:
	1. Times
	2. common attribute (same name) have equal value.
	3. remove redundant.
	
	
Supplier JOIN supply;

SELECT * 
FROM supplier NATURAL JOIN supply;

(PROJECT [PNUM] (SELECT [SNum='S2'] (Supply))) JOIN Part;

4.   Show all information of parts with a color of Red and weight more than 5 lbs.

SELECT [Color='Red' AND Weight>5] (Part);

expected: empty set

Show all information of parts with a color of Blue and weight more than 5 lbs.

expected: P6 and P7.

SELECT [Color='Blue' AND Weight>5] (Part);

5.   Show all information of parts with a color of Red or Blue.

SELECT [Color='Red' Or 'Blue'] (Part);

-- mind the data type mismatch

SELECT [Color='Red' Or 'Blue'] (Part);

SELECT [Color='Red' OR COLOR='blue'] (Part);
-- RA interpreter does not support OR.

1. Red parts
SELECT [Color='Red'] (Part)

2. Blue parts
SELECT [COLOR='Blue'] (Part)

3. 1 or 2: R1 union R2

-- workarounds.
SELECT [Color='Red'] (Part)
UNION
SELECT [COLOR='Blue'] (Part);

19.  Show all information of parts supplied by supplier S2 and S3.

(PROJECT [PNUM] (SELECT [SNum='S2' AND SNum = 'S3'] (Supply))) JOIN Part;
-- x==1 and x==2 -> False

Expected: P4.

1. PNum by S2: 

PROJECT [PNUM] (SELECT [SNum='S2'] (Supply))

2. PNum by S3: 

PROJECT [PNUM] (SELECT [SNum='S3'] (Supply))

3. In 1 and also 2.

(PROJECT [PNUM] (SELECT [SNum='S2'] (Supply)))
INTERSECT
(PROJECT [PNUM] (SELECT [SNum='S3'] (Supply)));

4. R3 JOIN Part

((PROJECT [PNUM] (SELECT [SNum='S2'] (Supply)))
INTERSECT
(PROJECT [PNUM] (SELECT [SNum='S3'] (Supply)))) JOIN Part;

17.  Show all information of parts supplied by supplier S2 or S3.

Expected: P1, P2, P4, P5, P6, P7

1. PNum by S2: 

PROJECT [PNUM] (SELECT [SNum='S2'] (Supply))

2. PNum by S3: 

PROJECT [PNUM] (SELECT [SNum='S3'] (Supply))

3. In 1 or in 2.

(PROJECT [PNUM] (SELECT [SNum='S2'] (Supply)))
UNION
(PROJECT [PNUM] (SELECT [SNum='S3'] (Supply)));

4. R3 JOIN Part

((PROJECT [PNUM] (SELECT [SNum='S2'] (Supply)))
UNION
(PROJECT [PNUM] (SELECT [SNum='S3'] (Supply)))) JOIN Part;

19b. Show all information of parts supplied by supplier S2 but not S3.

(PROJECT [PNUM] (SELECT [SNum='S2' AND SNum <> 'S3' ] (Supply))) JOIN Part;

Expected: P1, P2.

[SNum='S2' AND SNum <> 'S3' ] x==1 & x <> 2 (subsumption: x==1 => x<>2)

1. PNum by S2: 

PROJECT [PNUM] (SELECT [SNum='S2'] (Supply))

2. PNum by S3: 

PROJECT [PNUM] (SELECT [SNum='S3'] (Supply))

3. In 1 and not in 2.

(PROJECT [PNUM] (SELECT [SNum='S2'] (Supply)))
MINUS
(PROJECT [PNUM] (SELECT [SNum='S3'] (Supply)));

4. R3 JOIN Part

((PROJECT [PNUM] (SELECT [SNum='S2'] (Supply)))
MINUS
(PROJECT [PNUM] (SELECT [SNum='S3'] (Supply)))) JOIN Part;

SPring 2020 HW #3

(b) List the titles of all films with the actor with id #148.

SELECT DISTINCT f.title
FROM film AS f INNER JOIN film_actor AS fa
	ON (f.film_id = fa.film_id)
WHERE fa.actor_id = 148;

PROJECT [title] (film JOIN (film_actor with actor_id 148))



PROJECT [title] (film JOIN (SELECT [actor_id = 148] (film_actor)));
-- empty set

-- remove last_update before join.
PROJECT [title] (film JOIN (PROJECT [film_id, actor_id] (SELECT [actor_id = 148] (film_actor))));


common attributes; two interpretation: (1) same name, (2) same semantic meaning..

PROJECT [title] (film JOIN (PROJECT [film_id] (SELECT [actor_id = 148] (film_actor))));