Database «Ships»

The database of naval ships that took part in World War II is under consideration. The database has the following relations:

Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)

Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.

The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons).

The Ships relation includes the ship name, its class name, and launch year.

The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the Outcomes relation.

Lets note some moments which we should consider in analyzing scheme at [picture 3.1](/en/book_database_ships/page1.html "Database schema"). The Outcomes table have the composite primary key {ship, battle}. This restriction denies entering the same ship which was in action in the same battle more then one time in database. But the same ship may be present in this table more then once if it was in different actions. The class of ship is defined in the Ships table. This table has foreign key (class` column) to Classes table.

The peculiarity of this scheme is in the fact that the Outcomes and Ships tables have no connection between each other. In other words, the Outcomes table may have ships which are absent in the Ships table. Here is the reason of most mistakes in solutions. It seemes that ships from Outcomes table have unknown class, therefore the characteristics are also unknown. Thats not so. As it follows from the description of knowledge domain, the name of lead ship is the same as name of class of such ships. Thats why if the name of ship in the Outcomes table matches the class name in the Classes table, so this is the lead ship and its characteristics are certain.

The way of improving this scheme is obviously to everyone: iss need to connect the Ships and the Outcomes table by ship name, in this case the shipcolumn in the Outcomes table becomes a foreign key to Ships table. Undoubtedly, it is so, but in the real case all information might be not available. For instance, there is archive information about ships in action, but there are no data about classes of this ships. In this case we need to enter this ship in Ships table first, and theclass` column must to allow to enter NULL value.

From the other side, there is no obstacles for us to enter the lead ship from Outcomes table to the Ships table, moreover. It is true, because the year of launch is not obligatory for entering. In this reason, we should note that the administrator of DB and the application developer are the different peoples as a rule. The developer and his users doesn`t have permissions to modify data in all cases.

The bad structure isn`t mean the impossibility to extract reliable data. The evidence is in this tasks. Such “bad” structure is more useful for educational purposes then “good”, because it makes us to write more complex queries. The authors of this scheme follows this reasons seemingly [2]. Besides, queries for “bad” scheme will get true results (but becomes less efficient) even if we improve structure , in the case we set connection between the Ships and the Outcomes tables.

Finally, we should note that launched column in the Ships table allow to enter NULL value. It means that launch year may be unknown. This is also true for ships from Outcomes table which is absent in Ships table.

Well, lets move to the tasks solving. We note, that in this chapter we do not consider very simple tasks anymore, because the majority of typical solutions were considered previously.