1 / 7

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

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

aletha
Télécharger la présentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related