Download Relational Model: Understanding Relations, Keys, and Database Design and more Schemes and Mind Maps Innovation in PDF only on Docsity!
2.2 Relational Model (RM)
- Relational Model by Codd (1970): mathematical foundation: set theory,
- only a single structural concept Relation,
- entity/object types and relationship types are uniformly modeled by relation schemata.
- properties of entities/objects and relationships are represented by attributes (in the
relation schemata).
- a relation schema consists of a name and a set of attributes,
Continent: name, area
- each attribute is associated with a domain that specifies the allowed values of the
attribute. Often, attributes also can have a null value.
Continent: name: VARCHAR(25), area: NUMBER
- “First Normal Form”: only domains of atomic datatypes, no records, lists, sets etc.
- A (relational) database schema R is given by a (finite) set of (relation) schemata.
Continent:... ; Country:... ; City:... ; encompasses:... ; isMember:...
- for every relation, a set of (primary) key attributes is distinguished
45
2.2.1 Relations
- A (database) state associates each relation schema to a relation.
- elements of a relation are called tuples.
Every tuple represents an entity or a relationship. (name: Asia, area: 4.5E7)
- relations are unordered. Columns are also unordered.
Example:
Continent
name area
VARCHAR(20) NUMBER
Europe 10523000
Africa 30221500
Asia 44614500
North America 24709000
South America 17840000
Australia 9000000
Relations: Example
Continent
name area
Europe 10523000
Africa 30221500
Asia 44614500
North America 24709000
South America 17840000
Australia 9000000
Country
name code population area ...
Germany D 83536115 356910
Sweden S 8900954 449964
Russia R 143666931 17075200
Poland PL 38642565 312683
Bolivia BOL 1098580 7165257
.. .. .. ..
encompasses
country continent percent
VARCHAR(4) VARCHAR(20) NUMBER
R Europe 20
R Asia 80
D Europe 100
......... - with referential integrity constraints
(to be explained later)
47
Graphical representation of the relational schema of the MONDIAL database (excerpt):
Organization
name
✄ abbrev
city ✄
country ✄
province ✄
established
isMember
✁ organization
✁ country
type
Country
name
✄ code ✄
capital ✄
province ✄
area
population
borders
✁ country
✁ country
length
located
✁ city
✁ country
✁ province
river ✄
lake ✄
sea ✄
City
✄ name
✄ country ✄
✄ province ✄
population
latitude
longitude
elevation
Province
name ✁
✁ country ✁
area
population
✁ capital
✁ capprov
geo_lake
lake ✄
✁ country
✁ province
Lake
✄ name
area
elevation
geo_river
river ✄
✁ country
✁ province
River
✄ name
length
✁ river
✁ lake
✁ sea
geo_sea
sea ✄
✁ country
✁ province
Sea
✄ name
depth
area
2.3 Logical Schema: Mapping ERM to RM
Starting with the ER schema, the relational schema is designed.
[Overview slide]
Let E ER
an entity type and R ER
a relationship type in the ERM.
, {A
1
,... , A
n
}) → E(A
1
,... , A
n
- For weak entity types, the key attributes of the identifying entity type must be added.
- Relationship types:
(RER, {RO 1 : E 1 ,... , ROk : Ek}, {A 1 ,... , Am}) →
B(E
1
_K
11
,... , E
1
_K
1 p 1
,... , E
k
_K
k 1
,... , E
k
_K
kpk
, A
1
,... , A
m
where {K i 1
,... , K
ipi
} are the primary keys of E i
, 1 ≤ i ≤ k.
- Renaming of foreign key attributes is allowed
(e.g. coinciding attribute names in different referenced keys)
In case that k = 2 and a (1,1) relationship cardinality, the relation schema of the
relationship type and that of the entity type may be merged.
- Aggregate types can be ignored if the underlying relationship type is mapped.
51
ENTITY TYPES
(E
ER
, {A
1
,... , A
n
}) → E(A
i 1
,... , A
ik
where {Ai 1
,... , Ai k
} ⊆ {A 1 ,... , An} are the scalar (i.e., not multivalued) attributes of EER –
multivalued attributes are mapped separately.
continent
name area
Asia 4.5E
Continent
name area
VARCHAR(20) NUMBER
Europe 10523000
Africa 30221500
Asia 44614500
North America 24709000
South America 17840000
Australia 9000000
The candidate keys of the relation are the candidate keys of the entity type.
MULTIVALUED ATTRIBUTES
... one thing left:
Attributes of relations must only be single values.
(E
ER
, {A
1
,... , A
i
,... , A
n
}) where A i
is a multivalued attribute
→ E_A
i
(K
1
,... , K
p
, A
i
where {K 1
,... , K
p
} are the primary keys of E.
(renaming is allowed, especially if there is only one key attribute)
{K
1
,... , K
p
, A
i
} are the primary keys of the relation E_A i
code Country language
Languages
country language
D German
CH German
CH French
53
WEAK ENTITY TYPES
For weak entity types, the key attributes of the identifying entity type(s) must be added.
Country
in
name
area pop.
code
356910 83536115
Germany (^) D
Province
in Prov.
name
area (^) pop.
35751 10272069
Baden-W.
City
name pop.
Freiburg 198496
< 1 , 1 >
< 1 , 1 >
City
name country province population ...
Freiburg D Baden-W. 198496 ..
Berlin D Berlin 3472009 ..
Freiburg CH FR NULL ..
Cordoba E Andalucia 328326 ..
Cordoba RA Cordoba 1207774 ..
Recursive Symmetric Relationship Types
- recursive non-symmetric relationship types (river-flowsInto):
use role names as column names.
- recursive symmetric relationship types (borders): invent column names - Symmetric storage would introduce redundancy and risk for inconsistencies.
Store only one direction and create a symmetric SQL view from it (belongs to the
“external level” of the 3-level-architecture)
code (^) Country name
borders
< 0 , ∗ > < 0 , ∗ >
borders
country1 country2 length
D F 451
F D 450
D CH 334
CH F 573
SQL view (see later)
CREATE VIEW symmborders AS
(SELECT * FROM borders) UNION (SELECT country2, country1, length FROM borders)
57
EXERCISE
Exercise 2.
Give a relational schema for the following ER schema:
Supplier
Product Part
delivers
< 0 , ∗ >
< 0 , ∗ > < 0 , ∗ >
component
< 0 , ∗ > belongs to
< 0 , ∗ > contains
nr
name
addr
nr
name
color weight
nr
name
amount date
2.4 Relational Databases – Formalization
SYNTAX
(note the similarities with first-order logic)
- A (relational) signature is a set of relation schemata R i
X
i
X) consists of a name (here, R) and a finite set
X = {A
1
,... , A
m
m ≥ 1 of attributes.
X is the format of the schema.
- a (relational) database schema R consists of a relational signature (i.e., a set of
(relation) schemata), optionally with integrity constraints.
- alternative notations for relation schemata: - abbreviation: R(A 1 ,... , An) instead of R({A 1 ,... , An}). - if the order of the attributes {A 1
,... , A
m
} is relevant (i.e., for representation as a
table),
X is denoted as a vector [A 1
,... , A
m
].
59
RELATIONAL DATABASES – FORMALIZATION: DOMAINS
Consider a relation schema R(
X)
X is associated to a (non-empty) domain of atomic values, called
dom(A).
X) := dom(A 1
) ×... × dom(A m
- Example: Continent(name, area)
dom(continent.name) = VARCHAR, dom(continent.area) = NUMBER
dom(Continent) = VARCHAR × NUMBER
Note the following:
- the assignment of domains to attributes belongs to the database schema.
- in first-order logic, the definition of the domain of a structure belongs to the semantics.
KEYS
While in the ER model, the keys serve only for an intuitive modeling, in relational database
design they play an important role for the database performance and for the ability of the
database to incorporate and maintain key constraints.
The notion of keys is defined as for the ER model:
For a set
K ⊆
X of attributes of a relation schema R, a relation r ∈ Rel(
X) satisfies the key
constraint
K if for all tuples μ 1
, μ 2
∈ r:
If μ 1
K) = μ 2
K) (i.e., μ 1
and μ 2
coincide on the values of
K), then μ 1
= μ 2
More Concrete Requirements on Keys
(to be formalized on the next slides)
- keys should be minimal: no subset
K
′
(
K satisfies the key property,
- no “embedded relations” (i.e., partial functional dependencies): for no subset
X
′ (
X of
the attributes of R, any subset
K
′ (
K satisfies the key property wrt.
X
′ .
[3rd Normal Form, cf. Slide 374; Example see Slide 67]
63
KEYS: ADDITIONAL FORMAL REQUIREMENTS
The relational model provides a more concise formalization of keys (cf. Slide 326 ff. on
Normalization Theory for details).
These are based on the definition of functional dependencies :
Given a relation R(
X),
V ,
W ⊆
X.
r satisfies the functional dependency (FD)
V →
W if for all tuples μ 1 , μ 2 ∈ r,
μ 1
V ) = μ 2
V ) ⇒ μ 1
W ) = μ 2
W ).
W functionally depends on
V ”)
Example 2.
Consider the relation schema Country(name, code, area, population, capital, capprov).
The following functional dependencies hold wrt. the intended application domain:
{code} → {name}, {name} → {code}
{code} → {area, population, capital, capprov}
{code} → {name, code, area, population, capital, capprov}
{name} → {name, code, area, population, capital, capprov} ✷
Keys (Cont’d)
- In general, there are more than one key (called candidate keys ) for a relation schema R.
- One of these candidate keys is distinguished (by the designer) to be the primary key.
In the schema, it is represented by underlining these attributes.
65
KEYS: ADDITIONAL FORMAL REQUIREMENTS
- Formalization of the Key Constraint :
K ⊆
X is a possible key of R(
X) if
K →
X.
Additionally:
- keys must be minimal, i.e., no subset
K
′ (
K satisfies the key property:
there is no subset
K
′ (
K s.t.
K
′ →
X.
(otherwise: take
K
′ as key)
- every single attribute should be fully dependent on the complete key: for every
A ∈ (
X \
K): there is no subset
K
′ (
K s.t.
K
′ → A.
(otherwise: if there is some attribute that depends only on a part of the key, split this
relationship into a separate table, cf. example on Slide 67 and section on Normalization
Theory, Slide 326.)
Although looking formally, the second criterion is also easy to understand and prevents
bad/dangerous database design.
INCLUSION CONSTRAINTS AND REFERENTIAL INTEGRITY
Consider relation schemata R 1
X
1
) and R 2
X
2
). Let
Y
1
X
1
and
Y
2
X
2
two attribute
vectors of the same length.
r 1
= S(R
1
) and r 2
= S(R
2
) satisfy an inclusion constraint R 1
Y
1
⊆ R
2
Y
2
if and only if for
all μ 1 ∈ r 1 there is a μ 2 ∈ r 2 s.t. μ 1 (
Y 1 ) = μ 2 (
Y 2 ).
Referential Integrity
Y
2
is the key of R 2
, there is a referential integrity constraint from R 1
Y
1
to R 2
Y
2
Y
1
is called a foreign key in R 1
that references R 2
Y
2
- encompasses.continent ⊆ Continent.name
- encompasses.country ⊆ Country.code
Referential integrity constraints result from incorporating the keys of the participating entities
into the table that represents the relationship.
69
NULL VALUES – UNKNOWN VALUES
- up to now, tuples are total functions.
- if for some attribute, there is no value, a null value can be used
Semantics:
- “value exists, but is unknown”
(e.g., geo-coordinates of some cities)
- “value does not yet exist, but will exist in the future”
(e.g., inflation of a newly founded country)
- “attribute not applicable” (e.g. “last eruption date” for mountains other than volcanoes)
X is a mapping s.t.
for all A ∈
X, μ(A) ∈ dom(A) ∪ {null}.
A relation is called partial if it contains partial tuples.
2.4.1 Exercise
Exercise 2.
Consider the relation schema R(
X), where
X = {A, B} and dom(A) = dom(B) = { 1 , 2 }.
X) and Rel(
X).
- A is a key of R. Which relations r ∈ Rel(
X) violate the key constraint? ✷
