DuckDB Database Project: Brazilian Mortality

June 6, 2025 · View on GitHub

Course: MC536 Databases
Repository: DuckDB-Database-Project-BrazilianMortality

Overview

This repository contains the implementation of a DuckDB database for analyzing Brazilian mortality, health, and economic indicators at the municipal and state levels, which are directly related to the "Good Health and Well-Being" Sustainable Development Goal. Data is loaded via Python, with a denormalized schema and scripts for preprocessing, loading, and querying.

Overview
Team Members
Database Schemas
Project Goals
Datasets
- Raw Data
- Preprocessed Data
Technology Stack
Project Structure
Setup and Usage
- Prerequisites
Analysis and Queries
Results

Team Members

Database Schemas

The core of this project is the non-relational database designed to integrate the different datasets. Below are visualizations of the conceptual and relational models:

Conceptual Model

Logical (Relational) Model

Project Goals

This project aims to implement a DuckDB database populated via Python with Brazilian data on mortality, health, and GDP of cities and states. The main objectives are:

Design a denormalized database schema that captures deaths, causes, basic health units, municipalities, states, and demographic details.
Populate the database with preprocessed datasets using automated loading scripts (Jupyter Notebooks).
Perform data validation to ensure referential integrity and consistency.
Formulate and execute five non-trivial queries to extract insights.

Non-trivial queries:

Estimate population, total deaths, and mortality rate (per 1000) by state
Find the most common cause of death (modal cause) for each age
Analyze death statistics by GDP per capita decile
Get the top 3 causes of death (under age 5) by mother's education level
Analyze non-external mortality rates grouped by population per health unit

Datasets

Sources

The data used in was obtained from the following sources:

Raw Data

The following raw datasets were used and are available in the datasets/ folder:

Mortalidade_Geral_2010.csv: General mortality records for Brazil in 2010.
vw_pib_percapita.csv: GDP per capita for Brazilian municipalities (view).
IBGE_cidades_2010.csv: Municipality names and IBGE city codes.
Unidades_Basicas_Saude-UBS.csv: Basic Health Unit registry.
CID-10-CATEGORIAS.csv: ICD-10 categories descriptions.

Preprocessed Data

After cleaning and transforming raw inputs, the following files in preprocessed_datasets_NoSQL/ were loaded into the database (each corresponds to one table in the logical model):

Death.csv: Stores date, time, location, death cause, deceased's personal data and other informations about the death.
Location.csv: Stores information about Brazilian states, cities, their GDP, their GDP per capita and their basic health units

Technology Stack

Database: DuckDB
Language: Python 3.x
Libraries:
- duckdb: DuckDB adapter for Python
- pandas: Data manipulation and analysis (used in preprocessing and query result display)
- jupyterlab/notebook: For running the .ipynb files
Tools: database.db (database management and data storage)

Project Structure

Postgres-Database-Project-BrazilianMortality/
│
├── datasets/  # Original raw datasets
│   ├── Mortalidade_Geral_2010.csv
│   ├── vw_pib_percapita.csv
│   ├── IBGE_cidades_2010.csv
│   ├── Unidades_Basicas_Saude-UBS.csv
│   └── CID-10-CATEGORIAS.csv
│
├── preprocessed_datasets_NoSQL/  # Cleaned CSV files ready for DB import
│   ├── Death.csv
│   └── Municipality.csv
│
├── models/  # Database model files
│   ├── Conceptual_Model.png
│   ├── Logical_Model.pgerd
│   ├── Logical_Model.png
│   └── Physical.sql
│
├── queries/  # Individual queries 
│   ├── cause_by_mothers_education.sql
│   ├── death_cause_by_age.sql
│   ├── life_expectancy_by_gdp.sql
│   ├── mortality_by_health_unit_density.sql
│   └── mortality_by_state.sql
│
├── results/  # Output CSV files from analytical queries
│   ├── cause_by_mothers_education.csv
│   ├── death_cause_by_age.csv
│   ├── life_expectancy_by_gdp.csv
│   ├── mortality_by_health_unit_density.csv
│   └── mortality_by_state.csv
│
├── database_creation_and_queries.ipynb  # Jupyter notebook for DB creation, data loading, and querying
├── preprocessing.ipynb  # Jupyter notebook for preprocessing raw data
├── python/  # Python notebooks for workflows
│   ├── preprocessing.ipynb  # Data cleaning and preprocessing
│   ├── tables.ipynb  # Creates DB connection, tables, and loads data
│   └── queries.ipynb  # Contains the five project queries
└── README.md  # This file

Setup and Usage

Prerequisites

Python 3.x
DuckDB
pip (Python package installer)
Required Python libraries:

pip install duckdb pandas jupyterlab ipykernel>=6

*(Note: `ipykernel` needed for Jupyter)*

5. Clone Repository: Clone this repository to your local machine. bash git clone <repository-url> cd DuckDB-Database-Project-BrazilianMortality 6. CSV Files: Ensure the CSV files are present in the /preprocessed_datasets_NoSQL directory.

Database Setup

Establish Connection: Open the python/tables.ipynb. Locate the database connection parameters near the beginning of the script. This will establish the connection to the file that stores the data database.db
```
# --- Database Connection Parameters ---
def connection():
  conn = ddb.connect("database.db")
  return conn
```

Running the Code

Start Jupyter: Navigate to the repository directory in your terminal and launch Jupyter Lab or Notebook:
```
jupyter lab
```
or
```
jupyter notebook
```
Open Notebook: Open the python/tables.ipynb notebook in Jupyter.
Execute Cells: Run the cells in the notebook sequentially.
- The notebook will first connect to the database.
- It will then execute the script to populate de database.
- Next, it will read data from the CSV files in the /preprocessed_datasets_NoSQL directory and load it into the corresponding database tables. Progress messages will be printed.
- Finally, it will execute the 5 predefined analytical queries and display their results within the notebook (using pandas DataFrames).

Data Preprocessing

The preprocessing.ipynb notebook details the steps taken to transform the original input files into the simplified format used for database loading. This involved selecting relevant columns, renaming columns, handling missing values (where appropriate), and potentially filtering data. Running this notebook is optional if the files in /preprocessed_datasets_NoSQL are already present and deemed correct.

Analysis and Queries

Data analysis was done through queries, which can be executed with the queries.ipynb notebook. The queries used were:

Further information about each query may be found in the queries.ipynb notebook, as well as in their specific .sql file.

Results

The output/results generated by executing the 5 analytical SQL queries in the main notebook are saved as CSV files in the /results directory for easy inspection: