Database Lab #1

Lab period of Wednesday, 16 September 2015

In the lab, we are using PostgreSQL, an open-source, client-server database. The server is running on cs-linux, various clients may run on any of the J118 workstations, in fact on any computer at Bishop's. (connections from outside the University are disallowed). The client can be psql, which runs in a terminal and allows you to type SQL commands and see results, pgadmin3, or a program that YOU write.

Today, use psql. open a terminal (or use ssh or putty from somewhere else to get a terminal on linux.ubishops.ca) and type:

psql -h cs-linux ships

I have made you a database user. Your database username is the same as your linux username. You will be asked for your password. Initially, it is your student number.

You can now type SQL statements. Here are some examples: (The first is to change your password)

alter user me password 'somethingcryptic';
select * from battles;		-- see the contents of a table
select * from ships, classes;	-- don't like this? try either of the following:
select * from ships, classes where classes.class=ships.class;
select * from ships natural join classes;

To get help on commands, type \h or use the link to documentation on the course website
For example, to recall the SELECT syntax, type \h select
use \d to see tables, \d battles to see schema of battles, etc.

Use up-arrow to recall previously typed lines, \e to edit last query.

(When you get tired of typing your password, see Remembering password below.)

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"

CLASSES(class, type, country, numguns, bore, displacement)
SHIPS (name, class, launched)
BATTLES (name, date)
OUTCOMES (ship, battle, result)

The VIEW OB is the obvious join of Outcomes with Battles. In essence, it adds the column date to Outcomes. Views are only stored as a select statement, so the query planner must do the same work as though you had typed the view definition. This view will simplify the "fight another day" query. You use a view like a table

OB (ship, battle, result, date)

Queries during the lab:

Do the following queries, or something similar that appeals to you:

Give the class names and countries of the classes that carried guns of at least 16-inch bore.
```
select class, country from classes where bore >= 16;
```
List (the names of) all the ships mentioned in the database. This will probably involve a union.
```
select name from ships
union
select ship from outcomes;
```

Find the names of the ships with a 16-inch bore.

select name 
from ships, classes
where ships.class = classes.class
  and bore=16;

Ships sunk in the battle of Denmark Strait. Just the names.

select ship
from outcomes
where battle='Denmark Strait' and result='sunk';

Ships, with country, number of guns and bore (if known) sunk in the battle of Denmark Strait. (outer join)

select ship, numguns, bore
from outcomes left join ships on ship=name left join classes on 
ships.class = classes.classships.class = classes.class
where battle='Denmark Strait' and result='sunk';

Ships that violated the treaty of Washington of 1921 (heavier than 35,000 tons.)

select name from ships natural join classes 
where displacement > 35000;

Ships that "lived to fight another day" -- were damaged in one battle and later fought in another battle.
Hint: join OB with itself.

select ob1.ship 
from ob ob1, ob ob2
where ob1.ship = ob2.ship	-- same ship
  and ob1.result = 'damaged'	-- damaged in one battle
  and ob1.date < ob2.date;	-- fought in a later battle

Please create a text file with these queries, and results of 3 interesting ones.