Geoinformation Technology: lecture 3 Mapping of OO Models onto Tables

1
Geoinformation Technology lecture 3Mapping of
OO Models onto Tables

• 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. 2
(Geoobjekte und ihre Modellierung) of the open
e-content platform www.geoinformation.net.
2
Mapping onto Tables
3
Mapping onto Tables Overview

• Motivation of the relational data model
• From objects to tables
• Mapping of objects
• Keys
• Mapping of relationships
• Referential integrity
• Special cases aggregation and generalization,
• Queries to databases
• Further simplification
• Structures of ESRI ArcMap

4
Relational Data Model Motivation

• until now . . .
• Mapping of reality onto structures and processes
  of an information system
• Description of the logical structure of the data,
  its properties, its behavior and its
  interrelationships
• now...
• How are the data mapepd onto the structures of a
  concrete database system?
• Relational data model

5
Mapping From Objects to Tables
Classes and associations are represented by
tables, i.e. relations
district
state
municipality
n
n

• name string
• inhabitants number
• area number

• name string
• inhabitants number
• area number

• name string
• inhabitants number
• area number

getname( ) string getinhabitants( )number
getarea( ) number
getname( ) string getinhabitants( )number
getarea( ) number
getname( ) string getinhabitants( )number
getarea( ) number
6
Mapping Terms and Concepts

• A relational database consists of one or more
  tables (relations)
• The columns of a table are called attributes and
  are typed wrt. a value domain
• The rows of a table are called tuples
• Each relation is identified by a relation name
• The set of attributes together with the relation
  name determine the structure of a table, the
  relational schema

Relational schema
attribute
name
A1
. . .
. . .
AN
. . .
. . .
. . .
. . .
tuple
. . .
attribute value
7
Mapping Rules for Objects

• Every class corresponds (at least) to one table
• The name of the table corresponds to the name of
  the class
• Each object attribute corresponds to a table
  attribute (only for scalar valued attributes)
• Typically an identifier" is introduced as an
  additional table attribute representing the
  identity of the object
• The identifier forms the primary key of the table
  
• Methods are not taken into account
• Representation of the relational schema
• NAME (identifier, attribute 1,..., attribute n)
  

class
attribute 1 attribute 2 . . . attribute n
8
Example for Object Mapping Rules

• Relational schema of the class municipality
• municipality(Id number,
• name string,
• inhabitants number,
• area number)

9
Mapping Keys

• Minimal set of attributes, whose values identify
  a tuple unambiguously within a relation
• Please note
• an identifier attribute does not have to be
  introduced in any case also existing attributes
  can be used
• advantage of an identifier attribute a simple
  type (like an integer number) can be chosen
• In the following, identifiers are generally used

name
A1
. . .
. . .
AN
. . .
. . .
. . .
. . .
tuple
. . .
Attributes, which are used as the primary key,
are shown underlined
10
Mapping Example for Keys

• At Technische Universität Berlin, a student is
  identified by its matriculation number
• In Berlin, a student is identifiable only by the
  combination of its matriculation number and the
  respective university name

student
matriculation number
. . .
. . .
1234567
. . .
. . .
. . .
. . .
. . .
student
matriculation number
Univ.
. . .
1234567
TUB
. . .
. . .
. . .
. . .
11
Mapping Associations The General Case

• General case mn association
• Every relationship is represented by its own
  table the name of the relationship turns into
  the name of the table
• Associations may relate more than two classes
  (see lower UML model)
• Involved classes are represented by their keys
  (identifier) the keys are the attributes of the
  relationship
• The set of all attributes forms the key of the
  table
• Representation of the relational schema
• NAME(key class 1,..., key class n)

class 1
class 2
class 1
class 3
class 2
12
Example for the General Case

• river(riverId number, name string,
  length number)
• country(countryId number, name
  string, inhabitants number, area number)
• flows_through(riverId number, countryId
  number)

m
flows through
n
13
Mapping Referential Integrity

• Integrity assumption existence of the associated
  tuples ("referential integrity") For attributes
  of a relationship table there exist identifiers
  in the relations of the involved classes
• Error case A "pointer" to an object that leads
  into the void ("Dangling Pointer")

14
Example for a Dangling Pointer

?
15
Mapping Special Case 1n Relationship

• Refinement / simplification of the general
  mapping schema for nm associations
• Aggregation and composition usually are 1n
  relationships
• an object of class 1 (aggregate class) is in
  relation with n objects of class 2 (component
  class)
• Relational schema NAME(aggregateClassId,
  componentClassId)
• Question Why is the key of the component class
  key of the relationship table?
• Integration / elimination of relations
• The relationship is added as a further attribute
  to the component class (see example on next
  slide)
• Please note This is an integration of relations
  having the same key!
• Advantage Saving of an additional table

16
Example for the Integration of Relations

• instead of use an

equivalent representation
district( districtId, name,
inhabitants, area)
district( districtId, name,
inhabitants, area)
municipality( municipalityId, name,
inhabitants, area) AGGDistrictMunicipality(
municipalityId, districtId)
municipality( municipalityId, name,
inhabitants, area, districtId)
17
Mapping Special Case 11 Relationship

• Refinement / simplification of the general
  mapping schema for nm associations
• An object of class 1 is in relation with exactly
  one object of class 2
• Relational schema NAME(class_1_Id, class_2_Id)
• Question What about choosing the key in this
  case?
• Integration / elimination of relations in
  principle analogously to the 1n relationship
• however, rule for the 1n relationship is only
  partially applicable, because
• different integration scenarios possible -
  depending on the election of the key

18
Example for the Mapping of a 11 Relationship
1
1
Office
Professor( ProfId, PersNo, Name,
FirstName) Room( RoomId, RoomNo, Size,
Location) Office(ProfId, RoomId)
Professor( ProfId, PersNo, Name, FirstName,
RoomId) Room( RoomId, RoomNo, Size,
Location)
19
Mapping of the Generalization Association

• Generalization is a 11 relationship (between
  tables!)
• There is no explicit relationship that is
  representable as a relation (table) there
  exists only a hierarchy of classes
• Two options for modelling generalization
  associations
• Option 1 Extend the relations (tables)
  representing the superclass and all subclasses by
  an additional attribute objectId, which realizes
  the connection between superclass and subclass
• or adopt the primary key of the top most
  superclass (transitively) in any subclass
• Option 2 Extend all tables representing the
  subclasses by the attributes of the superclass

20
Generalization Example Option 1

• geomFigure(objectId, centerpoint, visible)
• triangle(objectId, a,b,c)

In order to get the full information about a
triangle, the relations geomFigure and triangle
must be joined.
21
Generalization Example Option 2

• There are only specialized relations.
• The inheritance is no longer visible.
• No instances of geomFigure possible

• triangle(objectId, centerpoint, visible,
  a,b,c)

22
Example Query for Joining Information

• DISTRICT(
• districtId,
• name,
• inhabitants,
• area)
• MUNICIPALITY(
• municipalityId,
• name,
• inhabitants,
• area,
• districtId)

Query the names of the municipalities of the
district Rhein-Sieg with more than 50 000
inhabitants SELECT municipality.name FROM
MUNICIPALITY, DISTRICT WHERE
municipality.inhabitants gt 50 000 AND
MUNICIPALITY.districtId
DISTRICT.districtId AND DISTRICT.name
Rhein-Sieg
23
Simplified Mappings Example for Maps

• In some cases, further considerations lead to
  even simpler table structures

24
Simplified Mappings Table Structure of a Map
1
1
1
1...
2
3...
property
parcel
polygon
edge
geometry
restricts
-owner person
2...
restricts

• PROPERTY(propertyId, owner)
• POLYGON(polygonId, parcelId, propertyId)
• EDGE(edgeId, polygon1, polygon2, node1, node2)
• NODE(nodeId,X,Y)
• Exercise 1 In which sense does this relational
  schema conflicts with the rules introduced in the
  lecture?
• Exercise 2 Discuss to what extent this
  relational schema reflects the UML-diagram
  correctly.

2
node
1
geometry
1
point

• X number
• Y number

25
Simplified Mappings Example Query
1
1
1
1...
2
3...
property
parcel
polygon
edge
geometry
restricts
-owner person
2...
restricts

• Query Which are the neighbour polygons of the
  polygon with the ID X?
• SELECT polygon1
• FROM edge
• WHERE polygon2 X
• UNION
• SELECT polygon2
• FROM edge
• WHERE polygon1 X

2
node
1
geometry
1
point

• X number
• Y number

26
Tables in ArcMap
27
ArcMap Structures

• A Layer in ArcMap is the representation of a set
  of objects of the same geometry type (point,
  polygon or polyline)
• The theme of these geo-objects is represented by
  an associated table
• Each row of the associated table corresponds to
  one geo-object
• Aggregation is implicit (by the identifier)
• Example There is not an object Spree" but only
  individual polylines, that form the river Spree
• Queries on the table in SQL

Arc Map document
data frames
layer
table
28
ArcMap Federal States
29
ArcMap States, Government-districts and
Districts
30
ArcMap Attribute Table of the Districts
31
ArcMap Identif. of a District with the Mouse
32
ArcMap States and Rivers
33
ArcMap Attribute Table of the Rivers
34
References

• Kemper, Alfons, André Eickler Datenbanksysteme.
  
• 3. Auflage - Oldenbourg, München, Wien,
  1999
• Rumbaugh, James et al. Object-Oriented
  Modeling and Design. Prentice Hall,
  1991