GeoDatabases: lecture 2 The Relational Data Model

1
Geo-Databases lecture 2The Relational Data
Model

• Prof. Dr. Thomas H. Kolbe
• Institute for Geodesy and Geoinformation Science
• Technische Universität Berlin

Credits This material is mostly an english
translation of the course module no. 8
(Geo-Datenbanksysteme) of the open e-content
platform www.geoinformation.net.
2
Introduction to the relational data model
3
Fundamentals of the relational model

• Structure
• All data is presented in tables (relations)
• Fixed number of columns (attributes)
• Variable number of rows (tuples or datasets)
• All values of one column from the same value
  domain
• Characteristrics
• Simple structuring
• Set-oriented processing
• Avoidance of redundancy by splitting and
  distribution of data into multiple tables

4
Operations

• With this background, the following operations
  are particularly useful
• Projection onto certain columns of a table
• Selection of certain rows of a table on the basis
  of a specific selection criterion
• Joining data from different tables

Implemented in the query language SQL
5
Structured Query Language (SQL)

• Popular, ISO standardised query language
  (currently SQL2003)
• SQL is based on natural language concepts
• SQL can be subdivided into the following areas
• Data Definition Language (DDL) for creation and
  modification of tables
• Data Manipulation Language (DML) for updating and
  querying of data
• Due to the complexity we will just give an
  overview of SQL1999 (for more information see
  literature)

For the time being all examples will be based on
SQL1999!
6
Tables in SQL

• To begin with single columns
• Each column of a table is assigned exactly one
  data type
• A data type defines the set of possible values
  and the allowed operations for this type
• Important data types
• integer integer numbers
• real floating-point numbers
• numeric(p,s) fixed-point numbers with precision
  p and scale s
• char(L) character string with fixed length L
• varchar(L) character string with maximum length
  L
• date data type for representation of dates
• blob(L) Binary Large Object, binary data with
  maximum length L

7
Creation of tables in SQL

• Tables are built using the CREATE TABLE command

8
Identification of data sets

• Problem How can we identify tuples
  unambiguously?
• Solution We need a key that unambiguously
  identifies each tuple!
• A key may comprise multiple attributes.
• There can be multiple keys for one table.
• Possible keys for the sample table
• Nr
• Name, Straße, Ort

9
Primary key

• It is advantageous to name a primary key to the
  system
• CREATE TABLE Studenten ( PRIMARY KEY (MatNr)
  )
• If no suitable primary key can be found, an
  artifical key may be generated by the system.

10
Foreign key

• Foreign keys in one table always refer to primary
  keys of other tables
• By means of a foreign key we can relate tuples
  from different tables

11
References

• Overview
• Ramez Elmasri, Shamkant B. Navathe, Fundamentals
  of Database Systems, 3. Ed., Addison Wesley, 2001
  
• Hector Garcia-Molina, Jeffrey D. Ullman, Database
  Systems The Complete Book, Prentice Hall, 2002
• Jim Melton, Alan R. Simon, SQL 1999
  Understanding Relational Language Components,
  Morgan Kaufmann Publishers, 2001
• Seminal work
• E. F. Codd, A relational model of data for large
  shared data banks, in Communications of the ACM,
  Vol. 13, Seite 377-387, 1970