Problem Set
The database you will use for this assignment contains information about people, vacation locations, and when those people visited them. The relations are:
Person ( ID, Name)
Location( PlaceName , Country, MainAttraction )
Visited( ID, PlaceName, Year)
You should assume that place names are unique in the world. A sample instance of this database is given below.
Person ID Name Visited ID PlaceName Year
__________________________ __________________________________
1 Aaron 1 Athens 1999
2 Betty 1 Philadelphia 2003
3 Charles 2 Philadelphia 2002
4 Dorothy 2 Seattle 2003
5 Evan 2 Agra 2004
6 Francine 3 Philadelphia 2002
3 Philadelphia 2005
4 Whistler 2006
4 Philadelphia 2004
5 Agra 2001
5 Santorini 2005
6 Sydney 2006
6 Philadelphia 2002
Location PlaceName Country MainAttraction
____________________________________________________
Sydney Australia Opera House
Agra India Taj Mahal
Whistler Canada Skiing
Santorini Greece Caldera
Athens Greece Acropolis
Philadelphia USA Cheesesteaks
Seattle USA Space Needle
1) (15 Point)
The following relational algebra and tuple relational calculus queries refer to the aforementioned schema. For each query
(a) explain in words the meaning of the result each one computes,
and (b) evaluate it over the sample instance provided.
1.1) p PlaceName Location − p PlaceName (sYear > 2003 Visited)
1.2) {T | $P( PÎ Person ^ P.Name = T.Name
^ $ V1 (V1 ÎVisited ^ $ V2 (V2 Î Visited
^ V1.ID = P.ID ^ V2.ID = P.ID
^ V1.PlaceName = T. PlaceName
^ V2.P laceName = T. PlaceName
^ V1.Year 6 ≠ V2.Y ear ))) }
1.3) (p ID, PlaceName Visited) ÷ pID Person
2) (60 point)
Write (a) relational algebra and (b) tuple relational calculus queries for each of the following. If a query is long, feel free to break it up into a series of queries with intermediate answers stored in temporary relations (e.g. “temp=.....”).
2.1) Return the names of everybody who visited Greece in 2004.
2.2) Return the names of the unfortunate people who didn’t visit anywhere at all
2.3) Return the names of the lucky people who visited all of the places listed in Location table
2.4) Return all the places that have been visited by exactly one person.
(Remember that neither the relational algebra nor the relational calculus has a “count” operation so you will have to find another way to test the “exactly one person” property.)
2.5)Return the names of the people that have visited only places that were also visited by everybody
คิดว่าเข้า copy มาจากหนังสือ Text เลยมั้ง มีใครเคยบอก อ. เปล่าว่าขอ การบ้านเป็นภาษาไทยอะ
