Exam 1 Solutions
----------------

(1)

  create table theater (
    tno integer,
    tname varchar2(30),
    city  varchar2(30),
    primary key (tno)
  );
  create table screen (
    tno integer,
    sno integer,
    sname varchar2(30),
    primary key (tno,sno),
    foreign key (tno) references theater
  );
  create table show (
    tno integer,
    sno integer,
    stime date,
    mno integer,
    numVisitors integer,
    primary key (tno,sno,stime),
    foreign key (tno,sno) references screen,
    foreign key (mno) references movie
  );
  create table movie (
    mno integer,
    mname varchar2(50),
    year number(4),
    country varchar2(50),
    primary key (mno)
  ); 

(2)

  create table entree (
    ename varchar2(20),
    primary key (ename)
  );
  create table ingredient (
    iname varchar2(20),
    primary key (iname)
  );
  create table uses (
    ename varchar2(20),
    iname varchar2(20),
    qty   number(5,2),
    primary key (ename,iname),
    foreign key (ename) references entree,
    foreign key (iname) references ingredients
  );

(3a) select headline
     from   articles, keywords
     where articles.id = keywords.id and
           keyword = 'Arafat';

(3b) PROJECT[id](SELECT[k1<>k2 and k2<>k3 and k1<>k3](RENAME[id,k1](keywords) JOIN 
                                                      RENAME[id,k2](keywords) JOIN 
                                                      RENAME[id,k3](keywords)))

(3c) select id
     from   articles
     where  text like '%Pol Pot%';

(3d) select a1.author, a1.id, a1.dateline
     from   articles a1
     where  a1.id in (select id
                      from   keywords
                      group by id
                      having count(keyword) >= 3) and
            a1.dateline =
              (select min(a2.dateline)
               from   articles a2
               where  a2.author=a1.author and
                      a2.id in (select id
                                from   keywords
                                group by id
                                having count(keyword) >= 3));

(3e) PROJECT[author](articles) - 
     PROJECT[author](articles JOIN SELECT[keyword='Milosevic'](keywords))

(3f) update articles
     set    text = headline
     where  text is null;

(4) Run the following query and capture the results in a file:

    select 'insert into items values(' || ino || ',''' || iname || ''');'
    from   items;

    Minor editing of the file will recreate the insert statements.

(5a) select A,r.B,r.C,D
     from   r,s,t
     where  r.B=s.B and r.C=s.C and r.B=t.B and r.C=t.C;

(5b) (select B from r) union (select B from s) union (select B from t);

(5c) select B,C
     from   r
     where  not exists (select *
                        from   s
                        where  r.B=s.B and r.C=s.C);

(5d) select r1.A, r1.B
     from   r r1
     where  not exists (select C
                        from   t
                        where  not exists (select *
                                           from   r r2
                                           where  r2.A=r1.A and r2.B=r1.B and
                                                  r2.C=t.C));

(6a)  B    (6b)  A B C D   (6c)  A C  (6d)  B A C  (6e)  t.A D
     ---        ---------       -----      -------      -------
      5          1 5 2 2         1 8        5 1 2         1  2
      6          1 6 2 2                    5 1 3         2  4
                 1 7 2 2                    5 1 4
                 1 6 4 2                    6 1 2 
                 1 5 3 2                    6 1 3
                 1 5 4 2                    6 1 4
                 1 6 3 2
                 1 7 4 2 

(7a) No, the two sets are not equal. 
     Counter example:
     r(A B C)   PROJECT[A,B](r)  PROJECT[A,C](r)   PROJECT[A,B](r) JOIN PROJECT[A,C](r)
     --------   ---------------  ---------------   ------------------------------------
       a b c       a b              a c                  a b c
       a d e       a d              a e                  a b e
                                                         a d c
                                                         a d e

(7b) r is a subset of PROJECT[A,B](r) JOIN PROJECT[A,C](r)