This exercise builds upon Exercise 232 concerning World War

This exercise builds upon Exercise 2.3.2 concerning World War II capital ships. Recall it involves the following relations: Classes (.class, type, country\', numGuns, bore, displacement) Ships name, class, launched) Battles, date) Outcomes (ship, battle, result) Figures 2.22 and 2.23 give some sample data for these four relations. Note that, unlike the data for Exercise 2.4.1, there are some \"dangling tuples\" in this data, e.g._, ships mentioned in Outcomes that are not mentioned in Ships. Write expressions of relational algebra to answer the following queries. You may use the linear notation if you wish. For the data of Figs. 2.22 and show the result of your query. However, your answer should work for arbitrary data, not just the data of these figures. Give the class names and countries of the classes that carried guns of at least 1 6 - inch bore. Find the ships launched prior to 1921. Find the ships sunk in the battle of the Denmark Strait. List the name, displacement. and number of guns of the ships engaged in the battle of Guadalcanal Find the classes that had only one ship as a member of that class.

Solution

a) select class, country from classes where bore >= 16
b) select ship.name from classes,ships where classes.class = ships.class and ships.class < 1921
c) select ship from outcomes where result=\"sunk\" and battle = \"Denmark Strait\"
e) select name, displacement, numGuns from ships,classes,battles where classes.class = ships.class and ships.name = outcomes.ship and outcomes.battle = \"Guadalcanal\"
f) select classes, count(*) as cont from classes, ships where classes.class = ships.class and cont = \"1\" group by class