Database Management Systems (COP 5725) (Spring 2015) Instructor: Dr. Markus Schneider TAs: Siliang Xia Homework 3 Name: UFID: Email Address: Pledge (Must be signed according to UF Honor Code) On my honor, I have neither given nor received unauthorized aid in doing this assignment. _______________________________________________ Signature For scoring use only: Exercise 1 Exercise 2 Exercise 3 Total Maximum 45 30 25 100 Received Exercise 1 (SQL Queries) [45 points] Let us assume we are given a database about countries, continents, rivers, etc. The following information is available:     ER diagram for the database schema in JPG format and PDF format. PDF file that describes the database schema. Text file that contains create table commands to create the database schema. Text file that contains insert commands for over 22,000 tuples to fill the tables. Please write SQL queries for the colloquial queries below and show the result tables. Note that the CISE Oracle DBMS has to be used in order to learn about the system environment for your project. This means that MySQL, PostgreSQL, and other database systems are not allowed. Each query is worth 3 points. 1. Find the province(s) that has the largest number of islands in the world. Output the country, the province, and the number of islands. 2. Find the provinces that surround every lake that its country has ["surround" means that a lake is located fully inside the province]. Output the country and the province. 3. Find the deserts that are next to more than 3 countries. Output the desert and the number of countries. 4. List the top five countries that will have the largest population after five years. [Assume that the population in five years = population this year * (1 + growth rate)^5]. Output country, population, and rank. 5. List the names and GDPs of those countries which are members of the NATO and more than 5 percent of their population are Muslims. 6. Find the languages with more than 50 million speakers, and then list the names of countries in which each of those languages is the most popular language, along with the percentage of each country’s population speaking that language. Output country name, language, percentage. 7. Find all names of rivers which cross at least 10 provinces in the same country. 8. Display each island in Africa and its area if the area is larger than 1000 square kilometers. The output should be in descending order of the size of the areas. 9. List the 10 country names with the highest population density as well as the percentage of world population each one contains. 10. Find the countries that do not have access to any sea but at least 5 provinces with deserts. 11. Display the names of all countries which have people from 6 or more religions, together with their corresponding count of the number of religions. 12. List the names, GDPs, and inflation rates of all countries which were dependent sometime in the past, have gained independence, and are ruled by any type of monarchy. 13. Find the highest mountain for each continent. 14. Find the countries that have more than three rivers, and have lakes next to more than three provinces. 15. Find the counties whose depth of the deepest sea is less than the elevation of the highest mountain. Display the country name and depth of its deepest sea. Exercise 2 (SQL and Relational Algebra) [30 points] Consider following schema for employee skill management: manager(mid, mname, salary, hiretime) employee(eid, ename, salary, hiretime, managerid) skill(sid, sname, category) manager_skill(mid, sid, level) employee_skill(eid, sid, level) Here are some assumptions:  Employee table and manager table have no overlap  An employee can be associated with only one manager Please write SQL queries for following questions: 1. Find the names of the managers who manage at least one "super capable" employee who masters all skills listed in the skill table. [5 points] 2. Find the name(s) of the manager(s) whose salary is higher than all of his employees and whose ‘hiretime’ is earlier than all of his employees by using the predicate ‘all’). [5 points] 3. Write an SQL Expression for question 2 by using the predicate ‘exists’. [5 points] 4. Write a corresponding Relational Algebra expression for question 2. [5 points] 5. a) Explain what the following Relational Algebra expression means. [5 points] (sid, eid(σcategory=’TECH’(employee_skill ⋈ skill)) / eid(employee)) – sid(manager_skill) b) Write an SQL query for the given Relational Algebra expression. [5 points] Exercise 3 (Query by Example) [25 pts] Suppose we have following schema: Drivers (did, dname, gender, age) Reserve (did, cid, day, cost) Cars (cid, cname, model, color, rid) RentalCompany (rid, rname, revenue, rating) IsMember(did, rid, join_time, member_type) Answer following questions using QBE(draw your tables): 1. Find names of drivers who has reserved a car from rental company ‘AVIS’ for date ‘03/01/2015’ and whose age is between 21 and 30 (not equal to 21 or 30). [5 points] 2 . Find the youngest driver who is a member of both company ‘Avis’ and company ‘Hertz’. [7 points] 3. Update ‘member_type’ = ‘VIP’ for those drivers who were members of company. [8 points] 4. Find the rental company which has largest summation number of cars of model ‘Ford’ and ‘GE’. [5 points]