Assignment 3 solution

This assignment introduces an example concerning World War II capital ships. It involves the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched) –launched is for year launched
Battles(name, date_fought)
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.
Exercise 1. (2 points)
1. Create simple SQL statements to create the above relations (no constraints for initial creations).
2. Insert the following data.
For Classes:
(‘Bismarck’,’bb’,’Germany’,8,15,42000);
(‘Kongo’,’bc’,’Japan’,8,14,32000);
(‘North Carolina’,’bb’,’USA’,9,16,37000);
(‘Renown’,’bc’,’Gt. Britain’,6,15,32000);
(‘Revenge’,’bb’,’Gt. Britain’,8,15,29000);
(‘Tennessee’,’bb’,’USA’,12,14,32000);
(‘Yamato’,’bb’,’Japan’,9,18,65000);
For Ships
(‘California’,’Tennessee’,1921);
(‘Haruna’,’Kongo’,1915);
(‘Hiei’,’Kongo’,1914);
(‘Iowa’,’Iowa’,1943);
(‘Kirishima’,’Kongo’,1914);
(‘Kongo’,’Kongo’,1913);
(‘Missouri’,’Iowa’,1944);
(‘Musashi’,’Yamato’,1942);
(‘New Jersey’,’Iowa’,1943);
(‘North Carolina’,’North Carolina’,1941);
(‘Ramilles’,’Revenge’,1917);
(‘Renown’,’Renown’,1916);
(‘Repulse’,’Renown’,1916);
(‘Resolution’,’Revenge’,1916);
(‘Revenge’,’Revenge’,1916);
(‘Royal Oak’,’Revenge’,1916);
(‘Royal Sovereign’,’Revenge’,1916);
(‘Tennessee’,’Tennessee’,1920);
(‘Washington’,’North Carolina’,1941);
(‘Wisconsin’,’Iowa’,1944);
(‘Yamato’,’Yamato’,1941);
For Battles
(‘North Atlantic’,’27-May-1941′);
(‘Guadalcanal’,’15-Nov-1942′);
(‘North Cape’,’26-Dec-1943′);
(‘Surigao Strait’,’25-Oct-1944′);
For Outcomes
(‘Bismarck’,’North Atlantic’, ‘sunk’);
(‘California’,’Surigao Strait’, ‘ok’);
(‘Duke of York’,’North Cape’, ‘ok’);
(‘Fuso’,’Surigao Strait’, ‘sunk’);
(‘Hood’,’North Atlantic’, ‘sunk’);
(‘King George V’,’North Atlantic’, ‘ok’);
(‘Kirishima’,’Guadalcanal’, ‘sunk’);
(‘Prince of Wales’,’North Atlantic’, ‘damaged’);
(‘Rodney’,’North Atlantic’, ‘ok’);
(‘Scharnhorst’,’North Cape’, ‘sunk’);
(‘South Dakota’,’Guadalcanal’, ‘ok’);
(‘West Virginia’,’Surigao Strait’, ‘ok’);
(‘Yamashiro’,’Surigao Strait’, ‘sunk’);
Exercise 2. (16 points)
Write SQL queries for the following requirements. (The questions of this exercise are of two points each.)
1. The treaty of Washington in 1921 prohibited capital ships heavier than 35,000 tons. List the ships that violated the treaty of Washington.
2. List the name, displacement, and number of guns of the ships engaged in the battle of Guadalcanal.
3. List all the capital ships mentioned in the database. (Remember that not all ships appear in the Ships relation.)
4. Find those countries that had both battleships and battlecruisers.
5. Find those ships that “lived to fight another day”; they were damaged in one battle, but later fought in another.
6. Find the countries whose ships had the largest number of guns.
7. Find the names of the ships whose number of guns was the largest for those ships of the same bore.
8. Find for each class with at least three ships the number of ships of that class sunk in battle.
Exercise 3. (4 points)
Write the following modifications.
1. (2 points) Two of the three battleships of the Italian Vittorio Veneto class – Vittorio Veneto and Italia – were launched in 1940; the third ship of that class, Roma, was launched in 1942. Each had 15-inch guns and a displacement of 41,000 tons. Insert these facts into the database.
2. (1 point) Delete all classes with fewer than three ships.
3. (1 point) Modify the Classes relation so that gun bores are measured in centimeters (one inch = 2.5 cm) and displacements are measured in metric tons (one metric ton = 1.1 ton).
Exercise 4. (12 points)
Add the following constraints.
[Some constraints might not possible be added right away. In such cases, delete first the violating tuples. Some constraints need a view with check option in order to be enforced.]
1. (1 point) Every class mentioned in Ships must be mentioned in Classes.
2. (1 point) Every battle mentioned in Outcomes must be mentioned in Battles.
3. (1 point) Every ship mentioned in Outcomes must be mentioned in Ships.
4. (1 point) No class of ships may have guns with larger than 16-inch bore.
5. (2 points) If a class of ships has more than 9 guns, then their bore must be no larger than 14 inches.
6. (2 points) No ship can be in battle before it is launched.
7. (2 points) No ship can be launched before the ship that bears the name of the first ship’s class.
8. (2 points) No ship fought in a battle that was at a later date than another battle in which that ship was sunk.