Coursework Specification

You work for an organisation that requires reporting on population information. You have been tasked with designing and implementing a new system to allow easy access to this population information. The organisation has provided you with an SQL database to work from available here.

Reports

• All the countries in the world organised by the largest population to smallest.
• All the countries in a continent organised by the largest population to smallest.
• All the countries in a region organised by the largest population to smallest.
• The top N populated countries in the world where N is provided by the user.
• The top N populated countries in a continent where N is provided by the user.
• The top N populated countries in a region where N is provided by the user.
• All the cities in the world organised by the largest population to smallest.
• All the cities in a continent organised by the largest population to smallest.
• All the cities in a region organised by the largest population to smallest.
• All the cities in a country organised by the largest population to smallest.
• All the cities in a district organised by the largest population to smallest.
• The top N populated cities in the world where N is provided by the user.
• The top N populated cities in a continent where N is provided by the user.
• The top N populated cities in a region where N is provided by the user.
• The top N populated cities in a country where N is provided by the user.
• The top N populated cities in a district where N is provided by the user.
• All the capital cities in the world organised by the largest population to smallest.
• All the capital cities in a continent organised by the largest population to smallest.
• All the capital cities in a region organised by largest to smallest.
• The top N populated capital cities in the world where N is provided by the user.
• The top N populated capital cities in a continent where N is provided by the user.
• The top N populated capital cities in a region where N is provided by the user.
• The population of people, people living in cities, and people not living in cities on each continent.
• The population of people, people living in cities, and people not living in cities in each region.
• The population of people, people living in cities, and people not living in cities in each country.

Additionally, the following information should be accessible to the organisation:

• The population of the world.
• The population of a continent.
• The population of a region.
• The population of a country.
• The population of a district.
• The population of a city.

Finally, the organisation has asked if it is possible to provide the number of people who speak the following languages from greatest number to smallest, including the percentage of the world population:

• Chinese.
• English.
• Hindi.
• Spanish.
• Arabic.

Country Report

A country report requires the following columns:

• Code.
• Name.
• Continent.
• Region.
• Population.
• Capital.

City Report

A city report requires the following columns:

• Name.
• Country.
• District.
• Population.

Capital City Report

A capital city report requires the following columns:

• Name.
• Country.
• Population.

Population Report

For the population reports, the following information is requested:

• The name of the continent/region/country.
• The total population of the continent/region/country.
• The total population of the continent/region/country living in cities (including a %).
• The total population of the continent/region/country not living in cities (including a %).

Quality

The following criteria will be assessed for overall quality:

• Metrics from GitHub. Also used to assess individual contribution.
• Code quality including comments.
• Correct usage of branches.
• Continuous integration working.
• Kanban/Project Board being used.
• Quality and coverage of unit tests.
• Project requirements met.