-- Voorschriften voor het creeren van Oracle database objecten:
-- - tabellen
-- - primary keys
-- - unique keys
-- - foreign keys
-- - check constraints

CREATE TABLE Woning
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , OH_nr VARCHAR(10) NOT NULL
 , Straat VARCHAR(50) NOT NULL
 , huisnummer VARCHAR(10)
 , postcode VARCHAR(7) NOT NULL
 , plaatsnaam VARCHAR(100) NOT NULL
 , bouwjaar INTEGER
 , Huisnummertoevoeging VARCHAR(2)
 , Exploitatiebegindatum DATE
 );

CREATE TABLE Huurder
 (  
 huurdernr INTEGER NOT NULL
 , voorletter VARCHAR(10) NOT NULL
 , achternaam VARCHAR(50) NOT NULL
 , telefoonnummer VARCHAR(11) NOT NULL
 , Geslacht VARCHAR(1) NOT NULL
 );

CREATE TABLE Huurovereenkomst
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Huurdernr INTEGER NOT NULL
 , einddatum DATE
 , datumhuuropzegging DATE
 , Blijftklant VARCHAR(1)
 , Tijdelijkeleegstand VARCHAR(1)
 , Naamplaatjebestellen VARCHAR(1)
 , Tekstnaamplaatje VARCHAR(100)
 , Contract INTEGER
 );

CREATE TABLE Woningopnamecontroles
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Controlesoort VARCHAR(2) NOT NULL
 , Controledatum DATE NOT NULL 
 , Beoordeling VARCHAR(3) NOT NULL
 , Medewerkernr INTEGER NOT NULL
 );

CREATE TABLE Ruimte
 (  
 Ruimtenr INTEGER NOT NULL
 , Omschrijving VARCHAR(25) NOT NULL
 , Volgorde INTEGER
 );

CREATE TABLE Beoordelingswaarde
 (  
 Code VARCHAR(3) NOT NULL
 , omschrijving VARCHAR(25) NOT NULL
 , Kleur VARCHAR(10)
 , volgorde INTEGER
 );

CREATE TABLE Ruimteelement
 (  
 Elementnr INTEGER NOT NULL
 , Omschrijving VARCHAR(25) NOT NULL
 , Standaardindicatie VARCHAR(1)
 );

CREATE TABLE RuimteelementInRuimte
 (  
 Ruimtenr INTEGER NOT NULL
 , Elementnr INTEGER NOT NULL
 , Standaardindicatie VARCHAR(1)
 );

CREATE TABLE Ruimtecontroles
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Controlesoort VARCHAR(2) NOT NULL
 , Ruimtenr INTEGER NOT NULL
 , Beoordeling VARCHAR(3) DEFAULT 'NVT' NOT NULL
 );

CREATE TABLE Ruimteelementcontroles
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Controlesoort VARCHAR(2) NOT NULL
 , Ruimtenr INTEGER NOT NULL
 , Elementnr INTEGER NOT NULL
 , Standaardindicatie VARCHAR(1) NOT NULL
 , Beoordeling VARCHAR(3) NOT NULL
 , Kenmerken VARCHAR(4000)
 , Acties VARCHAR(4000)
 , ActieDoor VARCHAR(1)
 , AccoordNaEindcontrole VARCHAR(1)
 , Geschattekosten INTEGER
 , Doorberekendekosten INTEGER
 );

CREATE TABLE Medewerker
 (  
 Medewerkernr INTEGER NOT NULL
 , Naam VARCHAR(25) NOT NULL
 , Gebruikernaam VARCHAR(20)
 , Wachtwoord VARCHAR(20)
 );

CREATE TABLE Overname
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Ruimtenr INTEGER NOT NULL
 , Overnamenr INTEGER NOT NULL
 , Omschrijving VARCHAR(150) NOT NULL
 , Accoord VARCHAR(1)
 , Overnameprijs INTEGER
 , Overnameelement INTEGER
 , Actie VARCHAR(1) DEFAULT 'N'
 );

CREATE TABLE Sleutel
 (  
 Sleutelnr INTEGER NOT NULL
 , Naam VARCHAR(25) NOT NULL
 , Prijs INTEGER NOT NULL
 );

CREATE TABLE Centraleverwarming
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , Merk VARCHAR(25) NOT NULL
 , type VARCHAR(3) NOT NULL
 , warmwater VARCHAR(1) NOT NULL
 , aanlegjaar INTEGER NOT NULL
 , Opmerkingen VARCHAR(4000)
 , Vulslangaanwezig VARCHAR(1) NOT NULL
 , Vulsleutelaanwezig VARCHAR(1) NOT NULL
 , Ontluchtingssleutelaanwezig VARCHAR(1) NOT NULL
 , aantalradiatoren INTEGER DEFAULT 0 NOT NULL
 , aantalwarmtemeters INTEGER DEFAULT 0
 , aantalthermostaatkranen INTEGER DEFAULT 0
 );

CREATE TABLE ZAVPerRuimte
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , Ruimtenr INTEGER NOT NULL
 , ZAV_nr INTEGER NOT NULL
 , OmschrijvingZAV VARCHAR(150) NOT NULL
 , Accoordvoorvergoeding VARCHAR(1) NOT NULL
 , Vergoeding INTEGER
 , begindatumhuurovereenkomst DATE NOT NULL 
 , Toevoegdatum DATE
 );

CREATE TABLE WMOPerRuimte
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , Ruimtenr INTEGER NOT NULL
 , WMO_nr INTEGER NOT NULL
 , OmschrijvingWMO VARCHAR(150) NOT NULL
 , begindatumhuurovereenkomst DATE NOT NULL 
 , Toevoegdatum DATE
 );

CREATE TABLE Warmtemeter
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , Ruimtenr INTEGER NOT NULL
 , warmtemeternr INTEGER NOT NULL
 , Kleur VARCHAR(15)
 );

CREATE TABLE Meterstandopname
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Standsoort VARCHAR(2) NOT NULL
 , Beginstand INTEGER
 , Eindstand INTEGER
 );

CREATE TABLE Warmtemeteropname
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Ruimtenr INTEGER NOT NULL
 , Warmtemeternr INTEGER NOT NULL
 , Beginstand INTEGER
 , Eindstand INTEGER
 );

CREATE TABLE Sleutelcontroles
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Sleutelnr INTEGER NOT NULL
 , AantalAanwezig INTEGER NOT NULL
 , AantalBenodigd INTEGER NOT NULL
 );

CREATE TABLE FotosPerRuimte
 (  
 VHE_nr VARCHAR(10) NOT NULL
 , begindatum DATE NOT NULL 
 , Controlesoort VARCHAR(2) NOT NULL
 , Ruimtenr INTEGER NOT NULL
 , Fotonr INTEGER NOT NULL
 , Urlfoto VARCHAR(25) NOT NULL
 , Datum DATE NOT NULL 
 );

CREATE TABLE Overnameelement
 (  
 Elementnr INTEGER NOT NULL
 , Omschrijving VARCHAR(25) NOT NULL
 );

CREATE TABLE Meterstandsoort
 (  
 Standsoort VARCHAR(2) NOT NULL
 , Omschrijving VARCHAR(25) NOT NULL
 );

CREATE TABLE Overnameelementinruimte
 (  
 Ruimtenr INTEGER NOT NULL
 , Elementnr INTEGER NOT NULL
 );

CREATE TABLE Medewerkerrollen
 (  
 Medewerkernr INTEGER NOT NULL
 , Rol VARCHAR(20) NOT NULL
 );


-- Primary Keys 
ALTER TABLE Woning ADD CONSTRAINT PK_T1 PRIMARY KEY (VHE_nr);

-- Primary Keys 
ALTER TABLE Huurder ADD CONSTRAINT PK_T2 PRIMARY KEY (huurdernr);

-- Primary Keys 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT PK_T4 PRIMARY KEY (VHE_nr, begindatum);

-- Primary Keys 
ALTER TABLE Woningopnamecontroles ADD CONSTRAINT PK_T5 PRIMARY KEY (VHE_nr, begindatum, Controlesoort);

-- Primary Keys 
ALTER TABLE Ruimte ADD CONSTRAINT PK_T6 PRIMARY KEY (Ruimtenr);

-- Primary Keys 
ALTER TABLE Beoordelingswaarde ADD CONSTRAINT PK_T7 PRIMARY KEY (Code);

-- Primary Keys 
ALTER TABLE Ruimteelement ADD CONSTRAINT PK_T8 PRIMARY KEY (Elementnr);

-- Primary Keys 
ALTER TABLE RuimteelementInRuimte ADD CONSTRAINT PK_T9 PRIMARY KEY (Elementnr, Ruimtenr);

-- Primary Keys 
ALTER TABLE Ruimtecontroles ADD CONSTRAINT PK_T10 PRIMARY KEY (VHE_nr, begindatum, Controlesoort, Ruimtenr);

-- Primary Keys 
ALTER TABLE Ruimteelementcontroles ADD CONSTRAINT PK_T11 PRIMARY KEY (VHE_nr, begindatum, Controlesoort, Ruimtenr, Elementnr);

-- Primary Keys 
ALTER TABLE Medewerker ADD CONSTRAINT PK_T12 PRIMARY KEY (Medewerkernr);

-- Primary Keys 
ALTER TABLE Overname ADD CONSTRAINT PK_T13 PRIMARY KEY (VHE_nr, begindatum, Ruimtenr, Overnamenr);

-- Primary Keys 
ALTER TABLE Sleutel ADD CONSTRAINT PK_T14 PRIMARY KEY (Sleutelnr);

-- Primary Keys 
ALTER TABLE Centraleverwarming ADD CONSTRAINT PK_T15 PRIMARY KEY (VHE_nr);

-- Primary Keys 
ALTER TABLE ZAVPerRuimte ADD CONSTRAINT PK_T16 PRIMARY KEY (VHE_nr, ZAV_nr, Ruimtenr);

-- Primary Keys 
ALTER TABLE WMOPerRuimte ADD CONSTRAINT PK_T17 PRIMARY KEY (VHE_nr, WMO_nr, Ruimtenr);

-- Primary Keys 
ALTER TABLE Warmtemeter ADD CONSTRAINT PK_T18 PRIMARY KEY (VHE_nr, warmtemeternr, Ruimtenr);

-- Primary Keys 
ALTER TABLE Meterstandopname ADD CONSTRAINT PK_T19 PRIMARY KEY (VHE_nr, begindatum, Standsoort);

-- Primary Keys 
ALTER TABLE Warmtemeteropname ADD CONSTRAINT PK_T20 PRIMARY KEY (VHE_nr, begindatum, Ruimtenr, Warmtemeternr);

-- Primary Keys 
ALTER TABLE Sleutelcontroles ADD CONSTRAINT PK_T21 PRIMARY KEY (VHE_nr, begindatum, Sleutelnr);

-- Primary Keys 
ALTER TABLE FotosPerRuimte ADD CONSTRAINT PK_T22 PRIMARY KEY (VHE_nr, begindatum, Controlesoort, Ruimtenr, Fotonr);

-- Primary Keys 
ALTER TABLE Overnameelement ADD CONSTRAINT PK_T23 PRIMARY KEY (Elementnr);

-- Primary Keys 
ALTER TABLE Meterstandsoort ADD CONSTRAINT PK_24 PRIMARY KEY (Standsoort);

-- Primary Keys 
ALTER TABLE Overnameelementinruimte ADD CONSTRAINT PK_25 PRIMARY KEY (Elementnr, Ruimtenr);

-- Primary Keys 
ALTER TABLE Medewerkerrollen ADD CONSTRAINT PK_26 PRIMARY KEY (Medewerkernr, Rol);

-- Unique Keys 
ALTER TABLE Woning ADD CONSTRAINT UK_T1_101 UNIQUE (huisnummer, Straat, plaatsnaam);
-- Unique Keys 
ALTER TABLE Woning ADD CONSTRAINT UK_T1_102 UNIQUE (postcode, huisnummer);
-- Unique Keys 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT UK_T4_103 UNIQUE (VHE_nr, einddatum);
-- Unique Keys 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT UK_T4_104 UNIQUE (begindatum, Huurdernr);
-- Unique Keys 
ALTER TABLE Ruimte ADD CONSTRAINT UK_T6_105 UNIQUE (Omschrijving);
-- Unique Keys 
ALTER TABLE Beoordelingswaarde ADD CONSTRAINT UK_T7_106 UNIQUE (omschrijving);
-- Unique Keys 
ALTER TABLE Ruimteelement ADD CONSTRAINT UK_T8_107 UNIQUE (Omschrijving);
-- Unique Keys 
ALTER TABLE Medewerker ADD CONSTRAINT UK_T12_108 UNIQUE (Naam);
-- Unique Keys 
ALTER TABLE Sleutel ADD CONSTRAINT UK_T14_109 UNIQUE (Naam);
-- Unique Keys 
ALTER TABLE Overnameelement ADD CONSTRAINT UK_T23_110 UNIQUE (Omschrijving);
-- Unique Keys 
ALTER TABLE Meterstandsoort ADD CONSTRAINT UK_24_111 UNIQUE (Omschrijving);


-- Foreign Keys 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT FK_T4_201 FOREIGN KEY (VHE_nr) REFERENCES Woning (VHE_nr);

-- Foreign Keys 
ALTER TABLE Woningopnamecontroles ADD CONSTRAINT FK_T5_202 FOREIGN KEY (Beoordeling) REFERENCES Beoordelingswaarde (Code);

-- Foreign Keys 
ALTER TABLE Ruimtecontroles ADD CONSTRAINT FK_T10_203 FOREIGN KEY (Beoordeling) REFERENCES Beoordelingswaarde (Code);

-- Foreign Keys 
ALTER TABLE Ruimteelementcontroles ADD CONSTRAINT FK_T11_204 FOREIGN KEY (Beoordeling) REFERENCES Beoordelingswaarde (Code);

-- Foreign Keys 
ALTER TABLE Ruimteelementcontroles ADD CONSTRAINT FK_T11_205 FOREIGN KEY (Ruimtenr, Elementnr) REFERENCES RuimteelementInRuimte (Ruimtenr, Elementnr);

-- Foreign Keys 
ALTER TABLE Woningopnamecontroles ADD CONSTRAINT FK_T5_207 FOREIGN KEY (Medewerkernr) REFERENCES Medewerker (Medewerkernr);

-- Foreign Keys 
ALTER TABLE Ruimtecontroles ADD CONSTRAINT FK_T10_211 FOREIGN KEY (Ruimtenr) REFERENCES Ruimte (Ruimtenr);

-- Foreign Keys 
ALTER TABLE Warmtemeteropname ADD CONSTRAINT FK_T20_215 FOREIGN KEY (VHE_nr, Ruimtenr, Warmtemeternr) REFERENCES Warmtemeter (VHE_nr, Ruimtenr, warmtemeternr);

-- Foreign Keys 
ALTER TABLE Meterstandopname ADD CONSTRAINT FK_T19_216 FOREIGN KEY (VHE_nr, begindatum) REFERENCES Huurovereenkomst (VHE_nr, begindatum);

-- Foreign Keys 
ALTER TABLE FotosPerRuimte ADD CONSTRAINT FK_T22_218 FOREIGN KEY (VHE_nr, begindatum, Controlesoort, Ruimtenr) REFERENCES Ruimtecontroles (VHE_nr, begindatum, Controlesoort, Ruimtenr);

-- Foreign Keys 
ALTER TABLE Overname ADD CONSTRAINT FK_T13_219 FOREIGN KEY (Overnameelement) REFERENCES Overnameelement (Elementnr);

-- Foreign Keys 
ALTER TABLE Centraleverwarming ADD CONSTRAINT FK_T15_221 FOREIGN KEY (VHE_nr) REFERENCES Woning (VHE_nr);

-- Foreign Keys 
ALTER TABLE Medewerkerrollen ADD CONSTRAINT FK_26_222 FOREIGN KEY (Medewerkernr) REFERENCES Medewerker (Medewerkernr);


-- Gelijkheidsregel 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT FK_T4_301 FOREIGN KEY (Huurdernr) REFERENCES Huurder (huurdernr);

-- Gelijkheidsregel 
ALTER TABLE Woningopnamecontroles ADD CONSTRAINT FK_T5_302 FOREIGN KEY (VHE_nr, begindatum) REFERENCES Huurovereenkomst (VHE_nr, begindatum);

-- Gelijkheidsregel 
ALTER TABLE Ruimtecontroles ADD CONSTRAINT FK_T10_303 FOREIGN KEY (VHE_nr, begindatum, Controlesoort) REFERENCES Woningopnamecontroles (VHE_nr, begindatum, Controlesoort);

-- Gelijkheidsregel 
ALTER TABLE RuimteelementInRuimte ADD CONSTRAINT FK_T9_304 FOREIGN KEY (Ruimtenr) REFERENCES Ruimte (Ruimtenr);

-- Gelijkheidsregel 
ALTER TABLE Sleutelcontroles ADD CONSTRAINT FK_T21_305 FOREIGN KEY (Sleutelnr) REFERENCES Sleutel (Sleutelnr);

-- Gelijkheidsregel 
ALTER TABLE Ruimteelementcontroles ADD CONSTRAINT FK_T11_306 FOREIGN KEY (VHE_nr, begindatum, Controlesoort, Ruimtenr) REFERENCES Ruimtecontroles (VHE_nr, begindatum, Controlesoort, Ruimtenr);

-- Gelijkheidsregel 
ALTER TABLE Meterstandopname ADD CONSTRAINT FK_T19_307 FOREIGN KEY (Standsoort) REFERENCES Meterstandsoort (Standsoort);

-- Waarderegel 
ALTER TABLE Woningopnamecontroles ADD CONSTRAINT CK_T5_3_501 CHECK ( Controlesoort IN ('SO', 'VC', 'EC'));
-- Waarderegel 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT CK_T4_9_502 CHECK ( Blijftklant IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT CK_T4_11_502 CHECK ( Tijdelijkeleegstand IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Huurovereenkomst ADD CONSTRAINT CK_T4_12_502 CHECK ( Naamplaatjebestellen IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Ruimteelement ADD CONSTRAINT CK_T8_3_502 CHECK ( Standaardindicatie IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE RuimteelementInRuimte ADD CONSTRAINT CK_T9_3_502 CHECK ( Standaardindicatie IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Ruimteelementcontroles ADD CONSTRAINT CK_T11_7_502 CHECK ( Standaardindicatie IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Ruimteelementcontroles ADD CONSTRAINT CK_T11_11_502 CHECK ( AccoordNaEindcontrole IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Overname ADD CONSTRAINT CK_T13_7_502 CHECK ( Accoord IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Centraleverwarming ADD CONSTRAINT CK_T15_5_502 CHECK ( warmwater IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Centraleverwarming ADD CONSTRAINT CK_T15_8_502 CHECK ( Vulslangaanwezig IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Centraleverwarming ADD CONSTRAINT CK_T15_9_502 CHECK ( Vulsleutelaanwezig IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Centraleverwarming ADD CONSTRAINT CK_T15_10_502 CHECK ( Ontluchtingssleutelaanwezig IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE ZAVPerRuimte ADD CONSTRAINT CK_T16_5_502 CHECK ( Accoordvoorvergoeding IN ('J', 'N'));
-- Waarderegel 
ALTER TABLE Ruimteelementcontroles ADD CONSTRAINT CK_T11_10_503 CHECK ( ActieDoor IN ('K', 'S'));
-- Waarderegel 
ALTER TABLE Centraleverwarming ADD CONSTRAINT CK_T15_4_504 CHECK ( type IN ('HR', 'LR', 'VR'));