Design for Minimum Cardinality

• You should step through the Create Relationships section
  • This is, for the most part, very straight forward.
  • And probably something you have figured out at this point
  • And I'm tired of the really bored faces.
• Design for Minimum Cardinality.
  • He talks of this in terms of a plan, but we need to also talk about DBMS support for this.
  • This is the start of exploring more sql.
  • I am going to do it as a two/three stage process
    • Introduce the concept of triggers and cascading updates/deletes.
    • Introduce the problems from this chapter.
    • Revist SQL
    • Revisit, if needed the problems.
  • We can create stored functions and stored procedures in sql
    • This is a new development in sql and mysql.
    • Functions extend the sql language
      • Return values.
    • Procedures implement logic
      • They operate on tables (mostly)
      • They do not return a value.
    • There are variables, control structures and more.
    • We will look at these in upcoming classes
• Triggers
  • Triggers add a level of event driven programming to the database.
  • A trigger is associated with a table.
  • When a specified event occurs in the table, the triggers is called.
  • Connolly lists the uses of triggers as
    • Validating input data
    • Maintaining complex integrity constraints that can't be done as table refferential integrity constraints
    • Supporting alerts that an action needs to be taken (Message on screen or email to order more widgets, we are getting low)
    • Recording audit information, who changed thing.
    • Supporting replication across databases or across machines.
  • Executing a trigger is somtimes called "Firing"
  • The mysql trigger creation syntax

    CREATE TRIGGER trigger_name
          trigger_time trigger_event
          ON table_name FOR EACH ROW
          trigger_body
    	

  • Where trigger_time is either BEFORE or AFTER
  • And trigger_event is INSERT, UPDATE, or DELETE
    • They explain that these events are nut just INSERT
      • INSERT, LOAD DATA, REPLACE
  • If the trigger fails, the corresponding statement is not executed
  • Connolly tells us triggers are problmatic becuase
    • Complexity
    • Hidden functionality:
      • It is not apparent that triggers exist and what they do
      • And basic operations on tables do not perform as expected
    • Overhead - more processing
  • There is much more associated with triggers, we will look at examples later.
• Cascading updates/deletes.
  • This is a part of creating a constraint that we did not look at
  • It is documented here

  • CONSTRAINT FOREIGN KEY
         (column name, ...) 
        REFERENCES table_name (column_name, ...)
        ON DELETE reference_option
        ON UPDATE reference_option
           

  • Where reference_option is one of RESTRICT, CASCADE, SET NULL, NO ACTION
  • The key is in the parent table
  • The foreign key is in the child table
  • CASCADE: Delete or update a row in the parent table will cause a delete or update of the corresponding rows in the child table.
    • IE change a key value in the parent, the value will change in the child as well.
    • Or delete a key value in the parent table, corresponding child table vaules will be deleted.
  • SET NULL: Delete the parent row, child values will be set to NULL
    • Note the child foreign key filed must be nullable
  • RESTRICT: Reject if it will cause a problem (This is the default)
  • NO ACTION: Required by SQL standard. Is equivelent to REJECT in MySSL.
• Look at the following table
  • 
  • If you decide you MUST have Manditory relationshps
  •