CSE 3330/5330                                                              Summer 2008

Project #2 Description

 

 

In this project, you will design and implement a database for keeping track of geographical information pertaining to the United States, Canada, and Mexico. (This can be expanded to keep track of other countries, but we will not do that to keep the application manageable). The geographical entities you will keep track of include countries, states, cities, rivers, seas, oceans, lakes, etc. You will first design an EER schema for this database application. Then, you will map the EER schema into a relational schema and implement it on ORACLE. Finally, you will load some data files into your ORACLE database, and run some queries and update transactions.

 

The following are the high-level requirements for the GEOGRAPHY database:

1.       The database will keep track of COUNTRIES, STATES, and CITIES. Each COUNTRY has a unique CountryName, CapitalCity, and NeighbouringCountries of each COUNTRY. In addition, the continent to which each country belongs is kept. The PopulationHistory of each country is kept. PopulationHistory is a list of (Year, Population) entries (not every year has to be on the list).

2.       Countries are made up of STATES (sometimes, the divisions within a country (for example, in Canada) are called provinces, but we will call them states for uniformity). Assume that a STATE is uniquely identified by the StateName. For each STATE, the CapitalCity of the state is kept, and the database will keep track of the NeighbouringStates and NeighbouringCountries, if any. For some states, the population history is also kept.

3.       The database will keep track of some major CITIES. A city is identified by the combination (CityName, StateName). For some cities, the database will keep track of the current Population for the city, and the Longitude/Latitude co-ordinates of the city center.

4.       The database will keep track of some of the major WATER_BODIES, which can be RIVERS, LAKES, SEAS, CANALS, or OCEANS. A WATER_BODY is identified by a unique WBName. The database will keep track of the Cities, Countries, States, or Continents that are ON a water body (ON is defines as adjacent to or intersect with a water body).

 

You will first design an EER diagram for the GEGRAPHY database requirements specified above, and create an EER schema diagram and documentation report describing your design choices. As part of this assignment, you should identify any missing or incomplete requirements, and explicitly state them in your documentation. You should also explicitly state any assumptions you made that were not part of the above requirements.

 

The second part of the assignment will be to map the EER schema design to a relational schema, and create the tables corresponding to the relational schema using the ORACLE DBMS. You will add to your report a listing of the CREATE TABLE statements. Specify as many constraints (key, referential integrity) as you can in the relational schema. You should state the choices you made during the EER to relational mapping, and the reasons for your choices. You can also update the EER design during this phase if needed as long as you clearly document the changes and the reasons for changing the EER schema.

 

The third part of the project is to load some data into the database, and apply certain update transactions and queries. You will load data about some of the cities, states, provinces, and water bodies in the United States, Canada, and Mexico, and some of their surrounding and intersecting bodies of water.

 

The following are the tasks for the third part of the project:

1.       Load the information into the database tables that you created in Part 2 of the assignment. You can either write a loading program, or use SQL/PLUS (insert command), or use SQL/FORMS. The data files will be available by the last week of July on the course Web page.

2.       Write two database update transactions using JAVA and JDBC to add a new estimate to the population history of a country and a state. The transactions will be similar except that one will be for countries and the other for states. The input will be a new population value and the year it was estimated, as well as the name of the country or state. Your transaction should add this value to the population history, but it should check that there is no value currently for the year entered, and that the estimate is for a new year that is after (later than) the last population year in the database for that country or state.

3.       Write the following SQL queries using SQLPLUS, and turn in a script file showing both the queries and the query results:

(a) Which states in the USA share a border with Mexico?

(b) Which states in Canada share a border with Minnesota?

(c) Which are the cities in the database that are on the ¡°Gulf of Mexico¡± ?

(d) For the USA and Mexico, give the country name and the number of states in each country.

(e) List all states in the USA whose capital city has a population of more that 500,000.

(f) List the names of all states in the USA that are neither adjacent to Mexico nor to Canada.

(g) List all states in the USA that are not adjacent to any sea or ocean.

(h) Which states in the USA have a population greater than ¡°Texas¡±?

(i) which state (province) in Canada has the largest current population?

 

4. Run your update transactions to enter some new population estimates of certain countries and states as will be given in the input data. Before updating the population histories, print the current population history, then run your update, then print the new population history, to show that your transaction works correctly.

 

Instructions on what to turn in will be available on the course site by the middle of July.

 

Due Dates:

Part 1: Monday July 21, 2008

Part 2: Monday July 28, 2008

Part 3: Thursday August 7, 2008

 

Late penalty: -5% per day late.

 

Project teams: Each project can be done in a team of 2 persons, or individually.