DROP TABLE IF EXISTS admin_test.Bibliothek;
DROP TABLE IF EXISTS admin_test.Person;
DROP TABLE IF EXISTS admin_test.Buch;

CREATE TABLE admin_test.Bibliothek (
BID int UNIQUE PRIMARY KEY,
Name varchar(100) NOT NULL,
Adresse varchar(100) NOT NULL,
UNIQUE(Name, Adresse),
Leiter varchar(100),
CONSTRAINT fk_Leiter FOREIGN KEY (Leiter) References Person(Vorname)
);

CREATE TABLE admin_test.Buch (
Title varchar(100) NOT NULL PRIMARY KEY,
ISBN INT NOT NULL UNIQUE KEY);

CREATE TABLE admin_test.Person (
Vorname varchar(100) NOT NULL,
Nachname varchar(100) NOT NULL,
PRIMARY KEY (Vorname, Nachname),
Geburtsdatum TIMESTAMP,
Wohnort varchar(100) NOT NULL,
Lieblingsbuch varchar(100),
CONSTRAINT fk_Lieblingsbuch FOREIGN KEY (Lieblingsbuch) REFERENCES admin_test.Buch(Title) );