Micah Acinapura			Database systems			Chapter 4

key: @ = selections
	 # = pojection
	 % = intersection
	 U = unioin
	 >< = join
	 X = cross join
	p() = rename

1. The idea that relational operators can be composed has two concepts. One is that operators
 such as intersection can be created out of other operators, i.e R-(R-S), which lets the
 develop less operators because the can make ones out of ones they already built. The other 
 concept is that operators like %,U,-,\, can be combined to act as one "special" operator.
 
 2. (1) R1 U R2			min = N2		max = N1+N2
 	(2) R1^ R2			min = 0 		max = N1
 	(3) R1 - R2 		min = 0			max = N1
 	(4) R1 X R2			min = N1*N2		max = N1*N2
 	(5) @a=5(R1)		min = 0			max = N1
 	(6) #a(r1)			min = 1 		max = N1
 	(7) R1/R2			min = 0			max = N1
 	
 	
 3. (1) #sname(#sid( (@color='red'Parts) >< catalog) >< suppliers)
 	(2) #sname(#sid( (@color='red'Parts) >< suppliers) U (@color='green'Parts) >< suppliers))
	(3) #sname(#sid((@color='red' Parts) >< (@address = '221PacketStreet' Suppliers))) 
 	(4) #sname(#sid( (@color='red'Parts) >< suppliers ) % (@color='green'Parts) >< suppliers))
 	(5) (šsid,pid Catalog)/(špid Parts)
 	(6) (šsid,pid Catalog)/(špid @color = 'red' Parts)
 	(7) (šsid,pid Catalog)/(špid (@color = 'red' V 'green') Parts)
 	(8) (šsid,pid Catalog)/(špid @color = 'red' Parts) U 
 			(šsid,pid Catalog)/(špid @color = 'green' Parts)
 	(9) p(R1, catalog)
 		p(R2, catalog)
 		šR1.sid,R2.sid (@(R1.pid = R2.pid ^ R1.sid!=R2.sid ^ R1.cost > R2.cost (R1XR2)));
 		
 		--similar to 4.5 #6
 		
 	(10)p(R1, catalog)
 		p(R2, catalog)
 		špid(@(R1.pid = R2.pid ^ R1.sid != R2.sid) (R1XR2));
 		
 	(11)
 
 4. (1) Project the name of suppliers who have red parts or parts that cost less then $100.
 	(2) Project the name of suppliers who have red parts that cost lets then $100.
 	(3) Project the name of suppliers who have red parts or green parts 
 		or parts that cost less then $100.
 	(4) Project the name of suppliers who have red or green parts that cost lets then $100.
 	(5) Project the name of suppliers who have red parts AND green parts 
 		or parts that cost less then $100.
 		
 
 5.	(1) SELECT eid FROM aircraft A, certified C WHERE A.aname = 'Boeing' AND C.aid = A.aid;
 	(1)#eid(@(certified.aid = #aid(@(aircraft.name = 'Boeing))));
 	
 	(2) SELECT ename FROM employees E WHERE E.eid = 
 			(SELECT eid FROM aircraft A, certified C WHERE A.aname = 'Boeing' AND C.aid = A.aid);
 	
 	(2) ¸ename( @ eid = (#eid(@(certified.eid = #eid(@(aircraft.name = 'Boeing)))) employees));
 	
 	(3) SELECT aid FROM aircraft A where A.crusing_range >= 
 			(SELECT distance FROM flights F WHERE F.source = 'Bonn' ^ F.destination = 'Madras');
 	
 	(3) šaid(aircraft.crusing_range = 
 						(šdistance(@(flights.source = 'Bonn' ^ flights.destination = 'Madras'))))
 	
 	(4) SELECT flno FROM flights F WHERE f.distance =< 
 			(SELECT crusing_range FROM aircraft A where A.aid =
 				(SELECT aid FROM certified C WHERE c.eid =
 					(SELECT eid FROM employees E WHERE salary > 10,00)));
 	
 	(4) ((#eid(certified(#eid(salary>10,000, employees))><aircraft A)#aid >< flights)#flno
 	
 	(5) SELECT ename FROM employees E WHERE E.eid = (SELECT eid FROM certified C WHERE C.aid =
 			(SELECT aid FROM aircraft A WHERE A.crusing_range > 3,000 AND A.aname != 'Boeing');
 	
 	(5) ¸ename(@(employees.eid = šeid(certified.aid = 
 				(šaid(@ aircraft.crusing_range > 3,00 ^ aircraft.aname != 'Boeing')))));
 	
 	(6) 
 	(6) p(R1, employees)
 		P(R2, employees)
 		šeid(R1) - (šeid( @(R1.salary < R2.salary (R1XR2))));
 		
 	(7)
 	(7) p(R1, employees)
 		P(R2, employees)
 		p(R3, šeid(R1) - (šeid( @(R1.salary < R2.salary (R1XR2)))))
 		p(R4, employees)
 		šeid(R4-R3-( šeid(R1) - (šeid( @(R1.salary < R2.salary (R1XR2))))));

	(8,9,10) dont know how to count in SQL.
	(11)?