consider the following database that contains

Question

Consider the following database that 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.

Consider the schema given above.

(a) Give a primary key for each relation. Are there any relations for which there is an alternate candidate key which you have not chosen as the primary key? Why or why not?

(b) State all referential integrity constraints (inclusion dependencies) that should hold on these relations.

( c) Explain how the difference between an outer join and an inner join. What could be the use of each?

Explain what each of the following relational algebra queries would return:

a) π_PlaceName σ_Year<2005(Visited)

b) π_Name(Person) ? (π_ID(Person) - π_ID(Visited)))

c) π_Name(Person) ? (π_ID,_PlaceName(Visited ÷ (π_PlaceNameLocation)))