Description
Problem 1: SQL (10 points)
You are given the following relational schema:
EMP(SSN,EmpName) ASSIGN(SSN,CityName,StartYear,EndYear) CITY(CityName,Country)
Keys are underlined. In ASSIGN, CityName is a foreign key referencing CityName in CITY, and SSN is a foreign key referencing SSN in EMP. The domain of the attributes StartYear and EndYear is Years. The meaning of ASSIGN is as follows: a tuple (n,c,s,e) represents the fact that the employee with SSN equal to n was assigned to the city c from the year s to the year e (not including e). For example, there could be two assignments in Paris:
(123,Paris,2000,2001),(555,Paris,2002,2005).
The first assignment was for one year: 2000; the second, for three years: 2002, 2003, and 2004. It is possible that the same person was assigned to the same city more than once and that more than one person is assigned to the same city at the same time. There are no years beyond 2015 in the database. Write the following queries in SQL:
Query 1.1 List the names of all the employees and for each employee calculate the average assignment length of this employee. The result should be sorted (descending) by that length. Query 1.2 Let the maximum assignment length across all times, cities and employees be M. List all cities where there was an assignment of length M. Query 1.3 List all employees that were assigned to Paris immediately after they were assigned to Moscow, i.e., there was no gap between the two assignments. Query 1.4 List all employees having an assignment overlapping, perhaps partially, with an assignment of Judy Brown to the same city. An example of partial overlap:
(123,Paris,2000,2002),(555,Paris,2000,2005),
Query 1.5 List all employees who were assigned to the same cities as Jim Smith, though perhaps at different times.
Please write all your SQL statements into a text file, named Project01_SQL.txt. Please use semicolons to separate each command from the others. Make sure that the resulting file can be executed in Tora without any modification (assuming the relations EMP, ASSIGN and POST are already present).
Problem 2: JDBC (10 points, plus 5 extra credits)
You are given a relation instance named Map, with the following schema (we use Oracle’s syntax here):
CREATE TABLE Map ( City VARCHAR(40) PRIMARY KEY, Latitude DECIMAL(*,10) NOT NULL, Longitude DECIMAL(*,10) NOT NULL );
Each row in Map corresponds to a geographic location, whose latitude and longitude are expressed in degrees. For example, the content of Map may look like this:
City Latitude Longitude Wues 49.79667 6.15556 Wolwelange 49.82861 5.76472 Wolfsmuhle-l`es-Ellange 49.53333 6.31667 Wollefsmillen 49.71639 6.49 Wintrange 49.50139 6.35278 Wilwerdange 50.14056 6.02389 … … …
Your goal is to develop a Java application able to answer the following queries:
Range queries (5 points) Given the name of a city c and a radius r (expressed in kilometers) return all the cities whose distance from c is strictly less than r. By distance we mean the length of the shortest trajectory over the earth’s surface that connects the two cities (more details are given below).
Top-k nearest neighbors (5 points) Given the name of a city c and a strictly positive integer k return the k cities that are closest to c.
Shortest round-trip query (extra credit: 5 points) Given the names of two cities, c1 and c2, find the name of a third, distinct city c3 so that the sum of the distances d(c1,c2) + d(c2,c3) + d(c3,c1) is minimized.
The Map relation will be stored inside an Oracle database; your application will need to access it using JDBC. In order to be graded, your code must meet the following constraints:
1. You must write all your code in a single class, named Project01_Main. 2. The package of Project01_Main must be edu.buffalo.cse462. 3. Class Project01_Main must be executable, that is it must implement the method main(String[] args). 4. Invocations to Project01_Main must respect a given interface, that is described below. 5. The output generated by Project01_Main must respect a given format, described below. 6. You should not use external libraries, except for ojdbc6.jar. Your code must be compilable with JDK v7.
Students who fail to comply with these rules will not receive credits, as their projects will not be graded.
Invocation interface and output format
The first two arguments passed to the main method of Project01_Main are always the username and password for accessing an Oracle account, where the Map relation is stored. The third one is a string specifying the kind of query to be processed: its possible values are RANGE_QUERY, NN_QUERY or MIN_ROUNDTRIP_QUERY.
Range queries
For range queries (flagged with RANGE_QUERY), the fourth and fifth parameters are the name of a city and a decimal number, for example:
java edu.buffalo.cse462.Project01_Main