Assignment2.java -----------------------------------------------------------------------------------------------

import java.sql.*;

public class Assignment2 { // A connection to the database Connection connection; // Statement to run queries Statement sql; // Prepared Statement PreparedStatement ps; // Resultset for the query ResultSet rs; //CONSTRUCTOR Assignment2(){ } //Using the input parameters, establish a connection to be used for this session. Returns true if connection is sucessful public boolean connectDB(String URL, String username, String password){ return false; } //Closes the connection. Returns true if closure was sucessful public boolean disconnectDB(){ return false; } public boolean insertCountry (int cid, String name, int height, int population) { return false; } public int int getCountriesNextToOceanCount(int oid) { return -1; } public String getOceanInfo(int oid){ return ""; }

public boolean chgHDI(int cid, int year, float newHDI){ return false; }

public boolean deleteNeighbour(int c1id, c2id){ return false; } public String listCountryLanguages(int cid){ return ""; } public boolean updateHeight(int cid, int decrH){ return false; } public boolean updateDB(){ return false; } } Assignment2.java ---------------------------------------------------------------------------------------------

a2.ddl -------------------------------------------------------------------------------------------

DROP SCHEMA IF EXISTS A2 CASCADE; CREATE SCHEMA A2; SET search_path TO A2;

DROP TABLE IF EXISTS country CASCADE; DROP TABLE IF EXISTS language CASCADE; DROP TABLE IF EXISTS religion CASCADE; DROP TABLE IF EXISTS hdi CASCADE; DROP TABLE IF EXISTS ocean CASCADE; DROP TABLE IF EXISTS neighbour CASCADE; DROP TABLE IF EXISTS oceanAccess CASCADE; DROP TABLE IF EXISTS query1 CASCADE; DROP TABLE IF EXISTS query2 CASCADE; DROP TABLE IF EXISTS query3 CASCADE; DROP TABLE IF EXISTS query4 CASCADE; DROP TABLE IF EXISTS query5 CASCADE; DROP TABLE IF EXISTS query6 CASCADE; DROP TABLE IF EXISTS query7 CASCADE; DROP TABLE IF EXISTS query8 CASCADE; DROP TABLE IF EXISTS query9 CASCADE; DROP TABLE IF EXISTS query10 CASCADE;

-- The country table contains all the countries in the world and their facts. -- 'cid' is the id of the country. -- 'name' is the name of the country. -- 'height' is the highest elevation point of the country. -- 'population' is the population of the country. CREATE TABLE country ( cid INTEGER PRIMARY KEY, cname VARCHAR(20) NOT NULL, height INTEGER NOT NULL, population INTEGER NOT NULL); -- The language table contains information about the languages and the percentage of the speakers of the language for each country. -- 'cid' is the id of the country. -- 'lid' is the id of the language. -- 'lname' is the name of the language. -- 'lpercentage' is the percentage of the population in the country who speak the language. CREATE TABLE language ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, lid INTEGER NOT NULL, lname VARCHAR(20) NOT NULL, lpercentage REAL NOT NULL, PRIMARY KEY(cid, lid));

-- The religion table contains information about the religions and the percentage of the population in each country that follow the religion. -- 'cid' is the id of the country. -- 'rid' is the id of the religion. -- 'rname' is the name of the religion. -- 'rpercentage' is the percentage of the population in the country who follows the religion. CREATE TABLE religion ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, rid INTEGER NOT NULL, rname VARCHAR(20) NOT NULL, rpercentage REAL NOT NULL, PRIMARY KEY(cid, rid));

-- The hdi table contains the human development index of each country per year. (http://en.wikipedia.org/wiki/Human_Development_Index) -- 'cid' is the id of the country. -- 'year' is the year when the hdi score has been estimated. -- 'hdi_score' is the Human Development Index score of the country that year. It takes values [0, 1] with a larger number representing a higher HDI. CREATE TABLE hdi ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, year INTEGER NOT NULL, hdi_score REAL NOT NULL, PRIMARY KEY(cid, year));

-- The ocean table contains information about oceans on the earth. -- 'oid' is the id of the ocean. -- 'oname' is the name of the ocean. -- 'depth' is the depth of the deepest part of the ocean CREATE TABLE ocean ( oid INTEGER PRIMARY KEY, oname VARCHAR(20) NOT NULL, depth INTEGER NOT NULL);

-- The neighbour table provides information about the countries and their neighbours. -- 'country' refers to the cid of the first country. -- 'neighbor' refers to the cid of a country that is neighbouring the first country. -- 'length' is the length of the border between the two neighbouring countries. CREATE TABLE neighbour ( country INTEGER REFERENCES country(cid) ON DELETE RESTRICT, neighbor INTEGER REFERENCES country(cid) ON DELETE RESTRICT, length INTEGER NOT NULL, PRIMARY KEY(country, neighbor));

-- The oceanAccess table provides information about the countries which have a border with an ocean. -- 'cid' refers to the cid of the country. -- 'oid' refers to the oid of the ocean. CREATE TABLE oceanAccess ( cid INTEGER REFERENCES country(cid) ON DELETE RESTRICT, oid INTEGER REFERENCES ocean(oid) ON DELETE RESTRICT, PRIMARY KEY(cid, oid));

-- The following tables will be used to store the results of your queries. -- Each of them should be populated by your last SQL statement that looks like: -- INSERT INTO queryX (SELECT )

CREATE TABLE query1( c1id INTEGER, c1name VARCHAR(20), c2id INTEGER, c2name VARCHAR(20) );

CREATE TABLE query2( cid INTEGER, cname VARCHAR(20) );

CREATE TABLE query3( c1id INTEGER, c1name VARCHAR(20), c2id INTEGER, c2name VARCHAR(20) );

CREATE TABLE query4( cname VARCHAR(20), oname VARCHAR(20) );

CREATE TABLE query5( cid INTEGER, cname VARCHAR(20), avghdi REAL );

CREATE TABLE query6( cid INTEGER, cname VARCHAR(20) );

CREATE TABLE query7( rid INTEGER, rname VARCHAR(20), followers INTEGER );

CREATE TABLE query8( c1name VARCHAR(20), c2name VARCHAR(20), lname VARCHAR(20) );

CREATE TABLE query9( cname VARCHAR(20), totalspan INTEGER );

CREATE TABLE query10( cname VARCHAR(20), borderslength INTEGER );

a2.ddl --------------------------------------------------------------------------------------------------------------

Instructions 1. Read this assignment thoroughly before you proceed. Failure to follow instructions can affect your grade. 2. Download the database schema a2.ddl from the course website. 3. Download the file a2.sql from the course website. 4. Download the java skeleton file Assignment2.java from the course website. 5. Submit your work electronically using UNIX submit. Your submission must include the following files: a) a2.sql your queries for the interactive SQL part of the assignment (can include any view creation statement). If you define any views for a question, you must drop them after you have populated the answer table for that question. your java code for the embedded SQL part of the assignment. Be careful to submit the .java file (not the .class file.). To get you started, we provide a skeleton of this file that you must download from the assignment webpage. b) Assignment2.java Embedded SQL Queries [50 marks - 5 for each method] For this part of the assignment, you will create the class Assignment2.java which will allow you to process queries using JDBC. We will use the standard tables provided in the a2.ddl for this assignment. If you feel you need an intermediate view to execute a query in a method, you must create it inside that method. You must also drop it before exiting that method Rules Standard input and output must not be used. This will halt the "automarker" and you will probably end up with a zero The database, username, and password must be passed as parameters, never "hard-coded". Be sure to close all unused statements and result sets All return values will be String, boolean or int values A successful action (Update, Delete) is when o It doesn't throw an SQL exception, and o The number of rows to be updated or deleted is correct. Class name Assignment2.java Description Allows several interactions with a postgreSQL database Instance Variables (you may want to add more) Type Connection Description The database connection for this session Methods (you may want to add helper methods.) Constructor Assignment20) Description Identifies the postgreSQL driver using Class.forName method Metho boolean connectDB(String URL, String username, Stringrespectively, establish the Connection to be used for this session. Returns true if Description Using the String input parameters which are the URL, username, and password the connection was successful Closes the connection. Returns true if the closure was successful password) boolean disconnectDB() boolean insertCountry(int ciod, String name, int height, int population) Inserts a row into the country table. cid is the name of the country, name is the name of the country, height is the highest elevation point and population is the population of the newly inserted country. You have to check if the country with id cid exists. Returns true if the insertion was successful, false otherwise Returns the number of countries in table "oceanAccess" that are located next to the ocean with id oid. Returns -1 if an error occurs int getCountriesNext ToOceanCount(int oid) String getOceanlnfo(int oid) Returns a string with information of an ocean with id oid. The output should be formatted as "oid:oname:depth". Returns an empty string "" if the ocean does not exist Method boolean Description Changes the HDI value of the country cid for the year year to the HDI value chgHDI(int cid, int year, float supplied (newHDI). Returns true if the change was successful, false otherwise newHDI) boolean deleteNeighbour(int clid, c2id) Deletes the neighboring relation between two countries. Returns true if the deletion was successful, false otherwise. You can assume that the neighboring relation to be deleted exists in the database. Remember that if c2 is a neighbor of c1, c1 is also a neighbour of c2 Returns a string with all the languages that are spoken in the country with id cid. String listCountryLanguages(int cid) The list of languages should follow the contiguous format described below, and contain the following attributes in the order shown: (NOTE: before creating the string order your results by population) "l1id:l1lname:l1population l2id:12lname:12populationIn..." where lid is the id of the language Iname is name of the country population is the number of people in a country that speak the language, note that you will need to compute this number, as it is not readily available in the database. Returns an empty string"" if the country does not exist boolean updateHeight(int cid, int decrH) Decreases the height of the country with id cid. This decrease might happen due to the natural erosion (i.e. height - decrH). Returns true if the update was successful, false otherwise boolean updateDB) Create a table containing all the countries which have a population over 100 million. The name of the table should be mostPopulousCountries and the attributes should be cid INTEGER (country id) cname VARCHAR(20) (country name) Returns true if the database was successfully updated, false otherwise. Store the results in ASC order according to the country id (cid) Instructions 1. Read this assignment thoroughly before you proceed. Failure to follow instructions can affect your grade. 2. Download the database schema a2.ddl from the course website. 3. Download the file a2.sql from the course website. 4. Download the java skeleton file Assignment2.java from the course website. 5. Submit your work electronically using UNIX submit. Your submission must include the following files: a) a2.sql your queries for the interactive SQL part of the assignment (can include any view creation statement). If you define any views for a question, you must drop them after you have populated the answer table for that question. your java code for the embedded SQL part of the assignment. Be careful to submit the .java file (not the .class file.). To get you started, we provide a skeleton of this file that you must download from the assignment webpage. b) Assignment2.java Embedded SQL Queries [50 marks - 5 for each method] For this part of the assignment, you will create the class Assignment2.java which will allow you to process queries using JDBC. We will use the standard tables provided in the a2.ddl for this assignment. If you feel you need an intermediate view to execute a query in a method, you must create it inside that method. You must also drop it before exiting that method Rules Standard input and output must not be used. This will halt the "automarker" and you will probably end up with a zero The database, username, and password must be passed as parameters, never "hard-coded". Be sure to close all unused statements and result sets All return values will be String, boolean or int values A successful action (Update, Delete) is when o It doesn't throw an SQL exception, and o The number of rows to be updated or deleted is correct. Class name Assignment2.java Description Allows several interactions with a postgreSQL database Instance Variables (you may want to add more) Type Connection Description The database connection for this session Methods (you may want to add helper methods.) Constructor Assignment20) Description Identifies the postgreSQL driver using Class.forName method Metho boolean connectDB(String URL, String username, Stringrespectively, establish the Connection to be used for this session. Returns true if Description Using the String input parameters which are the URL, username, and password the connection was successful Closes the connection. Returns true if the closure was successful password) boolean disconnectDB() boolean insertCountry(int ciod, String name, int height, int population) Inserts a row into the country table. cid is the name of the country, name is the name of the country, height is the highest elevation point and population is the population of the newly inserted country. You have to check if the country with id cid exists. Returns true if the insertion was successful, false otherwise Returns the number of countries in table "oceanAccess" that are located next to the ocean with id oid. Returns -1 if an error occurs int getCountriesNext ToOceanCount(int oid) String getOceanlnfo(int oid) Returns a string with information of an ocean with id oid. The output should be formatted as "oid:oname:depth". Returns an empty string "" if the ocean does not exist Method boolean Description Changes the HDI value of the country cid for the year year to the HDI value chgHDI(int cid, int year, float supplied (newHDI). Returns true if the change was successful, false otherwise newHDI) boolean deleteNeighbour(int clid, c2id) Deletes the neighboring relation between two countries. Returns true if the deletion was successful, false otherwise. You can assume that the neighboring relation to be deleted exists in the database. Remember that if c2 is a neighbor of c1, c1 is also a neighbour of c2 Returns a string with all the languages that are spoken in the country with id cid. String listCountryLanguages(int cid) The list of languages should follow the contiguous format described below, and contain the following attributes in the order shown: (NOTE: before creating the string order your results by population) "l1id:l1lname:l1population l2id:12lname:12populationIn..." where lid is the id of the language Iname is name of the country population is the number of people in a country that speak the language, note that you will need to compute this number, as it is not readily available in the database. Returns an empty string"" if the country does not exist boolean updateHeight(int cid, int decrH) Decreases the height of the country with id cid. This decrease might happen due to the natural erosion (i.e. height - decrH). Returns true if the update was successful, false otherwise boolean updateDB) Create a table containing all the countries which have a population over 100 million. The name of the table should be mostPopulousCountries and the attributes should be cid INTEGER (country id) cname VARCHAR(20) (country name) Returns true if the database was successfully updated, false otherwise. Store the results in ASC order according to the country id (cid)