Description
Download the world_data.sql file*, and run it to create and populate the tables. You can run the file as follows:
psql -U postgres -f world_data.sql
Part I: The Schema.
Inspect the schema to see what attributes we have:
SELECT * FROM city LIMIT 2;
SELECT * FROM country LIMIT 2;
SELECT * FROM countrylanguage LIMIT 2;
- Draw a schema diagram of this data, showing how the data is arranged, and how the tables relate to each other.
Just use basic drawing software here – nothing fancy.
Include any links between tables – such as FOREIGN KEY relationships.
Hints:
To do this, you must identify all PRIMARY KEYs and FOREIGN KEYs. Open the world_data.sql file in a text editor, and search it for keywords. Most text editors allow searching via CTRL + f
You can also find this information by directly querying the database schema. There is a built-in table called information_schema, which contains all of the information about our tables, and we can ask it questions:
SELECT table_name, column_name, constraint_name FROM information_schema.constraint_column_usage;
- Is this database in Normal Form?
If so, which one is it in? 1NF, 2NF, 3NF?
If not, what is preventing it from being normalized? Can it be normalized?
Hints:
Look directly at the data, and also at the CREATE TABLE and ALTER TABLE statements in the world_data.sql file.
The tables also have CONSTRAINTs – what do they tell you?
*Notice that all this data is already in SQL format, as opposed to Assignment 1, where we loaded the data from a comma-separated values file (“.csv”) . Getting this data into SQL format was accomplised by a built-in utility called pg_dump – which makes it easy to export entire databases into a file of SQL commands which we can re-load whenever we want. We will be using this utility later in this course.
Part II: Queries.
Warm-ups: Some SQL practice:
- What are the top ten countries by economic activity (Gross National Product – ‘gnp’).
- What are the top ten countries by GNP per capita?
(watch out for division by zero here !)
- What are the ten most densely populated countries, and ten least densely populated countries?
- What different forms of government are represented in this data? (‘DISTINCT’ keyword should help here.)
Which forms of government are most frequent?
(distinct, count, group by order by)
- Which countries have the highest life expectancy? (watch for NULLs). Getting more serious – joins, joins with conditions, joins that require subqueries:
- What are the top ten countries by total population, and what is the official language spoken there? (basic inner join)
- What are the top ten most populated cities – along with which country they are in, and what continent they are on? (basic inner join)
- What is the official language of the top ten cities you found in Question #7?
(three-way inner join).
- Which of the cities from Question #7 are capitals of their country? (requires a join and a subquery).
- For the cities found in Question#9, what percentage of the country’s population lives in the capital city? (watch your int’s vs floats !).
Turn in the following:
- Your schema diagram – as a .png, .jpg, or .pdf
- DO NOT SEND ANY CELL PHONE PICTURES OF DIAGRAMS ! **
- A .sql file with your queries to answer Questions #1 – #10.
For queries, only submit the query (I don’t need results – I can run them). Text answers to Question 2 can just be included at the top of
your .sql file in comments.
*Notice that all this data is already in SQL format, as opposed to Assignment 1, where we loaded the data from a comma-separated values file (“.csv”) . Getting this data into SQL format was accomplised by a built-in utility called pg_dump – which makes it easy to export entire databases into a file of SQL commands which we can re-load whenever we want. We will be using this utility later in this course.
