See the pdf attached
Question 1 (SQL Queries) [85 points] We are given a geostatistical database about countries, continents, rivers, etc. The following information is available in Canvas together with this homework assignment for download: • An ER diagram of the geostatistical database in PDF format (HW3Ex1- geostatistical- database-ER-diagram.pdf ). • An informal description of the database schema in PDF format (HW3Ex1- geostatistical- database-schema-explanation.pdf ). • A text file that contains create table commands to create the database schema (HW3Ex1- geostatistical-database-schema.sql). • A text file hat contains insert commands for about 47,800 tuples to fill the database tables (HW3Ex1-geostatistical-database-input-data.sql). • A text file that contains drop table commands to delete the database schema and the data in the database (HW3Ex1-geostatistical-database-drop-tables.sql). In a first step, use the CISE Oracle DBMS and the Oracle SQL Developer software to create the database schema and fill the database with data. This will also help you learn about the system environment for your group project. In particular, the use of MySQL, PostgreSQL, and other database systems is not allowed. In a second step, look at the database schema in the file HW3Ex1-geostatistical-database- schema.sql. From lines 38 to 52 you will find the following lines: ALTER TABLE Country ADD C O N S T R A I N T F K _ C o u n t r y R E F C i t y FOREIGN KEY ( Code , Capital , Province ) R E F E R E N C E S City ( Country , Name , Province ) INITIALLY DEFERRED DEFERRABLE ; ALTER TABLE City ADD C O N S T R A I N T F K _ C i t y R E F P r o v i n c e FOREIGN KEY ( Country , Province ) R E F E R E N C E S Province ( Country , Name ) INITIALLY DEFERRED DEFERRABLE ; ALTER TABLE Province ADD C O N S T R A I N T F K _ P r o v i n c e R E F C o u n t r y FOREIGN KEY ( Country ) R E F E R E N C E S Country ( Code ) INITIALLY DEFERRED DEFERRABLE ; ALTER TABLE Province ADD C O N S T R A I N T F K _ P r o v i n c e R E F C i t y FOREIGN KEY ( Capital , Country , CapProv ) R E F E R E N C E S City ( Name , Country , Province ) INITIALLY DEFERRED DEFERRABLE ; Your task is to explore this scenario by using the Internet. The keywords INITIALLY DEFERRED DEFERRABLE are non-standard SQL. They are supported by several database systems such as Oracle and PostgreSQL. (a) [10 points] Answer the following questions. (1) [4 points] What is the meaning of these keywords? (2) [6 points] Why is the action indicated by the keyword INITIALLY DEFERRED DEFERRABLE needed in the scenario above? What is the problem? How is the problem solved? In a third step, write SQL queries for the colloquial queries below and show the results by providing screenshots for both your SQL queries and query results. The screenshots must be embedded into the PDF file that contains your solutions to this whole assignment. In order to increase readability, the 2 SQL queries should be written in a structured manner, all SQL keywords should be fully capitalized, and the table and attribute names should be written in the same way as in the schema file. (b) [75 points] Write SQL queries for the following questions and provide screenshots. (1) [3 points] Find the provinces that have the largest number of islands in the world. Output the country code, the province, and the number of islands. (2) [3 points] List the 10 country names (attribute “Country Name”) with the highest population density (attribute “Population Density”) as well as the percentage of the world population (at-tribute “Percentage”) each one contains. (3) [4 points] Find the names of those countries that are bounded by the largest lake. (4) [3 points] Find the two longest rivers that flow through at least one lake and that finally flow into the Atlantic Ocean. Output the name and the length of the rivers. (5) [2 points] Display each island in Africa and its area if the area is larger than 1000 square kilo-meters. The output should be in descending order of the size of the areas. (6) [4 points] Find the country c1 that used to have the maximum number n1 of countries/areas depending on it. Further, find the country c2 that now has the maximum number n2 of coun- tries/areas depending on it. Output c1, n1, c2, n2, and the difference between n1 and n2. (7) [1 point] Find the names of countries where agriculture takes more than 50% of its gross domes-tic product (GPD). (8) [4 points] Find the northernmost cities of each continent (except Asia). Display the names of these cities and their continent. List cities that are northern of other cities in the result table first. (9) [3 points] List the names and GDPs of those countries that are members of the NATO and more than 5 percent of their population are Muslims. (10) [4 points] Find the top five popular religions and the numbers of their believers in the world. (11) [3 points] Find the provinces that are located on more than 2 islands and whose country’s GDP is greater than 1000000. (12) [2 points] Find all countries that cross continental boundaries. (13) [2 points] Find the height of the highest mountain for each continent. (14) [3 points] Find the countries whose depth of the deepest sea is less than the elevation of the highest mountain. Display the country name, depth of its deepest sea, and the elevation of the highest mountain. (15) [3 points] Compute the total length of the border that China shares with its neighboring countries. (16) [5 points] List the names of organizations that have only Asian countries as members. (17) [4 points] Find what is larger. Is it the sum of the areas of the 10 largest countries (attribute top10) or the sum of the areas of the remaining countries (attribute rest world)? What is their difference (attribute difference)? Display the values for the attributes top10, rest world, and difference. (18) [1 point] Find all countries whose capitals have positive latitudes and less than 10000 inhabitants. (19) [3 points] Find the names of the lakes in the United States with an elevation that is above the average elevation of all lakes world-wide. (20) [1 point] Find names of rivers which cross at least 12 provinces in the same country. (21) [4 points] Find the largest population density (population/area) of provinces that have mountains of the “volcano” type. Output the province name, mountain name, and the population density. (22) [3 points] List the top five countries that will have the largest population after five years. [Assume that the population in five years is equal to the population this year * (1 + growth rate)5]. The population growth in the database schema is in percentage and should be divided by 100. Use the new attributes Country, Population after 5 years, and Rank for the resulting table schema. (23) [4 points] Determine the names of countries that have more than three rivers and that have lakes 3 next to more than three provinces. (24) [2 points] Find the name and length of the longest river on the American continent. (25) [4 points] List the country names that have more than 4 different kinds of religion and at least one religion takes more than 80%. 4 Question 2 (SQL and Relational Algebra) [15 points] For each of the following SQL statements, give an equivalent Relational Algebra expression if possible. If such an expression cannot be given, explain why. (a) [3 points] SELECT DISTINCT E2 . age , E2 . rank FROM employee E1 , employee E2 WHERE E1 . enum > E2 . enum ; (b) [6 points] SELECT deptId , MAX ( salary ) FROM employee WHERE rank = ' manager ' GROUP BY deptId ; (c) [6 points] SELECT DISTINCT enum FROM employee WHERE NOT EXISTS ( SELECT * FROM projects WHERE NOT EXISTS ( SELECT * FROM works WHERE employee . enum = works . enum AND works . pnum = projects . pnum ) ) ; 5