WW2 Ships Database

The following database schema involves an example concerning World War II capital ships. It involves the following relations. Use appropriate data types for each attribute.

• Classes (class, type, country, numGuns, bore, displacement)
• Ships (name, class, launched)
• Battles (name, beginDate, endDate) : Attributes beginDate and endDate are DATE type
• Outcomes (ship, battle, result)

Ships are built in "classes" from the same design, and the class is usually named for the first ship of that class. The relation Classes records the name of the class, the type ( bb for battleship, or bc for battlecruiser), the, country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships, and relation Outcomes gives the result (sunk, damaged, or ok) for each ship in each battle.

The following figures give some sample data for these four relations. Note that, all ships are included in the Ships relation and only the ones that engaged in battles are recorded in the Outcomes relation.

(a) Sample data for relation Classes

(b) Sample data for relation Battles

(c) Sample data for relation Outcomes

(d) Sample data for relation Ships

Problems

1. Based on the informal schema and sample data shown above, write the following table creation declarations in SQL. Primary keys and foreign keys need to be specified as well for all tables. After each subproblem in problem 1 use "describe tablename" to show the created table result.

a) A suitable schema for relation Classes.
b) A suitable schema for relation Ships. The launched column may use an integer type.
c) A suitable schema for relation Battles. Use the DATE type for beginDate and endDate.
d) A suitable schema for relation Outcomes.

2. Write the following insert SQL queries to create the contents shown in the above sample. Carefully consider the correct orders of the insert into the tables Battles, Classes, Outcomes, Ships based on the foreign key relationship. After the final subproblem of problem 2, show the final result of all of the inserts by running the select * result for each table.

a) Inserts for first relation
b) Inserts for second relation
c) Inserts for third relation
d) Inserts for fourth relation

3. Write the following simple SQL queries involving single relations based on the above database schema. Do NOT use nested subqueries in WITH and FROM clause.

a) Find the class name and country for all classes with at least 10 guns.
b) Find the class name, number of guns, and bore where the number of guns is not 9 and the bore is less than 16, and also show the result in ascending order determined by the displacement first, and the bore second, and the number of guns third.
c) Find all the countries that made a battlecruiser.
d) Find the names of all ships launched after 1918, but call (rename) the resulting column newship .
e) Find the names of ships sunk in battle and the name of the battle in which they were sunk.
f) Find all ships that have the same name as their class.
g) Find the names of all ships that begin with the letter R.
h) Find the names of all ships whose name consists of three or more words (e.g., King George V).

4. Write the following SQL queries based on the above database schema. Do NOT use nested subqueries in WITH and FROM clause.

a) Find the ships name and weight which are heavier than 35,000 tons, in the order of the weight and secondarily the ship name.
b) List the name, displacement, and number of guns of the ships engaged in the battle of Guadalcanal.
c) Find the classes that have only one ship as a member of that class.
d) List all ship names without duplicates where ships are the class of Renown or participated in the battle of North Atlantic or the launched year is before 1919.
e) List all the ship names based on the ascending order of the displacement and secondarily the name of the ship.
f) Find those countries that have both battleships and battlecruisers.
g) Find those ships that were damaged in a battle, and also fought in another battle.
h) List the ship names and the month and year (in the format of MM-YYYY) of the begin date and end date of the battle that they participated in where such battles occurred within the two year period of 1942 to 1943.

5. Write the following SQL queries involving subqueries based on the above database schema. You should use at least one sub query in each of your answers and write each query in two significantly different ways (e.g., each way should use different sets of the operators EXISTS, IN, ALL, SOME, ANY and may be combined with NOT). Do NOT use nested subqueries in WITH and FROM clause.

a) Find the countries whose ships had the largest number of guns.
b) Find the classes of ships where at least one of the ships in the class was damaged in a battle.
c) Find the names of the ships with a 16-inch bore.
d) Find the battles in which ships of the Kongo class participated.
e) Find the names of the ships whose number of guns was the largest for those ships of the same bore.

6. Write the following SQL queries involving aggregate functions based on the above database schema. Do NOT use nested subqueries in WITH and FROM clause.

a) Find the number of battleship classes.
b) Find the average number of guns of battleship type classes. Use only the Classes relation.
c) Find the average number of guns of battleships. This is the average among all battleships in the Ships relation.
d) Find for each class the year in which the first ship of that class was launched.
e) Find for each class with a sunken ship, the number of ships of that class sunk in battle.
f) Find for each class with at least three ships, the number of ships of that class sunk in battle.
g) Find the average displacement for all ships in each country. You need to consider each ship in the Ships relation and consider for the country as well.
h) Find for each battle, the total number of guns of all of the ships participating in the battle.

7. Write the following SQL queries involving modifications based on the above database schema. Show the before and after results (select * ) of the modified relations. The script should show the before result (select * ), run the problem SQL, the after result (select * ). Each subproblem should be executed one after another (i.e. you do NOT need to initialize the database after each subproblem).

a) The two British battlecruisers of the Nelson class - Nelson and Rodney - were both launched in 1927, had eight 16-inch guns, and a displacement, of 34,000 tons. Insert these facts into the database.
b) There are three battleships of the Italian Vittorio Veneto class. Two of them are named Vittorio Veneto, and Italia, which were both launched in 1940. The third ship of that class, named Roma, was launched in 1942. The Italian Vittorio Veneto class has nine 15-inch guns and a displacement of 41,000 tons. Insert these facts into the database.
c) Delete from Ships all ships sunk in the Surigao Strait battle. Also delete from the Outcomes table as well.
d) Modify the Classes relation so that gun bores are measured in centimeters (one inch = 2.5 centimeters) and displacements are measured in metric tons (one metric ton = 1.1 tons).
e) Modify the Outcomes relation where ships that were made in Japan will all have the result sunk.
f) Delete all ships that have classes with fewer than three ships.

8. Write the following SQL queries involving schema modifications based on the above database schema

a) An alteration to your Classes relation from 1-(a) to delete the attribute bore.
b) An alteration to your Ships relation from 1-(b) to include the attribute company and set the value to ShipCompany.