DROP TABLE "DB".Student;
DROP TABLE "DB".Vorlesung;
DROP TABLE "DB".Belegung;
DROP TABLE "DB".Lehrkraft;
DROP TABLE "DB".Veranstaltung;

create TABLE "DB".Student
       (Nummer INTEGER ,
       Person INTEGER ,
       Semesteranzahl INTEGER ,
       FBNummer INTEGER );

insert into "DB".Student values (1,1,8, 10);
insert INTO "DB".Student values (2,2,10, 20);
insert INTO "DB".Student values (3,3,12, 20);

CREATE TABLE "DB".vorlesung
       (Nummer INTEGER ,
       Titel varchar(30),
       Veranstalter INTEGER );

INSERT INTO "DB".Vorlesung VALUES (1,'DB',4);
INSERT INTO "DB".Vorlesung VALUES (2,'Mathe',5);
INSERT INTO "DB".Vorlesung VALUES (3,'BWL',5);

CREATE TABLE "DB".Belegung
       (Student INTEGER ,
       Vorlesung INTEGER ,
       Semester varchar(20),
       Note INTEGER ,
       Lesender INTEGER );

INSERT INTO "DB".Belegung VALUES (1,1, 'WS 03/04',5,4);
INSERT INTO "DB".Belegung VALUES (1,2, 'WS 03/04',1,4);
INSERT INTO "DB".Belegung VALUES (2,1, 'WS 03/04',1,5);
INSERT INTO "DB".Belegung VALUES (2,2, 'WS 03/04',1,4);
INSERT INTO "DB".Belegung VALUES (3,2, 'WS 03/04',3,5);
INSERT INTO "DB".Belegung VALUES (1,1, 'SS 04',2,5);

create TABLE "DB".Lehrkraft
       (Nummer INTEGER ,
       Person INTEGER ,
       FBNummer INTEGER ,
       Spezialis varchar(30));

insert INTO "DB".Lehrkraft values (4,4,10,'BWL');
insert INTO "DB".Lehrkraft values (5,5,20,'DB');
insert INTO "DB".Lehrkraft values (6,6,20,'DB');

create TABLE "DB".Veranstaltung
       (Veranstalter INTEGER ,
       Vorlesung INTEGER );

insert INTO "DB".Veranstaltung values (1,1);
insert INTO "DB".Veranstaltung values (1,2);
insert INTO "DB".Veranstaltung values (2,1);
insert INTO "DB".Veranstaltung values (2,2);
insert INTO "DB".Verantstaltung values (3,2);

create TABLE "DB".Person
       (Nummer INTEGER ,
       Name varchar(30));

insert INTO "DB".Person values (1,'Schmidt');
insert INTO "DB".Person values (2,'Meier');
insert INTO "DB".Person values (3,'Schulze');
insert INTO "DB".Person values (4,'Gerste');
insert INTO "DB".Person  values (5,'Thalheim');
insert INTO "DB".Person values (6,'Klein');


insert INTO "DB".Student values (4,5,50, 10);