|
|
CREATE TABLE STUDENT ( STUDENT_ID INT PRIMARY KEY, LASTNAME VARCHAR(40) NOT NULL, FIRSTNAME VARCHAR(40) NOT NULL, GENDER VARCHAR(1) NOT NULL, CONSTRAINT CHK_STUDENT_ID CHECK (STUDENT_ID>=100000 AND STUDENT_ID<=999999), CONSTRAINT CHK_GENDER CHECK (GENDER='m' OR GENDER='f'));CREATE TABLE EXAM ( STUDENT_ID INT, COURSE VARCHAR(40), GRADE DECIMAL(2,1), ATTEMPT INT, CONSTRAINT PK PRIMARY KEY(STUDENT_ID, ATTEMPT, COURSE), CONSTRAINT CHK_ATTEMPT CHECK (ATTEMPT IN (1,2,3)), CONSTRAINT CHK_GRADE CHECK ( GRADE IN (1.0,1.3,1.7,2.0,2.3,2.7,3.0,3.3,3.7,4.0,5.0) ), CONSTRAINT FK_STUDENT_ID FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(STUDENT_ID) ON DELETE CASCADE);
|
