Database Concepts

Part 1: Databases and Spatial DBMS Basics

1. What is a Database and a Spatial DBMS?

• Database: An organized, structured collection of data stored electronically in a computer system, designed for rapid search, retrieval, and updating.
• Spatial DBMS: A specialized database system engineered specifically to store, query, and manage geographic geometries (Points, Lines, Polygons) alongside traditional tabular data. Standard databases only understand text and numbers; a Spatial DBMS understands coordinates and spatial relationships (like "inside" or "touches").

2. Reasons for using a Database Management System (DBMS)

Instead of keeping a bunch of messy Excel spreadsheets (Flat Files), engineers use a DBMS for several critical reasons:

1. Reduces Data Redundancy: Data is stored in one central location, preventing multiple conflicting copies of the same file.
2. Maintains Data Integrity: Enforces strict rules (e.g., you cannot enter text into a column meant for numeric Pipe Diameters).
3. Data Security: Allows administrators to restrict who can view or edit specific data layers.
4. Concurrent Access: Multiple engineers can view and edit the database simultaneously without crashing the file.

Flat Files

No direct past exam questions have been asked from this section, but it is part of the syllabus and may appear in future exams.

A flat file is the simplest form of database — a single table stored as a plain text file (CSV, TXT) or spreadsheet (XLS) where all data is contained in one file with rows and columns. There are no relationships between tables, no indexing, and no query language.

Structure of a Flat File

Each row represents one record and each column represents one field/attribute. For example:

Flat Files vs. DBMS

Why GIS Moved Beyond Flat Files: Early GIS systems stored attribute data in flat files (e.g., .dbf files accompanying shapefiles). However, as datasets grew and projects required multi-user access, complex queries, and data integrity, GIS adopted relational database management systems (RDBMS). Flat files are still used for simple data exchange (e.g., CSV export from GPS receivers).

Part 2: The Four Major Database Models

When asked to explain the different types of DBMS structures, you must list these four.

1. Hierarchical Database Model

• Concept: Organizes data in a top-down, tree-like structure. A "parent" record can have multiple "child" records, but a child can strictly only have one parent (a 1-to-Many relationship).
• Example: A Province (Parent) contains multiple Districts (Children).
• Disadvantage: Very rigid. If you want to search across different branches of the tree, it is extremely slow. (Draw a simple organization chart tree structure for the exam).

2. Network Database Model

• Concept: Similar to the hierarchical model, but it allows a child record to have multiple parent records (a Many-to-Many relationship), forming a complex web or graph.
• Disadvantage: Highly complex to program and maintain. If one link breaks, the whole web can fail.

3. Relational Database Model (RDBMS)

• Concept: Data is stored in simple, flat 2D tables consisting of Rows (records) and Columns (attributes). Tables are independent but can be mathematically "joined" together using common ID fields.
• Advantage: Highly flexible, intuitive, and the undisputed standard for modern GIS.

4. Object-Oriented Database Model

• Concept: Data is stored as "objects" (similar to object-oriented programming like Python or C++). An object contains both the data (attributes) and the executable code (methods/behaviors) that act upon it.

Part 3: RDBMS in GIS and Maintaining Relationships

This is the most heavily tested concept in the chapter.

1. What is an RDBMS and How is it Used in GIS?

A Relational Database Management System (RDBMS) organizes spatial and non-spatial data into tables (called relations).

• How it is used in GIS: The GIS software stores the geographic geometry (the shape of a land parcel) in one table and the descriptive attributes (owner name tax value) in another table. It seamlessly links them together behind the scenes allowing you to click a polygon on the map and instantly see its tabular data.

2. How are Relationships Maintained in an RDBMS? (3 Marks)

Relationships between separate tables are established and maintained using mathematical keys strictly avoiding hard-coded links.

• Primary Key: A column in a table where every single row has a unique non-repeating identifier (e.g. a unique Citizen ID number).
• Foreign Key: A column in a different table that contains values corresponding to the Primary Key of the first table.
• The Mechanism: By matching the Foreign Key in Table B to the Primary Key in Table A the database temporarily joins the rows together creating a unified view of the data.

3. Why does GIS use RDBMS instead of other database systems? (3 Marks)

1. Flexibility: Unlike the rigid hierarchical model RDBMS allows you to join any table to any other table dynamically as long as they share a common key.
2. Simplicity: Tables (rows and columns) are extremely intuitive for humans to read and understand.
3. SQL Support: RDBMS uses Structured Query Language (SQL) standardizing how data is queried and edited across almost all software platforms (ArcGIS QGIS PostGIS).

Part 4: The SQL Join Scenario (10 Marks)

How can you join two different tables having one common attribute using a SQL assuming the following tables with common attribute landuse id in Table-1 and id in Table-2?

Table-1: Vector data of landuse polygons with columns fid landuse_id both being integer.
Table-2: Non-spatial table describing the land use with columns id and landuse_type (such as built-up farmland etc.). (2082 Shrawan)

This question tests if you can actually write the SQL code to perform the theoretical join we just discussed.

The Problem Setup:

• Table-1 (Spatial): Contains landuse polygons. Columns: fid landuse_id.
• Table-2 (Non-spatial): Contains descriptive text. Columns: id landuse_type.

How to join them: You join the two tables by matching the common attribute: landuse_id from Table-1 must equal id from Table-2.

The SQL Query Answer:

Write this exact SQL statement on your exam paper:

Explanation for the Examiner:

• SELECT: Specifies exactly which columns we want to see in the final output (the feature ID and its text description).
• FROM: Specifies the primary spatial table.
• INNER JOIN: Tells the database to combine it with Table-2.
• ON: The logical condition that maintains the relationship. It instructs the software to link the rows only where the Foreign Key (landuse_id) matches the Primary Key (id).

Data Modeling

No direct past exam questions have been asked from this section, but it is part of the syllabus and may appear in future exams.

Data modeling is the process of defining how real-world geographic features and their relationships will be organized, structured, and stored in a GIS database. It bridges the gap between the real world and the digital database.

Levels of Data Modeling

1. Conceptual Model: A high-level, non-technical description of what data is needed. Uses Entity-Relationship (ER) diagrams.
   • Entities: The real-world objects to be represented (e.g., Road, Building, River, Parcel)
   • Attributes: Properties of each entity (e.g., Road has name, width, surface type)
   • Relationships: How entities relate to each other (e.g., a Building "is located on" a Parcel, a Road "crosses" a River)
2. Logical Model: Translates the conceptual model into a database structure — defining tables, columns, data types, primary keys, and foreign keys. This is where normalization rules are applied.
3. Physical Model: The actual implementation in a specific database system (e.g., PostGIS table creation SQL, ArcGIS geodatabase feature classes). Includes decisions about indexing, storage, and performance optimization.

Example: Simple GIS Data Model for a Municipality

Key Principle: Good data modeling ensures that the database can efficiently answer the spatial questions the project demands. A poorly modeled database leads to data redundancy, slow queries, and analytical errors.

Views of the Database

No direct past exam questions have been asked from this section, but it is part of the syllabus and may appear in future exams.

A database can be viewed at three different levels of abstraction, known as the Three-Schema Architecture (ANSI/SPARC model). This architecture ensures data independence — changes at one level do not affect the others.

The Three Schemas

1. External Schema (View Level): What individual users or applications see. Different users can have different views of the same data. For example, a drainage engineer sees only pipe networks and catchment boundaries, while a land-use planner sees parcels and zoning layers — both accessing the same underlying database.
2. Conceptual Schema (Logical Level): The community view of the entire database. It describes what data is stored and the relationships between entities, without specifying how the data is physically stored. This is the level defined by the data model (ER diagrams, table structures).
3. Internal Schema (Physical Level): How the data is physically stored on disk — file structures, indexing methods, storage allocation, and access paths. This is managed by the DBMS and is invisible to end users.

Data Independence

• Logical Data Independence: The external schema is insulated from changes in the conceptual schema. If a new column is added to a table, existing user views are not affected.
• Physical Data Independence: The conceptual schema is insulated from changes in the internal schema. If the database is moved to a faster disk or the indexing method changes, the logical structure remains the same.

Why This Matters in GIS: In a large municipal GIS, hundreds of users (engineers, planners, tax officers) access the same spatial database. The three-schema architecture ensures each user gets a customized view without interfering with others, and the database administrator can optimize physical storage without disrupting any user's workflow.

Database Normalization

1. What is Data Normalization?

Data Normalization is a systematic process of organizing data within a relational database to minimize redundancy (unnecessary duplication of data) and ensure data integrity (accuracy and consistency). It involves breaking down large clunky tables into smaller well-structured tables and defining relationships between them using primary and foreign keys.

By normalizing a database a GIS engineer ensures that updating a property owner's name only requires changing it in one single place rather than updating hundreds of separate parcel records.

2. Explain 1NF and 2NF (with rules)

Normalization is done in stages called "Normal Forms."

• First Normal Form (1NF):
  • The Rule of Atomicity: To be in 1NF every cell in a table must contain a single indivisible (atomic) value.
  • Explanation: You cannot have a column that holds multiple values. For example if a table has a column called "Phone_Numbers" and a single row contains "9841234567, 9801234567" it violates 1NF.
  • The Fix: You must split this data so that each phone number gets its own distinct row and ensure every column has a unique name.

• Second Normal Form (2NF):
  • The Rule of Full Dependency: To be in 2NF the table must already be in 1NF and it must have no partial dependencies.
  • Explanation: This applies when a table uses a composite primary key (a primary key made of two or more columns combined). Every non-key attribute must depend on the entire composite key not just part of it.
  • The Fix: If your table has a composite key of Project_ID and Engineer_ID but also has a column for Engineer_Name the name only depends on the Engineer_ID not the Project_ID. This is a partial dependency. You must remove Engineer_Name and place it in a separate "Engineers" table.

Database Analysis (SQL and Keys)

1. What is SQL? Give an example.

• Definition: SQL (Structured Query Language) is the standard programming language designed specifically for managing, querying, and manipulating data stored in Relational Database Management Systems (RDBMS). In GIS, it is the exact language running behind the scenes whenever you use the "Select by Attributes" tool.
• The Example: If the exam asks you to select a few rows based on a condition, write down a simple scenario and the corresponding SQL code.
  • Scenario: We have a database table named Land_Parcels and we want to find all parcels that have an area greater than 500 square meters.
  • SQL Code:

2. What is the function of a Primary Key in a database table?

The primary key is the absolute most important column in any GIS attribute table (often automatically generated as FID or OBJECTID). You should list these specific functions for full marks:

1. Guarantees Uniqueness: It ensures that no two rows in a table are exactly identical. Even if two landowners have the exact same name and address, their unique Primary Key (e.g., Citizen_ID) separates them.
2. Prevents Null Values: A primary key column strictly cannot be empty (NULL). Every single spatial feature must have an identifier.
3. Anchors Relationships: It serves as the mathematical anchor point to link to other tables. When another table wants to reference this record, it uses the Primary Key as a "Foreign Key," allowing the RDBMS to instantly join the tables together.
4. Optimizes Searching: Databases automatically "index" primary keys, making spatial queries and data retrieval extremely fast, even with millions of geographic records.

Querying a Database

1. What is meant by querying a database?

Definition: Querying a database means submitting a specific, logically structured request to the Database Management System (DBMS) to retrieve a subset of data that matches certain defined conditions.

In the context of GIS: A query acts as a strict mathematical filter. Instead of scrolling through an attribute table with 753 local level boundaries looking for specific ones, a query automatically searches the database and isolates only the spatial features (polygons) and tabular records that meet your exact engineering or demographic criteria, completely ignoring the rest. Crucially, a query only views or selects the data; it does not permanently delete or alter the original database.

2. SQL Example: Select Rural Municipalities by Population

How can you use a SQL to select all the rural municipalities having population between 10000 and 30000, provided that you have a polygon vector data of municipalities with columns: id, name, type-of-municipality (gaunpalika: rural municipality, nagarpalika, upmahanagarpalika, mahanagarpalika) and population?

The Problem Setup:

• Data Source: A polygon vector table (let's call the table Municipalities).
• Available Columns: id, name, type-of-municipality, population.
• The Goal: Find all rows where the municipality is a Rural Municipality ("gaunpalika") AND the population is between 10,000 and 30,000.

The SQL Query Answer:

To secure full marks, write this exact SQL statement on your exam paper. (Note: When column names contain hyphens like type-of-municipality, they are usually wrapped in quotes or brackets in SQL to prevent math errors, but writing it standardly is accepted in theory exams).

(Alternative valid syntax for the population line: AND population BETWEEN 10000 AND 30000;)

Explanation for the Examiner:

To guarantee maximum marks, briefly explain what your code does below the query:

• SELECT id, name, population: This command tells the database which specific columns of data you want returned to look at. (You could also use SELECT * to return all columns).
• FROM Municipalities: This identifies the specific spatial attribute table you are pulling the data from.
• WHERE type-of-municipality = 'gaunpalika': This is the first filter condition. It forces the database to ignore all nagarpalikas, upmahanagarpalikas, and mahanagarpalikas. (Note: Text strings must be enclosed in single quotes).
• AND: This logical Boolean operator ensures that both conditions must be strictly true for a row to be selected.
• population >= 10000 AND population <= 30000: This applies the mathematical range filter to the numeric population column.

End of Chapter 5 – Database Concepts