Silke Eckstein Benjamin Köhncke
Institut für Informationssysteme Technische Universität Braunschweig www.ifis.cs.tu-bs.de
Relational
Database Systems 1
• Alternative ER Notations
• Extended ER –Inheritance
–Complex Relationships
• UML
2 Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig
3 Extended Data Modeling
• There is a plethora of alternative notations for ER diagrams
–Different styles for entities, relationships and attributes
–No standardization among them –Also, notations are often freely mixed
•ER diagrams can look completely different depending on the used tool / book
• In the following, we introduce the (somewhat popular) crow’s foot notation
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 3 EN 3.5
3.1 ER – Alternative Notations
• Crow’s foot notation was initially developed by Gordon Everest
–Derivate of 3.1 ERD notation –Main Goal
•Consolidate graphical representation
•Provide a better and faster overview
•Allow for easier layouting
–Widespread use in many current tools and documentations
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 4 EN 3.5
3.1 ER – Crow’s Foot Notation
• Entity Types
–Entity Types are modeled with a named box –Attribute names are written inside the box separated
by a line
•Key attributes are marked with a leading asterisk
•Composite attributes are represented with indentation
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 5 EN 3.5
3.1 ER – Crow’s Foot Notation
Book
isbn author
firstName
lastName
title
Book
* isbn author firstName lastName title
• Relationship Types
–Relationship types are modeled by lines connecting the entities
–Line is annotated with the name of the relationship which is a verb
–Cardinalities are represented graphically
•(0, 1) : Zero or one
•(1, 1) : Exactly one
•(0, *) : Zero or more
•(1, *) : one or more
•ATTENTION: Cardinalities are written on the opposite side of the relationship (in contrast to “classic ER”)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 6 EN 3.5
3.1 ER – Crow’s Foot Notation
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 7 EN 3.5
3.1 ER – Crow’s Foot Notation
owns
Person (0, *) (1, *) Cat
Person owns Cat
• What happens to n-ary relationships or relationship attributes?
* supplies *
Supplier Costumer
Part
*
number
• Problem
–N-ary relationship types are not supported by crow’s foot notation, neither are relationship attributes
• Workaround solution:
–Intermediate entities must be used
•N-ary relationships are broken down in a series of binary relationship types anchoring on the intermediate entity
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 8
3.1 ER – Crow’s Foot Notation
R
A C
B
Ra
A R
B
RB C
Rc
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 9 EN 3.5
3.1 ER – Crow’s Foot Notation
* *
Supplies number
supplies
Supplier Customer
Part
*
number
Customer Supplier
Part
is used provides
contains
• Originally, ER diagrams were intended to be used on a conceptual level
–Model data in an abstract fashion independent of implementation
• Crow’s foot notation sacrifices some conceptual expressiveness
–Model is closer to the logical model (i.e. the way the data is later really stored in a system)
–This is not always desirable and may obfuscate the intended semantics of the model
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 10
3.1 ER – Crow’s Foot Notation
3.1An example (cf. last week)
Student Professor
registration number
name
title credits
id
name department
Lecture
Course of Study enrolls
name part of
prereq.
curriculum semester
id
attends teaches
instantiates time
day of
week room
semester
Lecture instance 1
N N
N N 1
N
N
1
N N N
• The same example using Crow’s Foot Notation –Note that the “part of” relationship had to use an
intermediate entity
3.1 An example
Student +registrationNumber name
CourseOfStudy +name
Lecture +id title credits Lecture Instance
time dayOfWeek room semester
PartOf curriculumSemester
Professor +id name department attends
enrolls
has has
hasPrerequisite instanciates
teaches
• Barker’s notation
–Based on Crow’s Foot Notation
–Developed by Richard Barker for Oracle’s CASE modeling books and tools in1986
–Cardinalities are represented differently
•(0, 1) : Zero or one
•(1, 1) : Exactly one
•(0, N) : Zero or more
•(1, N) : one or more
•Cardinalities position similar to Crow’s Foot notation and opposite to classic ER
–Different notation of subtypes
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 13
3.1 ER – Even more notations…
• Black Diamond Notation –Cardinalities are represented differently
•Cardinality annotation per relationship, not per relationship end
•1:1
•1:N
•N:M
–Also, N-ary relationships possible
•ternary
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 14
3.1 ER – Even more notations…
1 1
1 N
N M
N 1
1
• Traditional ER modeling proved to be very successful in classic “DB” domains:
–Accounting –Banking –Airlines
–Business and industry applications in general –…
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 15
3.2 Extended Data Modeling
• However, in the late 70s, popularity of DBs extended into fields with more
complicated data formats
–Computer-aided design and manufacturing (CAD/CAM)
–Geographic information systems (GIS) –Medical information systems –…
• Expressiveness of ERD is not sufficient here
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 16
3.2 Extended Data Modeling
• Extended entity relationship (EER) models provide many additional features for more accurate conceptual modeling
–Refinement of relationship types
•Specialization and generalization
•Class, subclass, and inheritance
–Entity sets with existence dependencies –Extended modeling of domains and constraints
• Extended ER contains all features of “classic” ER
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 17
3.2 Extended Data Modeling
• Problem:
–Model secret lairs to base highly secret research activities
–Secret island and secret space station are special kinds of secret lairs, share many attributes, but still need some unique attributes
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 18
3.2 Subclasses / Superclasses
SecretSpace Station
id name
staff capacity style factor orbital speed mass SecretIsland
id name
staff capacity
style factor location
climate zone
• Solution: Subclasses and superclasses
• A subclass entity type inherits all attributes and constraints from its superclass entity type
–Subclasses may add additional attributes, constraints or relationship types –In EER, subclass relationship types
are annotated with an open arc, which is opened to the super class (think of set inclusion)
–Describes an “is-a” relationship
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 19
3.2 Subclasses / Superclasses
Secret Lair
id name
staff capacity
style factor
orbital speed mass
Secret Space Station
Climate zone location
Secret Island Lair
• Subclass entity types represent subsets of the entity set of the superclass’ entity type
–That is, an entity which is contained in the subclass is also contained in the superclass
–In particular, no entity can only exist in a subclass set
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 20
3.2 Subclasses / Superclasses
Secret Lair e1
e3 e4
e6 e5 e2 Secret Lair
Secret Space Station
Secret Space Station
Secret Space Station
• Possible implementation: Two distinct database entries that represent the same instance
–The same instance appears as a database entry in the superclass and subclass sets, and they are related to each other –1:1 relationship on entity level
•Linking two database entries of the same entity in a specialized role –Often, this solution is easier and more flexible to implement
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 21
3.2 Subclasses / Superclasses
Secret Lair e1
e4 e6 Secret Lair
Secret Space Station
e3 e5 e3 e2
e5 e2
• The process of defining a set of subclasses for a superclass is called specialization
–Specialized entity types supplement additional attributes and relationships –“Secret lair” can be specialized into
“secret space station” and “secret island”
• The inverse process is generalization –Generalization suppresses differences
among specialized subclasses
–“Secret space station” and “secret island”
are generalized to “secret lair”
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 22
3.2 Specialization / Generalization
Secret Lair
id name
staff capacity
style factor
orbital speed mass Secret Space Station Secret Island
location climate
zone
• Specialization and generalization usually result in the same model
–However, the process of how to reach the model is different
–Specialization: top-down conceptual refinement
•Start with super classes, find suitable subclasses –Generalization: bottom-up conceptual synthesis
•Model sub classes, find proper generalized super class
3.2 Specialization / Generalization
• Specializations can be constrained and modeled in further detail regarding two properties
–Exclusiveness (indicated by a labeled circle)
•Disjoint: Subclasses are mutually exclusive (default, label d)
•Overlapping: Each entity may be contained in more than one subclass (label o)
–Completeness
•Total: No entity is member of the superclass without being member of a subclass (denoted by double line)
•Partial: There are entities that are not contained in any subclass (default)
3.2 Constraints on Specialization
• Examples
–Disjoint and total:
“A secret lair may either be a secret island or a secret space station (but nothing else).”
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 25
3.2 Constraints on Specialization
Secret Lair
id name
staff capacity
style factor
orbital speed mass Secret Space Station Secret Island Lair
location climate
zone d
• Examples
–Overlapping and partial:
“A villain is a mad scientist, or a super villain, any combination of both, or something else (just a villain).”
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 26
3.2 Constraints on Specialization
Villain id name
Super Power Super Villain Mad Scientist
Scientific Field
o
• Specializations may be predicate-defined –A subclass is predicate-defined if there is a predicate
(condition) that implies an entity’s membership –Condition is added to the specialization line
–Predicate-defined specialization are not necessarily total
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 27
3.2 Constraints on Specialization
Person
id name
Hero Villain
d evilness
Hero Squad evilness > 10
AND ambition > 50 evilness < -10 AND ambition > 50 ambition
• Specializations may be attribute-defined –Attribute-defined is a special case of predicate-defined,
where the membership in subclasses depends on a single attribute value
–Attribute is added to line connecting circle and superclass, condition added to lines connecting circle and subclasses
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 28
3.2 Constraints on Specialization
Person
id name
Hero Villain
d evilness
Hero Squad
< 0 ≥ 0
evilness
• Consequences of specialization
–Deleting an entity from the superclass also deletes it from all subclasses
–Inserting an entity in a superclass automatically inserts it into all matching predicate-defined subclasses
–In a total specialization, inserting one entity into a superclass implies that it has to be inserted into at least one subclass, too
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 29
3.2 Constraints on Specialization
• A subclass may be further specialized
• If every subclass has just one superclass, the inheritance structure is a
specialization hierarchy
• If there are subclasses having
more than one superclass at the same time, the structure is a specialization lattice
–Shared subclasses possible with multiple inheritance
• Subclasses recursively inherit all attributes and relationships of their superclasses up to the root
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 30
3.2 Hierarchies and Lattices
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 31
3.2 Hierarchies and Lattices
Villain id name
Super Power Super Villain Mad Scientist
Scientific Field
o
Villain id name
Super Power Super Villain Mad Scientist
Scientific Field
o
Evil Mad Super Scientist
Hierarchy Lattice
• An Evil Mad Super Scientist is a Mad Scientist as well as a Super Villain
–Inherits attributes and
relationships of both superclasses
• Science and philosophy always strived to explain the world and the nature of being
–First formal school of studies:
Aristotle’s metaphysics
(“beyond the physical,” around 360 BC) –Traditional branches of metaphysics
•Ontology
–Study of being and existence
•Natural theology
–Study of God, nature and creation
•Universal science –“First Principles” and logics
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 32
3.2 Taxonomies & Ontologies
• Ontology tries to describe everything which is (exists), and its relation and categorization with respect to other things in existence
–What is existence? Which things exists? Which are entities?
–Is existence a property?
–Which entities are fundamental?
–What is a physical object?
–How do the properties of an object relate to the object itself?
What features are the essence?
–What does it means when a physical object exists?
–What constitutes the identity of an object?
–When does an object go out of existence, as opposed to merely change?
–Why does anything exist rather than nothing?
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 33
3.2 Taxonomies & Ontologies
• Parts of metaphysics evolved into natural philosophy
–Study of nature and the physical universe –In the late 18th century, it became just “science”
–Ontology is still a dominant concept in science
•Representation of all knowledge about things
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 34
3.2 Taxonomies & Ontologies
• Ars Generalis Ultima –Created in 1305 by Ramon Llull
–“Ultimate” solution for the Ars Magna (Great Art)
•Mechanical combination of terms to create knowledge
•Base hope: all facts and truths can be created in such a way –Heavy use of Arbor Scientiae (Tree of Knowledge)
•Tree structure showing an hierarchy of philosophical concepts
•Together with various “machines” (paper circles, charts, etc.) reasoning was possible
3.2 Taxonomies & Ontologies
• Taxonomies (τάξις : arrangement) are part of ontology
–Groups things with similar properties into taxa –Taxa are put into an hierarchical structure
•Hierarchy represents supertype–subtype relationships
•Represents a specialization of taxa, starting with the most general one
–Taxonomies can be modeled with ER using specialization hierarchies
•Taxa are represented by entity types
3.2 Taxonomies & Ontologies
• Example: Linnaean Taxonomy
–Classification of all living things by Carl von Linné in 1738
–Classification into multiple hierarchy layers
•Domain, Kingdom, Phylum, Subphylum, Class, Cohort, Order, Suborder, Infraorder, Superfamily, Family, Genus, Species –Each layer adds additional
properties and restrictions
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 37
3.2 Taxonomies
• Domain: Eukaryotes
–Organisms having cell membranes
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 38
3.2 Taxonomies
Domain
Kingdom Sub-Domains
Animals Here
• Example: Red Squirrel
(Binomial Name: Tamiasciurus hudsonicus) –Kingdom: Animals
–Phylum: Chordata (with backbone)
–Class: Mammalia (with backbone, nursing its young)
–Order: Rodentia (backbone, nursing its young, sharp front teeth) –Suborder: Scriuomorpha (backbone, nursing its young, sharp front
teeth, like squirrel)
–Family: Scriudae (backbone, nursing its young, sharp front teeth, like squirrel, bushy tail & lives on trees (i.e. real squirrel))
–Genus: Tamiasciurus (backbone, nursing its young, sharp front teeth, like squirrel, bushy tail & trees, from N-America)
–Species: Hudsonicus (backbone, nursing its young, sharp front teeth, like squirrel, bushy tail & trees, from N-America, brown fur with white belly)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 39
3.2 Taxonomies
• Example: Edible Dormouse (Binomial Name: Glis Glis)
–Kingdom: Animals
–Phylum: Chordata (with backbone)
–Class: Mammalia (with backbone, nursing its young)
–Order: Rodentia (backbone, nursing its young, sharp front teeth) –Suborder: Scriuomorpha (backbone, nursing its young, sharp front
teeth, like squirrel)
–Family: Gliradae (backbone, nursing its young, sharp front teeth, like squirrel, sleeps long)
–Genus: Glis (backbone, nursing its young, sharp front teeth, bushy tail, like squirrel, eaten by Romans)
–Species: Glis (backbone, nursing its young, sharp front teeth, bushy tail, climbs trees, nothing more to classify)
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 40
3.2 Taxonomies
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 41
3.2 Taxonomies
Rodentia (Rodents) d Sciuromorpha (Squirrel-like) Myomorpha
(Mouse-like) Castorimorpha
(Beaver-like) Hystricomorpha
(Hedgehog-like) Anomaluromorpha (Springhare-like) d
Sciuridae
(Squirrel) Aplodontiidae (Mountain Beaver) Gliridae
(Dormouse)
Glirinae (Real Dormouse) Graphiurinae
(African Dormouse) Leithiinae
(Other Dormice)
Glirulus
(Japanese DM) Glis
(Edible Dormouse)
Glis (Yummy) Ratufinae
(Giant Squirrel) Sciurillinae (Pygmy Squirrel) …
Hudsonicus
(Red Squirrel) Douglasi
(Douglas Squirrel) Tamiasciurus
(Pine Squirrel) Pteromyini (Flying Squirrel)
Sciurus
(Common Squirrel) Microsciurus (Micro Squirrel) … d
Scruinae (Real Squirrel)
Sciurini (Tree Squirrel)
d
d
d d d
• Recently, creating ontological models became fashionable in CS
–So called ontologies
–Widely used in medical informatics, bio-informatics, Semantic Web, etc.
• In addition to “normal” data models, ontologies offer reasoning capabilities
–Allow to classify instances automatically –Allow to extract additional facts from the model
• In CS, ontologies are usually modeled using special languages
–OWL, DAML+OIL, IDEF, …
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 42
3.2 Ontologies in CS
• Inheritance may lead to two special problems –Polymorphism
–Multiple inheritance
• Polymorphism
–Usually, subclasses inherit all attributes and relationships of their supertypes
–Subtypes may define additional attributes/relationships –What happens if an attribute in the subtype means
something different?
–What happens if an attribute is not needed at all?
–What if some attribute should have a different name?
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 43
3.2 Polymorphism
• Example:
–Sovereign territory just doesn’t make sense for a space station
•Should be removed
–Geo coordinates are also useless
•But: Orbital trajectory somehow represents the same concept (location)
–Unfortunately, relational databases and ER don’t provide any useful support for polymorphism
•Avoid models where you need it!
•If it is really necessary, constraints and null-values may be used to help out…
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 44
3.2 Polymorphism
Secret Lair
id name
sovereign territory geo
Coordinates
mass Secret Space
Station
orbital trajectory
• Multiple inheritance
–A subclass may have multiple superclasses
•Inheritance lattice instead of inheritance hierarchy –But: What happens if superclasses define the
same attribute/relationship differently
•Which one should be inherited?
•Are both needed?
•ER provides no support for conflicting multi-inheritance
–Avoid models with such conflicts
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 45
3.2 Multiple Inheritance
Super Power Super Villain Mad Scientist
Scientific Field
Evil Mad Super Scientist
• In a superclass–subclass relationship, the subclass inherits all attributes and relationships of the superclass(es)
• However, sometimes it is beneficial that a subclass inherits from only one superclass (chosen from a set of potential
distinct superclasses)
–Every space station has an owner
–A space station owner is either a space agency or a super villain
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 46
3.2 Union Types
• Solution: Union types –Denoted by a “u” in a circle
–Space agency and Super villain are neither related, nor of the same type
–An owner is either a space agency or a super villain
3.2 Union Types
Space Agency Super Villain
Owner U
owns
Space Station
• Alternative ER Notations
• Extended ER –Inheritance
–Complex Relationships
• UML
3 Extended Data Modeling
• UML (Unified Modeling Language) is a set of multiple modeling languages and diagram types
–First standardized in 1997
–Unification of dominating object-oriented software design methods
•James Rumbaugh: OMT
•Grady Booch: Boochs Method
•Ivar Jacobsen: OOSE
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 49
3.3 UML
• UML provides support for various software modeling problems –Static structural diagrams
• Class diagram
• Component diagram
• Deployment diagram
• Composite structure diagram
• Object diagram
• Package diagram –Dynamic behavior diagrams
• Activity diagram
• State diagram
• Use-case diagram –Interaction diagrams
• Communication diagram
• Sequence diagram
• Timing diagram
• Interaction overview diagram
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 50
3.3 UML
• For data modeling, only class diagrams are used –Closely related to ER diagrams in crow’s foot notation
•Additional notations for logical design and operations
• Entity type becomes class
–Attributes written as in crow’s foot notation
•Usually, also domains are modeled –Operations are usually not needed in
pure data models
–Entity type instances are called objects
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 51
3.3 UML
CLASS NAME attribute 1 : domain attribute n : domain operation 1 operation m
…
…
• In UML, relationship types are called associations
• Simplest case: just a plain line
–Although using just a line is valid, a good model should provide additional information
•Name
•Direction
•Multiplicity
•Order
•Navigability
•Special Aggregation Types
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 52
3.3 UML
Super Hero Sidekick
• Better:
“A super hero may mentor multiple sidekicks”
–Careful: Multiplicity in opposite direction to Chen ER
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 53
3.3 UML
Super Hero Sidekick
mentors mentee mentor
1 *
Roles
Multiplicity (Cardinality) Association name Read direction
• Association navigability
–Denoted by an arrowhead and small cross
–Models how you can navigate among objects involved in the association
–One-way association –Example:
•For each hero, you can navigate to the substances which may kill him
•You cannot natively navigate from a substance to a hero
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 54
3.3 UML
Super Hero can kill Substance
• UML does not allow to add attributes to associations directly
• Workaround: Association classes –Association classes belong to an association
(indicated by dashed line) –They share the association name
–Each instance of the association creates an according class object
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 55
3.3 UML
Hero membership Super Team
Membership since: Date
* *
• Association classes cannot directly be replaced by a “normal” class
–Introduces additional semantics
–The replacement model allows that a hero is assigned twice to the same super team!
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 56
3.3 UML
Hero membership Super Team
Membership since: Date
* *
Hero
Super Team Membership
since: Date
* *
1 1
• Qualified associations
–Associations may be qualified by an additional attribute
•Each association instance between objects is classified by this attribute
•Semantically stronger as a classifier than an attribute of an association class
–Within a programming language, the qualification attribute would end up as a key of a (hash) map
–Example: “Von Doom Industries employs Victor von Doom as CEO”
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 57
3.3 UML
Person employs position Evil Organization
• XOR restrictions on associations
–A class having multiple associations can be modeled in such a way that only one of these associations can be active at a time
–Example: “A villain lives either in a secret lair, or in a prison (but not in both).”
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 58
3.3 UML
is safely in custody in
Secret Lair Villain Prison
(xor) happily lives in
• For n-ary associations (n > 2), the diamond returns
3.3 UML
Super Hero Villain
Location fights against
Fights Against date : Date
• Aggregation
–The aggregation is a special association within UML –Colloquial: “is part of” or “consist of”
–Denoted by a small, empty diamond
–Aggregation just states that one class is part of another;
it poses no further restrictions
•Objects may still exist independently of each other
•Objects may be part of several other objects
–Example: “A plan to take over the world consists of several things that need to be done.”
3.3 UML
Plan to take over the world Stuff To Do
* *
whole part
• Composition (also called strong aggregation) –Stricter version of aggregation
•Diagrammed by solid diamond –Based on multiplicity of the part-side
•1: An object is always part of just one other object.
If the “main” object is deleted, the part needs to be assigned to another “master” or is deleted.
•0..1: An object may be part of at most one other object.
May also exist alone.
•* : Not allowed. Part of one object max.
–Example: “A doomsday machine is made of multiple parts”
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 61
3.3 UML
Doomsday Machine Machine Part
1 0..1
• Generalization
–Induces a class-subclass relationship (“is-a”)
•Diagrammed with an hollow arrow –By default, generalization is disjoint
•Overlapping is additionally annotated in curly brackets –By default, generalization is
partial (incomplete in UML)
•Total (complete) is also annotated in curly brackets
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 62
3.3 UML
Villain
id : int name : string
Mad Scientist
scientific field : varchar
Super Villain
Super power: varchar
Evil Mad Super Scientist {overlapping}
• Classification attributes
–Similar to EER’s attribute-defined relationship types –Denoted by “:attributeName”
–All objects of a given subtype have the same value for the classifier attribute
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 63
3.3 UML
Super-Powered
ethical attitude : varchar
Super Hero Super Villain :ethical attitude
• Class (entity type)
• Associations (relationship types)
• Association class
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 64
3.3 UML: Summary
CLASS NAME attribute 1 : domain attribute n : domain operation 1 operation m
…
…
Class1 Class2
name role2 role1
m1 m2
Roles Multiplicity(Cardinality) Association name
Read direction
Class2 Class1
ACLass
• XOR restriction
• Qualifying attribute
• Aggregation
• Composition
• Navigable association
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 65
3.3 UML: Summary
Class 2 q-attr Class 1
Class 1 Class 2 Class 3
(xor)
Class 1 Class 2
Main Part
Main Part
• Generalization / Specialization
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 66
3.3 UML: Summary
General
Special 1 Special 2 {overlapping, total} :attribute1
attribute1: Domain1 Classification Attribute Generalization Modifier
• Design Patterns: General reusable solution to a commonly occurring problem
–Originated from architecture by Christopher Alexander in 1979
–Concept adopted by Beck and Cunningham for Software Engineering designs
–First great book of software design pattern: Elements of Reusable OO Software by Gamma et al.
–No “real” data model patterns exist
•We informally introduce some
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 67
3.3 Data Model Design Patterns
• Design Patterns
–Speed up development process by reusing proven and tested paradigms –Patterns are quite general and must be
instantiated to the actual problem
• Reading Patterns (excerpt) –Name: Name of the pattern
–Intend: What is the reasoning behind the pattern?
–Motivation: A scenario using the pattern
–Applicability: The situation/context the pattern can be used –Structure: Micro-model of the pattern (UML, ER)
–Participants: List and explanation of all classes/entity types used and their role in the design
–Collaboration: Description of the interaction among the classes –Consequences: List of side-effects and trade-offs of usage –…
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 68
3.3 Data Model Design Patterns
• Pattern: Composite List
• Scenario:
–Consider a strong room having multiple shelf slots.
Each shelf slot is part of the room, it cannot exist alone nor can it separated. The room properties also affect the slot properties.
• Applicability:
–There is a composition. The whole has multiple parts of the same type which are assigned to the whole and cannot exits alone. There is at least one part.
Properties of the whole also affect the parts.
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 69
3.3 Data Model Design Patterns
List 1 1..* List Item
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 70
3.3 Data Model Design Patterns
StrongRoom +name +location +securityLevel
StorageSlot +rackNo +slotNo +currentValue
1 1..*
• Pattern: Exemplar–Type
• Scenario:
–Consider a library. You have book titles, and of each title the library owns several actual book exemplars which you can borrow independently.
• Applicability:
–There is a simple association. Object associations stay fixed. Class names usually contain terms like type, group, description, etc. A description can, for a short time, exist alone. Without the exemplar class, you would just have a higher degree of redundancy.
3.3 Data Model Design Patterns
Type Description 1 * Exemplar
3.3 Data Model Design Patterns
BookDescription +title +author +publisher +isbn
BookExemplar +inventoryNo +rackNo +borrowedTo +dateOfPurchase
* 1
• Pattern: Component assembly
• Scenario:
–Assume a car has to be modeled.
It has an engine and four wheels. Those parts are usually attached to the object.
Moving/selling the car usually also means moving/selling the engine and the wheels.
However, the individual parts can be replaced.
• Applicability:
–There are physical objects which are composed into a larger object. The parts usually stay together.
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 73
3.3 Data Model Design Patterns
Main
1 n Part 2 1 m Part 1
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 74
3.3 Data Model Design Patterns
Car
Wheel
Engine Door
1
1 1
4 1
2..4
• Pattern: Coordinator
• Scenario:
–Assume an exam has to be modeled. A student is examined by an professor about a certain lecture.
This takes place at an specific time at a specific location.
• Applicability:
–There are simple associations
–The coordinator replaces an n-ary (n ≥ 2) association with an association class (association is “materialized”) –The coordinator has only few attributes, but multiple
associations to exactly one object of other classes.
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 75
3.3 Data Model Design Patterns
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 76
3.3 Data Model Design Patterns
Student Professor
Lecture
Examination +dateTime +room 1 *
* 1
* 1
Class 1
Class 2 Association Class
Class 3 Class 1
Class 2
Coordinator Class 3
1 1
1
1 1
1
* *
* attrib1
attrib1
• Pattern: Roles
• Scenario:
–Consider a tutorial. Persons may participate as listeners. As well, there is one (or multiple) speakers (who are also persons and may also be a listeners).
• Applicability:
–There are multiple associations between two classes.
–An object may have multiple roles in regard to the other at the same time.
–Objects have the same attributes independent of their role.
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 77
3.3 Data Model Design Patterns
Class 1 role1 role2
Class 2
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 78
3.3 Data Model Design Patterns
Person +name Tutorial
+location +dateTime
+speaker +participant
• Pattern: Changing roles
• Scenario:
–Assume you model a CV. A person has changing occupations in different roles (types).
Depending on the role, different attributes are needed.
• Applicability:
–An object can have different roles at different times.
Different roles imply different attributes.
–Roles are modeled by inheritance.
–Associations between object and roles are only extended, i.e. not deleted nor assigned to another object.
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 79
3.3 Data Model Design Patterns
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 80
3.3 Data Model Design Patterns
Person +name
Occupation +timespan
Employed +company +position
Self-Employed +fieldOfWork
Student +university +courseOfStudy
* 1
• Pattern: Group
• Scenario:
–Employees are part of an department.
• Applicability:
–There is a single association
–Several objects belong, at the same time, to one group object.
–Associations between member and group can be created and be removed.
–Group can (for a short time) exist without members.
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 81
3.3 Data Model Design Patterns
Relational Database Systems 1 – Wolf-Tilo Balke – Institut für Informationssysteme – TU Braunschweig 82
3.3 Data Model Design Patterns
Employee +name +address +specialization
Department +name +abbreviation +address 0..1
*
• Pattern: Group history
• Scenario:
–Employees are part of an department. They can switch departments and an history has to be kept.
• Applicability:
–An object belongs to different groups over time.
–The history is modeled using an associative class.
–Object associations are not changed when membership changes (a new one is added).
–Current group can be found by examining the association class objects.
3.3 Data Model Design Patterns 3.3 Data Model Design Patterns
Employee +name +address +specialization
Department +name +abbreviation +address
*
* Membership +timespan