CREATE TABLE STUDENTS 
  ( StudentID       		CHAR(4) NOT NULL,
   First      		VARCHAR(15) NOT NULL,
    Middle  		CHAR,
    Last      		VARCHAR(15) NOT NULL,
   YEAR    		CHAR(2) DEFAULT 'Fr' NOT NULL ,
    MAJOR 		CHAR(4) DEFAULT 'Und',
    NumCreditsEarned	INT DEFAULT 0,
    GPA       		DECIMAL(4,3),
    HOMETOWN 		VARCHAR(20),
    BALANCEDue  		DECIMAL(7,2) DEFAULT 0,
  CONSTRAINT STDPK
       PRIMARY KEY (StudentID)    );


CREATE TABLE CatalogCourses
 (Dept CHAR(4) NOT NULL,
 Course CHAR(3) NOT NULL,
 Title VARCHAR(30),
 Description VARCHAR(100),
 Credits INT DEFAULT 3,
CONSTRAINT CCPK
        PRIMARY KEY (Dept,Course)   
);


CREATE TABLE Prerequisites 
 ( Prog CHAR(4) NOT NULL,
   Class CHAR(3) NOT NULL,
   PreProg CHAR(4) NOT NULL,
   PreClass CHAR(3) NOT NULL,
CONSTRAINT PrePK
        PRIMARY KEY (Prog, Class, PreProg, PreClass),
   CONSTRAINT PreCourse
         FOREIGN KEY (PreProg, PreClass) REFERENCES CatalogCourses(Dept,Course)
	 ON DELETE  CASCADE,
   CONSTRAINT PostCourse
         FOREIGN KEY (Prog, Class) REFERENCES CatalogCourses(Dept,Course)
	 ON DELETE  CASCADE
);


CREATE TABLE Professors
 ( Name CHAR(15) NOT NULL,
   Dept CHAR(4) NOT NULL,
   Position VARCHAR(10),
   office CHAR(5),
   phone CHAR(4),
   email CHAR(8),
CONSTRAINT PrPK
        PRIMARY KEY (Name)
);







CREATE TABLE Rooms
(room           CHAR(8) NOT NULL,
 capacity       INT DEFAULT 30 NOT NULL,
 FrontComputer  CHAR DEFAULT 'N',
 DataConnection CHAR DEFAULT 'N',
 DataProjector  CHAR DEFAULT 'N',
 Overhead       CHAR DEFAULT 'N',
 NumbStudentWorkstations INT DEFAULT 0,
CONSTRAINT RmPK
        PRIMARY KEY (room)
);


CREATE TABLE Sections
 (sectionID  CHAR(5) NOT NULL,
  Dept       CHAR(4) NOT NULL,
  Course     CHAR(3) NOT NULL,
  Section    CHAR(2) NOT NULL,
  Sem        CHAR(2) NOT NULL,
  Year       INT NOT NULL,
  time       CHAR(8),
  room       CHAR(8),
  professor  CHAR(15),
  stoppoint  INT,
  junk       INT,
CONSTRAINT SctPK
        PRIMARY KEY (sectionID),
   CONSTRAINT SECTCC
         FOREIGN KEY (Dept, Course) REFERENCES CatalogCourses(Dept,Course)
	ON DELETE CASCADE  ,
   CONSTRAINT SECTRM
         FOREIGN KEY (room) REFERENCES Rooms(room)
	ON DELETE CASCADE  ,
   CONSTRAINT SECTPROF
         FOREIGN KEY (professor) REFERENCES Professors(Name)
	ON DELETE CASCADE  
	);
 

CREATE TABLE enrollments
   (sectionID            CHAR(5) NOT NULL,
    studentID            CHAR(4) NOT NULL,
    specialpermission    CHAR(8),
    grade                CHAR(2),
   CONSTRAINT ENRLPK
          PRIMARY KEY(sectionID,StudentID),
   CONSTRAINT ENRLSECT
         FOREIGN KEY (sectionID) REFERENCES SECTIONS(sectionID)
	ON DELETE CASCADE      ,
  CONSTRAINT ENRLSTUD
         FOREIGN KEY (studentID) REFERENCES STUDENTS(StudentID)
	ON DELETE CASCADE         );
