*********************************************************
*                                                       *
*        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> /debug_sql 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: 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: 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: 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: 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: Buggy relation found: intensive

DES>