drop table Enroll;
create table Enroll (
   sid        int     not null, 
   cid        char(4) not null,
   enrollDate date    not null,
   room       text    not null,
   prof       text    not null
);
-- No PK because we're illustrating a design point.
-- What could the PK be?

insert into Enroll(sid, cid, enrollDate, room, prof) 
values (1, 'A', '1-23-2006', 'HC2023', 'Bond');

insert into Enroll(sid, cid, enrollDate, room, prof) 
values (1, 'B', '1-23-2006', 'HC2023', 'Leiter');

insert into Enroll(sid, cid, enrollDate, room, prof) 
values (2, 'A', '2-13-2006', 'HC2023', 'Bond');

insert into Enroll(sid, cid, enrollDate, room, prof) 
values (3, 'C', '1-23-2006', 'LT027' , 'Moneypenny');

insert into Enroll(sid, cid, enrollDate, room, prof) 
values (4, 'D', '1-12-2006', 'DN2112', 'Goodnight');

select *
from Enroll;


drop table BadDecomp1;
create Table BadDecomp1(
   sid        int     not null, 
   cid        char(4) not null,
   enrollDate date    not null
);
-- What could the PK be here?

insert into BadDecomp1(sid, cid, enrollDate)
values (1, 'A', '1-23-2006');

insert into BadDecomp1(sid, cid, enrollDate)
values (1, 'B', '1-23-2006');

insert into BadDecomp1(sid, cid, enrollDate)
values (2, 'A', '2-13-2006');

insert into BadDecomp1(sid, cid, enrollDate)
values (3, 'C', '1-23-2006');

insert into BadDecomp1(sid, cid, enrollDate)
values (4, 'D', '1-12-2006');

select *
from BadDecomp1;


drop table BadDecomp2;
create Table BadDecomp2(
   enrollDate date    not null,
   room       text    not null,
   prof       text    not null
);
-- What could the PK be here?

insert into BadDecomp2(enrollDate, room, prof)
values ('1-23-2006', 'HC2023', 'Bond');

insert into BadDecomp2(enrollDate, room, prof)
values ('1-23-2006', 'HC2023', 'Leiter');

insert into BadDecomp2(enrollDate, room, prof)
values ('2-13-2006', 'HC2023', 'Bond');

insert into BadDecomp2(enrollDate, room, prof)
values ('1-23-2006', 'LT027' , 'Moneypenny');

insert into BadDecomp2(enrollDate, room, prof)
values ('1-12-2006', 'DN2112', 'Goodnight');

select *
from BadDecomp2;


--  And now for the bad part:
select *
from BadDecomp1 b1, 
     BadDecomp2 b2
where b1.enrollDate = b2.enrollDate;

-- Compare that to the original:
select *
from Enroll;

 
-- It gets worse:
-- Students taking a class from Prof. Leiter
select *
from BadDecomp1 b1, 
     BadDecomp2 b2
where b1.enrollDate = b2.enrollDate
  and b2.prof = 'Leiter'

-- Compare to the original:
select *
from Enroll
where prof = 'Leiter';