orion> sh exer07.sh # # Solutions (I hope) to chapter 7 relational algebra exercises # Each exercise is prefaced by the exercise write-up, and is # done in two ways: First step-by-step, and then in as few # steps as possible. # # Problem 15: Get supplier numbers for suppliers who supply # # project J1 # cat P15.txt LOAD SPJ.DB T1 := SPJ WHERE J# = 'J1'; ANS1 := T1[S#]; PRINT ANS1 ANS2 := (SPJ WHERE J# = 'J1')[S#]; PRINT ANS2 QUIT # ./algebra < P15.txt list of table ANS1 S# S1 S2 S3 >> list of table ANS2 S# S1 S2 S3 > algebra session complete # # Problem 21: Get full details for parts supplied by a # supplier in London # cat P21.txt LOAD SPJ.DB T1 := S WHERE CITY = 'LONDON'; T2 := T1[S#]; T3 := T2 JOIN SPJ; T4 := T3[P#]; ANS1 := T4 JOIN P; PRINT ANS1 ANS2 := ((S WHERE CITY = 'LONDON')[S#] JOIN SPJ)[P#] JOIN P; PRINT ANS2 QUIT ./algebra < P21.txt >>>>>> list of table ANS1 P# PNAME COLOR WEIGHT CITY P1 NUT RED 12 LONDON P6 COG RED 19 LONDON >> list of table ANS2 P# PNAME COLOR WEIGHT CITY P1 NUT RED 12 LONDON P6 COG RED 19 LONDON > algebra session complete # # Important note: The JOIN operation in ALGEBRA works by # joining together all similarly named fields. Projection # of extraneous fields is important. # # Problem 31: Get project names for projects supplied by # suplier S1 # cat P31.txt LOAD SPJ.DB T1 := SPJ WHERE S# = 'S1'; T2 := T1[J#]; T3 := T2 JOIN J; ANS1 := T3[JNAME]; PRINT ANS1 ANS2 := ((SPJ WHERE S# = 'S1')[J#] JOIN J)[JNAME]; PRINT ANS2 QUIT ./algebra < P31.txt >>>>> list of table ANS1 JNAME SORTER CONSOLE >> list of table ANS2 JNAME SORTER CONSOLE > algebra session complete # # Problem 40: Get project numbers for projects not # supplied with any red part by any London # supplier. # cat p40.txt LOAD SPJ.DB T1 := P WHERE COLOR = 'RED'; T2 := T1[P#]; T3 := T2 JOIN SPJ; T4 := S WHERE CITY = 'LONDON'; T5 := T4[S#]; T6 := T5 JOIN T3; T7 := T6[J#]; T8 := J[J#]; ANS1 := T8 MINUS T7; PRINT ANS1 ANS2 := J[J#] MINUS (((P WHERE COLOR = 'RED')[P#] JOIN SPJ) JOIN (S WHERE CITY = 'LONDON')[S#])[J#]; PRINT ANS2 QUIT ./algebra < p40.txt list of table ANS1 J# J2 J5 J6 >> list of table ANS2 J# J2 J5 J6 > algebra session complete # # Problem 42: Get part numbers for parts supplied to all # projects in London (a "forall" question). # cat P42.txt LOAD SPJ.DB T1 := J WHERE CITY = 'LONDON'; T2 := T1[J#]; T3 := SPJ[P#,J#]; ANS1 := T3 DIVIDEBY T2; PRINT ANS1 ANS2 := SPJ[P#,J#] DIVIDEBY (J WHERE CITY = 'LONDON')[J#]; PRINT ANS2 QUIT ./algebra < P42.txt >>>>> list of table ANS1 P# P3 P5 >> list of table ANS2 P# P3 P5 > algebra session complete