From Reality to Databases: a OnetoMany Relationship

1
From Reality to Databases a One-to-Many
Relationship

• Stefano Spaccapietra
• Database Laboratory Swiss Federal Institute
  of Technology Lausanne (EPFL)
• joint work with
• Christine PARENT Christelle VANGENOT

http//lbd.epfl.ch
2
Outline

• Database design essentials
• Multiple representation
• Design alternatives

3
Database Terminology
Database design essentials

• Database design (data modeling) is the activity
  to elaborate a formal representation of relevant
  information about some subset of the real world
  that is of interest for users (applications) of
  the data.
• The outcome of the database design process is the
  schema of the database.
• The formalism used to express the schema is a
  data model.

4
Data Model
Database design essentials

• A data model is a set of concepts and rules.
• Relational data model table/relation,
  attribute/column, tuple/row, primary key, foreign
  key,
• Entity-Relationship data model entity, entity
  type, relationship, relationship type, attribute,
  role, cardinality, identifier,
• Object-oriented data model object, class,
  attribute, reference attribute, is-a hierarchy,
  inheritance,

5
Evolution of Data Models
Database design essentials
Spatio Temporal
Multi- representation
Expressive power
Extended ER
UML ODMG
Codasyl
Object Oriented
ER
Relational
Data Models
6
Database Design the Analysis Phase
Database design essentials
recognition structuring
Real World
perception
A database is a representation of that part of
reality we are interested in.
7
Database Design the Definition Phase
Database design essentials
description
Jean is a young man. He is married to Arlette,
and owns a green Honda CRV.
8
Fundamental Abstraction Classification
Database design essentials
From reality to representation Abstracting from
details to think in more generic terms, e.g. in
terms of object classes rather than individual
objects.
Lisa
Fred .
Dylan Anne ...
Zoë

• Object class Person
• properties - family name,
• - first name
• - age, ...

9
The Database Schema
Database design essentials

• A schema is a collection of types.
• The database will store instances of these types.
• An instance is a set of values taken by the
  properties attached to the type.

10
Schema and Instances
Database design essentials
11
Attributes of an Object Type
Database design essentials
Employee
Emp Ename telephones academic-achievements
positions
degree year title start-date
end-date salaries
complex, optional, multivalued
atomic, mandatory, monovalued
date amount
year month
12
Example of an ER schema
Database design essentials
boss
Boss-of
Employee
Supplier
subord.
E
name salary
Sname address
Delivery
Assigned-to
quantity
R
Sells
Department
Item
Dname floor quantity
Iname type
13
Non-determinism in Database Design
Database design essentials

• A database design is about choosing a
  representation
• The outcome is a
• partial
• subjective
• unfaithful
• description
• How do we introduce flexibility to support
  different ways of abstracting a representation
  from reality ?

14
Multiple Classification
Multiple representation
Land Vehicle
Car
Vehicle
Ford
Transport Mean
Vintage Car
Movie Accessory
Imported Good
Collectible
15
Multiple Viewpoints
Multiple representation
ROAD
Cartographer viewpoint
Traffic manager viewpoint
Construction engineer viewpoint
16
Multiple Spatial Resolution
125'000 scale
150'000 scale
17
Multidimensional Representation Space
Multiple representation
Classification
two representations of the same object in the
same viewpoint at two different resolution levels
Time Time granularity
Viewpoint
Space granularity
18
A Snapshot Database
Multiple representation
19
A Map
Multiple representation
20
Classification Dimension
Multiple representation

• Current Status refinement hierarchies

Person
faculties
persons
Is-a
technicians
Student
Employee
students
secretaries
Faculty
Technician
Secretary
21
Limitation Roles
Multiple representation
persons
companies
car-owners
Person
Car-owner
Company
intersection classes
Person-with-car
Company-with-car
partition constraint


22
A More Direct Representation
Multiple representation
Intersection link
23
Viewpoint Dimension
Multiple representation

• Relational DBMS support (mostly non-updatable)
  views, but semantics is poor
• Object-oriented DBMS have rich semantics but poor
  view mechanisms
• Object-relational DBMS ?
• Object-oriented expressiveness augmented with
  intersection links, roles and revised inheritance
  rules will provide the best solution

24
Space Granularity Multi-resolution
Multiple representation

• Cartographic Generalization is costly
• -gt store the result for reuse

• How do we express the links
• between different representations ?
• -gt update propagation

25
Multiple Geometries for the Same Object
Multiple representation
River described as an area or as a line
M
River
mr geo
Resolution Level 2
Resolution Level 1

• One possible solution stamping spatial
  attributes with the spatial resolution
• Spatial integrity constraints
• Sinuosity (River.geometry2) Sinuosity
  (River.geometry1)
• Length (River.geometry2) Length
  (River.geometry1)

26
Multiple Abstraction Levels Reformulation
Multiple representation

• Replacing a group of objects with a new object
• Example a set of buildings close to each other
  is replaced with a built-up area

27
Aggregation
Multiple representation

• Grouping of objects according to semantic and
  spatial relationships
• e.g., a set of buildings and adjacent fields
  belonging to the same farmer grouped into a
  single object Farm
• Derivation rules
• Farm.geometry Spatial Union (Field.geometry,Build
  ing.geometry)
• Aggregation constraint
• the fields and the buildings composing the same
  farm must belong to the same farmer and the
  fields must be adjacent.

Farm
1,n
1,n
Composed
Composed
Field
Building
28
Cartographic Approximation
Multiple representation
5 ground buildings (1,2,3,4,5) represented by 3
cartographic buildings (a,b,c)

• No 1-1 or n-1 mapping between ground and
  cartographic buildings
• N-m relationship

t ( 1,2,3,4,5 , a,b,c )
Ground Building
Cartographic Building
typify
A ground building can participate into 0 or 1
 typify  relationship
29
Topological Relationships
Multiple representation
At resolution level 1, the road is adjacent to
the enbankment. At resolution level 2, the
embankment is no longer represented. The road is
seen as adjacent to the building.
Level 1
Road
M
Near
M
Embankment
M
Level 2
30
Hierarchical value domains
Multiple representation

• Describe the same property at different
  abstraction levels
• Hierarchical value domains for attributes
• (similar to classification hierarchies for
  objects)

31
Multidimensional Representation Space
Design alternatives
How is the representation space - presented to
users? - implemented in Ddatabases?
Classification
Time granularity
Viewpoint
Space granularity
32
Possible Design Architectures
Design alternatives

• One single schema
• One schema per (combination of ) coordinate(s) on
  dimension(s)
• One schema per with an intrinsic schema

33
A Single Schema
Design alternatives
owner
Cartographic building
Parcel
M
along
Road
landuse
Typify
agr/use
M
near
Building
landuse
Parcel/use
composed
on/under
agr/owner
Castle
Plot
owner
Parcel/owner
Bridge
34
A multi-resolution schema per viewpoint
Design alternatives
owner
Cartographic building
Parcel
M
along
Road
landuse
Typify
agr/use
M
Building
near
landuse
Parcel/use
Viewpoint 1
agr/owner
on/under
Parcel/owner
Bridge
owner
M
composed
Building
Castle
Plot
Viewpoint 2
35
A Schema Per Viewpoint and Resolution
Design alternatives
Viewpoint A Resolution 2
Viewpoint B Resolution 2
Viewpoint A Resolution 1
Viewpoint B Resolution 1
inter-schema correspondences
36
A Schema Per Resolution and Viewpoint
Design alternatives
Building
Building
on
Castle
On / under
Bridge
Road
Parcel
Parcel/owner
agr/owner
agr/use
composed
Road
Parcel/use
Plot
near
Cartographic building
37
An Intrinsic Schema
Design alternatives
Intrinsic schema
schema B
schema A

• Intrinsic schema description of real world
  entities independently of any viewpoint

38
Murmur IST Project (2000-2002)

• A conceptual data model supporting space, time,
  and multirepresentation (extension of MADS)
• A corresponding query language (multirepresentatio
  n algebra)
• Two application cases (cartographic, risk
  assessment)
• A schema editor for visual data definition (DDL)
• A query editor for visual data manipulation
  (DML), including intelligent zooming and temporal
  travelling
• Implementation on a commercial GIS