Geodatabases

Database System = Database Management System (DBMS) + Databases

Databases: either relational, object-oriented or object-relational; collection of data that’s logically related to each other; can store, manipulate & retrieve data

DBMS: software (PostgreSQL, MySQL, Oracle Database, SQLite) that allows to create, define & manipulate DBs, allows users for data to be stored persistently (is not unintentionally deleted!), updated, analyzed and queried (by SQL); controlls data to check consistency (definitions of data types, update rules… -> integrity constraints), can organize DBs, access to DBs is only possible through DBMS; has a well-defined stable user interface which is long-lasting and only extended, not changed; allows data independence through abstraction

Data: collection of discrete values

The client accesses the databases through a DBMS. Examples for clients: SQL shell, pgAdmin, python

• Every 2 years, amount of digital data is doubled -> large datasets that need to be organized somehow

• storing data in files is not future-proof (for example due to discontinued file formats)

• data processing can be performed directly with the database

• is efficient for storing large amounts of data -> less redundancy, which leads to less errors and inconsistencies

Data models are a collection of concepts for the desription of an abstract version of the real world. Concepts can be objects (vector data) or continuous (raster data). Which one to use is application specific. They are modelled using UML (unified modelling language) which can create models and convert them to database schemas.

There are:

• relational models (tables, what we learn about): simple structure, redundancy is avoided by splitting data across several tables; columns and rows, each column is assigned to one data type; fundamental operations: projection onto certain columns, selection of certain rows, joining data from different tables

• object-oriented models: more complex than relational model, uses concepts of object-oriented programming (classes i.e.)

• objet-relational models (what we use): an extension of the relational model in which user-defined types are possible; is necessary for spatial information

A software extension of an object-relational DBMS which supports spatial data models. Examples are Oracle Spatial and PostGIS.

Geographical data forms can be

• point, line and polygon objects

• raster data

• 3D point clouds

• networks

SQL is a popular query language found in all relational DBMS. It’s a declarative language which is divided into several sublanguages:

• Data Definition Language DDL for creating and modifying tables (CREATE, ALTER, DROP)

• Data Manipulation Language DML for inserting, updating and querying data (SELECT, INSERT; DELETE, UPDATE)

• Data Control Language DCL for defining access privileges of users (GRANT, REVOKE)

• Transdaction Control Language TCL for managing changes by DML (COMMIT, ROLLBACK)

• …

The ACID paradigm enforces rules for transactions (groups several data base operations to one unit)

Atomicity: transaction is a non-seperable unit; either all changes are made or non

Consistency: transaction leaves a consistent state after it ends or is undone completely

Isolation: concurrent transactions do not affect each other, every transaction is executed as if it’s the only one running

Durability: effect of transaction stays permanently in the database, even after system failure