CREATE TABLE "locations" (
regioncode text NOT NULL,
zonenum bigint NOT NULL,
    PRIMARY KEY (regioncode, zonenum)
);

First 3 rows:
regioncode      zonenum
------------  ---------
Taguatinga          315
Taguatinga          315
Guará               222
...


CREATE TABLE "amenities" (
amenityref bigint NOT NULL DEFAULT nextval('amenities_amenityref_seq'::regclass),
houseid bigint NOT NULL,
cablestatus text NOT NULL,
    PRIMARY KEY (amenityref),
    FOREIGN KEY (houseid) REFERENCES households(housenum)
);

First 3 rows:
  amenityref    houseid  cablestatus
------------  ---------  -------------
           1          3  avail
           2          4  available
           3          7  Available
...


CREATE TABLE "infrastructure" (
infraref bigint NOT NULL DEFAULT nextval('infrastructure_infraref_seq'::regclass),
wateraccess text NOT NULL,
roadsurface text NOT NULL,
parkavail text NOT NULL,
    PRIMARY KEY (infraref)
);

First 3 rows:
  infraref  wateraccess                           roadsurface        parkavail
----------  ------------------------------------  -----------------  -------------
         1  Yes, available at least in one room   Asphalt, concrete  Available
         2  Yes,  available at least in one room  Asphalt, concrete  Available
         6  Yes, available at least in one room   Asphalt, concrete  Not available
...


CREATE TABLE "service_types" (
serviceref bigint NOT NULL DEFAULT nextval('service_types_serviceref_seq'::regclass),
domestichelp text NOT NULL,
socsupport text NOT NULL,
    PRIMARY KEY (serviceref)
);

First 3 rows:
  serviceref  domestichelp         socsupport
------------  -------------------  ------------
           1  No domestic workers  No
          14  No domestic workers  No
          21  No domestic workers  Yes
...


CREATE TABLE "households" (
housenum bigint NOT NULL,
residentcount bigint NOT NULL,
locregion text NOT NULL,
loczone bigint NOT NULL,
serviceplan bigint NULL,
socioeconomic jsonb NULL,
    PRIMARY KEY (housenum),
    FOREIGN KEY (locregion) REFERENCES locations(regioncode),
    FOREIGN KEY (locregion) REFERENCES locations(zonenum),
    FOREIGN KEY (loczone) REFERENCES locations(regioncode),
    FOREIGN KEY (loczone) REFERENCES locations(zonenum),
    FOREIGN KEY (serviceplan) REFERENCES service_types(serviceref)
);

First 3 rows:
  housenum    residentcount  locregion      loczone    serviceplan  socioeconomic
----------  ---------------  -----------  ---------  -------------  ----------------------------------------------------------------------------------------------------------------
         4                4  Taguatinga         315              1  {'Tenure_Type': 'Owned', 'Expend_Coeff': 33.78, 'Income_Bracket': 'More than R$ 1,760 and less than R$ 2,640'}
         7                3  Taguatinga         315              1  {'Tenure_Type': 'owned', 'Expend_Coeff': 37.1846, 'Income_Bracket': 'More than R$ 2,640 and less than R$ 4,400'}
        22                3  Taguatinga         315              1  {'Tenure_Type': 'OWNED', 'Expend_Coeff': 37.2258, 'Income_Bracket': 'More than R$ 4,400 and less than R$ 8,800'}
...


CREATE TABLE "properties" (
propref bigint NOT NULL DEFAULT nextval('properties_propref_seq'::regclass),
houselink bigint NOT NULL,
infralink bigint NOT NULL,
dwelling_specs jsonb NULL,
    PRIMARY KEY (propref),
    FOREIGN KEY (houselink) REFERENCES households(housenum),
    FOREIGN KEY (infralink) REFERENCES infrastructure(infraref)
);

First 3 rows:
  propref    houselink    infralink  dwelling_specs
---------  -----------  -----------  -----------------------------------------------------------------------
       19           77            1  {'Bath_Count': 1, 'Room_Count': 3, 'Dwelling_Class': 'Brickwork house'}
       20          102            1  {'Bath_Count': 1, 'Room_Count': 2, 'Dwelling_Class': 'apartment'}
       21          103           21  {'Bath_Count': 1, 'Room_Count': 2, 'Dwelling_Class': 'Apartment'}
...


CREATE TABLE "transportation_assets" (
transref bigint NOT NULL DEFAULT nextval('transportation_assets_transref_seq'::regclass),
housetag bigint NOT NULL,
vehicleinventory jsonb NULL,
    PRIMARY KEY (transref),
    FOREIGN KEY (housetag) REFERENCES households(housenum)
);

First 3 rows:
  transref    housetag  vehicleinventory
----------  ----------  ---------------------------------------------------------------------------------------------------------
         4          22  {'Newest_Year': 'after 2014', 'vehicle_counts': {'Auto_Count': 2, 'Bike_Count': 0, 'Motor_Count': 0}}
         5          35  {'Newest_Year': '2010 TO 2013', 'vehicle_counts': {'Auto_Count': 1, 'Bike_Count': 1, 'Motor_Count': 0}}
         6          37  {'Newest_Year': 'nOt apPLIcaBlE', 'vehicle_counts': {'Auto_Count': 0, 'Bike_Count': 0, 'Motor_Count': 0}}
...
