DBMS- Database Management System
DBMS stand for Database Management System. A database required on all machines that connect to a database management systems. It is a general-purpose software system that facilitates the process of defining, constructing, and manipulating databases for various applications
What is DBMS?
A Database Management System is a collection of programs that enable users to create and maintain a database. Database and DBMS software together called a Database system. The examples of Database system are: Microsoft Office Access, Oracle, Microsoft SQL Server, PostgreSQL, etc.
Key Features:
- Data Integrity: Ensures the accuracy and consistency of data.
- Data Security: Protects sensitive data through access controls.
- Data Redundancy Control: Reduces duplication of data.
- Concurrency Control: Manages simultaneous data access by multiple users.
- Backup and Recovery: Provides mechanisms to recover data in case of a failure.
Advantages:
- Controlling Redundancy
- Enforcing integrity constraints
- Restricting unauthorized access
- Providing multiple user interface
- Representing complex relationships among data
- Database administrators
- Database designers
- Data backup and recovery
- End user
- System analysts and application programming
Types of DBMS
There are different types of DBMSs, and they can be broadly categorized as follows:
1. Hierarchical DBMS
- Data is stored in a tree-like structure where each record has a single parent and possibly many children.
- Example: IBM’s Information Management System (IMS).
2. Network DBMS
- Similar to hierarchical DBMS, but records can have multiple parent records (many-to-many relationships).
- Example: Integrated Data Store (IDS).
3. Relational DBMS (RDBMS)
- The most common type, where data is stored in tables (also called relations) that have rows and columns.
- Tables can be related to each other through keys (Primary and Foreign Keys).
- Example: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
4. Object-Oriented DBMS
- Data is stored as objects, similar to how objects are represented in object-oriented programming.
- Example: ObjectDB, db4o.
5. NoSQL DBMS
- Designed for unstructured or semi-structured data, often used in big data and real-time web apps.
- Can be document-based, key-value, column-family, or graph-based.
- Example: MongoDB, Cassandra, Redis.
Database Server
In ArcGIS software, the term database servers refers to instances of Microsoft SQL Server Express that have been enabled to store desktop or workgroup Geodatabases.
To set up a database server, install SQL Server Express and enable it to store Geodatabases, add a connection to the SQL Server instance under the Database Servers node in the Catalog tree, and create Geodatabases.
Database Management System in GIS
A GIS database or DBMS includes data about the spatial locations and shapes of geographic features recorded as points, lines, areas, pixels, grid cells, or TINs, as well as their attributes.
Types of DBMS Used in GIS
| DBMS Type | Example Systems | GIS Usage |
|---|---|---|
| Relational (RDBMS) | PostgreSQL, SQL Server, Oracle, MySQL | Most common; stores attribute & spatial data |
| Object-Oriented DBMS | Informix, ObjectDB | Stores complex spatial objects |
| File-Based DBMS | MS Access (.mdb), SQLite | Small datasets, single-user projects |
| NoSQL (Non-relational) | MongoDB, Cassandra | Used in modern cloud GIS, real-time data |
DBMS Data Types supported in ArcGIS
You might have spatial or nonspatial data in a database that you want to use in ArcGIS. That database does not have to contain an enterprise Geodatabase for you to connect directly to it from the Catalog tree in ArcGIS for Desktop.
- Dameng
- IBM Db2
- IBM Informix
- Microsoft SQL Server
- Oracle
- PostgreSQL
- SAP HANA
- SQLite
- Teradata Warehouse Appliance
Dameng
| Data types | Dameng data types created |
|---|---|
| BLOB | BLOB |
| Date | Datetime6 |
| Double | Dec(p,s) |
| Float | Dec(p,s) |
| Geometry | ST_Geometry |
| GUID | Char(38) |
| Long Integer | Integer |
| Object ID | Integer |
| Short Integer | Smallint |
| Text | Varchar |
IBM Db2
| Data types | Db2 data types created |
|---|---|
| BLOB | BLOB |
| Date | timestamp |
| Double | decimal(p,s) |
| Float | decimal(p,s) |
| Geometry | ST_Geometry |
| Global ID | char(38) |
| GUID | char(UUID len) |
| Long Integer | integer |
| Object ID | Character(38) when created in enterprise geodatabasesInteger with identity property when created in a database |
| Raster | BLOB |
| Short Integer | smallint |
| Text | varchar |
IBM Informix
| Data types | Informix data types created |
|---|---|
| BLOB | BLOB |
| Date | datetime |
| Double | decimal(31,8) |
| Float | decimial(31,8) |
| Geometry | ST_Geometry |
| Global ID | char(38) |
| GUID | char(UUID len) |
| Long Integer | integer |
| Object ID | char(38) when created in an enterprise geodatabaseserial when created in a database |
| Raster | BLOB |
| Short Integer | smallint |
| Text | varchar |
Microsoft SQL Server
| Data types | SQL Server data types created |
|---|---|
| BLOB | varbinary(max) |
| Date | datetime2(7) |
| Double | numeric(p,s) |
| Float | numeric(p,s) |
| Geometry | intgeometrygeography |
| Global ID | uniqueidentifier |
| GUID | uniqueidentifier |
| Long integer | int |
| Raster | BLOB, int |
| OBJECT ID | int(4) when created in an enterprise geodatabaseinteger with identity property when created in a database |
| Short integer | smallint |
| Text | varchar, nvarchar, varchar(max), nvarchar(max) |
Oracle
| Data types | Oracle data types created |
|---|---|
| BLOB | BLOB |
| Date | timestamp |
| Double | number (38,8) |
| Float | number (38,8) |
| Geometry | ST_Geometry, number(38), or SDO_Geometry |
| Global ID | char or nchar (UUID len) |
| GUID | char or nchar (UUID len) |
| Long Integer | number (38) |
| Object ID | ArcGIS to create a feature class or table in an Oracle 12c database or use the Add Incrementing ID Field geoprocessing tool to add an ID field to a table in an Oracle 12c database. |
| Raster | BLOB or number (38) |
| Short Integer | number (5) |
| Text | varchar2, CLOB, nvarchar2, or NCLOB |
PostgreSQL
| Data types | PostgreSQL data types created |
|---|---|
| BLOB | byte |
| Date | timestamp without zone |
| Double | numeric(p,s) |
| Float | numeric(p,s) |
| Geometry | ST_Geometry, Geometry (PostGIS), Geography (PostGIS) |
| Global ID | varchar(38) |
| GUID | varchar(38) |
| Long Integer | integer |
| Object ID | Integer in a geodatabase Serial in a database |
| Raster | bytea |
| Short Integer | smallint |
| Text | character varying |
SAP HANA
| Data types | SAP HANA data types created |
|---|---|
| BLOB | BLOB |
| Date | timestamp |
| Double | decimal(38,8) |
| Float | decimal(38,8) |
| Geometry | ST_Geometry |
| Global ID | character(38) |
| GUID | character(38) |
| Long Integer | integer |
| Object ID | bigint |
| Raster | Not applicable |
| Short Integer | smallint |
| Text | varchar(50) |
SQLite
| Data types | SQLite data types created |
|---|---|
| BLOB | blob(n) |
| Date | realdate |
| Double | float64 |
| Float | float64 |
| Geometry | geometryblob |
| Global ID | uuidtext |
| GUID | uuidtext in SQLite databases, text in GeoPackage files |
| Long Integer | int32(n) |
| Object ID | int32 |
| Short Integer | int16(n) |
| Text | text(n) |
Teradata Warehouse Appliance
| Data types | Teradata data types created |
|---|---|
| BLOB | BLOB |
| Date | timestamp |
| Double | decimal (38,8) |
| Float | decimal (38,8) |
| Geometry | CLOB (ST_Geometry) |
| GUID | character(38) character set latin |
| Long Integer | integer |
| Object ID | integer |
| Short Integer | smallint |
| Text | varchar (50) character set Unicode |
Differences between DBMS and RDBMS
| DBMS | RDBMS |
|---|---|
| DBMS applications store data as file. | RDBMS applications store data in a tabular form. |
| DBMS data is stored in either a hierarchical form or a navigational form. | RDBMS has an identifier called the primary key, and the data values are stored in the form of tables. |
| Normalization is not present in DBMS. | Normalization is present in RDBMS. |
| DBMS uses a file system to store data, so there will be no relation between the tables. | RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well. |
| DBMS has to provide some uniform methods to access the stored information. | RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information. |
| DBMS does not support distributed database. | RDBMS supports distributed database. |
| DBMS is meant to be for small organization and deal with small data. it supports single user. | RDBMS is designed to handle large amount of data. it supports multiple users. |
| Example: Oracle, SQL Server. | Example: MySQL, PostgreSQL, SQL server, oracle etc. |
