CSE 4051: Project #10 -- Language Database

Due: Wedensday, 28 April 2008.

Task

You may work in teams of upto two people from the class. You may not work with the same person twice.

mysql-connector-java-3.1.8-bin.jar

Information needed to access the database I am running for this project.

com.mysql.jdbc.Driver
jdbc:mysql://sql.cs.fit.edu/sil?user=cse4051&password=jdbc

Example SQL queries that work on MySQL.

SELECT * FROM languages WHERE (name REGEXP "^J" AND speakers >=10000) ORDER BY name
SELECT name,speakers,family FROM languages WHERE speakers >=10000 ORDER BY name
SELECT name,speakers,family FROM languages WHERE (speakers >=100000 AND speakers <=200000) ORDER BY name
SELECT name,speakers,family FROM languages WHERE speakers BETWEEN 100000 AND 200000 ORDER BY name;
SELECT name,speakers,family,fips FROM languages,countries WHERE countries.code=languages.code and (name REGEXP "^J" AND speakers <=10000) ORDER BY name
SELECT DISTINCT family FROM languages ORDER BY family;
SELECT alias, languages.* from sil.aliases,sil.languages where sil.aliases.code=sil.languages.code and aliases.alias LIKE "%obo%";
SELECT * from sil.aliases where alias LIKE "%cantonese%";

Data

The table languages (show columns from languages):

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| code     | char(3)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| family   | varchar(20) | YES  |     | NULL    |       |
| class    | varchar(60) | YES  |     | NULL    |       |
| speakers | int(11)     | YES  |     | NULL    |       |
| scc      | varchar(4)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

AAA|Ghotuo|Niger-Congo|Atlantic-Congo/Volta-Congo/Benue-Congo/Edoid/North-Central/Ghotuo-Uneme-Yekhee|9000|Nigr
AAB|Arum-Cesu|Niger-Congo|Atlantic-Congo/Volta-Congo/Benue-Congo/Platoid/Benue/Tarokoid|4000|Nigr
AAC|Ari|Trans-New Guinea|Main Section/Central and Western/Gogodala-Suki/Gogodala|80|Papu
AAE|Albanian, Arbëreshë|Indo-European|Albanian/Tosk|80000|Ital
AAF|Aranadan|Dravidian|Unclassified|600|Inda
AAG|Are|Indo-European|Indo-Iranian/Indo-Aryan/Southern zone/Unclassified|2590|Inda
AAI|Arifama-Miniafia|Austronesian|Malayo-Polynesian/Central-Eastern/Eastern Malayo-Polynesian/Oceanic/Western Oceanic/Papuan Tip/Nuclear/North Papuan Mainland-D'Entrecasteaux/Are-Taupota/Are|2150|Papu
AAK|Ankave|Trans-New Guinea|Main Section/Central and Western/Angan/Angan Proper|1600|Papu
AAL|Afade|Afro-Asiatic|Chadic/Biu-Mandara/B/B.1/Kotoko Proper|25000|Nigr
AAM|Aramanik|Nilo-Saharan|Eastern Sudanic/Nilotic/Southern/Kalenjin/Nandi-Markweta/Nandi|0|Tanz

The table aliases (show columns from aliases):

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| alias | varchar(45) | YES  |     | NULL    |       |
| code  | char(3)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Otwa|AAA
Otuo|AAA
Arum-Chessu|AAB
Alai|AAD
Arbëreshë|AAE
Eranadans|AAF
Arya|AAG
Kalika Arya Bhasha|AAG
Ade Bhasha|AAG
Aray|AAG

The table sil_countries:

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| scc     | varchar(4)  | YES  |     | NULL    |       |
| country | varchar(20) | YES  |     | NULL    |       |
| fips    | char(2)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

sil country|country|
Afgh|Afghanistan|AF
Alba|Albania|AL
Alge|Algeria|AG
Amer|American Samoa|AQ
Ando|Andorra|AN
Ango|Angola|AO
Angu|Anguilla|AV
Anti|Antigua|AC
Arge|Argentina|AR
Arme|Armenia|AM

Image files

• http://www.odci.gov/cia/publications/factbook/maps/[FIPS]-map.gif
• http://www.odci.gov/cia/publications/factbook/flags/[FIPS]-lgflag.gif

Or, locally:

• http://www.cs.fit.edu/~ryan/factbook/factbook2007/maps/[FIPS]-map.gif
• http://www.cs.fit.edu/~ryan/factbook/factbook2007/flags/[FIPS]-lgflag.gif

Example

Helpful Stuff

• JDBC tutorial, in particular Retrieving Values from Result Sets
• The MySQL WWW page, and a JDBC driver for MySQL. In particular, the jar file you need is mysql-connector-java-3.1.12-bin.jar.
• Introduction to Structured Query Language, a tutorial.
• Structure query language (SQL). Intro to SQL, an interactive tutorial.
• CIA factbook CIA factbook
• SIL Ethnologue

Turning it in

Turn in the Java source code for the program using the submission server. To submit your file, use proj11. Do not use jar files. (OK, if you use packages send me one jar file for the source code of your project.) Call your application LanguageDatabase. Your program will be invoked something like this:

java -classpath .:<path>mysql-connector-java-3.1.12-bin.jar LanguageDatabase

System.setProperty ("jdbc.drivers", "com.mysql.jdbc.Driver");

File to be submitted: Control code:

Course=cse4051 Project=proj12