CREATE_ADE_DB.sql

-- This document was automatically created by the ADE-Manager tool of 3DCityDB (https://www.3dcitydb.org) on 2021-01-10 14:53:44 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create tables ************************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEB_building 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_building
(
    id INTEGER NOT NULL,
    fweareaname VARCHAR2(1000),
    fwesystemname VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEB_buildingsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_buildingsurvey
(
    id INTEGER NOT NULL,
    building_consistsoffwesur_id INTEGER,
    buildingfootprintarea NUMBER,
    buildingfootprintarea_uom VARCHAR2(1000),
    objectclass_id INTEGER,
    occupants INTEGER,
    surveydescription VARCHAR2(1000),
    surveyyear INTEGER,
    surveyyearversion NUMBER,
    totalfloorarea NUMBER,
    totalfloorarea_uom VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEB_energysurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_energysurvey
(
    id INTEGER NOT NULL,
    buildingsurv_energysurvey_id INTEGER,
    electricitypotentialfrompv NUMBER,
    electricitypotentialfrompvun VARCHAR2(1000),
    residentialelectricitydema_1 VARCHAR2(1000),
    residentialelectricitydemand NUMBER,
    spacecoolingdemand NUMBER,
    spacecoolingdemandunit VARCHAR2(1000),
    spaceheatingdemand NUMBER,
    spaceheatingdemandunit VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEB_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_foodsurvey
(
    id INTEGER NOT NULL,
    buildingsurvey_foodsurvey_id INTEGER,
    foodcategory VARCHAR2(1000),
    foodcategory_codespace VARCHAR2(1000),
    foodconsumption NUMBER,
    foodconsumptionunit VARCHAR2(1000),
    fooddemand NUMBER,
    fooddemandunit VARCHAR2(1000),
    foodproduction NUMBER,
    foodproductionenergydemand NUMBER,
    foodproductionenergydemandun VARCHAR2(1000),
    foodproductionunit VARCHAR2(1000),
    foodproductionwaterdemand NUMBER,
    foodproductionwaterdemanduni VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEB_watersurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEB_watersurvey
(
    id INTEGER NOT NULL,
    buildingsurve_watersurvey_id INTEGER,
    domestichotwaterdemand NUMBER,
    domestichotwaterdemandunit VARCHAR2(1000),
    domestichotwaterenergydema_1 VARCHAR2(1000),
    domestichotwaterenergydemand NUMBER,
    domesticsolidwaste NUMBER,
    domesticsolidwasteunit VARCHAR2(1000),
    domesticwaterdemand NUMBER,
    domesticwaterdemandunit VARCHAR2(1000),
    domesticwaterwaste NUMBER,
    domesticwaterwasteunit VARCHAR2(1000),
    totaldomesticwaste NUMBER,
    totaldomesticwasteunit VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create foreign keys ******************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEB_building 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_building ADD CONSTRAINT FWEB_building_fk FOREIGN KEY (id)
REFERENCES building (id);

-- -------------------------------------------------------------------- 
-- FWEB_buildingsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_buildingsurvey ADD CONSTRAINT FWEB_buildingsurvey_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWEB_buildingsurvey ADD CONSTRAINT FWEB_buildings_objectcl_fk FOREIGN KEY (objectclass_id)
REFERENCES objectclass (id);

ALTER TABLE FWEB_buildingsurvey ADD CONSTRAINT FWEB_buildi_build_consi_fk FOREIGN KEY (building_consistsoffwesur_id)
REFERENCES FWEB_building (id);

-- -------------------------------------------------------------------- 
-- FWEB_energysurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_energysurvey ADD CONSTRAINT FWEB_energysurvey_fk FOREIGN KEY (id)
REFERENCES FWEB_buildingsurvey (id);

ALTER TABLE FWEB_energysurvey ADD CONSTRAINT FWEB_energy_build_energ_fk FOREIGN KEY (buildingsurv_energysurvey_id)
REFERENCES FWEB_buildingsurvey (id);

-- -------------------------------------------------------------------- 
-- FWEB_foodsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_foodsurvey ADD CONSTRAINT FWEB_foodsurvey_fk FOREIGN KEY (id)
REFERENCES FWEB_buildingsurvey (id);

ALTER TABLE FWEB_foodsurvey ADD CONSTRAINT FWEB_foodsu_build_foods_fk FOREIGN KEY (buildingsurvey_foodsurvey_id)
REFERENCES FWEB_buildingsurvey (id);

-- -------------------------------------------------------------------- 
-- FWEB_watersurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEB_watersurvey ADD CONSTRAINT FWEB_watersurvey_fk FOREIGN KEY (id)
REFERENCES FWEB_buildingsurvey (id);

ALTER TABLE FWEB_watersurvey ADD CONSTRAINT FWEB_waters_build_water_fk FOREIGN KEY (buildingsurve_watersurvey_id)
REFERENCES FWEB_buildingsurvey (id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Indexes ************************************* 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEB_buildingsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_build_build_consi_fkx ON FWEB_buildingsurvey (building_consistsoffwesur_id);

CREATE INDEX FWEB_building_objectcl_fkx ON FWEB_buildingsurvey (objectclass_id);

-- -------------------------------------------------------------------- 
-- FWEB_energysurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_energ_build_energ_fkx ON FWEB_energysurvey (buildingsurv_energysurvey_id);

-- -------------------------------------------------------------------- 
-- FWEB_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_foods_build_foods_fkx ON FWEB_foodsurvey (buildingsurvey_foodsurvey_id);

-- -------------------------------------------------------------------- 
-- FWEB_watersurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEB_water_build_water_fkx ON FWEB_watersurvey (buildingsurve_watersurvey_id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Sequences *********************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++