/* basic tables for conference paper submissions */

create table confAuthor
/* table to contain information on authors of papers */
( authorID     bigint(20) unsigned not null auto_increment,
  first        varchar(25) not null,
  last         varchar(25) not null,
  organization varchar(255) not null, -- e.g., Grinnell College
  email        varchar(255) not null,
  primary key  (authorID)
);

create table confPaper
/* table to store paper information */
( paperID   bigint(20) unsigned not null auto_increment,
  title     varchar(255) not null,
  status    varchar(255),  -- e.g., received, accepted, rejected
  primary key (paperID)
);

create table confAuthorPaper
/* mapping between authors and papers */
( authorID  bigint(20) unsigned,
  paperID   bigint(20) unsigned 
);

/* populating tables from accepted papers on Theory and Formal Methods 
   to SIGCSE 2008 */
/* paper 1 */

insert into confAuthor values
    (1, "Michal", "Armoni", "Weizmann Institute of Science", 
           "made-up-1@school1.edu"),
    (2, "Noa", "Lewenstein", "Netanya Academic College", 
           "made-up-2@school2.edu"),
    (3, "Mordechai", "Ben-Ari", "Weizmann Institute of Science", 
           "made-up-3@school1.edu");

insert into confPaper values
    (null, "Teaching Students to Think Nondeterministically",
        "accepted");

insert into confAuthorPaper
       set authorID=1, paperID=1;
insert into confAuthorPaper
       set authorID=2, paperID=1;
insert into confAuthorPaper
       set authorID=3, paperID=1;

/* paper 2 */
insert into confAuthor values
    (NULL, "Irene", "Polycarpou", "Florida International University",
        "made-up-4@school4.edu");

insert into confAuthor values
    (null, "Ana", "Pasztor", "Florida International University",
        "made-up-5@school4.edu");
 
insert into confAuthor values
    (null, "Malek", "Adjouadi", "Florida International University",
        "made-up-6@school4.edu");

insert into confPaper (title, status) values
    ("A Conceptual Approach to Teaching Induction for Computer Science",
        "accepted");

insert into confAuthorPaper values
    (4, 2),
    (5, 2),
    (6, 2);

/* paper 3 */
insert into confAuthor
    set first="Joan", last="Lucas", 
        organization="State University of New York, College at Brockport",
        email="made-up-7@school5.edu";

insert into confAuthor
    set first="Jonathan", last="Jarvis",  
        organization="Advanced Micro Devices",
        email="ficticious1@company1.com";


insert into confPaper values
    (3, 
    "Incorporating Transformations into JFLAP for Enhanced Understanding of Automata", 
    "submitted");

insert into confAuthorPaper
    set authorID=7, paperID=3;

insert into confAuthorPaper
    set authorID=8, paperID=3;


/* populating tables from accepted papers on Compilers and 
   Programming Langauges to SIGCSE 2008 */

/* paper 4 */
insert into confAuthor values
   (null, "Marc", "Corliss", "Hobart and William Smith Colleges",
          "made-up-8@school6.edu");
insert into confAuthor values
   (null, "Christopher", "Lewis", "VMware, Inc.", "ficticious2@company2.com");

insert into confPaper (title, status) values
   ("Bantam: A Customizable, Java-Based, Classroom Compiler", 
       "submitted"); -- actually this was accepted, but this version gives a better example

insert into confAuthorPaper values
   ( 9, 4),
   (10, 4);

/* paper 5 */
insert into confAuthor
   set first="Li",
       last="Xu",
       organization="UMass Lowell",
       email="made-up-9@school7.edu";

insert into confPaper
   set title="Language Engineering in The Context of A Popular, Inexpensive Robot Platform",
       status="accepted";

insert into confAuthorPaper
   set paperID=5,
       authorID=11;

/*paper 6 */
insert into confAuthor values
   (12, "Ariel", "Ortiz", "Tecnologico de Monterrey, Campus Estado de Mexico",
        "made-up-10@school8.edu");

insert into confPaper values
   (6, 
    "Language Design and Implementation using Ruby and the Interpreter Pattern",
    "submitted"); -- actually this was accepted, but this version gives a better example

insert into confAuthorPaper values
   (12, 6);

/* sample 1-table queries */

SELECT * FROM confAuthor;
SELECT * from confAuthor ORDER BY last;
SELECT * from confAuthor ORDER BY last DESC;

SELECT * from confAuthor where organization="Florida International University";
SELECT * from confAuthor where organization="Florida International University"
       ORDER BY last;

SELECT authorID, last, email from confAuthor;
SELECT authorID, last, email from confAuthor ORDER BY first;

SELECT * from confPaper;
SELECT * from confPaper where status="submitted";
SELECT * from confPaper where status="submitted" OR status="accepted";
SELECT * from confPaper where status LIKE "sub%";
SELECT * from confPaper where title LIKE "%teach%";

update confPaper set status="accepted" WHERE paperID=6;
   -- warning:  WHERE clause is vital here!

insert into confPaper (title, status) values ("Test Example", "sample");
SELECT * from confPaper;
delete from confPaper WHERE paperID=7;
   -- warning:  WHERE clause is vital here!

/* remove previous versions of the tables */
/* -- commented out, to minimize potential problems 
   -- from accidental cutting and pasting!
drop table confAuthor; -- error if the table does not currently exist
drop table confPaper;
drop table confAuthorPaper;
*/