drop table lehrer;
drop table fach;
drop table lehrbefugnis;
drop table klasse;
drop table schueler;
drop table unterricht;
drop table zeugnis;

create table lehrer (
  name varchar(40) not null,
  gebdat date,
  adresse varchar(80),
  gehalt integer,
  primary key(name)
);

create table fach (
  fach varchar(40) not null,
  primary key (fach)
);

create table lehrbefugnis (
  lehrername varchar(40) not null,
  fach varchar(40) not null,
  primary key (lehrername,fach),
  foreign key (lehrername) references lehrer(name),
  foreign key (fach) references fach
);

create table klasse (
  jahrgang integer not null,
  buchst char not null,
  klassenlehrer varchar(40) not null,
  klassensprecher varchar(40),
  primary key (jahrgang,buchst),
  foreign key (klassenlehrer) references lehrer(name)
);

create table schueler (
  name varchar(40) not null,
  gebdat date,
  adresse varchar(80),
  jahrgang integer not null,
  buchst char not null,
  primary key (name),
  foreign key (jahrgang,buchst) references klasse
);

create table unterricht (
  jahrgang integer not null,
  buchst char not null,
  lehrername varchar(40) not null,
  fach varchar(40) not null,
  stunden integer,
  primary key (jahrgang,buchst,fach),
  foreign key (jahrgang,buchst) references klasse(jahrgang,buchst),
  foreign key (lehrername,fach) references lehrbefugnis(lehrername,fach)
);

create table zeugnis (
  schuelername varchar(40) not null,
  fach varchar(40) not null,
  note char(10),
  primary key (schuelername,fach),
  foreign key (schuelername) references schueler(name),
  foreign key (fach) references fach
);

alter table klasse 
  add foreign key (klassensprecher) references schueler(name);