DROP TABLE Gehege CASCADE CONSTRAINTS;
DROP TABLE Tier CASCADE CONSTRAINTS;
DROP TABLE Art CASCADE CONSTRAINTS;

CREATE TABLE Gehege(
  GNr INTEGER,
  Gname VARCHAR(6),
  Flaeche INTEGER,
  PRIMARY KEY(GNR)
);

CREATE TABLE Art(
  Gattung VARCHAR(7),
  MinFlaeche INTEGER,
  PRIMARY KEY (Gattung)
);

CREATE TABLE Tier(
  GNr INTEGER,
  Tname VARCHAR(8),
  Gattung VARCHAR(7),
  PRIMARY KEY (GNr, Tname),
  CONSTRAINT FTier1 FOREIGN KEY(GNr) REFERENCES Gehege(GNr),
  CONSTRAINT FTier2 FOREIGN KEY(Gattung) REFERENCES Art(Gattung)
);

INSERT INTO Gehege VALUES(1,'Wald',20);
INSERT INTO Gehege VALUES(2,'Feld',10);
INSERT INTO Gehege VALUES(3,'Weide',9);

INSERT INTO Art VALUES('Baer',8);
INSERT INTO Art VALUES('Hase',2);
INSERT INTO Art VALUES('Schaf',5);

INSERT INTO Tier VALUES(1,'Laber','Baer');
INSERT INTO Tier VALUES(1,'Sabber','Baer');
INSERT INTO Tier VALUES(2,'Klopfer','Hase');
INSERT INTO Tier VALUES(3,'Bunny','Hase');
INSERT INTO Tier VALUES(2,'Harald','Schaf');
INSERT INTO Tier VALUES(3,'Walter','Schaf');

COMMIT;
SELECT * FROM Gehege;
SELECT * FROM Tier;
SELECT * FROM Art;