CREATE_ADE_DB.sql

-- This document was automatically created by the ADE-Manager tool of 3DCityDB (https://www.3dcitydb.org) on 2021-03-29 17:59:30 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create tables ************************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEL_energysurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_energysurvey
(
    id INTEGER NOT NULL,
    biomasspotentialbioethanol NUMBER,
    biomasspotentialbioethanolun VARCHAR2(1000),
    biomasspotentialbiogas NUMBER,
    biomasspotentialbiogasunit VARCHAR2(1000),
    biomasspotentialforest NUMBER,
    biomasspotentialforestunit VARCHAR2(1000),
    biomasspotentialplantoil NUMBER,
    biomasspotentialplantoilunit VARCHAR2(1000),
    biomasspotentialresidualso_1 VARCHAR2(1000),
    biomasspotentialresidualsoli NUMBER,
    biomasspotentialsolidfuel NUMBER,
    biomasspotentialsolidfueluni VARCHAR2(1000),
    biomassprimaryenergypotent_1 VARCHAR2(1000),
    biomassprimaryenergypotentia NUMBER,
    biomasstoelectricitypotent_1 VARCHAR2(1000),
    biomasstoelectricitypotentia NUMBER,
    biomasstothermalpotential NUMBER,
    biomasstothermalpotentialuni VARCHAR2(1000),
    biomassweight NUMBER,
    biomassweightunit VARCHAR2(1000),
    electricitypotentialfrompv_1 VARCHAR2(1000),
    electricitypotentialfrompvbu NUMBER,
    landusesurve_energysurvey_id INTEGER,
    residentialelectricitydema_1 VARCHAR2(1000),
    residentialelectricitydemand NUMBER,
    spacecoolingdemand NUMBER,
    spacecoolingdemandunit VARCHAR2(1000),
    spaceheatingdemand NUMBER,
    spaceheatingdemandunit VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEL_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_foodsurvey
(
    id INTEGER NOT NULL,
    animalcalorie INTEGER,
    animalcalorieunit VARCHAR2(1000),
    cropcalorie INTEGER,
    cropcalorieunit VARCHAR2(1000),
    foodcategory VARCHAR2(1000),
    foodcategory_codespace VARCHAR2(1000),
    foodconsumption NUMBER,
    foodconsumptionunit VARCHAR2(1000),
    fooddemand NUMBER,
    fooddemandunit VARCHAR2(1000),
    foodproduction NUMBER,
    foodproductionenergydemand NUMBER,
    foodproductionengerydemandun VARCHAR2(1000),
    foodproductionunit VARCHAR2(1000),
    foodproductionwaterdemand NUMBER,
    foodproductionwaterdemanduni VARCHAR2(1000),
    landusesurvey_foodsurvey_id INTEGER,
    vegetalcalorie INTEGER,
    vegetalcalorieunit VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEL_land_use 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_land_use
(
    id INTEGER NOT NULL,
    fweareaname VARCHAR2(1000),
    fwesystemname VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEL_landusesurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_landusesurvey
(
    id INTEGER NOT NULL,
    buildingfootprintarea NUMBER,
    buildingfootprintarea_uom VARCHAR2(1000),
    croptype VARCHAR2(1000),
    croptype_codespace VARCHAR2(1000),
    land_use_consistsoffwesur_id INTEGER,
    objectclass_id INTEGER,
    population INTEGER,
    soiltype VARCHAR2(1000),
    soiltype_codespace VARCHAR2(1000),
    surfacearea NUMBER,
    surfacearea_uom VARCHAR2(1000),
    surveydescription VARCHAR2(1000),
    surveyyear INTEGER,
    surveyyearversion NUMBER,
    PRIMARY KEY (id)
);

-- -------------------------------------------------------------------- 
-- FWEL_watersurvey 
-- -------------------------------------------------------------------- 
CREATE TABLE FWEL_watersurvey
(
    id INTEGER NOT NULL,
    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),
    irrigationdemand NUMBER,
    irrigationdemandunit VARCHAR2(1000),
    landusesurvey_watersurvey_id INTEGER,
    totaldomesticwaste NUMBER,
    totaldomesticwasteunit VARCHAR2(1000),
    transpirationloss NUMBER,
    transpirationlossunit VARCHAR2(1000),
    PRIMARY KEY (id)
);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create foreign keys ******************************** 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEL_energysurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_energysurvey ADD CONSTRAINT FWEL_energysurvey_fk FOREIGN KEY (id)
REFERENCES FWEL_landusesurvey (id);

ALTER TABLE FWEL_energysurvey ADD CONSTRAINT FWEL_energy_landu_energ_fk FOREIGN KEY (landusesurve_energysurvey_id)
REFERENCES FWEL_landusesurvey (id);

-- -------------------------------------------------------------------- 
-- FWEL_foodsurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_foodsurvey ADD CONSTRAINT FWEL_foodsurvey_fk FOREIGN KEY (id)
REFERENCES FWEL_landusesurvey (id);

ALTER TABLE FWEL_foodsurvey ADD CONSTRAINT FWEL_foodsu_landu_foods_fk FOREIGN KEY (landusesurvey_foodsurvey_id)
REFERENCES FWEL_landusesurvey (id);

-- -------------------------------------------------------------------- 
-- FWEL_land_use 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_land_use ADD CONSTRAINT FWEL_land_use_fk FOREIGN KEY (id)
REFERENCES land_use (id);

-- -------------------------------------------------------------------- 
-- FWEL_landusesurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_landusesurvey ADD CONSTRAINT FWEL_landusesurvey_fk FOREIGN KEY (id)
REFERENCES cityobject (id);

ALTER TABLE FWEL_landusesurvey ADD CONSTRAINT FWEL_landusesu_objectcl_fk FOREIGN KEY (objectclass_id)
REFERENCES objectclass (id);

ALTER TABLE FWEL_landusesurvey ADD CONSTRAINT FWEL_land_land_use_cons_fk FOREIGN KEY (land_use_consistsoffwesur_id)
REFERENCES FWEL_land_use (id);

-- -------------------------------------------------------------------- 
-- FWEL_watersurvey 
-- -------------------------------------------------------------------- 
ALTER TABLE FWEL_watersurvey ADD CONSTRAINT FWEL_watersurvey_fk FOREIGN KEY (id)
REFERENCES FWEL_landusesurvey (id);

ALTER TABLE FWEL_watersurvey ADD CONSTRAINT FWEL_waters_landu_water_fk FOREIGN KEY (landusesurvey_watersurvey_id)
REFERENCES FWEL_landusesurvey (id);

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- *********************************** Create Indexes ************************************* 
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
-- -------------------------------------------------------------------- 
-- FWEL_energysurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_energ_landu_energ_fkx ON FWEL_energysurvey (landusesurve_energysurvey_id);

-- -------------------------------------------------------------------- 
-- FWEL_foodsurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_foods_landu_foods_fkx ON FWEL_foodsurvey (landusesurvey_foodsurvey_id);

-- -------------------------------------------------------------------- 
-- FWEL_landusesurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_land_land_use_con_fkx ON FWEL_landusesurvey (land_use_consistsoffwesur_id);

CREATE INDEX FWEL_landuses_objectcl_fkx ON FWEL_landusesurvey (objectclass_id);

-- -------------------------------------------------------------------- 
-- FWEL_watersurvey 
-- -------------------------------------------------------------------- 
CREATE INDEX FWEL_water_landu_water_fkx ON FWEL_watersurvey (landusesurvey_watersurvey_id);

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