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 none

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

An object holds certain properties/attributes and reacts with a defined behavior (either a change of an objects state or an interaction with the environment -> other objects) to its surrounding; a class is like a template for objects and typically is an aggregation of similar but distinguishable objects.

A class holds class attributes which the objects can inherit.

Going from objects to superclasses generalizes concepts: A superclass represents a concept, a subclass adopts and/or overrides methods of the superclass and defines new, additional attributes and methods. The objects are then instances of the subclasses.

Aggregation is the concept that a class is an aggregation of several objects, i.e. a car and its parts; Generalization relates not objects but classes to each other. A superclass would be the Generalization of a more specialized (sub-)class.

Example:

Aggregation: A car consists of several parts.

Generalization: A rectangle is a geometrical figure.

Encapsulation reduces the visibility of attributes outside of the class and therefore protects the data so that less mistakes happen when data is changed. The attributes can be read and/or accessed by methods, i.e. set bachelor degree ().

Aggregation and Composition are both special types of association between objects.

In an Aggregation, an objects is an aggregation of several other objects, i.e. a car and its parts.

In a Composition, which is a strict form of aggregation, the existence of the parts depend on the compositional object. If the compositional object is deleted, the parts are deleted as well. This helps with data integrity.

1. Each class which doesn’t take part in the inheritance hierarchy is projected onto a table (class name = table name, class attributes = table attributes; sometimes, data types have to be adapted to SQL data types!)

2. Each association, besides inheritance, is projected onto a table (name of association or new name = table name, key attributes of the involved classes = attributes)

1. Refinement: Some tables can be combined (for example some tables which originate from associations -> for 1:n relationships (key of one class can be added to the other class as foreign key)! For n_m relationships the association table should stay)

   1. other example: 1:1 relationships could be combined

2. For inheritance relationships: only the classes are translated into tables but not the inheritance relationships (for object-realtional schemas, tables can inherit attributes from parent tables)

   1. either one table per class in the hierarchy: key attributes are the root in the hierarchy -> foreign key of subclasses

2. a single table for the entire hierarchy: all class attributes that appear in hierarchy are attributes, not corresponding attributes are set to 0

• super key: attribute(s) that can uniquely identify all rows in a table

• composite key: a key that contains more than one attribute, the largest composite key would be a key involving all the comulns in the table

• candidate key: a key that contains the least possible attributes to uniquely idetnify any table row (for example a unique id!); a table may have more than 1 candidate key

• primary key: a key that uniquely identifies each row, is chosen from sets of all canditate keys, can be a composite key or only one; has to be unique and cannot have null values; used to enforce the entity integrity of the table (make each row unique & accessible), helps to set up relationships between tables

  • examples: Student ID, Social security number, Vehicle Identification number, automatically generated numbner (auto increment, i.e. BIGSERIAL)

• foreign key: links to the primary key of another table -> established relationship between two tables, helps with cascading updates and deletes (important to mapping), can improve query performance by using joins; if unique: 1:1-relation, if 1:n or n:m the foreign key is NOT unique (but for n:m relationships, a junction table is necessary)

In Postgres, INNER JOIN is the default join, therefore, if we type JOIN, an inner join will be performed

LEFT JOIN = LEFT OUTER JOIN

RIGHT JOIN = RIGHT OUTER JOIN