Enterprise  Database  Management  Systems  

Phase#4  of  your  Database  Application  

Database  Constraints,  Triggers,  Views  and  Stored  Procedures  

Due  Date:    Sunday  May  5,  2019  

The  weight  of  this  project  component  is  10%  of  the  course  grade.  

Please  submit  one  “pdf”  document  showing  a  script  of  all  your  interactions  

1. Add  to  your  CREATE  TABLE  statements  from  Phase  3  foreign  keys  and  other  constraints  where   necessary.  

2. Write   five   data   modification   commands   (INSERT,   DELETE   and   UPDATE)   on   your   database   application.   These   commands   should   be   ”interesting”,   in   the   sense   that   they   involve   some   complex   feature,   such   as   inserting   the   result   of   a   query,   updating   several   tuples   at   once,   or   deleting  a  set  of  tuples  that  is  more  than  one  but  less  than  all  the  tuples  in  a  relation.  Include  in   your  submission  the  results  displayed  by  the  corresponding  EXPLAIN  statements  to  show  your   modification  commands  running  in  a  convincing  fashion.    

3. Create  two  views  on  top  of  your  database  schema.  Show  your  CREATE  VIEW  statements  and  the   response   of   the   system.   Also,   show   a   query   involving   each   view   and   the   system   response.   Finally,  show  what  happens  when  you  try  to  update  your  view,  say  by  inserting  a  new  tuple  into   it.  Are  any  of  your  views  updateable?  Why  or  why  not?  (Essentially,  a  view  is  updateable  if  it  is  a   selection  on  one  base  table.)  

4. Write  two  stored  functions  or  procedures.  Each  stored  procedure/function  should  involve  more   than  one  SQL  statement.  Each  should  use  one  or  more  parameters  in  a  significant  way.  Include   listings   of   your   code   in   your   submission.   Also,   include   corresponding   EXPLAIN   statements   showing   these   stored  procedures  or   functions   called  at   least  once  each.   Similarly,   include   the   results  of  queries  that  demonstrate  that  the  functions  have  had  their  intended  effect.  

5. Write   two   SQL   Triggers   or   SQL   Assertions.   It   would   be   ”interesting”   if   at   least   one   trigger’s   action  calls  a  stored  procedure  or  function  and  at  least  one  is  an  ”instead  of”  trigger.   Include  your   trigger  declarations  (CREATE  TRIGGER  statements)  along  with  your  submission.   Also,  show,  for  each  trigger,  the  effect  of  two  database  modifications.  One  modification  should   trigger  the  trigger  (making  the  trigger’s  condition  true),  and  the  other  not  (making  the  trigger’s   condition   false).   Include   in   your   submission  queries   that   demonstrate   that   the   trigger  has   an   effect  in  the  first  case  and  not  in  the  second.