DROP TABLE Student;
CREATE TABLE Student
(
 MatNr integer PRIMARY KEY CHECK ( MatNr > 9999 AND MatNr < 10000000),
 Name varchar(50)
);

DROP TABLE Course;
CREATE TABLE Course
(
 LVNr integer PRIMARY KEY,
 LVTitel varchar(100) 
);

DROP TABLE Teacher;
CREATE TABLE Teacher
(
 Name varchar(50) PRIMARY KEY
); 

DROP TABLE CourseInstance;
CREATE TABLE CourseInstance
(
 LVNr integer REFERENCES Course ( LVNr ),
 Semester char(6),
 LVLeiter varchar(50) REFERENCES Teacher ( Name ),
 PRIMARY KEY (LVNr,Semester)
);

DROP TABLE Grades;
CREATE TABLE Grades
(
 LVNr integer,
 Semester char(6),
 MatNr integer REFERENCES Student ( MatNr ),
 Note integer CHECK ( Note > 0 AND Note < 6),
 FOREIGN KEY (LVNr, Semester) REFERENCES CourseInstance ( LVNr, Semester ),
 PRIMARY KEY (LVNr, Semester, MatNr)
);