*********************************************************
*                                                       *
*        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/awards1

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

DES> -- SQL Debugger


DES> -- November 2011


DES> -- Example 1 Awards


DES> -- Missing tuple 'Anna', c.f. Section 4 of FLOPS paper


DES> -- Refer to awards1UserTrace.txt for a user trace and awards1DevelopmentTrace.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> /* Tables */


DES> DROP TABLE IF EXISTS registration, courses, allInOneCourse;


DES> CREATE TABLE allInOneCourse (
  student varchar(45) NOT NULL,
  pass integer,
  PRIMARY KEY (student)
);


DES> CREATE TABLE courses (
  id varchar(11) NOT NULL,
  level int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);


DES> CREATE TABLE registration (
  student varchar(45) NOT NULL,
  course varchar(11) NOT NULL,
  pass integer,
  FOREIGN KEY(course) references courses(id),
  PRIMARY KEY (student,course)
);


DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Adrian',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Alba',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Alisha',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Amaya',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Arabella',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Ava',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Dexter',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Emma',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Evelyn',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Gabriel',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Gavin',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('George',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Harper',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Henry',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('James',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Jennifer',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Maggy',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Miguel',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Nicolas',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Noah',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Olivia',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Owen',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Paul',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Rocco',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Shane',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Stella',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Tosca',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO allInOneCourse(student,pass)  VALUES ('Violet',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO courses(id,level)  VALUES ('c1',  1);

Info: 1 tuple inserted.                     

DES> INSERT INTO courses(id,level)  VALUES ('c2',  2);

Info: 1 tuple inserted.                     

DES> INSERT INTO courses(id,level)  VALUES ('c3',  3);

Info: 1 tuple inserted.                     

DES> INSERT INTO courses(id,level)  VALUES ('c4',  4);

Info: 1 tuple inserted.                     

DES> INSERT INTO courses(id,level)  VALUES ('c5',  5);

Info: 1 tuple inserted.                     

DES> INSERT INTO courses(id,level)  VALUES ('c0',  0);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES ('Alba','c1',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Alba','c2',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Anna','c0',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Anna','c1',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Anna','c2',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Anna','c3',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Carla','c0',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('James','c0',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('James','c1',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('James','c2',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('James','c3',1);

Info: 1 tuple inserted.                     

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

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Juan','c2',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Juan','c5',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Mica','c1',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Mica','c2',0);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Miguel','c2',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Paul','c0',1);

Info: 1 tuple inserted.                     

DES> INSERT INTO registration(student,course,pass) VALUES  ('Pedro','c2',1);

Info: 1 tuple inserted.                     

DES> /* Views */


DES> /* Students, level of their courses and  pass/ffail flag */


DES> create or replace view standard(student, level, pass) as
   select R.student, C.level, R.pass
   from courses C, registration R
   where C.id = R.course;


DES> /* Students from the basic course (level = 0) */


DES> create or replace view basic(student) as
   select S.student
   from standard S
   where S.level = 0 and S.pass=1;


DES> /* Intensive students */


DES> create or replace view intensive(student) as
   (select A.student
    from allInOneCourse A  where A.pass=1)
   union
   (select A1.student
    from standard A1, standard A2, standard A3
    where A1.student = A2.student and A2.student = A3.student
          and
          A1.level = 1  and A2.level = 2 and A3.level = 3);


DES> /* Awards are only for students from the basic level */


DES> create or replace view awards(student) as
  select student from  basic
  except
  select student from  intensive;


DES> /*
create or replace view awards(student) as
select student
  from  basic
  where student not in (select student from intensive);
*/


Info: Batch file processed.

DES> /development on                       


DES> /debug_sql awards

Info: Question: all(awards)
Info: Debugging view 'awards'.
  (
    SELECT ALL student
    FROM 
      basic
  )
  EXCEPT
  (
    SELECT ALL student
    FROM 
      intensive
  );
{                                           
  1 - awards('Carla')
}
Input: Is this the expected answer for view 'awards'? (y/n/m/mT/w/wN/a/h) [n]: m'Anna'
Info: processAnswer(all(awards),missing(awards(Anna)))
Info: Processing:                           
CREATE VIEW "intensive_slice1"(student) AS ( SELECT * FROM intensive AS R WHERE EXISTS (SELECT intensive.student FROM intensive WHERE (true) AND student = R.student AND intensive.student = 'Anna'))

  CREATE VIEW intensive_slice1(student) AS
    SELECT ALL *
    FROM 
      intensive AS R
    WHERE EXISTS (
        SELECT ALL intensive.student
        FROM 
          intensive
        WHERE ((true AND student = R.student) AND intensive.student = 'Anna') );
Info: Initial logic program:
  buggy(allInOneCourse) :-
    state(all(allInOneCourse),nonvalid).
  buggy(awards) :-
    state(all(awards),nonvalid),
    state(all(basic),valid),
    state(all(intensive),valid).
  buggy(awards) :-
    state(subset(intensive_slice1,intensive),valid).
  buggy(basic) :-
    state(all(basic),nonvalid),
    state(all(standard),valid).
  buggy(courses) :-
    state(all(courses),nonvalid).
  buggy(intensive) :-
    state(all(intensive),nonvalid),
    state(all(allInOneCourse),valid),
    state(all(standard),valid).
  buggy(registration) :-
    state(all(registration),nonvalid).
  buggy(standard) :-
    state(all(standard),nonvalid),
    state(all(courses),valid),
    state(all(registration),valid).
  state(all(awards),nonvalid).
Info: Question: subset(intensive_slice1,intensive)
Info: Debugging view 'intensive'.           
  (
    SELECT ALL A.student
    FROM 
      allInOneCourse AS A
    WHERE A.pass = 1
  )
  UNION
  (
    SELECT ALL A1.student
    FROM 
      standard AS A1, 
      standard AS A2, 
      standard AS A3
    WHERE ((((A1.student = A2.student AND A2.student = A3.student) AND A1.level = 1) AND A2.level = 2) AND A3.level = 3)
  );
Input: Should 'intensive' include a tuple of the form 'Anna'? (y/n/a) [y]: n
Info: processAnswer(subset(intensive_slice1,intensive),wrong(intensive(Anna)))
Info: processAnswer(all(intensive),wrong(intensive(Anna)))
Info: Processing:                           
CREATE VIEW "standard_slice1"(student,level,pass) AS ( SELECT * FROM standard AS R WHERE EXISTS (SELECT standard.student FROM standard AS A1, standard AS A2, standard AS 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.student = R.student AND (A1.level = R.level AND A1.pass = R.pass)) AND standard.student = 'Anna'))

  CREATE VIEW standard_slice1(student,level,pass) AS
    SELECT ALL *
    FROM 
      standard AS R
    WHERE EXISTS (
        SELECT ALL standard.student
        FROM 
          standard AS A1, 
          standard AS A2, 
          standard AS 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.student = R.student AND (A1.level = R.level AND A1.pass = R.pass))) AND standard.student = 'Anna') );
Info: Processing:
CREATE VIEW "standard_slice2"(student,level,pass) AS ( SELECT * FROM standard AS R WHERE EXISTS (SELECT standard.student FROM standard AS A1, standard AS A2, standard AS A3 WHERE (((((A1.student = A2.student AND A2.student = A3.student) AND A1.level = 1) AND A2.level = 2) AND A3.level = 3)) AND (A2.student = R.student AND (A2.level = R.level AND A2.pass = R.pass)) AND standard.student = 'Anna'))

  CREATE VIEW standard_slice2(student,level,pass) AS
    SELECT ALL *
    FROM 
      standard AS R
    WHERE EXISTS (
        SELECT ALL standard.student
        FROM 
          standard AS A1, 
          standard AS A2, 
          standard AS A3
        WHERE ((((((A1.student = A2.student AND A2.student = A3.student) AND A1.level = 1) AND A2.level = 2) AND A3.level = 3) AND (A2.student = R.student AND (A2.level = R.level AND A2.pass = R.pass))) AND standard.student = 'Anna') );
Info: Processing:
CREATE VIEW "standard_slice3"(student,level,pass) AS ( SELECT * FROM standard AS R WHERE EXISTS (SELECT standard.student FROM standard AS A1, standard AS A2, standard AS A3 WHERE (((((A1.student = A2.student AND A2.student = A3.student) AND A1.level = 1) AND A2.level = 2) AND A3.level = 3)) AND (A3.student = R.student AND (A3.level = R.level AND A3.pass = R.pass)) AND standard.student = 'Anna'))

  CREATE VIEW standard_slice3(student,level,pass) AS
    SELECT ALL *
    FROM 
      standard AS R
    WHERE EXISTS (
        SELECT ALL standard.student
        FROM 
          standard AS A1, 
          standard AS A2, 
          standard AS A3
        WHERE ((((((A1.student = A2.student AND A2.student = A3.student) AND A1.level = 1) AND A2.level = 2) AND A3.level = 3) AND (A3.student = R.student AND (A3.level = R.level AND A3.pass = R.pass))) AND standard.student = 'Anna') );
Info: Current logic program:
  buggy(allInOneCourse) :-
    state(all(allInOneCourse),nonvalid).
  buggy(awards) :-
    state(all(awards),nonvalid),
    state(all(basic),valid),
    state(all(intensive),valid).
  buggy(awards) :-
    state(subset(intensive_slice1,intensive),valid).
  buggy(basic) :-
    state(all(basic),nonvalid),
    state(all(standard),valid).
  buggy(courses) :-
    state(all(courses),nonvalid).
  buggy(intensive) :-
    state(all(intensive),nonvalid),
    state(all(allInOneCourse),valid),
    state(all(standard),valid).
  buggy(intensive) :-
    state(subset(standard_slice1,standard),valid),
    state(subset(standard_slice2,standard),valid),
    state(subset(standard_slice3,standard),valid).
  buggy(registration) :-
    state(all(registration),nonvalid).
  buggy(standard) :-
    state(all(standard),nonvalid),
    state(all(courses),valid),
    state(all(registration),valid).
  state(all(awards),nonvalid).
  state(all(intensive),nonvalid).
  state(subset(intensive_slice1,intensive),nonvalid).

Info: Question: subset(standard_slice1,standard)
Info: Debugging view 'standard'.            
  SELECT ALL R.student, C.level, R.pass
  FROM 
    courses AS C, 
    registration AS R
  WHERE C.id = R.course;
Input: Should 'standard' include a tuple of the form 'Anna,1,1'? (y/n/a) [y]: 
Info: processAnswer(subset(standard_slice1,standard),valid)
Info: Current logic program:
  buggy(allInOneCourse) :-
    state(all(allInOneCourse),nonvalid).
  buggy(awards) :-
    state(all(awards),nonvalid),
    state(all(basic),valid),
    state(all(intensive),valid).
  buggy(awards) :-
    state(subset(intensive_slice1,intensive),valid).
  buggy(basic) :-
    state(all(basic),nonvalid),
    state(all(standard),valid).
  buggy(courses) :-
    state(all(courses),nonvalid).
  buggy(intensive) :-
    state(all(intensive),nonvalid),
    state(all(allInOneCourse),valid),
    state(all(standard),valid).
  buggy(intensive) :-
    state(subset(standard_slice1,standard),valid),
    state(subset(standard_slice2,standard),valid),
    state(subset(standard_slice3,standard),valid).
  buggy(registration) :-
    state(all(registration),nonvalid).
  buggy(standard) :-
    state(all(standard),nonvalid),
    state(all(courses),valid),
    state(all(registration),valid).
  state(all(awards),nonvalid).
  state(all(intensive),nonvalid).
  state(subset(intensive_slice1,intensive),nonvalid).
  state(subset(standard_slice1,standard),valid).

Info: Question: subset(standard_slice2,standard)
Info: Debugging view 'standard'.            
  SELECT ALL R.student, C.level, R.pass
  FROM 
    courses AS C, 
    registration AS R
  WHERE C.id = R.course;
Input: Should 'standard' include a tuple of the form 'Anna,2,1'? (y/n/a) [y]: 
Info: processAnswer(subset(standard_slice2,standard),valid)
Info: Current logic program:
  buggy(allInOneCourse) :-
    state(all(allInOneCourse),nonvalid).
  buggy(awards) :-
    state(all(awards),nonvalid),
    state(all(basic),valid),
    state(all(intensive),valid).
  buggy(awards) :-
    state(subset(intensive_slice1,intensive),valid).
  buggy(basic) :-
    state(all(basic),nonvalid),
    state(all(standard),valid).
  buggy(courses) :-
    state(all(courses),nonvalid).
  buggy(intensive) :-
    state(all(intensive),nonvalid),
    state(all(allInOneCourse),valid),
    state(all(standard),valid).
  buggy(intensive) :-
    state(subset(standard_slice1,standard),valid),
    state(subset(standard_slice2,standard),valid),
    state(subset(standard_slice3,standard),valid).
  buggy(registration) :-
    state(all(registration),nonvalid).
  buggy(standard) :-
    state(all(standard),nonvalid),
    state(all(courses),valid),
    state(all(registration),valid).
  state(all(awards),nonvalid).
  state(all(intensive),nonvalid).
  state(subset(intensive_slice1,intensive),nonvalid).
  state(subset(standard_slice1,standard),valid).
  state(subset(standard_slice2,standard),valid).

Info: Question: subset(standard_slice3,standard)
Info: Debugging view 'standard'.            
  SELECT ALL R.student, C.level, R.pass
  FROM 
    courses AS C, 
    registration AS R
  WHERE C.id = R.course;
Input: Should 'standard' include a tuple of the form 'Anna,3,0'? (y/n/a) [y]: 
Info: processAnswer(subset(standard_slice3,standard),valid)
Info: Current logic program:
  buggy(allInOneCourse) :-
    state(all(allInOneCourse),nonvalid).
  buggy(awards) :-
    state(all(awards),nonvalid),
    state(all(basic),valid),
    state(all(intensive),valid).
  buggy(awards) :-
    state(subset(intensive_slice1,intensive),valid).
  buggy(basic) :-
    state(all(basic),nonvalid),
    state(all(standard),valid).
  buggy(courses) :-
    state(all(courses),nonvalid).
  buggy(intensive) :-
    state(all(intensive),nonvalid),
    state(all(allInOneCourse),valid),
    state(all(standard),valid).
  buggy(intensive) :-
    state(subset(standard_slice1,standard),valid),
    state(subset(standard_slice2,standard),valid),
    state(subset(standard_slice3,standard),valid).
  buggy(registration) :-
    state(all(registration),nonvalid).
  buggy(standard) :-
    state(all(standard),nonvalid),
    state(all(courses),valid),
    state(all(registration),valid).
  state(all(awards),nonvalid).
  state(all(intensive),nonvalid).
  state(subset(intensive_slice1,intensive),nonvalid).
  state(subset(standard_slice1,standard),valid).
  state(subset(standard_slice2,standard),valid).
  state(subset(standard_slice3,standard),valid).

Info: Final logic program:
  buggy(allInOneCourse) :-
    state(all(allInOneCourse),nonvalid).
  buggy(awards) :-
    state(all(awards),nonvalid),
    state(all(basic),valid),
    state(all(intensive),valid).
  buggy(awards) :-
    state(subset(intensive_slice1,intensive),valid).
  buggy(basic) :-
    state(all(basic),nonvalid),
    state(all(standard),valid).
  buggy(courses) :-
    state(all(courses),nonvalid).
  buggy(intensive) :-
    state(all(intensive),nonvalid),
    state(all(allInOneCourse),valid),
    state(all(standard),valid).
  buggy(intensive) :-
    state(subset(standard_slice1,standard),valid),
    state(subset(standard_slice2,standard),valid),
    state(subset(standard_slice3,standard),valid).
  buggy(registration) :-
    state(all(registration),nonvalid).
  buggy(standard) :-
    state(all(standard),nonvalid),
    state(all(courses),valid),
    state(all(registration),valid).
  state(all(awards),nonvalid).
  state(all(intensive),nonvalid).
  state(subset(intensive_slice1,intensive),nonvalid).
  state(subset(standard_slice1,standard),valid).
  state(subset(standard_slice2,standard),valid).
  state(subset(standard_slice3,standard),valid).

Info: Buggy relation found: intensive

DES>