forked from Christian.Pape/gsd-questions
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
23 lines
701 B
23 lines
701 B
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
|
|
);
|