Exam 10.12.1999
1. Land masses are either islands or continents. All masses have a name and an area; the name is the key. Some continents are connected to each other, e.g. Asia is connected to Europe and (of you ignore the Suez Canal) to Africa. No island is connectes to any other island or to a continent.
Bodies of water are either oceans or straits. A body of water has a name (the key) and an area. Islands may be either located in one ocean (e.g. Hawaii is in the Pacific Ocean) or separated from the continent by a strait (e.g. Honshu is separated from Asia by the Sea of Japan; the latter is a "strait" for our purposes). You should not assume that a strait is adjacent to only one continent or to only one island.
In the space below, design an entity/Relationship diagram suitable for representing the above information. Do not forget to indicate the keys and the multiplicity of relationships.
2. The Beer database is comprise of the following relations:
Keys are underlined.
a) Using SQL find the names and address of all drinkers, who frequent Joes's Bars and like some beer that Joes's Bar sells. Do not print any drinker more than once.
b) Using SQL delete from drinkers all drinkers in the 650 area code. You may assume that phone numbers are represented in character string of the form ')xxx) yyy-zzzz', where xxx is the area code.
c) Using SQL find for each price (that appears in Sells) the number of bars that serve at least one beer at that price.
d) Using SQL declare an assertion that says that no drinker can frequent a bar with the same address as the drinker.
e) Using SQL write a declaration for the Bars relation, including the fact that name is a kay, the default address is 'who knows', and the licence value can only be one of the strings 'beer' and 'full'. Use appropriate data types for the attributes.
f) Using relational algebra find all pairs of drinkers (i.e. their names) that have the same address. Produce the pair in only one order; e.g. if you produce (a, b), don't produce (b, a).
g) Using relational algebra find all the bars mentioned in both Sells and Frequents, but not in Bars.
3. Suppose we have a relation R with attributes ABCDE and fuctional dependencies A --> B, C -->D, BD --> E, and E -->C.
a) What are all the keys fir R?
b) Which of the given functional dependencies violate BCNF.
c) Suppose we decompose R into several relations, one of which have schema ACE. What nontrivial functional dependecies hold for ACE? You need not mention a functional dependency, if it follows from another that you mention by adding attributes to the left side, and you need not mention functional dependencies that have more than one attribute on the right.
4. Figure 1. represents a database in ODL.
a) Using OQL find the names of the set of star pairs living at the same address.
b) Using OQL find the names of the studios (in alphabetical order) which have proceded a film having Harrison Ford as a star.
Interface Movie
(extent Movies
key (title, year))
{attribute string title;
attribute integer year;
attribute integer length;
relationship Ser<Star> stars
inverse Srar::starredIn;
relationships Stuodio ownedBy
inverse Studio::owns;};
Interface Star
(extent Stars
key name)
{attribute string name;
attribute string address;
relationship Set<Movie> starredIn;
inverse Movie::stars;};
Interface Studio
(extent Studios
key name)
{attribute string name;
attribure string address;
relationship Set<Movie> owns;
inverse Movie::ownedBy;};
Figure 1.
Maximum credits: task 1 (max 16), task 2 (max 21), task 3 (max 15), task 4 (max 8)