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 you
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 XXXXXXXXXX.
(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 Alge
a) [15 points]
For each of the following SQL statements, give an equivalent Relational Alge
a 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