Database Assignment 1
Relational algebra for the SHIPS database, due 22 September 2017,
t class time. Best attempted before the lab of 20 September (because that
involves similar queries in SQL).
Capital ships
The tables in database ships exists
on
the server, so you can see the schema. Most of the data is the same
as that given in the textbook, on p. 56. I have added a few ships to
make some queries more interesting. Your queries should be correct,
independent of the data that may be included.
The data in the text is currently included. Note that there is NO
referential integrity between outcomes and ships.
In relation Battles, There is a single
date for the battle of Denmark Strait.
I have added some extra rows to Outcomes, I must point out that these rows are
in no way actual historical data, they will just serve to test the
query about ships that "lived to fight another day"
The Schema
- CLASSES(class,
type, country, numguns, bore, displacement)
- SHIPS (name,
class, launched)
- BATTLES (name,
date)
- OUTCOMES (ship, battle, result)
psql ships
... to go look at the on-line version of the data
The assignment:
Note that there are some "dangling tuples" for example ships
mentioned in outcomes that are not in the SHIPS table.
"Write expressions in relational algebra to answer the following
queries. You may do this as trees or use 'liner notation' "
- Give the class names and countries of the classes that carried
guns of at least 16-inch bore.
- Find the ships launched prior to 1921.
- find the ships sunk in the battle of Denmark Straight.
- The treaty of Washington in 1921 prohibited ships
heavier than 35,000 tons. List the ships that violated the
treaty of Washington.
- list the name, displacement, and number of guns of the ships
engaged in the battle of Guadalcanal.
- List all the ships mentioned in the database. (Remember that
all these ships may not appear in the SHIPS relation.)
- ! Find the classes that had only 1 ship as a member of that
class
- ! Find those countries that had both battleships (bb) and
battlecruisers (bc).
- ! Find those ships that "lived to fight another day"; they
were damaged in one battle and later fought in another. (hint:
join outcomes with battles, for the date, and then work with
that intermediate result)
The last 3 are harder (in pure relational algebra) so you may do g.
with grouping. Please hand in even if you don't do these
problems.