JIT ‐ Building IT Systems
Fall, 2020
Take‐Home Task 2: Our World
(Weight: 30%.
Due: 29
November)
Overview
This task requires
you to use several
of the
technologies
introduced this
semester:
programming
(Python),
database queries
(SQLite) and web development (HTML).
Goal
This task involves writing a Python program which interrogates an SQLite database and
generates HTML documents as its output. It is thus an excellent example of the main theme
of this unit, i.e., the way different “computer languages” can be used together to build new
IT systems.
The aim of this task is to develop a program that creates HTML documents to provide a visual
representation of world population statistics according to data stored in a database. More
specifically:
given a country, show all the cities in that country that have a population of at least
a certain value (threshold).
For example, the screenshot in Figure 1 shows the HTML document produced to represent
the cities in Australia with a population of at least 100,000.
JIT ‐ Building IT Systems
Fall, 2020
If you browse the data in the database using the DB
Browser SQLite, you will see that there are 14 Australian
cities included in the database.
Querying the database in the DB Browser SQLite for cities
in Australian with a population >= 100,000 returns 13 rows
as shown in Figure 2.
Notice that those 13 cities in the results set are displayed
in the HTML page generated in Figure 1.
Information about the query is displayed at the top of the
HTML (eg the country name, the threshold and the number
of cities with a population of at least that threshold.
The names of the cities are displayed in the body of the
page, and cities with a larger population are displayed in a
bigger font size ‐ making a comparison of population sizes
immediately obvious to the reader.
The supplied Python template file includes a number of
tests that provide the country name, threshold and HTML
filename prefix. For example, Test 2 was used to produce
the output in Figure 1:
Test 2: Aus cities > 100000
>>> show_population(['Australia'], 100000, 'Test02')
Notice that the first parameter is a list of string, with each string representing one
country. So your program must accept a list of (one or more) countries, and produce a set of
HTML pages: one per country. For example, see Test 5 which contains as its first parameter
a list of five countries.
Figure 2: SQL query ‐ Australian Cities >= 100,000
Figure 1: HTML output file
JIT ‐ Building IT Systems
Fall, 2020
Test 5 - 4 countries
>>> show_population(['Germany', 'New Zealand', 'Austria',
'Australia'], 1000000, 'Test05')
In this case, five HTML pages must be generated – each containing a visual representation of
the country's cities and those cities' population as described above.
The Database
An SQLite database file named world.db containing the data to use for this task has been
supplied. (This database is a sample database acquired for teaching purposes only, and is not
meant to represent a complete or up‐to‐date set of values.)
There are three tables in this database: City, Country and CountryLanguage. Among them,
two tables are needed for this task, City and Country. The definition of these two tables is
explained below.
City Table
The City table provides information about each city in the database. See Figure 3. Each row
in the table consists of five fields. Only three of the fields in this table are of interest to us in
this assessment task: Name, the full name of the city; CountryCode, a code representing the
country it belongs to; and Population, the number of people living in this city. These (and
other) fields are defined by the schema below:
ID numeric,
Name text,
CountryCode text,
District text,
Population numeric,
PRIMARY KEY (ID)
Figure 3: City Table
JIT ‐ Building IT Systems
Fall, 2020
Country Table
The Country table providesinformation about the countriesthose cities belong to. See Figure
4. Each row in the table consists of many fields, only two of which are of interest to us in this
assessment task: Code, a unique identifier for the country (the primary key in this table), and
Name, the full name of the country. These (and some of the other) fields are defined by the
schema below:
Code Text,
Name Text,
Continent Text,
Region Text,
SurfaceArea Numeric,
…
PRIMARY KEY (Code)
Importantly, note that the two tables have one field in common: Code in the Country table,
and CountryCode in the City table. (Hint: you will need to join the tables to produce all of the
required information from your SQLite query.)
General Requirements
Given a list of countries, we want to know the population of each country's cities that are at
or above a certain threshold. The required output is a set of HTML pages, one per country.
Each HTML page must contain a visual representation of the country's city populations.
Cities with a larger population must be displayed in a bigger font.
You are required to develop a Python program that accesses the supplied SQLite database
described above to generate HTML documents. The calling code to your program specifies:
one or more countries (supplied as a list);
a population threshold (supplied as an integer value); and
the output HTML filename prefix to be used (supplied as a string).
For example, the following tests have been included in the template, each showing a call to
the function show_population, with three arguments supplied (a list of one or more
strings representing the countries, a number representing the population threshold, and a
string representing the output HTML filename prefix):
"""
Test 1: China cities > 1m
>>> show_population(['China'], 1000000, 'Test01')
Figure 4: Country Table
JIT ‐ Building IT Systems
Fall, 2020
Test 2: Aus cities > 100000
>>> show_population(['Australia'], 100000, 'Test02')
Test 3 - 2 countries
>>> show_population(['China', 'Australia'], 1000000, 'Test03') #
Test 4 - 3 countries
>>> show_population(['China', 'India', 'United States'], 10000, 'Test04')
Test 5 - 4 countries
>>> show_population(['Germany', 'New Zealand', 'Austria', 'Australia'],
1000000, 'Test05')
Test 6 - 6 countries
>>> show_population(['Indonesia', 'Japan', 'Thailand', 'Taiwan', 'Ireland',
'United Kingdom'], 1000, 'Test06')
Test 7 - empty results set
>>> show_population(['Australia'], 5000000, 'Test07')
"""
The result of each test is a collection of one or more HTML documents, one per country,
showing all cities in that country with a population at or above the given threshold.
The name of each HTML document should reflect the test used to produce it, including the
prefix supplied (eg "Test06") followed by the country name (eg "Indonesia"). For Test06, note
that there will be six HTML documents created, each named with the prefix followed by the
country name.
For example
the HTML
documents
generated
from Test 3
should be
similar to
those shown in
Figure 5 and
Figure 6.
Figure 5: Test 3 Output file: "Test03_China.html"
JIT ‐ Building IT Systems
Fall, 2020
The idea isthat the more populated cities are displayed in larger fonts. For instance, Shanghai
is more populated than Peking, while Zibo is considerably less populated. In Figure 5, the font
used for Shanghai is obviously larger than that of Peking and Zibo. You should also use
different colours to display each city. Although not a requirement, it would be useful to
display similarly sized cities in the same colour. In any event, and in each case in the figures
above, the relative populations of the cities in each of the specific countries is shown clearly
by the font sizes.
Another feature of the documents shown in Figure 5 and Figure 6 is that on the top of the
page you must display the country name, e.g., "Cities of China", and the number of cities in
this country, e.g., 35 in this case.
In addition, your program must hyperlink each of the pages together in the order of the list
of countries supplied, to make it easy for the user to search through them.
In Figure 5 and Figure 6 above we can see hyperlinks at the bottom of the page to the previous
or next categories in the list. However, no ‘previous’ link is produced for the first category in
the country list and no ‘next’ link is produced for the last page in a list.
For example, if the given list of countries is ["Australia", "China", "India"] then your function
must generate three HTML pages, one for Australia, one for China, and one for India. In the
China page, the ‘previous’ link should take us to the Australia page and the ‘next’ link should
take us to the India page. However, for the Australia page, there is no ‘previous’ link, and for
the India page there is no ‘next’ link.
You may not import any additional modules into your program other than those already
included in the given Python template file.
Specific Tasks
Develop a Python program which generates such pages using the data in the SQLite database
world.db. As illustrated in the Python template file accompanying these instructions, you
must define a function called show_population which, given a list of countries, a
population threshold and a title, produces HTML files that can be displayed in a web browser,
one per country.
Figure 6: Test 3 Output file: "Test03_Australia.html"
JIT ‐ Building IT Systems
Fall, 2020
As the database changes from time to time (to include updates of world population figures
etc.) your program must work for any given database of the same structure. That means,
you may not hard‐code your solution to produce the results expected with this particular set
of data. The HTML pages produced by your program must be a result of your program
querying the database and producing the HTML content according to the results returned
from the database query.
The specific things you must do to complete this portfolio task are as follows. NB: This is a
large task. Although it is possible to complete the task using only one Python function,
show_population, we recommend that you break your solution down into several
functions to make your program code manageable and readable. You must use the supplied
template file OurWorld_template.py as a starting point.
1. Write a Python function named show_population that hasthree parameters: (a) a list
of strings representing one or more countries, (b) an integer value representing a
population threshold, and (c) a string representing the name for this particular multi‐page
document.
For example, if we execute the function call
show_population(['China', 'India', 'United States'], 10000,
'Test04')
the first parameter (a list of three values) indicates that we want three pages created, one
each for China, India and the United States. Each of these pages must show the cities for
the particular country, whose population is equal to or above the threshold of 10000 (the
second parameter).
The third parameter is simply a name used to uniquely identify this collection of HTML
pages. You should use it together with the country name to create the filename for the
each HTML file. In this particular example your program must produce the following three
HTML files:
Test04_China.html
Test04_India.html
Test04_United States.html
Cities must be displayed in alphabetical order regardless of their population.
At the bottom of each page, you must have hyperlinks to the previous page and next page
in the list, if any.
The font size used for each city must reflect its relative population as described above.
2. Some queries will produce no results at all. In this case you should still produce an HTML
page, even if there are no cities listed. An example is showed in Figure 7 below (which is
one of the pages produced by Test 7).
Do not change the supplied tests. However, you may of course include additional tests.
JIT ‐ Building IT Systems
Fall, 2020
Figure 7: A page with no results returned
Development hints
Before you can begin this task you must ensure that you have access to SQLite DB
Browser so that you can work with the supplied database. You must also have access
to the necessary SQLite‐Python module so that you can call SQLite functions from
Python code.
Given a country, you will need to execute a query on the database, to get the list of
cities with a population at least as high as the threshold, and that population figure.
The query involves a join of both the country and city tables. It is recommended that
you debug your database queries in the DB Browser before attempting to incorporate
them into your Python program.
Use the screenshots shown above to guide your solution, but you do not need to
duplicate the precise choices of font sizes and colours. Importantly, however, your
SQL query must return exactly the same cities and display their relative populations in
a fashion similar to the examples above.
This is a large program, so it is suggested that you tackle it incrementally. Before you
even begin coding you should use the DB Browser to determine exactly what query
your Python program needsto execute. Then develop code to generate just one HTML
page. Finally, write the code to iterate over a list of countries to generate all
documents.
You need to work out how to define the font size and colour for the text to be
displayed on web pages. This can be done by changing the font’s ‘style’. Refer to the
lecture demonstrations and workshop exercises for how to do this.
Deliverables
You should develop your solution by completing and submitting a single .py file containing:
1. a function show_population which has all the characteristics defined above.
When executed in the presence of an appropriately configured SQLite database, this
program must produce the required HTML files.
JIT ‐ Building IT Systems
Fall, 2020
2. the completed “statement” at the beginning of the Python file to confirm that this is
your own individual work including your name and student number in the places
indicated. I will assume that a submission without a completed statement is not
your own work and will not mark it.
Do not submit any SQL or HTML files.
Apart from working correctly, your program code must be well‐presented and easy to
understand, thanks to (sparse) commenting that explains the purpose of significant code
segments and helpful choices of variable and function names. Professional presentation of
your code will be taken into account when marking this task.
If you are unable to solve the whole problem, submit whatever parts you can get working.
You will receive partial marks for incomplete solutions.
Academic Integrity
This assignment is for individual assessment only. That means the work you submit must be
your own work and not the work of anyone else. You are not permitted to collaborate with
your peers on this assignment, other than to discuss high‐level strategies. You are not
permitted to ask or commission someone else to write the assignment for you, or help you
write the assignment.
If you are in any doubt as to what is permitted and what is considered a breach of academic
integrity, please talk to one of the teaching staff as soon as possible.
Author: Colin Fidge (QUT)
Revised: Donna Teague (QUTC 2020)