| Reltionships |
One table (relation) may be linked with another in what is known as a
relationship. Relationships may be built into the database structure to
facilitate the operation of relational joins at runtime.
- A relationship is between two tables in what is known as a
one-to-many or parent-child or master-detail relationship
where an occurrence on the 'one' or 'parent' or 'master' table may have any
number of associated occurrences on the 'many' or 'child' or 'detail' table.
To achieve this the child table must contain fields which link back
the primary key on the parent table. These fields on the
child table are known as a foreign key, and the parent
table is referred to as the foreign table (from the viewpoint of the
child).
- It is possible for a record on the parent table to exist without
corresponding records on the child table, but it should not be
possible for an entry on the child table to exist without a
corresponding entry on the parent table.
- A child record without a corresponding parent record is
known as an orphan.
- It is possible for a table to be related to itself. For this to be
possible it needs a foreign key which points back to the primary
key. Note that these two keys cannot be comprised of exactly the same
fields otherwise the record could only ever point to itself.
- A table may be the subject of any number of relationships, and it
may be the parent in some and the child in others.
- Some database engines allow a parent table to be linked via a
candidate key, but if this were changed it could result in the link to
the child table being broken.
- Some database engines allow relationships to be managed by rules known
as referential integrity or foreign key restraints. These will
prevent entries on child tables from being created if the foreign
key does not exist on the parent table, or will deal with entries
on child tables when the entry on the parent table is updated
or deleted.
The join operator is used to combine data from two or more relations (tables)
in order to satisfy a particular query. Two relations may be joined when they
share at least one common attribute. The join is implemented by considering each
row in an instance of each relation. A row in relation R1 is joined to a row in
relation R2 when the value of the common attribute(s) is equal in the two
relations. The join of two relations is often called a binary join.
The join of two relations creates a new relation. The notation 'R1 x R2'
indicates the join of relations R1 and R2. For example, consider the following:
| Relation R1 |
| A |
B |
C |
| 1 |
5 |
3 |
| 2 |
4 |
5 |
| 8 |
3 |
5 |
| 9 |
3 |
3 |
| 1 |
6 |
5 |
| 5 |
4 |
3 |
| 2 |
7 |
5 |
| Relation R2 |
| B |
D |
E |
| 4 |
7 |
4 |
| 6 |
2 |
3 |
| 5 |
7 |
8 |
| 7 |
2 |
3 |
| 3 |
2 |
2 |
Note that the instances of relation R1 and R2 contain the same data values
for attribute B. Data normalisation is concerned with decomposing a relation
(e.g. R(A,B,C,D,E) into smaller relations (e.g. R1 and R2). The data values for
attribute B in this context will be identical in R1 and R2. The instances of R1
and R2 are projections of the instances of R(A,B,C,D,E) onto the attributes (A,B,C)
and (B,D,E) respectively. A projection will not eliminate data values -
duplicate rows are removed, but this will not remove a data value from any
attribute.
The join of relations R1 and R2 is possible because B is a common attribute.
The result of the join is:
| Relation R1 x R2 |
| A |
B |
C |
D |
E |
| 1 |
5 |
3 |
7 |
8 |
| 2 |
4 |
5 |
7 |
4 |
| 8 |
3 |
5 |
2 |
2 |
| 9 |
3 |
3 |
2 |
2 |
| 1 |
6 |
5 |
2 |
3 |
| 5 |
4 |
3 |
7 |
4 |
| 2 |
7 |
5 |
2 |
3 |
The row (2 4 5 7 4) was formed by joining the row (2 4 5) from relation R1 to
the row (4 7 4) from relation R2. The two rows were joined since each contained
the same value for the common attribute B. The row (2 4 5) was not joined to the
row (6 2 3) since the values of the common attribute (4 and 6) are not the same.
|
