|
- Recall what exactly is
a relationship:
Conclussion:
- A relationship
(such as
Works-For in the
figure) is a
set of tuples !!!
- Each tuple in the
relationship contains
the key attributes of
the entities
(Because the key attribute can identify an entity uniquely !!!)
Example: the works_for relationship in the above figure are these tuples:
(123-45-6789, 4) (= john works in payroll) (111-11-1111, 1) (= kate works in administration) (222-22-2222, 1) (= peter works in administration)
- Each tuple in the
relationship contains
the key attributes of
the entities
- Therefore:
- We can always represent a relationship using a new relation (= table)
Important design rule:
- We must not
create
too many
relations
(Because the data model will get too complicated and inefficient)
- We must try to minimize the number of relations (= tables) in the Relational Model
- A relationship
(such as
Works-For in the
figure) is a
set of tuples !!!
- There are
in general
two (2) ways to
represent
a relationship
using relations (tables):
- We can create
a
new relation (table)
- The tuples of the relationship are stored in this new table
This technique can always be used, but it is a technique that is only used when method 2 fails to work !!!
You must try method 2 first !!!
- We can
expand
a
existing relation (table)
to store the
tuples of the
relationship
- The tuples of the
relationship are stored
inside
the table of
an (existing) entity !!!
- We will make use of the key of that entity to store the tuples of the relationship !!!
- The tuples of the
relationship are stored
inside
the table of
an (existing) entity !!!
The first method is always possible
- However: this will increase the number of relations (= number of files)
The second method is preferred:
- Because it will not
increase the number of relations
(= files)
- It will only make an existing relation a little bit larger
but.... as we will see soon: method 2 does not work all the time
- We can create
a
new relation (table)
- Method 2
does "work" if we can:
- Represent
a relationship by:
- Extending an existing relation with an atomic valued attribue
- Represent
a relationship by:
- Note:
- Method 2
will always work in the case of
a weak relationship
- In fact:
- A weak relationship
must be
represented in a
Relational Model by:
- Extending the relation that represents the weak entity by expanding it to store the weak relationship
- A weak relationship
must be
represented in a
Relational Model by:
- Method 2
will always work in the case of
a weak relationship
- We will now go through the relationship one-by-one and see how they can be represented by relations