[Solved] CSE111Midterm

DATABASESYSTEMS

(200 points)

Consider the following relational schema and corresponding sample data:

Classes (class, type, country, numGuns, bore, displacement)

Ships (name, class, launched)

Battles (name, date)

Outcomes (ship, battle, result)

Write SQL statements for the following tasks in the file midterm.sql provided to you:

1. Create the tables in the schema. Use the exact given names for the tables and their attributes (10 points)
2. Populate every table with the corresponding sample data. Use the exact given values for all the attributes and pay close attention to how you handle the dates in order to support valid comparisons. (40 points)
3. For every country that launched ships between 1930 and 1940, inclusive, find the number of ships itlaunched. Print the country name and the number of ships it launched. (5 points SQL + 5 points execution tree)
4. Add the following data to the database: All the ships launched in 1920 or earlier participate in the

Denmark Strait battle and are damaged. If such a ship already participated in the Denmark Strait battle, do not include it anymore. (10 points)

5. For every country, find the number of damaged ships it has in battles. (5 points SQL + 5 points execution tree)
6. Find the country(ies) with the smallest number of damaged ships in battles. (5 points SQL + 5 points execution tree)
7. Delete from the Outcomes table all the ships from Japan that participate in the Denmark Strait (10 points)
8. Find the ships that survived a battle in which they were damaged and then fought in another battle. (5 points SQL + 5 points execution tree)
9. Find the countries that have both bb and bc shipsnot classes. Print the country name, the number of bb ships and the number of bc (5 points SQL + 5 points execution tree)
10. Double numGuns for the classes that have ships launched in 1940 or later. (10 points)
11. Find the classes that have exactly two ships in the class. (5 points SQL + 5 points execution tree)
12. Find the classes that still have exactly two ships in the class after considering all the battles. A sunk ship does not exist anymore. (5 points SQL + 5 points execution tree)
13. Delete from Ships all the ships that were sunk in a battle. (10 points)
14. Find the total numGuns across all the ships for every country. (5 points SQL + 5 points execution tree)
15. Whenever a ship is damaged in a battle, it looses one of its guns. Find the total numGuns across all the ships for every country under this assumption. Do not forget to include in your result the countries that do not have damaged (5 points SQL + 5 points execution tree)
16. Insert a ship into Ships for every class for which there does not exist a ship having the class name in Ships. The name of the ship is the same as the class name. launched is set to the minimum value of launched for the ships in the class, if any such ship exists. Otherwise, set launched to the minimum value in Ships. (10 points)
17. Find the number of ships launched by every country in every decade between 1910 and 1950: 1911 1920, 19211930, 1931-1940, and 19411950. The output consists of a column for the country and columns for every decade, 4 in total, named 19111920, 19211930, 1931-1940, and 19411950. There is a tuple for every country in Classes with the corresponding count. If no ships are launched in a decade, a 0 has to appear in the result. (5 points SQL + 5 points execution tree)

You can test your code by executing the command ./test.sh in the terminal. This generates the output for all the query statements. It is important to notice that modification statements change the content of the database, thus, the result of subsequent queries.

You have to turn in two files. The first file is midterm.sql with your SQL statements and tested with ./test.sh. The second file contains the optimized relational algebra query execution trees for the queries. For this, draw/write your answers in a doc file or presentation slides and convert to pdf. Upload both midterm.sql and the pdf file to CatCourses.