-- 9
CREATE TABLE Chicken (ID INT PRIMARY KEY,
	eID INT REFERENCES Egg(ID));
CREATE TABLE Egg(ID INT PRIMARY KEY,
	cID INT REFERENCES Chicken(ID));

  
-- 13
CREATE TABLE Students
( ID            INTEGER,
  Name          VARCHAR(20),
  Major         VARCHAR(10),
  GPA           DECIMAL(3,2),
  CONSTRAINT NStudents_PK
        PRIMARY KEY (ID)
);

CREATE TABLE Enrollment
( Stud_ID       INTEGER,
  Course        VARCHAR(15),
  CONSTRAINT NEnrollment_FK
        FOREIGN KEY (Stud_ID) REFERENCES Students(ID)
        ON DELETE CASCADE
);


select * from students;
select * from enrollment;
start transaction;
insert into students values (2, 'John', 'Chem', 3.0);
insert into enrollment values (2, 'Chem 0400');
commit;
select * from students;
select * from enrollment;
start transaction;
insert into students values (2, 'John', 'Chem', 3.0);
rollback;
select * from students;
select * from enrollment;

-- 15

CREATE TABLE Students
( ID            INTEGER,
  Name          VARCHAR(20),
  Major         VARCHAR(10),
  GPA           DECIMAL(3,2),
  CONSTRAINT NStudents_PK
        PRIMARY KEY (ID)
);
CREATE TABLE Enrollment
( Stud_ID       INTEGER,
  Course        VARCHAR(15),
  CONSTRAINT NEnrollment_FK
        FOREIGN KEY (Stud_ID) REFERENCES Students(ID)
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED
);

select * from students;
select * from enrollment;
start transaction;
insert into enrollment values (4, 'Chem 0400');
insert into students values (4, 'John', 'Chem', 3.0);
select * from students;
select * from enrollment;
commit;
start transaction;
insert into enrollment values (5, 'Chem 0400');
select * from students;
select * from enrollment;
commit;
select * from students;
select * from enrollment;
start transaction;
delete from students where id=4;
select * from students;
select * from enrollment;
commit;

-- 16
CREATE TABLE Chicken (ID INT PRIMARY KEY, eID INT);
CREATE TABLE Egg(ID INT PRIMARY KEY, cID INT);
ALTER TABLE Chicken ADD CONSTRAINT Chicken_FK
		FOREIGN KEY (eID) REFERENCES Egg(ID)
		DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE Egg ADD CONSTRAINT Egg_FK 
		FOREIGN KEY (cID) REFERENCES Chicken(ID)
		DEFERRABLE INITIALLY IMMEDIATE;


insert into chicken values (1, 2);

start transaction;
set constraints all deferred;
insert into chicken values (1, 2);
select * from chicken;
select * from egg;
insert into egg values (2, 1);
commit;

select * from chicken;
select * from egg;
drop table chicken;
drop table chicken cascade;
drop table egg;

SELECT (null OR false) AND ( null  AND null);
SELECT (null OR false) AND ( true  AND null);

SELECT (null OR false) AND ( false  AND null);
