Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

CS 338 Computer Applications in Business: Database

Assignment #2 – SQL

1.   The treaty of Washington in 1921 prohibited capital ships heavier than 35,000 tones. List the ships that violated the treaty and participated in at least one WWII battles. Note that this excludes ships launched before 1921. The result should display the ship name, its class, displacement and country,

2.   Find the class(es) where no ship of that class was sunk. The result should list the class and its country.

3.   Find the pair of ships (A, B) that never fought in the same battle (regardless fight on the same side or against each other). Note that (A, B) and (B, A) is the same pair and only one should be showed. The result should display the ship names.

4.   Find the heaviest (in displacement) battleship(s) that survived the war (that is, among all the ships survived the war, find the heaviest bb). The result should display the ship name and its country.

5.   For country(ies) with both battleship and battlecruiser, find the country(ies) whose battleships(s) never engaged in any battle.

6.   Find the luckiest ship(s). That is, they were damaged in every battle they participated in but never sunk. This excludes ships never participated in any battle. The result should display the ship names and their types.

7.   Find the capital ship(s) that participated in all the battles in 1942.

8.   Find the country(s) that had ships participated in every battle. Say a country had two ships and there were total 4 battles. Ship 1 went to battle 1, 3 and 4, and Ship 2 went to battle 2 and 4. So this country had ships went to all 4 battles and this country should be listed in the result.

9.   Find the ships that survived the most brutal battle. Brutality of a battle is measured by the total number of ships sunk from both sides. The result should show ship names and their courtiers.

10. Which country suffered the most naval loss, UK or Japan? To find the answer, list the number of ships UK and Japan lost (sunk) during the war. The result should be displayed as follows.

Marking (30 points)

Each question is worth 3 points and is marked according to the following scheme:

•   Good (no mistake or very small one): 3 points;

•   Ok (mostly correct): 2 points;

•   Poor (mostly wrong but has some merit): 1 point;

•   Wrong (totally off or no answer): 0 point.

Submission

The assignment file format should be DOC/DOCX (word document), PDF, TXT (text file), or JPG/JPEG (picture). It should be uploaded to the drop box on Learn: Submit -> Dropbox -> Assignment #2. The submission deadline is Oct 30th, 1pm .

WII Capital Ship Database

The Capital  Ships  database  stores  information  about  WWII  capital  ships.  It  involves  the  following relations:

Classes(className, type, country, numGun, bore, displacement)

Ships(name, class, launched) Battles(name, date)

Outcomes(shipName, battleName, result)

The foreign keys should be easy to deduce from the semantics and the attribute names. A capital ship could either be a battleship or a battlecruiser. Ships are built in “classes” (think of it as the data type of the ship) from the same design, and the class is usually named after the first ship of that class. The relation Classes records the name of the class, the type (bb for battleship and bc for battlecruiser), the country that designed the class, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tones). 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 the battles, and table Outcomes gives the result (sunk, damaged, or ok) for ships participated in battles.

Just in case you might need it, here is how to extract year from a date (%M for month and %D for day): SELECT strftime ('%Y', date) AS Year FROM  Battles;

Tables on the last page provide sample data for these four relations. Note that, these are just for your reference and make it easier to understand the semantics of the relations. Information in the tables is incomplete as it will make the tables too big. As such, there are some “dangling tuples” in this data, e.g., ships mentioned in Outcomes that are not mentioned in Ships. Your query should assume the information is complete (no “dangling tuples” in the actual database). A sample capital ship database in SQL is also provided so that you can test your answers.

Classes

Battles

Outcomes