use `db_am1_klein`;

DROP TABLE IF EXISTS `Schueler`;
DROP TABLE IF EXISTS `Klassen`;
DROP TABLE IF EXISTS `Leihgabe`;
DROP TABLE IF EXISTS `Buecher`;

CREATE TABLE `Buecher`(
	`ISBN` VARCHAR(15) NOT NULL,
	`Buchtitel` VARCHAR(30),
	PRIMARY KEY(`ISBN`)
) ENGINE  = InnoDB;

CREATE TABLE `Klassen`(
	`Klassenname` VARCHAR(3) NOT NULL,
	`Klassenlehrer` VARCHAR(30),
	PRIMARY KEY(`Klassenname`)
) ENGINE  = InnoDB;

CREATE TABLE `Schueler`(
	`Schuelernummer` SMALLINT NOT NULL,
	`Name` VARCHAR(30),
	`Vorname` VARCHAR(20),
	`Strasse` VARCHAR(25),
	`PLZ` VARCHAR(5),
	`Ort` VARCHAR(20),
	`Klassenname` VARCHAR(3),
	PRIMARY KEY(`Schuelernummer`),
	FOREIGN KEY(`Klassenname`) REFERENCES `Klassen`(`Klassenname`)
) ENGINE  = InnoDB;

CREATE TABLE `Leihgabe`(
	`Schuelernummer` SMALLINT NOT NULL,
	`Leihnummer` SMALLINT NOT NULL,
	`ISBN` VARCHAR(15),
	PRIMARY KEY(`Schuelernummer`, `Leihnummer`),
	FOREIGN KEY(`Schuelernummer`) REFERENCES `Schueler`(`Schuelernummer`),
	FOREIGN KEY(`ISBN`) REFERENCES `Buecher`(`ISBN`)
) ENGINE  = InnoDB;







