Module 06c - More Constraints Flashcards
Representing Ternary and higher order relationships
Ternary and higher-order relationships may be constrained by the binary relationship that comprise them.
MUST constraint—requires that one entity must be combined with another entity in the ternary (or higher-order) relationship.
MUST NOT constraint—requires that certain combinations of two entities are not allowed to occur in the ternary (or higher-order) relationship.
MUST COVER constraint—a binary relationship specifies all combinations of two entities that must appear in the ternary (or higher-order) relationship.
Design for minimum cardinality
Relationships can have the following types of minimum cardinality:
O-O: parent optional and child optional
M-O: parent mandatory and child optional
O-M: parent optional and child mandatory
M-M: parent mandatory and child mandatory
O-O minimum cardinality
No action needs to be taken for O-O relationships.
M-O minimum cardinality
Parent-required action Every child has a parent.
Operations never create orphans.
The DBMS will enforce the action as long as: Referential integrity constraints are properly defined. The foreign key column is NOT NULL.
O-M minimum cardinality
The DBMS does not provide much help.
Triggers or other application codes will need to be written.
O-M when a parent is required
O-M when a child is required
M-M minimum cardinality
The worst of all possible worlds: Especially in strong entity relationships.
In relationships between strong and weak entities the problem is often easier when all transactions are initiated from the strong entity side.
Complicated and careful application programming will be needed.
What’s cascading updating/deleting?
A cascading update occurs when a change to the parent’s primary key is applied to the child’s foreign key. E.g, primary key is Product Surrogate keys never change and there is no need for cascading updates when using them.
A cascading delete occurs when associated child rows are deleted along with the deletion of a parent row. For strong entities, generally do not cascade deletes For weak entities, generally do cascade deletes
Cascading when the parent is required
Cascading when the child is required
Triggers: definition
A trigger is a stored program that is executed by the DBMS whenever a specified event occurs on a specified table or view (defined in Chapter Seven).
Triggers are used to enforce specific minimum cardinality enforcement actions not otherwise programmed into the DBMS.
Documenting the minimal cardinality
Summary
