Database Lab #1

Monday, 27 September 2021, due Friday

Using the database software

In the lab, we are using PostgreSQL, an open-source, client-server database. The database server we are using is on the virtual machine linux.ubishops.ca
Today, proceed as in Lab 0.
Connect to the ships database:
psql ships

You will not asked for password.

You can now type SQL statements. Here are some examples: 
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.

Capital ships

More information on the SHIPS database can be found on the page of a cancelled assignment.

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.
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 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

Queries during the lab:

Open a text editor (Kwrite on linux, or as you like if using ssh) as you did last week.

Do the following queries, or something similar that appeals to you:
  1. Give the class names and countries of the classes that carried guns of at least 16-inch bore.
  2. List (the names of) all the ships mentioned in the database. This will probably involve a union.
  3. Find the names of the ships with a 16-inch bore.
  4. Ships sunk in the battle of Denmark Strait. Just the names.
  5. Ships, with country,  number of guns and bore (if known) sunk in the battle of Denmark Strait. (outer join)
  6. Ships that violated the treaty of Washington of 1921 (heavier than 35,000 tons.)
  7. Ships that "lived to fight another day" -- were damaged in one battle and later fought in another battle.
    Hint: join OB with itself.
Please save the text file with these queries, and results  of 3 interesting ones.
Type your NAME at the beginning of the file.

Submit the file: submit cs307 somefile.txt or email to me

Your own database

I have also created a database named with your username, of which you are the owner.  You may if you wish practice creating tables here, and even inserting data, should you wish.
To connect to your database, either

Back to CS 307 or Lin Jensen