● Relationship Type > Identifying Relationships: Identifying relationships are always mandatory. A foreign key value must exist in the child entity and the foreign key value must be found in the primary key of the parent. Identifying relationships propagate primary keys as primary keys to child entities, which can result in compound keys.
The use of compound keys is often valid and appropriate; however, they limit the flexibility of your data model. When you use a compound key, the data contained in the child entity can only be identified in the context of its parent. If you ever need to use the data independently, then you will have a problem
The cardinality of a mandatory relationship must be in the form of one-to-something.
When generating a physical model, foreign key columns propagated by a mandatory relationship default to NOT NULL.
● Relationship Type > Non-Identifying, Optional Relationships: Non-identifying relationships propagate the parent's primary key to the non-key attributes of the child. Since the relationship is optional the foreign key value is not always required in the child entity; however, if a value does exist, then the foreign key value must be found in the primary key of the parent.
The cardinality of an optional relationship takes the form of zero or one to something.
When generating a physical model, foreign key columns propagated by an optional relationship default to NULL.
● Relationship Type > Non-Identifying, Mandatory Relationships: Non-identifying relationships propagate the parent's primary key to the non-key attributes of the child. A foreign key value must exist in the child entity and the foreign key value must be found in the primary key of the parent.
The cardinality of a mandatory relationship must be in the form of one to something.
When generating a physical model, foreign key columns propagated by a mandatory relationship default to NOT NULL.
● Relationship Type > Non-Specific Relationships: Non-specific relationships denote many-to-many relationships. Because many-to-many relationships cannot be resolved, non-specific relationships do not propagate any foreign keys. Many-to-many relationships are undesirable and should be removed as you normalize your data model.
The following illustrates a non-specific relationship where the parent and the child are both optional. In the Relationship Editor you can specify whether the parent and the child are optional or mandatory.
● Cardinality: Cardinality displays the ratio of related parent and child instances. The cardinality ratio for the parent depends on whether the relationship is mandatory or optional. Although cardinality is a powerful concept for expressing business rules, you should know that no database can directly enforce cardinality. Enforcing cardinality constraints can be accomplished through procedural database logic or somewhere other than in the database.
The iconic cardinality representations for IE (Crows Feet) notation are illustrated below:
| ||||||||||||||||||
