CSE111– Midterm Solved

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)

 

Bismarck
bb
Germany
8
15
42,000
Iowa
bb
USA
9
16
46,000
Kongo
bc
Japan
8
14
32,000
North Carolina
bb
USA
9
16
37,000
Renown
bc
Britain
6
15
32,000
Revenge
bb
Britain
8
15
29,000
Tennessee
bb
USA
12
14
32,000
Yamato
bb
Japan
9
18
65,000
 

 

California
Tennessee
1915
Haruna
Kongo
1915
Hiei
Kongo
1915
Iowa
Iowa
1933
Kirishima
Kongo
1915
Kongo
Kongo
1913
Missouri
Iowa
1935
Musashi
Yamato
1942
New Jersey
Iowa
1936
North Carolina
North Carolina
1941
Ramillies
Revenge
1917
Renown
Renown
1916
Repulse
Renown
1916
Resolution
Revenge
1916
Revenge
Revenge
1916
Royal Oak
Revenge
1916
Royal Sovereign
Revenge
1916
Tennessee
Tennessee
1915
Washington
North Carolina
1941
Wisconsin
Iowa
1940
Yamato
Yamato
1941
 
 
 

Denmark Strait
1941-05-24
Guadalcanal
1942-11-15
North Cape
1943-12-26
Surigao Strait
1944-10-25
 
 
 

California
Surigao Strait
ok
Kirishima
Guadalcanal
sunk
Resolution
Denmark Strait
ok
Wisconsin
Guadalcanal
damaged
Tennessee
Surigao Strait
ok
Washington
Guadalcanal
ok
New Jersey
Surigao Strait
ok
Yamato
Surigao Strait
sunk
Wisconsin
Surigao Strait
damaged
 
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 battle. (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 ships—not classes. Print the country name, the number of bb ships and the number of bc ships. (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 ships. (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, 1921–1930, 1931-1940, and 1941–1950. The output consists of a column for the country and columns for every decade, 4 in total, named 1911–1920, 1921–1930, 1931-1940, and 1941–1950. 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.