SQL phase 3

Phase#3  of  the  Personal  Collection  Database  Application  

Due  Date:  Sunday  April  21,  2019  

Note:     You  may  need  to  save  anything  that  you  will  need  for  the  long-­‐term  in  the  file  system   in  case  your  Database  System  backup  fails.  

Here  are  the  three  requirements  for  Phase  3  of  the  project:  

a)   Write  an  SQL  database  schema  for  your  DB  application,  using  the  CREATE  TABLE   commands.  Pick  suitable  datatypes  for  each  attribute.  Include  in  your  submission  a   printout  of  the  CREATE  TABLE  commands  you  use  to  create  your  database  schema.  

Do  not  forget  to  declare  keys,  foreign  keys,  and  other  constraints  where  necessary.   Show  MySQL’s  response  to  a  request  to  “describe”  the  tables  that  you  created.    

https://dev.mysql.com/doc/refman/5.7/en/getting-­‐information.html  

b) Populate  the  tables  that  you  created  by  inserting  a  substantial  number  of  tuples  in   each  table.  You  can  keep  all  the  INSERT  SQL  commands  in  a  file  so  that  you  do  not   have  to  retype  them  in  case  you  need  to  rebuild  your  DB  application.   Execute  about  five  INSERT  commands  to  insert  tuples  into  one  of  your  relations.  

Show  the  relation  that  results  when  you  issue  the  corresponding  SELECT  *  command.  

c) Write   five   sample   queries   on   your   DB   application,   using   the   select-­‐from-­‐where   construct  of  SQL.  

To   receive   full   credit,   each   query   should   exhibit   some   interesting   feature   of   SQL:   queries  over  more   than  one  relation,  or   subqueries,   for  example.  Experiment  with   your  SQL  commands  on  a  small  database  (e.g.,  your  hand-­‐created  database),  before   running   them  on   the   large  database.   Initial  debugging   is  much  easier  when  you're   operating   on   small   amounts   of   data.   Once   you're   confident   that   your   queries   are   working,  run  them  on  your  complete  database.  

Use   the   EXPLAIN   statement   to   display   the   results   of   your   queries.   The   EXPLAIN   statement   is   similar   to   the   DESCRIBE   statement,   but   the   DECRIBE   statement   is   normally  used  to  display  TABLE  definitions  while  the  EXPLAIN  statement  is  used  to   display  query  results.  

https://dev.mysql.com/doc/refman/5.7/en/explain.html