Your cart is currently empty!
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…
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;
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;
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:
(watch out for division by zero here !)
Which forms of government are most frequent?
(distinct, count, group by order by)
(three-way inner join).
Turn in the following:
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.