PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE students (
  s_id         INTEGER,
  s_name       TEXT,
  gpa          REAL,
  PRIMARY KEY  (s_id)
);
INSERT INTO students VALUES(123,'Amy',3.9);
INSERT INTO students VALUES(234,'Bob',3.6);
INSERT INTO students VALUES(345,'Craig',3.5);
INSERT INTO students VALUES(456,'Doris',3.9);
INSERT INTO students VALUES(543,'Craig',3.4);
INSERT INTO students VALUES(567,'Edward',2.9);
INSERT INTO students VALUES(654,'Amy',3.9);
INSERT INTO students VALUES(678,'Fay',3.8);
INSERT INTO students VALUES(765,'Jay',2.9);
INSERT INTO students VALUES(789,'Gary',3.4);
INSERT INTO students VALUES(876,'Irene',3.9);
INSERT INTO students VALUES(987,'Helen',3.7);
CREATE TABLE colleges (
  c_name       TEXT,
  state        TEXT,
  enrollment   INT,
  PRIMARY KEY  (c_name)
);
INSERT INTO colleges VALUES('Stanford','CA',15000);
INSERT INTO colleges VALUES('Berkeley','CA',36000);
INSERT INTO colleges VALUES('MIT','MA',10000);
INSERT INTO colleges VALUES('Cornell','NY',21000);
CREATE TABLE majors (
  major        TEXT,
  full_name    TEXT,
  credits      TEXT,
  PRIMARY KEY  (major)
);
INSERT INTO majors VALUES('CS','Computer Science','300');
INSERT INTO majors VALUES('EE','Electrical Engineering','300');
INSERT INTO majors VALUES('bioengineering','Bioengineering','300');
INSERT INTO majors VALUES('biology','Biology','300');
INSERT INTO majors VALUES('history','History','300');
INSERT INTO majors VALUES('marine biology','Marine Biology','300');
INSERT INTO majors VALUES('psychology','Psychology','300');
CREATE TABLE programs (
  major        TEXT,
  c_name       TEXT,
  enrollment   INT,
  PRIMARY KEY  (major, c_name),
  FOREIGN KEY  (major) REFERENCES majors(major),
  FOREIGN KEY  (c_name) REFERENCES colleges(c_name)
);
INSERT INTO programs VALUES('CS','Berkeley',6);
INSERT INTO programs VALUES('CS','Cornell',3);
INSERT INTO programs VALUES('CS','MIT',5);
INSERT INTO programs VALUES('CS','Stanford',6);
INSERT INTO programs VALUES('EE','Cornell',3);
INSERT INTO programs VALUES('EE','Stanford',5);
INSERT INTO programs VALUES('bioengineering','Cornell',5);
INSERT INTO programs VALUES('bioengineering','MIT',3);
INSERT INTO programs VALUES('biology','Berkeley',5);
INSERT INTO programs VALUES('biology','MIT',3);
INSERT INTO programs VALUES('history','Cornell',5);
INSERT INTO programs VALUES('history','Stanford',6);
INSERT INTO programs VALUES('marine biology','MIT',5);
INSERT INTO programs VALUES('psychology','Cornell',3);
CREATE TABLE applications (
  s_id         INTEGER,
  c_name       TEXT,
  major        TEXT,
  decision     CHAR DEFAULT ('N'),
  PRIMARY KEY  (s_id, c_name, major),
  FOREIGN KEY  (s_id) REFERENCES students(s_id),
  FOREIGN KEY  (major, c_name) REFERENCES programs(major, c_name)
);
INSERT INTO applications VALUES(123,'Stanford','CS','Y');
INSERT INTO applications VALUES(123,'Stanford','EE','N');
INSERT INTO applications VALUES(123,'Berkeley','CS','Y');
INSERT INTO applications VALUES(123,'Cornell','EE','Y');
INSERT INTO applications VALUES(234,'Berkeley','biology','N');
INSERT INTO applications VALUES(345,'MIT','bioengineering','Y');
INSERT INTO applications VALUES(345,'Cornell','bioengineering','N');
INSERT INTO applications VALUES(345,'Cornell','CS','Y');
INSERT INTO applications VALUES(345,'Cornell','EE','N');
INSERT INTO applications VALUES(678,'Stanford','history','Y');
INSERT INTO applications VALUES(987,'Stanford','CS','Y');
INSERT INTO applications VALUES(987,'Berkeley','CS','Y');
INSERT INTO applications VALUES(876,'Stanford','CS','Y');
INSERT INTO applications VALUES(876,'MIT','biology','Y');
INSERT INTO applications VALUES(876,'MIT','marine biology','N');
INSERT INTO applications VALUES(765,'Stanford','history','Y');
INSERT INTO applications VALUES(765,'Cornell','history','N');
INSERT INTO applications VALUES(765,'Cornell','psychology','Y');
INSERT INTO applications VALUES(543,'MIT','CS','N');
INSERT INTO applications VALUES(234,'Stanford','CS','N');
INSERT INTO applications VALUES(345,'Stanford','CS','N');
INSERT INTO applications VALUES(456,'Stanford','CS','Y');
INSERT INTO applications VALUES(543,'Stanford','CS','N');
INSERT INTO applications VALUES(567,'Stanford','CS','N');
INSERT INTO applications VALUES(654,'Stanford','CS','Y');
INSERT INTO applications VALUES(678,'Stanford','CS','Y');
INSERT INTO applications VALUES(765,'Stanford','CS','N');
INSERT INTO applications VALUES(789,'Stanford','CS','N');
INSERT INTO applications VALUES(123,'MIT','CS','N');
INSERT INTO applications VALUES(234,'MIT','CS','N');
INSERT INTO applications VALUES(345,'MIT','CS','N');
INSERT INTO applications VALUES(456,'MIT','CS','N');
INSERT INTO applications VALUES(567,'MIT','CS','N');
INSERT INTO applications VALUES(654,'MIT','CS','N');
INSERT INTO applications VALUES(678,'MIT','CS','N');
INSERT INTO applications VALUES(765,'MIT','CS','N');
INSERT INTO applications VALUES(789,'MIT','CS','N');
INSERT INTO applications VALUES(876,'MIT','CS','N');
INSERT INTO applications VALUES(987,'MIT','CS','N');
COMMIT;
