CREATE TABLE CAMPAIGN(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    CODE VARCHAR(255) NOT NULL,
    REGION VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    NAME VARCHAR(255),
    BEGINDATE TIMESTAMP,
    ENDDATE TIMESTAMP,
    LOCALCREATION BOOLEAN
); 
ALTER TABLE CAMPAIGN ADD CONSTRAINT CONSTRAINT_2 PRIMARY KEY(TOPIAID);           
-- 1 +/- SELECT COUNT(*) FROM CAMPAIGN; 
CREATE TABLE FLIGHT(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    SYSTEMID VARCHAR(255) NOT NULL,
    FLIGHTNUMBER INTEGER NOT NULL,
    CAMPAIGN VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    IMMATRICULATION VARCHAR(255),
    BEGINDATE TIMESTAMP,
    ENDDATE TIMESTAMP,
    COMMENT VARCHAR(255),
    PLATFORMTYPE INTEGER
);      
ALTER TABLE FLIGHT ADD CONSTRAINT CONSTRAINT_7 PRIMARY KEY(TOPIAID);             
-- 4 +/- SELECT COUNT(*) FROM FLIGHT;   
CREATE TABLE FLIGHT_OBSERVER(
    FLIGHT VARCHAR(255) NOT NULL,
    OBSERVER VARCHAR(255) NOT NULL
);            
-- 10 +/- SELECT COUNT(*) FROM FLIGHT_OBSERVER;         
CREATE TABLE GEOPOINT(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    RECORDTIME TIMESTAMP,
    LONGITUDE NUMERIC,
    LATITUDE NUMERIC,
    ALTITUDE NUMERIC,
    SPEED NUMERIC,
    CAPTUREDELAY INTEGER,
    FLIGHT VARCHAR(255)
);      
ALTER TABLE GEOPOINT ADD CONSTRAINT CONSTRAINT_9 PRIMARY KEY(TOPIAID);           
-- 678 +/- SELECT COUNT(*) FROM GEOPOINT;               
CREATE TABLE OBSERVATION(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    OBSERVATIONNUMBER INTEGER NOT NULL,
    FLIGHT VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    OBSERVATIONTIME TIMESTAMP,
    PODSIZE INTEGER,
    DECANGLE INTEGER,
    AGE VARCHAR(255),
    CUE VARCHAR(255),
    BEHAVIOUR VARCHAR(255),
    SWIMDIR INTEGER,
    CALVES VARCHAR(255),
    PHOTO BOOLEAN,
    COMMENT VARCHAR(255),
    VALID BOOLEAN,
    DELETED BOOLEAN,
    OBSERVATIONSTATUS INTEGER,
    SPECIES VARCHAR(255),
    POSITION INTEGER
);    
ALTER TABLE OBSERVATION ADD CONSTRAINT CONSTRAINT_8 PRIMARY KEY(TOPIAID);        
-- 26 +/- SELECT COUNT(*) FROM OBSERVATION;             
CREATE TABLE OBSERVER(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    INITIALS VARCHAR(255) NOT NULL,
    CAMPAIGN VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    FIRSTNAME VARCHAR(255),
    LASTNAME VARCHAR(255),
    ORGANIZATION VARCHAR(255),
    EMAIL VARCHAR(255),
    LOCALCREATION BOOLEAN,
    PILOT BOOLEAN
); 
ALTER TABLE OBSERVER ADD CONSTRAINT CONSTRAINT_4 PRIMARY KEY(TOPIAID);           
-- 18 +/- SELECT COUNT(*) FROM OBSERVER;
CREATE TABLE OBSERVERPOSITION(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    POSITION INTEGER,
    OBSERVER VARCHAR(255),
    ROUTE VARCHAR(255)
);    
ALTER TABLE OBSERVERPOSITION ADD CONSTRAINT CONSTRAINT_77 PRIMARY KEY(TOPIAID);  
-- 236 +/- SELECT COUNT(*) FROM OBSERVERPOSITION;       
CREATE TABLE OBSERVERPOSITION_TRANSECTFLIGHT(
    TRANSECTFLIGHT VARCHAR(255) NOT NULL,
    OBSERVERPOSITION VARCHAR(255) NOT NULL
);            
-- 108 +/- SELECT COUNT(*) FROM OBSERVERPOSITION_TRANSECTFLIGHT;        
CREATE TABLE REGION(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    CODE VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    NAME VARCHAR(255),
    LATMIN NUMERIC,
    LATMAX NUMERIC,
    LONGMIN NUMERIC,
    LONGMAX NUMERIC
);  
ALTER TABLE REGION ADD CONSTRAINT CONSTRAINT_8F PRIMARY KEY(TOPIAID);            
-- 1 +/- SELECT COUNT(*) FROM REGION;   
CREATE TABLE ROUTE(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    BEGINTIME TIMESTAMP,
    DISTANCE NUMERIC,
    COMMENT VARCHAR(255),
    DELETED BOOLEAN,
    VALID BOOLEAN,
    UNEXPECTEDRIGHT VARCHAR(255),
    UNEXPECTEDLEFT VARCHAR(255),
    TURBIDITY INTEGER,
    SWELL INTEGER,
    SUBJECTIVECONDITIONS VARCHAR(255),
    SKYGLINT INTEGER,
    SEASTATE INTEGER,
    GLAREUNDER BOOLEAN,
    GLARETO INTEGER,
    GLARESEVERITY INTEGER,
    GLAREFROM INTEGER,
    EFFORTNUMBER INTEGER,
    CLOUDCOVER INTEGER,
    ROUTETYPE INTEGER,
    TRANSECTFLIGHT VARCHAR(255),
    FLIGHT VARCHAR(255),
    CIRCLEBACKCAUSE VARCHAR(255)
);    
ALTER TABLE ROUTE ADD CONSTRAINT CONSTRAINT_4A PRIMARY KEY(TOPIAID);             
-- 32 +/- SELECT COUNT(*) FROM ROUTE;   
CREATE TABLE SECTOR(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    SECTORNUMBER INTEGER NOT NULL,
    CAMPAIGN VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    NAME VARCHAR(255),
    LOCALCREATION BOOLEAN
);              
ALTER TABLE SECTOR ADD CONSTRAINT CONSTRAINT_91 PRIMARY KEY(TOPIAID);            
-- 3 +/- SELECT COUNT(*) FROM SECTOR;   
CREATE TABLE SPECIES(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    CODE VARCHAR(255) NOT NULL,
    REGION VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    COMMONNAME VARCHAR(255),
    LATINNAME VARCHAR(255),
    GROUPNAME VARCHAR(255),
    LOCALCREATION BOOLEAN,
    FAMILY VARCHAR(255),
    TYPE VARCHAR(255)
);    
ALTER TABLE SPECIES ADD CONSTRAINT CONSTRAINT_B PRIMARY KEY(TOPIAID);            
-- 136 +/- SELECT COUNT(*) FROM SPECIES;
CREATE TABLE STRATE(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    STRATETYPE INTEGER NOT NULL,
    SECTOR VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    NAME VARCHAR(255),
    LOCALCREATION BOOLEAN,
    CODE VARCHAR(255)
);           
ALTER TABLE STRATE ADD CONSTRAINT CONSTRAINT_92 PRIMARY KEY(TOPIAID);            
-- 10 +/- SELECT COUNT(*) FROM STRATE;  
CREATE TABLE TRANSECT(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    STRATE VARCHAR(255) NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    LENGTH NUMERIC,
    STARTX NUMERIC,
    ENDX NUMERIC,
    STARTY NUMERIC,
    ENDY NUMERIC,
    LOCALCREATION BOOLEAN,
    NBTIMES INTEGER,
    GRAPHICINDEX INTEGER
);              
ALTER TABLE TRANSECT ADD CONSTRAINT CONSTRAINT_7A PRIMARY KEY(TOPIAID);          
-- 445 +/- SELECT COUNT(*) FROM TRANSECT;               
CREATE TABLE TRANSECTFLIGHT(
    TOPIAID VARCHAR(255) NOT NULL,
    TOPIAVERSION BIGINT NOT NULL,
    TOPIACREATEDATE TIMESTAMP,
    CROSSINGNUMBER INTEGER,
    VALID BOOLEAN,
    DELETED BOOLEAN,
    TRANSECT VARCHAR(255),
    FLIGHT VARCHAR(255),
    FLIGHT_IDX INTEGER
);               
ALTER TABLE TRANSECTFLIGHT ADD CONSTRAINT CONSTRAINT_6 PRIMARY KEY(TOPIAID);     
-- 27 +/- SELECT COUNT(*) FROM TRANSECTFLIGHT;          
ALTER TABLE OBSERVATION ADD CONSTRAINT CONSTRAINT_87 UNIQUE(OBSERVATIONNUMBER, FLIGHT);          
ALTER TABLE REGION ADD CONSTRAINT CONSTRAINT_8FD UNIQUE(CODE);   
ALTER TABLE CAMPAIGN ADD CONSTRAINT CONSTRAINT_26 UNIQUE(CODE, REGION);          
ALTER TABLE OBSERVER ADD CONSTRAINT CONSTRAINT_43 UNIQUE(INITIALS, CAMPAIGN);    
ALTER TABLE SECTOR ADD CONSTRAINT CONSTRAINT_918 UNIQUE(SECTORNUMBER, CAMPAIGN); 
ALTER TABLE SPECIES ADD CONSTRAINT CONSTRAINT_B3 UNIQUE(CODE, REGION);           
ALTER TABLE TRANSECT ADD CONSTRAINT CONSTRAINT_7AF UNIQUE(NAME, STRATE);         
ALTER TABLE FLIGHT ADD CONSTRAINT CONSTRAINT_7B UNIQUE(SYSTEMID, FLIGHTNUMBER, CAMPAIGN);        
ALTER TABLE STRATE ADD CONSTRAINT CONSTRAINT_926 UNIQUE(STRATETYPE, SECTOR);     
ALTER TABLE OBSERVER ADD CONSTRAINT FK14C752D6DD3FD588 FOREIGN KEY(CAMPAIGN) REFERENCES CAMPAIGN(TOPIAID);        
ALTER TABLE ROUTE ADD CONSTRAINT FK67AB249DB8B5756 FOREIGN KEY(CIRCLEBACKCAUSE) REFERENCES OBSERVATION(TOPIAID);  
ALTER TABLE OBSERVERPOSITION ADD CONSTRAINT FK6AF71A1FD8D22614 FOREIGN KEY(OBSERVER) REFERENCES OBSERVER(TOPIAID);
ALTER TABLE CAMPAIGN ADD CONSTRAINT FKF7A90110607FB950 FOREIGN KEY(REGION) REFERENCES REGION(TOPIAID);            
ALTER TABLE FLIGHT_OBSERVER ADD CONSTRAINT FK4B90FDC5D8D22614 FOREIGN KEY(OBSERVER) REFERENCES OBSERVER(TOPIAID); 
ALTER TABLE OBSERVATION ADD CONSTRAINT FK74AD82C412F5648 FOREIGN KEY(FLIGHT) REFERENCES FLIGHT(TOPIAID);          
ALTER TABLE OBSERVERPOSITION_TRANSECTFLIGHT ADD CONSTRAINT FKAFFE281EE9D83644 FOREIGN KEY(TRANSECTFLIGHT) REFERENCES TRANSECTFLIGHT(TOPIAID);     
ALTER TABLE TRANSECTFLIGHT ADD CONSTRAINT FK5167F9FE412F5648 FOREIGN KEY(FLIGHT) REFERENCES FLIGHT(TOPIAID);      
ALTER TABLE SECTOR ADD CONSTRAINT FKC9FB5766DD3FD588 FOREIGN KEY(CAMPAIGN) REFERENCES CAMPAIGN(TOPIAID);          
ALTER TABLE ROUTE ADD CONSTRAINT FK67AB249412F5648 FOREIGN KEY(FLIGHT) REFERENCES FLIGHT(TOPIAID);
ALTER TABLE FLIGHT_OBSERVER ADD CONSTRAINT FK4B90FDC5412F5648 FOREIGN KEY(FLIGHT) REFERENCES FLIGHT(TOPIAID);     
ALTER TABLE ROUTE ADD CONSTRAINT FK67AB249E9D83644 FOREIGN KEY(TRANSECTFLIGHT) REFERENCES TRANSECTFLIGHT(TOPIAID);
ALTER TABLE GEOPOINT ADD CONSTRAINT FK6C73C0BF412F5648 FOREIGN KEY(FLIGHT) REFERENCES FLIGHT(TOPIAID);            
ALTER TABLE SPECIES ADD CONSTRAINT FK8849413C607FB950 FOREIGN KEY(REGION) REFERENCES REGION(TOPIAID);             
ALTER TABLE OBSERVERPOSITION ADD CONSTRAINT FK6AF71A1FC69C3A2A FOREIGN KEY(ROUTE) REFERENCES ROUTE(TOPIAID);      
ALTER TABLE OBSERVATION ADD CONSTRAINT FK74AD82C28BF1090 FOREIGN KEY(SPECIES) REFERENCES SPECIES(TOPIAID);        
ALTER TABLE OBSERVERPOSITION_TRANSECTFLIGHT ADD CONSTRAINT FKAFFE281ED8FCDC06 FOREIGN KEY(OBSERVERPOSITION) REFERENCES OBSERVERPOSITION(TOPIAID); 
ALTER TABLE STRATE ADD CONSTRAINT FKCAD542C1FD149134 FOREIGN KEY(SECTOR) REFERENCES SECTOR(TOPIAID);              
ALTER TABLE FLIGHT ADD CONSTRAINT FKB4318470DD3FD588 FOREIGN KEY(CAMPAIGN) REFERENCES CAMPAIGN(TOPIAID);          
ALTER TABLE TRANSECTFLIGHT ADD CONSTRAINT FK5167F9FE6FAE9C44 FOREIGN KEY(TRANSECT) REFERENCES TRANSECT(TOPIAID);  
ALTER TABLE TRANSECT ADD CONSTRAINT FK4C58B3EEDEDD426A FOREIGN KEY(STRATE) REFERENCES STRATE(TOPIAID);