5.1 Analyzing Requirements and Designing the Data Schema

Now you will delve into the crucial process of analyzing requirements and designing the data schema. You were requested to generate data for the dwellings, water distribution points, and educational facilities within the refugee camp.

To begin, it is vital to address the following key question: “What specific information needs to be captured for each dwelling, water distribution point, and educational facility within the refugee camp?” By identifying the different aspects to the information you’re interested in collecting, we can ensure that the database comprehensively represents the attributes and characteristics of these essential camp components.

First, you need to acquire information as follows:

5.2 Creating the Necessary Tables

Then, it’s time to create the tables with the following SQL that generates three tables and 3 to 4 columns with their correesponding data types:

-- Creating the Dwellings table
CREATE TABLE dwellings (
    dwelling_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    capacity INTEGER,
    occupancy_status VARCHAR(255),
    geom GEOMETRY(POLYGON, 32646)
);

-- Creating the Water Distribution Points table
CREATE TABLE water_distribution_points (
    point_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    capacity INTEGER,
    operational_status VARCHAR(255),
    geom GEOMETRY(POINT, 32646)
);

-- Creating the Educational Facilities table
CREATE TABLE educational_facilities (
    facility_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    facility_type VARCHAR(255),
    capacity INTEGER,
    geom GEOMETRY(POLYGON, 32646)
);

Note that the code after the geometry type refers to the Coordinate Reference System (CRS). This CRS is commonly used in the UTM (Universal Transverse Mercator) projection for the region around Bangladesh, including our region of interest (Chattogram).