/* Erstelle Tabelle Person */
CREATE TABLE Person(
	PNR            int         PRIMARY KEY,
	Vorname        varchar(25) NOT NULL,
	Nachname       varchar(25) NOT NULL,
	Geburtsdatum   date        NOT NULL,
	Wohnort        int
);

/* Erstelle Tabelle Ort */
CREATE TABLE Ort(
	ONR            int         PRIMARY KEY,
	Ortname        varchar(50) NOT NULL,
	Buergermeister int,
	CONSTRAINT fk_ort_bmeister FOREIGN KEY (Buergermeister) REFERENCES Person(PNR)
);

/* ALTER TABLE wegen zirkulärer Abhängigkeit */
ALTER TABLE Person
	ADD CONSTRAINT fk_person_wohnort FOREIGN KEY (Wohnort) REFERENCES Ort(ONR);

/* Erstelle Tabelle Jugendherberge */
CREATE TABLE Jugendherberge(
	JNR            int         PRIMARY KEY,
	Name           varchar(50) NOT NULL,
	Ort            int         NOT NULL,
	Manager        int         NOT NULL,
	CONSTRAINT fk_herberge_ort     FOREIGN KEY (Ort)     REFERENCES Ort(ONR),
	CONSTRAINT fk_herberge_manager FOREIGN KEY (Manager) REFERENCES Person(PNR)
);

/* Erstelle Tabelle Aufenthalt */
CREATE TABLE Aufenthalt(
	Gast           int         NOT NULL,
	Jugendherberge int         NOT NULL,
	Preis          int         NOT NULL,
	CONSTRAINT pk_aufenthalt          PRIMARY KEY (Gast, Jugendherberge),
	CONSTRAINT fk_aufenthalt_gast     FOREIGN KEY (Gast)           REFERENCES Person(PNR),
	CONSTRAINT fk_aufenthalt_herberge FOREIGN KEY (Jugendherberge) REFERENCES Jugendherberge(JNR)
);

/* Beachte zirkulaere Abhaengigkeit zwischen Tabellen Person und Ort */
/* Beispieldaten fuer Tabelle Person */
/* Part I - Alle Attribute bis auf Fremdschlüssel auf Tabelle Ort */
INSERT INTO Person (PNR, Vorname, Nachname, Geburtsdatum) VALUES
	(1,"Edan","Burgess","19-08-24"),
	(2,"Galena","Gomez","18-03-22"),
	(3,"Oscar","Jacobs","19-07-29"),
	(4,"Halla","Tillman","18-02-10"),
	(5,"Nero","Henderson","18-02-18"),
	(6,"Cally","Sosa","18-05-27"),
	(7,"Quemby","Moreno","18-04-30"),
	(8,"Austin","Gomez","18-01-01"),
	(9,"Patrick","Scott","19-05-24"),
	(10,"Savannah","Mclaughlin","19-08-12"),
	(11,"Darius","Buckner","19-10-13"),
	(12,"Dacey","Cooke","19-02-04"),
	(13,"Madison","Jacobs","18-07-22"),
	(14,"Courtney","Dyer","18-01-25"),
	(15,"Fleur","Sampson","18-08-29"),
	(16,"Brandon","Bartlett","19-02-03"),
	(17,"Xavier","Kelly","18-03-05"),
	(18,"Mallory","Jacobs","18-01-24"),
	(19,"Bevis","Gamble","18-10-20"),
	(20,"Heidi","Williamson","18-06-26");

/* Beispieldaten fuer Tabelle Ort */
INSERT INTO Ort (ONR, Ortname, Buergermeister) VALUES
	(1,"Velaine-sur-Sambre",5),
	(2,"Quinte West",19),
	(3,"Eastbourne",11),
	(4,"Maracalagonis",6),
	(5,"Dublin",13),
	(6,"München",9);

/* Beispieldaten fuer Tabelle Person */
/* Part II - Updates zum Hinzufuegen des Fremdschlüssels auf Tabelle Ort */
UPDATE Person SET Wohnort = 1 WHERE PNR = 1;
UPDATE Person SET Wohnort = 1 WHERE PNR = 2;
UPDATE Person SET Wohnort = 5 WHERE PNR = 3;
UPDATE Person SET Wohnort = 6 WHERE PNR = 4;
UPDATE Person SET Wohnort = 1 WHERE PNR = 5;
UPDATE Person SET Wohnort = 4 WHERE PNR = 6;
UPDATE Person SET Wohnort = 5 WHERE PNR = 7;
UPDATE Person SET Wohnort = 4 WHERE PNR = 8;
UPDATE Person SET Wohnort = 6 WHERE PNR = 9;
UPDATE Person SET Wohnort = 3 WHERE PNR = 10;
UPDATE Person SET Wohnort = 3 WHERE PNR = 11;
UPDATE Person SET Wohnort = 6 WHERE PNR = 12;
UPDATE Person SET Wohnort = 5 WHERE PNR = 13;
UPDATE Person SET Wohnort = 3 WHERE PNR = 14;
UPDATE Person SET Wohnort = 4 WHERE PNR = 15;
UPDATE Person SET Wohnort = 4 WHERE PNR = 16;
UPDATE Person SET Wohnort = 2 WHERE PNR = 17;
UPDATE Person SET Wohnort = 1 WHERE PNR = 18;
UPDATE Person SET Wohnort = 2 WHERE PNR = 19;
UPDATE Person SET Wohnort = 2 WHERE PNR = 20;

/* Beispieldaten fuer Tabelle Jugendherberge */
INSERT INTO Jugendherberge (JNR, Name, Ort, Manager) VALUES
	(1,"Jugendherberge Dublin",5,18),
	(2,"Herberge Great Lakes",2,11),
	(3,"Ontario Hostel",2,8),
	(4,"Hostel Wilson Island",2,14),
	(5,"Herberge Green Meadows",5,13),
	(6,"Jugendherberge München",6,16),
	(7,"Herberge Dolce Vita",4,6),
	(8,"Herberge Alpenland",6,6),
	(9,"Barcovan Beach Shelter",2,4),
	(10,"Hostel-by-the-Sea",3,20),
	(11,"Jugendherberge Quinte East",2,18),
	(12,"Shelter Maritime",3,13),
	(13,"Corsica Hostel",4,16),
	(14,"Trent River Lodging House",2,14),
	(15,"Wellers Bay Wildlife Hostel",2,19),
	(16,"Studentisches Reisegaststaette",6,14),
	(17,"Stolen Pot of Gold",5,14),
	(18,"Hostel Bella Italia",4,14),
	(19,"The Prancing Pony",5,14),
	(20,"L'Hôtel d'aventure",1,14);

/* Beispieldaten fuer Tabelle Aufenhalt */
INSERT INTO Aufenthalt (Gast, Jugendherberge, Preis) VALUES
	(8,1,43),
	(13,1,155),
	(12,4,14),
	(20,4,78),
	(5,7,116),
	(14,7,199),
	(10,14,48),
	(20,11,132),
	(3,13,7),
	(16,5,49),
	(15,13,216),
	(9,6,286),
	(1,15,257),
	(8,8,231),
	(14,10,223),
	(20,14,18),
	(19,2,178),
	(15,10,84),
	(7,6,110),
	(2,11,277),
	(20,5,218),
	(18,12,213),
	(10,13,247),
	(5,15,191),
	(3,11,187),
	(6,7,49),
	(9,2,122),
	(3,15,294),
	(6,1,109),
	(14,6,140),
	(9,14,273),
	(13,3,147),
	(14,3,285),
	(16,3,178),
	(9,4,250),
	(11,12,249),
	(10,10,70),
	(17,5,193),
	(8,13,114),
	(16,11,66),
	(8,6,152),
	(8,7,11),
	(13,2,178),
	(18,11,220),
	(10,11,156),
	(12,11,43),
	(9,8,237),
	(6,5,153),
	(4,8,281),
	(15,2,78),
	(5,14,106),
	(11,14,141),
	(8,11,233),
	(3,10,232),
	(20,13,231);

/*
DROP TABLE Aufenthalt;
DROP TABLE Jugendherberge;
ALTER TABLE Person DROP FOREIGN KEY fk_person_wohnort;
DROP TABLE Ort;
DROP TABLE Person;
*/