-- Created by Mateusz Pawlik (2014)

-- Table definitions

DROP TABLE IF EXISTS Fachbereiche CASCADE;
CREATE TABLE Fachbereiche (
    FName varchar(30),
    FNummer integer,
    LeiterSVN integer,
    StartDatum date
);

DROP TABLE IF EXISTS FBStandorte CASCADE;
CREATE TABLE FBStandorte (
    FNummer integer,
    Standort varchar(30)
);

DROP TABLE IF EXISTS Angestellte CASCADE;
CREATE TABLE Angestellte (
    VName varchar(30),
    NName varchar(30),
    SVN integer,
    GDatum date,
    Adresse varchar(30),
    Geschlecht char(1),
    Lohn integer,
    VorgSVN integer,
    FNummer integer
);

DROP TABLE IF EXISTS Abhaengige CASCADE;
CREATE TABLE Abhaengige (
    AngSVN integer,
    AbhName varchar(30),
    Geschlecht char(1),
    GDatum date,
    Beziehung varchar(10)
);

DROP TABLE IF EXISTS Projekte CASCADE;
CREATE TABLE Projekte (
    PName varchar(30),
    PNummer integer,
    PStandort varchar(30),
    FNummer integer
);

DROP TABLE IF EXISTS arbeitenAn CASCADE;
CREATE TABLE arbeitenAn (
    AngSVN integer,
    PNummer integer,
    Stunden integer
);

-- Primary keys

ALTER TABLE Fachbereiche ADD PRIMARY KEY (FNummer);
ALTER TABLE FBStandorte ADD PRIMARY KEY (FNummer, Standort);
ALTER TABLE Angestellte ADD PRIMARY KEY (SVN);
ALTER TABLE Abhaengige ADD PRIMARY KEY (AngSVN, AbhName);
ALTER TABLE Projekte ADD PRIMARY KEY (PNummer);
ALTER TABLE arbeitenAn ADD PRIMARY KEY (AngSVN, PNummer);

-- Foreign keys

ALTER TABLE Fachbereiche ADD FOREIGN KEY (LeiterSVN) REFERENCES Angestellte(SVN) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE FBStandorte ADD FOREIGN KEY (FNummer) REFERENCES Fachbereiche(FNummer) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE Angestellte ADD FOREIGN KEY (VorgSVN) REFERENCES Angestellte(SVN) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE Angestellte ADD FOREIGN KEY (FNummer) REFERENCES Fachbereiche(FNummer) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE Abhaengige ADD FOREIGN KEY (AngSVN) REFERENCES Angestellte(SVN) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE Projekte ADD FOREIGN KEY (FNummer) REFERENCES Fachbereiche(FNummer) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE arbeitenAn ADD FOREIGN KEY (AngSVN) REFERENCES Angestellte(SVN) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE arbeitenAn ADD FOREIGN KEY (PNummer) REFERENCES Projekte(PNummer) DEFERRABLE INITIALLY IMMEDIATE;

-- Insert data

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
---
INSERT INTO Angestellte VALUES ('Ileana', 'Wigglesworth', 1001, '1947-11-25', 'Tiverton', 'F', 10000, NULL, 1);
INSERT INTO Angestellte VALUES ('Matthew', 'Galois', 1002, '1930-01-09', 'Cricklade', 'M', 11000, NULL, 2);
INSERT INTO Angestellte VALUES ('Sarah', 'Brookshire', 1003, '1983-05-07', 'Tattershall', 'F', 7200, 1001, 1);
INSERT INTO Angestellte VALUES ('Berkly', 'Breschi', 1004, '1955-10-03', 'Gainsborough', 'M', 2100, 1001, 1);
INSERT INTO Angestellte VALUES ('Amelia', 'Petruccelli', 1006, '1920-08-06', 'Stratford', 'F', 1400, 1001, 1);
INSERT INTO Angestellte VALUES ('Francois', 'Reifenberg', 1007, '1983-11-02', 'Lacock', 'M', 4500, 1001, 1);
INSERT INTO Angestellte VALUES ('Ricardo', 'Whitebirch', 1008, '1973-06-01', 'Farindon', 'M', 3700, 1001, 1);
INSERT INTO Angestellte VALUES ('Eleanore', 'Bridge', 1009, '1944-02-22', 'Tiverton', 'F', 2200, 1002, 2);
INSERT INTO Angestellte VALUES ('Leonid', 'Mcmahon', 1010, '1967-06-27', 'Bushbury', 'M', 6800, 1002, 2);
INSERT INTO Angestellte VALUES ('Beatrix', 'Moren', 1011, '1974-02-08', 'Pebmarsh', 'F', 1300, 1002, 2);
INSERT INTO Angestellte VALUES ('Andrej', 'Butcher', 1012, '1925-04-19', 'Tattershall', 'M', 5100, 1002, 2);
INSERT INTO Angestellte VALUES ('Matilde', 'Palmitesta', 1005, '1978-07-04', 'Bushbury', 'F', 2300, 1002, 2);
INSERT INTO Angestellte VALUES ('Massimo', 'Fortes', 1013, '1961-10-25', 'Norwich', 'M', 8900, NULL, 3);
INSERT INTO Angestellte VALUES ('Lavena', 'Pavao', 1014, '1968-12-15', 'Lacock', 'F', 4500, 1013, 3);
INSERT INTO Angestellte VALUES ('Bobby', 'Tine', 1015, '1978-07-04', 'Tiverton', 'M', 1800, 1013, 3);
INSERT INTO Angestellte VALUES ('Emma', 'Poe', 1016, '1972-01-20', 'Pebmarsh', 'F', 3900, NULL, 4);
INSERT INTO Angestellte VALUES ('Celinda', 'Grassi', 1017, '1941-10-20', 'Lacock', 'F', 2400, 1016, 4);
INSERT INTO Angestellte VALUES ('Huberto', 'Poznauskis', 1018, '1936-09-28', 'Tiverton', 'M', 1100, 1016, 4);
INSERT INTO Angestellte VALUES ('Abigale', 'Weiner', 1019, '1932-02-07', 'Cricklade', 'F', 4700, 1016, 4);
---
INSERT INTO Fachbereiche VALUES ('Biology', 1, 1001, '1971-02-07');
INSERT INTO Fachbereiche VALUES ('FBCS', 2, 1002, '1983-05-07');
INSERT INTO Fachbereiche VALUES ('Medicine', 3, 1013, '1998-06-23');
INSERT INTO Fachbereiche VALUES ('Psychology', 4, 1016, '2010-04-13');
---
INSERT INTO FBStandorte VALUES (1, 'Oxted');
INSERT INTO FBStandorte VALUES (2, 'Salzburg');
INSERT INTO FBStandorte VALUES (3, 'Wien');
INSERT INTO FBStandorte VALUES (4, 'Salzburg');
---
INSERT INTO Projekte VALUES ('TORTOR', 1, 'Salzburg', 2);
INSERT INTO Projekte VALUES ('PROIN', 2, 'Oxted', 1);
INSERT INTO Projekte VALUES ('COMMODO', 3, 'Salzburg', 3);
INSERT INTO Projekte VALUES ('MAGNA', 4, 'Salzburg', 2);
INSERT INTO Projekte VALUES ('MASSA', 5, 'Oxted', 1);
INSERT INTO Projekte VALUES ('LIGULA', 6, 'Salzburg', 1);
INSERT INTO Projekte VALUES ('LUCTUS', 7, 'Erwood', 2);
INSERT INTO Projekte VALUES ('AUCTOR', 8, 'Swansea', 3);
INSERT INTO Projekte VALUES ('TELLUS', 9, 'Erwood', 3);
INSERT INTO Projekte VALUES ('AMET', 10, 'Bolton', 1);
---
INSERT INTO Abhaengige VALUES (1008, 'Letta', 'F', '1938-09-27', 'Frau');
INSERT INTO Abhaengige VALUES (1008, 'Louie', 'M', '1998-12-29', 'Sohn');
INSERT INTO Abhaengige VALUES (1008, 'Jeremie', 'M', '1994-11-06', 'Sohn');
INSERT INTO Abhaengige VALUES (1008, 'Liana', 'F', '2002-12-28', 'Tochter');
INSERT INTO Abhaengige VALUES (1007, 'Tamra', 'F', '1942-03-29', 'Frau');
INSERT INTO Abhaengige VALUES (1007, 'Gloria', 'F', '2003-11-14', 'Tochter');
INSERT INTO Abhaengige VALUES (1007, 'Ogdon', 'M', '2004-02-10', 'Sohn');
INSERT INTO Abhaengige VALUES (1003, 'Celestyn', 'M', '1976-03-23', 'Mann');
INSERT INTO Abhaengige VALUES (1003, 'Remus', 'M', '2009-11-23', 'Sohn');
INSERT INTO Abhaengige VALUES (1003, 'Fabian', 'M', '2003-12-27', 'Sohn');
INSERT INTO Abhaengige VALUES (1002, 'Hannah', 'F', '1967-09-19', 'Frau');
INSERT INTO Abhaengige VALUES (1002, 'Estrellita', 'F', '1901-05-01', 'Mutter');
INSERT INTO Abhaengige VALUES (1002, 'Marwin', 'M', '1953-08-16', 'Sohn');
INSERT INTO Abhaengige VALUES (1011, 'Klemens', 'M', '1964-03-03', 'Mann');
INSERT INTO Abhaengige VALUES (1011, 'Alfred', 'M', '1940-07-29', 'Vater');
INSERT INTO Abhaengige VALUES (1012, 'Alika', 'F', '1950-02-28', 'Tochter');
INSERT INTO Abhaengige VALUES (1012, 'Lizette', 'F', '1944-09-03', 'Tochter');
INSERT INTO Abhaengige VALUES (1019, 'Katrine', 'F', '1977-06-26', 'Tochter');
INSERT INTO Abhaengige VALUES (1019, 'Wolfgang', 'M', '1967-11-22', 'Sohn');
INSERT INTO Abhaengige VALUES (1006, 'Manda', 'F', '1944-08-31', 'Tochter');
INSERT INTO Abhaengige VALUES (1006, 'Bob', 'M', '1923-09-30', 'Mann');
INSERT INTO Abhaengige VALUES (1018, 'Tara', 'F', '1934-09-13', 'Frau');
INSERT INTO Abhaengige VALUES (1018, 'Joseph', 'M', '1958-07-09', 'Sohn');
INSERT INTO Abhaengige VALUES (1015, 'Diana', 'F', '1972-09-12', 'Frau');
INSERT INTO Abhaengige VALUES (1009, 'Madelene', 'F', '1993-10-22', 'Tochter');
---
INSERT INTO arbeitenAn VALUES ('1013','5','736');
INSERT INTO arbeitenAn VALUES ('1010','8','523');
INSERT INTO arbeitenAn VALUES ('1004','3','244');
INSERT INTO arbeitenAn VALUES ('1001','9','239');
INSERT INTO arbeitenAn VALUES ('1017','3','788');
INSERT INTO arbeitenAn VALUES ('1006','4','47');
INSERT INTO arbeitenAn VALUES ('1015','5','849');
INSERT INTO arbeitenAn VALUES ('1009','1','890');
INSERT INTO arbeitenAn VALUES ('1011','7','170');
INSERT INTO arbeitenAn VALUES ('1016','10','599');
INSERT INTO arbeitenAn VALUES ('1012','3','808');
INSERT INTO arbeitenAn VALUES ('1014','6','974');
INSERT INTO arbeitenAn VALUES ('1019','4','912');
INSERT INTO arbeitenAn VALUES ('1003','2','166');
---
COMMIT;