/* When the user Gradebook is created, the default TABLESPACE is
   defined. Therefore no tablespace specification is necessary
   when the tables are created in Gradebook account.
*/

 CREATE SEQUENCE seqClassID
	INCREMENT BY 1
        START WITH   2000 NOCYCLE;
/

 CREATE TABLE Majors (
        Code        NUMBER(5) NOT NULL,
        Major       VARCHAR2(30),
        shortName   VARCHAR2(10),
	CONSTRAINT  pk_majors PRIMARY KEY (Code)
     )
/

 CREATE TABLE Students (
	SID	     NUMBER(9, 0) NOT NULL,
 	LASTNAME     VARCHAR2(20) NOT NULL,
 	FIRSTNAME    VARCHAR2(25) NOT NULL,
	ACTIVE	     VARCHAR2(1) DEFAULT 'Y',
	OSUserName   VARCHAR2(15) default '' , 
	MajorCode    NUMBER(5) ,
	CONSTRAINT   fk_students_majors FOREIGN KEY (MajorCode)
				      REFERENCES Majors(Code),
	CONSTRAINT   pk_students PRIMARY KEY (SID)
    )
	PCTFREE 5
	PCTUSED 10
--	INITRANS 1  -- Should not be changed from its default
--	MAXTRANS 1  -- Should not be changed from its default
/

 CREATE TABLE Instructors (
	IID		NUMBER (9, 0),
	NAME		VARCHAR2(10) NOT NULL UNIQUE,
 	Password        VARCHAR2(10) NOT NULL,
 	FTPString       VARCHAR2(50) Default 'C:\Program Files\Exceed.nt\ftp.exe',
	CONSTRAINT pk_instructors PRIMARY KEY (IID)
     )
	-- physical attribute clause
	PCTFREE 5
	PCTUSED 10
/
 CREATE TABLE Classes (
	CID	      NUMBER(9,0)	  NOT NULL,
	IID	      NUMBER(9,0)	  NOT NULL,
 	YEAR          NUMBER(4,0)    NOT NULL,
 	QUARTER       VARCHAR2(8)  NOT NULL,
 	CNAME	      VARCHAR2(10) NOT NULL,
 	SECTION       NUMBER(2,0)    DEFAULT 1 NOT NULL,
	CONSTRAINT pk_Classes PRIMARY KEY (CID),
	CONSTRAINT unique_classes_UQNS unique (year, quarter, cName, section),
	CONSTRAINT fk_Classes_Instructors FOREIGN KEY (IID)
				      REFERENCES Instructors(IID)
				      ON DELETE CASCADE
     )
	PCTFREE 5
	PCTUSED 10
/
 CREATE TABLE Grades (
	SID	      NUMBER(9,0)    NOT NULL,
	CID	      NUMBER(9,0)    NOT NULL,
 	HW1           NUMBER(5,2) DEFAULT 0, 
 	HW2           NUMBER(5,2) DEFAULT 0, 
	HW3           NUMBER(5,2) DEFAULT 0, 
 	HW4           NUMBER(5,2) DEFAULT 0, 
 	HW5           NUMBER(5,2) DEFAULT 0, 
 	HW6           NUMBER(5,2) DEFAULT 0, 
 	HW7           NUMBER(5,2) DEFAULT 0, 
 	HW8           NUMBER(5,2) DEFAULT 0, 
 	HW9           NUMBER(5,2) DEFAULT 0, 
 	HW10          NUMBER(5,2) DEFAULT 0, 
 	HW11          NUMBER(5,2) DEFAULT 0, 
 	HW12          NUMBER(5,2) DEFAULT 0, 
 	HW13          NUMBER(5,2) DEFAULT 0, 
 	HW14          NUMBER(5,2) DEFAULT 0, 
 	HW15          NUMBER(5,2) DEFAULT 0, 
 	HW16          NUMBER(5,2) DEFAULT 0, 
 	HW17          NUMBER(5,2) DEFAULT 0, 
 	HW18          NUMBER(5,2) DEFAULT 0, 
 	HW19          NUMBER(5,2) DEFAULT 0, 
 	HW20          NUMBER(5,2) DEFAULT 0, 
 	MIDTERM1      NUMBER(5,2) DEFAULT 0, 
 	MIDTERM2      NUMBER(5,2) DEFAULT 0, 
 	FINAL         NUMBER(5,2) DEFAULT 0, 
 	TOTAL         NUMBER(5,2) DEFAULT 0, 
 	GRADE         VARCHAR2(2) DEFAULT '-' NOT NULL, 
 	RANKING       NUMBER(3)   DEFAULT 0, 
 	COMMENTS      VARCHAR2(1000),
	-- table constraints
	CONSTRAINT pk_grades PRIMARY KEY (SID, CID),
	CONSTRAINT fk_grades_students FOREIGN KEY (SID)
				      REFERENCES students(SID)
				      ON DELETE CASCADE,
	CONSTRAINT fk_grades_Classes FOREIGN KEY (CID)
				      REFERENCES classes(CID)
				      ON DELETE CASCADE
     )
	PCTFREE 5
	PCTUSED 10
--	INITRANS 1  -- Should not be changed from its default
--	MAXTRANS 1  -- Should not be changed from its default
/

 CREATE TABLE Policy (
	CID	NUMBER(9, 0)	NOT NULL,
 	NumHWK	NUMBER(2, 0)	DEFAULT 10     NOT NULL CHECK(NumHWK >= 0),
 	MaxHWK	NUMBER(3, 0)	DEFAULT 100    NOT NULL,
	PCTHWK	NUMBER(3, 0)	DEFAULT 40  NOT NULL,
	HWKDrops NUMBER(2, 0)	DEFAULT 0  NOT NULL,
 	MaxMidterm1  NUMBER(3, 0)	DEFAULT  100 NOT NULL,
 	PCTMidterm1  NUMBER(3, 0)	DEFAULT  30 NOT NULL,
 	MaxMidterm2  NUMBER(3, 0)	DEFAULT  100 NOT NULL,
 	PCTMidterm2  NUMBER(3, 0)	DEFAULT  0 NOT NULL,
 	MaxFinal     NUMBER(3, 0)	DEFAULT  100 NOT NULL,
 	PCTFinal     NUMBER(3, 0)	DEFAULT  30 NOT NULL,
	CONSTRAINT pk_Policy 	PRIMARY KEY (CID),
	CONSTRAINT fk_Policy_Classes FOREIGN KEY (CID)
				     REFERENCES classes(CID)
					ON DELETE CASCADE 
    )
	-- physical attribute clause
	PCTFREE 5
	PCTUSED 10
--	INITRANS 1  -- Should not be changed from its default
--	MAXTRANS 1  -- Should not be changed from its default
/
