DATABASE RELATIONSHIPS
The magic of relational database theory lies in the ability to link tables together in different ways.: Each relationship consists of a foreign key in one table and a primary key in another. The primary key attribute (column) has a unique value for each entity instance (row) found in the table, the foreign key attribute of each entity instance contains the value of the related row primary key. In a nutshell, the value contained in the foreign key is a pointer to the matching row in the other table.
There are four characteristics of a relationship:
- It is the action portion of the conceptual model
- It's direction indicates the source and destination
- It's cardinality defines the possible number of instances of a specific entity that could be associated with another entity
- Existence determines the precedence between entities
Action and the conceptual model:
If we think of a basic business rule in terms of a conventional
subject-verb-object English sentence, the subject and object are entity tables
and the verb is the associating relationship. The sentence subject
corresponds to the foreign key, the sentence object corresponds to the primary
key. Thus, in the sentence "the part is supplied by a vendor" (or
alternatively stated "the vendor supplies the part"), the parts
table has the foreign key, the vendors table has the primary key and the action
between them "is supplied by." or "supplies."
Directionality:
Every relationship has but one direction, that is, from parent entity (source)
to child entity (destination). In the previous example, the "vendor"
is the parent and the "part" is the child since the vendor
conceptually produces the parts as the parts would not exist had the vendor not
done so. The parent entity will always have the primary key, the child entity
will always have the foreign key.
Cardinality:
There are four cardinalities--one to one, one to many, many to many, and
recursive. In any relationship, it could be possible for zero, one, or many
instances to exist depending on the nullability of the primary and foreign keys.
This is where the role of the NULL or NOT NULL setting comes into play in key
column definition. Each cardinal mode is discussed in the following paragraphs:
One to one relationships:
One to one relationships exist when two tables are joined together that must
have precisely the same number of rows where for each row in the first table
there is one and only one matching row in the second table. It can be said that
the collection of values contained in the primary key column of one table are
identical to those found in the foreign key column of the second table. This
class of relationship serves to allow two smaller tables to act like one
larger one.
One to many relationships:
One to many relationships exist when two tables are joined together that can
have different numbers of rows and where the foreign key attribute in the first
table can hold the primary key value of any row in the second table. This type
of relationship is the most commonly used one and provides the means to
"look up" information in another table.
Many to many relationships:
Many to many relationships exist when two tables are joined together that
can have different numbers of rows and where the first table can reference zero,
one, or many instances in the second table and the second table can reference
zero, one, or many instances in the first table. Unlike the previous
relationships, these relationships are bidirectional. This type of relationship
is fairly common--consider the case where each vendor supplies common parts and
each of these parts could be supplied by different vendors.
A many to many relationship CANNOT be implemented directly. To make this work, a join table is created that consists of two foreign keys, each one linked to the primary key of each respective table.
Recursive relationships:
Recursive relationships are a sort of cross between the one to one and one to
many relations. In this case, there is only one table with a linked foreign and
primary key. It is self referential. For example, consider an organization
chart of a company. Each employee on the chart reports to someone else,
who in turn is an employee as well. So, if the primary key is the employee's ID
number and an attribute "REPORTS_TO" is the foreign key, any fact
about the supervisor is available to the subordinate employee when walking up
the table. In the same way, walking down the table reveals the facts about
a subordinate to the supervisor.
Existence:
Sometimes it matters whether the parent entity exists prior to creation of the
child entity. In other cases, the child entity can be created without a present
instance of a parent entity. Relationships define whether the prior existence of
the parent entity is optional or mandatory, although mandatory is generally the
default. Existentiality has to do with referential integrity and is established
and maintained by those rules.
Notation methods:
Two common methods of diagramming relationships are shown below:
Manual notation method:
ENTITY1 ( attribute1* attribute2 attribute 3 attribute4
attribute5 )
attribute1 >--< ENTITY2.attribute1
attribute2 ---< ENTITY3.attribute2 (optional)
attribute3 ==== ENTITY4.attribute1 (mandatory, no nulls)
attribute4 ---< attribute1
This diagram shows that ENTITY1 has four key attributes. Attribute1 is related in a many to many with ENTITY2.attribute1, attribute2 is related in a one to many with ENTITY3.attribute2, and attribute3 is related in a one to one with ENTITY4.attribute1. In addition, a recursive relationship exists between attribute4 and attribute1. Existence is mandatory for the attribute1, attribute3, and attribute4 relations, but optional for the attribute2 relation. Nulls are prohibited from the attribute3 relation keys.
Entity/Relationship method:
The figure below illustrates how this method is diagramed:.
