Clean up of Chapter 6

• Review: The maximum cardinality of a relationship.
  • 1 to 1
    • Place the PK of one as a FK in the other.
    • Force the FK field to be an AK (or to be unique)
  • 1 to N
    • Place the PK of the 1 as a FK in the N
  • N to M
    • Build a table where the PK of each is a FK, and the two PK's make up the PK for the new table.
• Minimum cardinality
  • This is much more difficult
  • Some of it requires special SQL code
  • Some of it requires enforcement from application or SQL triggers.
  • There are four cases we must consider:
    • O - O (Optional Optional)
    • M - O (Mandatory Optional)
    • O - M (Optional Mandatory)
    • M - M (Mandatory Mandatory)
  • The first entity in the relationship is the parent, the second a child
  • The parent is the one entity in a one to many relationship.
  • The child is the many entity in a one to many relationship.
  • Relationships where the child is required are the most difficult to maintain.
  • Consider
    • 
    • The Company to Department and Department to Employee are M-O
    • The Company to Phone_Contact are M-M
  • There are a number of actions we must consider as well
    • Insert (Parent or child)
    • Modify (Parent or child)
    • Delete (Parent or child)
  • For each of these, we will have a minimal cardinality enforcement action or just action for short.
• There are two tables worth considering on 226.

  Parent Required	Action on Parent	Action On Child
  Insert	None	Get A Parent
  		Prohibit
  Modify Key or FK	Change children's foreign keys values to match new value (cascade update)	OK, if new foreign key value matches existing parent
  	Prohibit	Prohibit
  Delete	Delete Children (Cascade delete)	None
  	Prohibit

  Child Is Required	Action On Parent	Action On Child
  Insert	Get Child	None
  	Prohibit
  Modify Key or FK	Update the FK of (at least one) child	If not last child, OK
  	Prohibit	If last child, prohibit or find a replacement
  Delete	None	If not last child, OK
  		If last child, prohibit or find a replacement

• O-O
  • Done
  • Make sure that FK field can be nullable.
  • But we really don't care if there is an entry or not, everything is fine.
• If a parent is required
  • Actions on the Parent
    • Every child must have a valid parent
    • Which is not null.
    • Adding a new parent is not a problem. It has no children yet.
    • Changing the PK of a parent is a problem,
      • Those children have the old key as a FK.
      • They need to be updated, or the relationship will be invalid.
      • This is a cascading update
    • Deleting the parent is also a problem:
      • Those children associated with the parent must be deleted.
      • This is called a cascading delete
      • Or the deletion must be prohibited.
  • Actions on the child
    • Adding a child
      • We must have a parent, or we can't do it.
      • Sometimes we add a dummy parent, which inherits all new children.
    • Updating a Child's FK
      • Again, it is straight forward:
      • If the new key is a valid PK in parent table, update.
      • Otherwise we don't allow the modification.
    • Deleting a child is not a problem.
• If a child is required
  • Actions on the parent
    • Add a parent
      • Just like above, get the required child or don't allow.
    • Update the parent's PK
      • A child's FK must be updated so that it belongs to the parent
      • Or this must be prohibited
    • Delete a parent:
      • Not a problem
  • Actions on the Child
    • Insert a new child
      • No problem
    • Modify the FK in the child
      • Only a problem if it is the last child.
      • Then this must be prohibited or a new child found.
    • Same with delete a child.