CREATE TABLE professor (ID Integer, 
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Office VARCHAR(50),
    PRIMARY KEY (ID));

CREATE TABLE student (ID Integer, 
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    PRIMARY KEY (ID));

CREATE TABLE lecture (ID Integer, 
    Title VARCHAR(50),
    Credits Integer,
    Room VARCHAR(50),
    PRIMARY KEY (ID));

CREATE TABLE attends (Student Integer, 
    Lecture Integer,
    FOREIGN KEY (Student) REFERENCES student(ID),
    FOREIGN KEY (Lecture) REFERENCES lecture(ID),
    PRIMARY KEY (Student, Lecture));

CREATE TABLE teaches (Professor Integer, 
    Lecture Integer,
    FOREIGN KEY (Professor) REFERENCES professor(ID),
    FOREIGN KEY (Lecture) REFERENCES lecture(ID),
    PRIMARY KEY (Professor, Lecture));

INSERT INTO professor (ID, FirstName, LastName, Office) 
VALUES (1, 'Julia', 'Maier', 'G12.42');

INSERT INTO lecture (ID, Title, Credits, Room) 
VALUES (1, 'Datenbanken', 6, 'HS10');

INSERT INTO student (ID, FirstName, LastName) 
VALUES (1, 'Max', 'Mustermann');

INSERT INTO teaches (Professor, Lecture) 
VALUES (1, 1);

INSERT INTO attends (Student, Lecture) 
VALUES (1, 1);

-- Contraint Violation (Default NO ACTION)

DELETE FROM lecture WHERE ID = 1;

DELETE FROM professor WHERE ID = 1;

DELETE FROM student WHERE ID = 1;

-- No Contraint Violation (Default NO ACTION)

DELETE FROM attends WHERE Student = 1;
DELETE FROM student WHERE ID = 1;

-- Change attends table to CASCADE (CAREFUL!)

ALTER TABLE attends DROP CONSTRAINT attends_student_fkey;

ALTER TABLE attends ADD CONSTRAINT attends_student_fkey 
FOREIGN KEY (Student) REFERENCES student(ID) 
ON DELETE CASCADE ON UPDATE CASCADE;

-- Update/DELETE (CASCADE)

UPDATE student SET ID=2 WHERE FirstName = 'Max' and LastName='Mustermann';

DELETE FROM student WHERE ID = 2;

SELECT geonameid, asciiname, country
FROM geoname
WHERE name = 'Wuppertal';
-- Cost 6644.81, Time 28.923ms
CREATE INDEX idx_name ON geoname (name);
-- Time 699ms, Size 5328kB
SELECT geonameid, asciiname, country
FROM geoname
WHERE name = 'Wuppertal';
-- Cost 12.45, Time 0.027ms


SELECT geonameid, asciiname, name
FROM geoname
WHERE country = 'DE';
-- Cost 7122.27, Time 54.128 ms
CREATE INDEX idx_country ON geoname (country);
-- Time 309ms, Size 1376 kB
SELECT geonameid, asciiname, name
FROM geoname
WHERE country = 'DE';
-- Cost 522.75, Time 11.403 ms
CLUSTER verbose  geoname using idx_country;
-- 4644 pages, Time 1245 ms
SELECT geonameid, asciiname, name
FROM geoname
WHERE country = 'DE';
-- Cost 522.75, Time 4.475 ms