*********************************************************
*                                                       *
*        DES: Datalog Educational System v.3.0          *
*                                                       *
*                                                       *
* Type "/help" for help about commands                  *
* Type "des." to continue if you get out of DES         *
*   from a Prolog interpreter                           *
*                                                       *
*                    Fernando Saenz-Perez (c) 2004-2011 *
*                                         GPD DISIA UCM *
*             Please send comments, questions, etc. to: *
*                                     fernan@sip.ucm.es *
*                                             Web site: *
*                           http://des.sourceforge.net/ *
*********************************************************

DES> /process examples/SQLDebugger/awards2

Info: Processing file 'examples/SQLDebugger/awards2.sql' ...

DES> -- SQL Debugger


DES> -- November 2011


DES> -- Example 2 Awards


DES> -- Wrong tuple 'Anna'


DES> -- Refer to example2UserTrace.txt for a user trace and example2DevelopmentTrace.txt for a development trace


DES> --


DES> -- Declarative Debugging of Wrong and Missing Answers for SQL Views


DES> -- FLOPS 2012


DES> 


DES> /abolish


DES> /multiline on


DES> /development off

Info: Development listings are already disabled.

DES> -- Turn development on to inspect more info


DES> -- /development on


DES> /* Tables */


DES> DROP TABLE IF EXISTS grants, payment, registration, courses;


DES> CREATE TABLE grants (
   student varchar(45) DEFAULT NULL,
   amount integer default 0
);


DES> CREATE TABLE courses (
  idcourses varchar(11) NOT NULL,
  style varchar(45) DEFAULT NULL,
  level int(11) DEFAULT NULL,
  teacher varchar(10) DEFAULT NULL,
  PRIMARY KEY (`idcourses`)
);


DES> INSERT INTO `courses` (`idcourses`, `style`, `level`, `teacher`) VALUES ('c1', 'salsa',    1, 'teach1');

Info: 1 tuple inserted.                     

DES> INSERT INTO `courses` (`idcourses`, `style`, `level`, `teacher`) VALUES ('c2', 'salsa',    2, 'teach2');

Info: 1 tuple inserted.                     

DES> INSERT INTO `courses` (`idcourses`, `style`, `level`, `teacher`) VALUES ('c3', 'Merengue', 3, 'teach3');

Info: 1 tuple inserted.                     

DES> INSERT INTO `courses` (`idcourses`, `style`, `level`, `teacher`) VALUES ('c4', 'salsa',    3, 'teach1');

Info: 1 tuple inserted.                     

DES> INSERT INTO `courses` (`idcourses`, `style`, `level`, `teacher`) VALUES ('c5', 'salsa',    3, 'teach1');

Info: 1 tuple inserted.                     

DES> INSERT INTO `courses` (`idcourses`, `style`, `level`, `teacher`) VALUES ('c6', 'salsa',    3, 'teach3');

Info: 1 tuple inserted.                     

DES> INSERT INTO `courses` (`idcourses`, `style`, `level`, `teacher`) VALUES ('c7', 'salsa',    0, 'teach1');

Info: 1 tuple inserted.                     

DES> CREATE TABLE registration (
  idregistration int(11) NOT NULL,
  student varchar(45) DEFAULT NULL,
  course varchar(11) DEFAULT NULL,
  PRIMARY KEY (`idregistration`)
);


DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (1, 'Juan', 'c1');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (2, 'Juan', 'c2');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (3, 'Juan', 'c6');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (4, 'Juan', 'c5');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (5, 'Pedro', 'c2');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (6, 'Ana', 'c1');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (7, 'Ana', 'c2');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (8, 'Ana', 'c5');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (9, 'Mica', 'c6');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (10, 'Juan', 'c7');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (11, 'Mica', 'c2');

Info: 1 tuple inserted.                     

DES> INSERT INTO `registration` (`idregistration`, `student`, `course`) VALUES (12, 'Mica', 'c1');

Info: 1 tuple inserted.                     

DES> CREATE TABLE payment (
  `id` int(11) NOT NULL,
  `student` varchar(45) DEFAULT NULL,
  `state` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);


DES> INSERT INTO `payment` (`id`, `student`, `state`) VALUES (1, 'Miguel', 'unpaid');

Info: 1 tuple inserted.                     

DES> /* Views */


DES> /* Salsa courses */


DES> create or replace view salsaCourses(id, level, teacher) as
  select c.idcourses, c.level, c.teacher
  from courses c
  where c.style = 'salsa';  


DES> /* Students of salsa courses */


DES> create or replace view salsaStudents(student, level, teacher) as
   select R.student, C.level, C.teacher
   from salsaCourses C, registration R
   where C.id = R.course;


DES> /* Students of intensive course (level = 0) */


DES> create or replace view intensiveStudents(student) as
   select S.student
   from salsaStudents S
   where S.level = 0;


DES> /* Students of first three courses with different teachers */


DES> /* This is the ill-defined view */


DES> create or replace view basicLevelStudents(student) as
select a1.student
from salsaStudents a1, salsaStudents a2, salsaStudents a3 
where a1.student = a2.student and a2.student = a3.student and 
(a1.level = 1) and (a2.level = 2) and (a3.level = 3) and
(a1.teacher <> a2.teacher) and (a2.teacher <> a3.teacher);


DES> /* Students of basic level with different teachers and no intensive level */


DES> /* Two equivalent formulations below (the first one is not accepted by MySQL) */


DES> /*create or replace view candidates(student) as
  select student
  from  basicLevelStudents
except
  select student
  from  intensiveStudents;
*/


DES> create or replace view candidates(student) as
  select student
  from  basicLevelStudents
where student not in (
  select student
  from  intensiveStudents);


DES> /* Candidates with no past due payments */


DES> create or replace view awards(student) as
  select student
  from candidates c
  where  
      c.student not in (select student
                    from payment
                    where state = 'unpaid')
      OR  /* UNION */
      c.student in (select student from grants);


DES> 


Info: Batch file processed.

DES> /debug_sql awards                    

Info: Debugging view 'awards'.
{                                           
  1 - awards('Ana'),
  2 - awards('Mica')
}
Input: Is this the expected answer for view 'awards'? (y/n/m/mT/w/wN/a/h) [n]: w1
Info: Debugging view 'intensiveStudents'.   
{                                           
  1 - intensiveStudents('Juan')
}
Input: Is this the expected answer for view 'intensiveStudents'? (y/n/m/mT/w/wN/a/h) [y]: 
Info: Debugging view 'candidates'.          
Input: Should 'candidates' include a tuple of the form 'Ana'? (y/n/a) [y]: n
Info: Debugging view 'basicLevelStudents'.  
Input: Should 'basicLevelStudents' include a tuple of the form 'Ana'? (y/n/a) [y]: n
Info: Debugging view 'salsaStudents'.       
Input: Should 'salsaStudents' include a tuple of the form 'Ana,1,teach1'? (y/n/a) [y]: 
Info: Debugging view 'salsaStudents'.       
Input: Should 'salsaStudents' include a tuple of the form 'Ana,2,teach2'? (y/n/a) [y]: 
Info: Debugging view 'salsaStudents'.       
Input: Should 'salsaStudents' include a tuple of the form 'Ana,3,teach1'? (y/n/a) [y]: 
Info: Buggy view found: basicLevelStudents

DES>