V6pV5TTe

1DROP TABLE IF EXISTS course, student, teacher, exam CASCADE;
2
3
4CREATE TABLE teacher (
5    PPSNO 		VARCHAR(30),
6    FirstName 	VARCHAR(35) 	NOT NULL,
7    SurName 	VARCHAR(50) 	NOT NULL,
8    Sex 		CHAR(1) 		DEFAULT 'M',
9    
10    PRIMARY KEY (PPSNO),
11    CONSTRAINT SEX_CHECK CHECK (Sex = 'M' OR Sex = 'F')
12);
13
14CREATE TABLE course (
15	ID 			INT 			NOT NULL AUTO_INCREMENT,
16    Name 		VARCHAR(200) 	NOT NULL,
17    TeacherID 	VARCHAR(30),
18    
19    
20    PRIMARY KEY (ID),
21    FOREIGN KEY (TeacherID) REFERENCES teacher(PPSNO)
22);
23
24CREATE TABLE student (
25	PPSNO 		VARCHAR(30),
26    FirstName 	VARCHAR(35) 	NOT NULL,
27    SurName 	VARCHAR(50) 	NOT NULL,
28    Sex 		CHAR(1) 		DEFAULT "M",
29    Course 		INT 			NOT NULL,
30    
31    PRIMARY KEY (PPSNO),
32    FOREIGN KEY (Course) REFERENCES course(ID),
33    CONSTRAINT SEX_CHECK CHECK (Sex = "M" OR Sex = "F")
34);
35
36CREATE TABLE exam (
37	ID 		INT 			NOT NULL AUTO_INCREMENT,
38    Course 	INT 			NOT NULL,
39    Teacher VARCHAR(30) 	NOT NULL,
40    Name 	VARCHAR(100) 	NOT NULL,
41    
42    PRIMARY KEY (ID),
43    FOREIGN KEY (Teacher) REFERENCES teacher(PPSNO),
44    FOREIGN KEY (Course) REFERENCES course(ID)
45);