Countries

Explore country data in the GeoNames model.

We'll cover the following

The raw.country table has several normalization issues. Before we list them, take a look at some data below:

─[ RECORD 1 ]──────┬─────────────────────────
iso                │ FR
iso3               │ FRA
isocode            │ 250
fips               │ FR
name               │ France
capital            │ Paris
area               │ 547030
population         │ 64768389
continent          │ EU
tld                │ .fr
currency_code      │ EUR
currency_name      │ Euro
phone              │ 33
postal_code_format │ #####
postal_code_regex  │ ^(\d{5})$
languages          │ fr-FR,frp,br,co,ca,eu,oc
geonameid          │ 3017382
neighbours         │ CH,DE,BE,LU,IT,AD,MC,ES
fips_equiv         │ ¤

Normalization failures

The main normalization failures we see are as follows:

  • Nothing guarantees the absence of duplicate rows in the table, so we need to add a primary key constraint.

    Here the isocode attribute looks like the best choice because it’s both unique and an integer.

  • The languages and neighbours, both attributes, contain multiple-valued content, a comma-separated list of either languages or country codes.

  • All non-key attributes should be dependent on the entire key, and the currencies and postal code formats are not dependent on the country to reach 2NF.

Checking for dependencies

A good way to check for dependencies on the key attributes is with the following type of query:

Get hands-on with 1400+ tech skills courses.