Database 3: 13 9 2005 08:30 10:30

1
Database 3 13 / 9 / 2005 0830 1030

• Andra Waagmeester

2
From miniworld to actual tables
3
Miniworld

• We need a more down to earth term...

4
ER-model Entity

• Objects, items, concepts and nouns

5
ER-models Attribute

• Characteristic, adjective or descriptive

6
ER-models Entity with attributes
DOB
Human
Hair colour
Length
7
ER-models Relation

• Usually verbs

8
ER-models Entity with relation
has
HUMAN
CAR
9
ER-models Entity with relation and attributes
dob
length
year
has
HUMAN
CAR
model
sex
10
ER-models Entity with relation and attributes
dob
length
year
has
HUMAN
CAR
since
model
sex
11
ER-models Cardinality
0
n
has
CAR
HUMAN
12
ER-models Strong versus weak entities
Atr1
Atr1
Atr1
key
Atr2
Atr2
13
ER-models Identifying relation
PERSON
1
1
Is married to
14
ER-Diagrams Composite attribute
NAP
NAME
ADDRESS
PLACE
FIRST NAME
FAMILY NAME
15
ER-diagrams Multivaried attribute
TELEPHONE
16
ER-diagrams Derived attribute
AGE
17
ER-models Total participation
has
CAR
HUMAN
18
ER-models structural constraint
(1,5) n
0
has
CAR
HUMAN
19
EER-models

• To cover additional, more complex miniworlds, we
  use Extended Entity-Relaition models
• Generalization and specialization

20
EER- Models Subclasses
Person
Client
Employee
21
EER-models Disjoint subclass of specialization
Person
d
Man
Woman
22
EER-models Partial disjoint
Person
d
Waiter
Cook
23
EER-models Total overlapping
Person
o
Man
Woman
24
EER-models Partial overlapping
Person
o
Waiter
Cook
25
Pause
26
From ER to Tables
ER-model
Tables
27
First step definition of the miniworld

• Mapping of regular entity types
• Mapping of weak entity types
• Mapping of binary 11 relations
• Mapping of binary 1 n relations
• Mapping of binary MN relations
• Mapping of multivalued relations
• Mapping of N-arry relationship types
• Think of a rule to map generalization

28
Step 1 Mapping of Regular Entities

• For each strong entity create a (relation) with
  all attributes.
• Composite attributes should be considered as
  separate attributes
• Choose any of the key attribute as the primary
  key
• The other keys should be secondary keys

29
Step 2 Mapping of weak entity types

• Create a relation for each weak entity with all
  attributes
• Include the key for the relating strong entity as
  the key attribute
• If the owner is weak, this should be mapped first

30
Step 3 Mapping of binary 11 relationship

• Create a relation for each relation
• Foreign key approach. Choose on of the entities
  of the relation and add all the attributes of the
  other
• Merged relation. Both entities are merged into
  one relation
• Cross reference a relation of both keys

31
Step 4 Mapping of binary 1N relations

• Include the key of the entity at the 1-side of
  the relations in the entity at the n-side

32
Step 5 Mapping of binary MN relations

• Create a new relation with bot primary keys

33
Step 6 Mapping of multi valued attributes

• For each multivalued attribute create a new
  relation

34
Step 7 Mapping of n-ary relations

• All keys of the relating entities ends up in one
  relation

35
Step 8 Mapping subclasses

• ?