DB HW2 B91902003 陳彥名 1.A π person-name ( σ company-name= “ First Bank Corporation ” (works) )

1. DB HW2 B91902003 陳彥名 1.A πperson-name(σcompany-name=“First Bank Corporation” (works) ) 1.B πperson-name ,city(σcompany-name=“First Bank Corporation” (employee ∞ works)) 1.C πperson-name ,city street( (σcompany-name=“First Bank Corporation”, && salary > 10000 (works) ) ∞ employee) 1.D πperson-name (employee ∞ works ∞ company) 1.E πperson-name(ρ( t1,employee ∞ manages) ∞t1.manager-name=t2.person-name &&t1.city=t2.city &&t1.street = t2.streetσ( t2,employee) )

2. 1.F πperson-name(σcompany-name≠“First Bank Corporation” (works) ) 1.G πperson-name (works)- πperson-name (ρ(t1,works) ∞t2.company-name =“Small Bank Corporation && t1.salary <=t2.salary ρ(t2,works) ) 1.H π company-name(company / (πcity(σcompany-name=“Small Bank Corporation” (company)))) 1.I ρ(t1, πperson-name , company-name( works) ) π person-name (σperson-name = person-name1 = person-name2 && company-name ≠ company-name1 ≠ company-name2 (ρ( C(3->company-name1,5->company-name2,4->person-name1,6->person-name2),t1 ∞ t1∞ t1) ))

3. [Query A] • SELECT T.[Planet's Name] • FROM TimeTable T • WHERE T.[Character's Name] = "Princess Leia" AND T.Movie=3; [Results of Query A] • Tatooine • Endor [Query B] • SELECT COUNT(T.[Character's Name]) • FROM TimeTable T • WHERE T.[Planet's Name] = "Dagobah"AND T.[Time of Departure]=10 AND T.Movie=3;[Results of Query B] [Results of Query B] • 0 [Query C] • SELECT distinct T.[Character's Name] • FROM TimeTable T, Characters C • WHERE T.[Planet's Name] = C.[Homeworld] AND T.[Character’s Name]=C.Name ANDT.Movie=2 ; [Results of Query C] • None [Query D] • SELECT distinct T.[Character's Name] • FROM TimeTable T, Planets P • WHERE T.[Planet's Name] = P.[Name] AND P.Affiliation = "rebels" ; [Results of Query D] • C-3 PO • Chewbacca • Darth Vader • Han Solo • Luke Skywalker • Princess Leia • R2-D2

4. [Query E] • SELECT distinct T.[Planet's Name] • FROM TimeTable T, Characters C • WHERE T.[Character's Name] = C.[Name] AND C.Race="Droid" ; [Results of Query [E] • Bespin • Dagobah • Endor • Hoth • Tatooine [Query F] • SELECT C.name, P.name, sum (T.[Time of Departure]-T.[Time of Arrival]+1) as TotalTime • FROM Characters C, TimeTable T, Planets P • WHERE T.[Character's Name]=C.Name and T.[Planet's Name]=P.Name and P.Affiliation="neutral" • GROUP BY • C.Name , P.Name C.name, P.name; [Results of Query F] • C-3 PO Bespin 5 • C-3 PO Tatooine 6 • Chewbacca Bespin 5 • Chewbacca Endor 6 • Chewbacca Tatooine 6 • Darth Vader Bespin 6 • Han Solo Bespin 5 • Han Solo Endor 6 • Han Solo Tatooine 6 • Jabba the HuttTatooine 25 • Lando Calrissian Bespin10 • Lando Calrissian Endor 2 • Lando Calrissian Tatooine 3 • Luke Skywalker Bespin 3 Luke Skywalker Dagobah 7 Luke Skywalker Endor 4 Luke Skywalker Tatooine 5 Obi-Wan Kanobi Tatooine 3 Owen Lars Tatooine 2 Princess Leia Bespin 5 Princess Leia Endor 6 Princess Leia Tatooine 3 R2-D2 Bespin 3 R2-D2 Dagobah 7 R2-D2 Endor 4 Rancor Tatooine 26 Yoda Dagobah 28

5. [Query G] • SELECT T.Movie, C.Name • FROM Characters C, TimeTable T, Planets P • WHERE ((C.Race="Human" AND P.Type="desert" ) OR( C.Race="Droid" AND P.Type="swamp")) • AND C.Name=T.[Character's Name] and P.Name=T.[Planet's Name] • ORDER BY T.Movie, C.Name [Results of Query G] • 1 Han Solo • 1 Luke Skywalker • 1 Obi-Wan Kanobi • 1 Owen Lars • 2 R2-D2 • 3 Han Solo • 3 Lando Calrissian • 3 Luke Skywalker • 3 Princess Leia • 3 R2-D2 [Query H] • SELECT distinct Movie, [Character's Name] • FROM TimeTable t1 • WHERE NOT EXISTS ( • SELECT * • FROM TimeTable t2 • WHERE • t1.Movie=t2.Movie AND (SELECT COUNT([Planet's Name]) • FROM TimeTable • WHERE t1.[Character's Name] = [Character's Name] AND Movie = t1.Movie) < (SELECT COUNT([Planet's Name]) FROM TimeTable WHERE t2.[Character's Name] = [Character's Name] and Movie = t2.movie)) [Results of Query H] • 1 Luke Skywalker • 2 Luke Skywalker • 2 R2-D2 • 3 Luke Skywalker

6. [Query I] • SELECT distinct Movie, [Planet's Name] • FROM TimeTable t1 • WHERE NOT EXISTS ( • SELECT * • FROM TimeTable t2 • WHERE • t1.Movie=t2.Movie AND (SELECT COUNT([Character's Name]) • FROM TimeTable • WHERE t1.[Planet's Name] = [Planet's Name] AND Movie = t1.Movie) < • (SELECT COUNT([Character's Name]) FROM TimeTable • WHERE t2.[Planet's Name] = [Planet's Name] and Movie = t2.movie)) [Results of Query I] • 1 Tatooine • 2 Bespin • 3 Tatooine [Query J] • SELECT distinct t.Movie ,C.Race, C.[Name] • FROM TimeTable t ,Characters C • WHERE C.Name = t.[Character's Name] AND NOT EXISTS( • SELECT * • FROM TimeTable t1,Characters C1 • WHERE C.Race = C1.Race AND t.Movie=t1.Movie AND( • SELECT SUM(t2.[Time of Departure]-t2.[Time of Arrival]+1) • FROM TimeTable t2 • WHERE t.[Character's Name] = C.Name AND C.Name = t2.[Character's Name] AND t.Movie=t2.Movie • ) < • (SELECT SUM(t3.[Time of Departure]-t3.[Time of Arrival]+1) • FROM TimeTable t3 WHERE t1.[Character's Name] = C1.Name AND C1.Name=t3.[Character's Name] AND t.Movie = t3.Movie • ) • );

7. [Results of Query J] 1 Droid C-3 PO 1 Human Darth Vader 1 Hutt Jabba the Hutt 1 Rancor Rancor 1 Unknown Yoda 1 Wookie Chewbacca 2 Droid R2-D2 2 Human Luke Skywalker 2 Hutt Jabba the Hutt 2 Rancor Rancor 2 Unknown Yoda 2 Wookie Chewbacca 3 Droid R2-D2 3 Human Luke Skywalker 3 Hutt Jabba the Hutt 3 Rancor Rancor 3 Unknown Yoda 3 Wookie Chewbacca [Query K] • SELECT distinct t.[Planet's Name] • FROM TimeTable t • WHERE t.[Planet's Name]NOT IN( • SELECT t1.[Planet's Name] • FROM TimeTable t1 • WHERE (t1.Movie = 1 OR t1.Movie = 2)); [Results of Query K] • Endor