1 / 34

Geoinformation Technology: lecture 3 Mapping of OO Models onto Tables

Geoinformation Technology: lecture 3 Mapping of OO Models onto Tables. Prof. Dr. Thomas H. Kolbe Institute for Geodesy and Geoinformation Science Technische Universität Berlin.

stephen-best
Download Presentation

Geoinformation Technology: lecture 3 Mapping of OO Models onto Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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. municipality • name: string • inhabitants: number • area: number + getname( ): string + getinhabitants( ):number + getarea( ): number municipality Id name inhabitants area 1 2 . . . Alfter Bornheim 22.700 45.000 3.473 8.200 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: m:n association • Every relationship is represented by its owntable; 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. river • name: string • length: number + getname( ): string + getlength( ):number country • name: string • inhabitants: number • area: number + getname( ): string + getinhabitants( ):number + getarea( ): number 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 relationshiptable 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. flows_through riverId countryId 1 1 … 20 11 (=Rheinland-Pfalz) 10 (=NRW) 2 (=Bayern) river riverId name length 1 2 … 19 21 … Rhein Donau … Elbe Weser 1.320 2.850 … 1.165 440 Example for a Dangling Pointer ?

  15. Mapping: Special Case 1:n Relationship • Refinement / simplification of the general mapping schema for n:m associations • Aggregation and composition usually are 1:n 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 1:1 Relationship • Refinement / simplification of the general mapping schema for n:m 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 relationsin principleanalogously to the 1:n relationship • however, rule for the 1:n relationship is only partially applicable, because • different integration scenarios possible - depending on the election of the key

  18. Professor Room -PersNo. -Name -First Name -RoomNo. -Size -Location Example for the Mapping of a 1:1 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 1:1 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. geomFigure -centerpoint: point -visible: boolean +display() +delete() +move() triangle rectangle circle -a: number -b: number -c: number -a: number -b: number -radius: number +display() +delete() +display() +delete() +display() +delete() Generalization Example – Option 1 In order to get the full information about a triangle, the relations geomFigure and triangle must be joined. • geomFigure(objectId, centerpoint, visible) • triangle(objectId, a,b,c)

  21. geomFigure -centerpoint: point -visible: boolean +display() +delete() +move() triangle rectangle circle -a: number -b: number -c: number -a: number -b: number -radius: number +display() +delete() +display() +delete() +display() +delete() 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 FROMMUNICIPALITY, DISTRICT WHERE municipality.inhabitants > 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 • 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. 3...* 2 1 1 1...* 1 edge polygon parcel property geometry restricts -owner: person 2...* restricts 2 node 1 geometry 1 point • X: number • Y: number

  25. Simplified Mappings: Example Query • 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 3...* 2 1 1 1...* 1 edge polygon parcel property geometry restricts -owner: person 2...* restricts 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

More Related