/* ----------------------------------------------------
   Generated by Enterprise Architect Version 15.2
   Title        : SO_create_00_DBFOsociology.sql
   Created On   : 25-JUN-2025 17:11:06
   DBMS         : MySql 8.0.30
   Workbench    : MySQLWorkbench 8.0.32
                  Monterey compatible. Dec 14. 2022
   Designed by  : Dr Hlaszny, Edit
   ----------------------------------------------------
*/

DROP DATABASE   IF EXISTS     DBFOsociology ;
CREATE DATABASE IF NOT EXISTS DBFOsociology ;

USE                           DBFOsociology ;

SET FOREIGN_KEY_CHECKS=1 ;
SET SQL_SAFE_UPDATES=0;

SET   TRANSACTION READ WRITE ;
START TRANSACTION ;

/*  Drop Tables
 */
DROP TABLE IF EXISTS BFO_TREE                     CASCADE ;
DROP TABLE IF EXISTS CLASS_ANNOTATIONS            CASCADE ;
DROP TABLE IF EXISTS DATA_PROPERTIES              CASCADE ;
DROP TABLE IF EXISTS DATA_PROPERTY_TREE           CASCADE ;
DROP TABLE IF EXISTS DISJOINT_OWL_CLASSES         CASCADE ;
DROP TABLE IF EXISTS HTML_HEADER                  CASCADE ;
DROP TABLE IF EXISTS HTML_TRAILER                 CASCADE ;
DROP TABLE IF EXISTS INVERSE_OBJECT_PROPERTIES    CASCADE ;
DROP TABLE IF EXISTS N_ARY_DATA_ASSERTIONS        CASCADE ;
DROP TABLE IF EXISTS N_ARY_PARTICIPANTS           CASCADE ;
DROP TABLE IF EXISTS N_ARY_RELATIONS              CASCADE ;
DROP TABLE IF EXISTS OBJECT_PROPERTIES            CASCADE ;
DROP TABLE IF EXISTS OBJECT_PROPERTY_TREE         CASCADE ;
DROP TABLE IF EXISTS ONTOLOGY_HEADER              CASCADE ;
DROP TABLE IF EXISTS ONTOLOGY_HEADER_ANNOTATIONS  CASCADE ;
DROP TABLE IF EXISTS ONTOLOGY_TRAILER             CASCADE ;
DROP TABLE IF EXISTS OWL_CLASSES                  CASCADE ;
DROP TABLE IF EXISTS SKOS_ANNOTATIONS             CASCADE ;
DROP TABLE IF EXISTS SO_TREE                      CASCADE ;
DROP TABLE IF EXISTS SUPER_OBJECT_PROPERTIES      CASCADE ;

/*  Create Tables
 */
CREATE TABLE ONTOLOGY_HEADER  -- ----------------------------------------------
(
   hdr_id   INT          NOT NULL COMMENT 'Defines the sequence in the ontology header.',
   xml_cmd  VARCHAR(512) NOT NULL COMMENT 'XML command of an OWL^XML encoded ontology.',

   CONSTRAINT PK_ontologyHdr PRIMARY KEY (hdr_id ASC)
) ;
ALTER TABLE ONTOLOGY_HEADER MODIFY COLUMN hdr_id  INT AUTO_INCREMENT;

CREATE TABLE ONTOLOGY_HEADER_ANNOTATIONS  -- ----------------------------------
(
   hdrAnn_id      INT          NOT NULL  ,
   iri            VARCHAR(64)  NOT NULL  ,
   literal        VARCHAR(512) NOT NULL  ,

   CONSTRAINT PK_ontologyHdrAnnot PRIMARY KEY (hdrAnn_id ASC)
) ;
ALTER TABLE ONTOLOGY_HEADER_ANNOTATIONS MODIFY COLUMN hdrAnn_id INT AUTO_INCREMENT;


CREATE TABLE SKOS_ANNOTATIONS  -- ---------------------------------------------
(
   skosAnn_id      INT          NOT NULL  ,
   skos_annotation VARCHAR(512) NOT NULL  ,

   CONSTRAINT PK_skosAnnot PRIMARY KEY (skosAnn_id ASC)
) ;
ALTER TABLE SKOS_ANNOTATIONS MODIFY COLUMN skosAnn_id INT AUTO_INCREMENT;

CREATE TABLE OWL_CLASSES  -- --------------------------------------------------
(
    class_IRI              VARCHAR(255) NOT NULL COMMENT ' ',
    superclass_of_IRI      VARCHAR(255) NOT NULL COMMENT ' ',
    class_individual_count INT          NOT NULL DEFAULT 0  ,
    annotated              BOOL         NOT NULL DEFAULT false COMMENT 'There is annotation for this entity (in one or more languages).'
--
--    some OWL classes do have 2 superclasses: the SO and the BFO ones !
--
--    CONSTRAINT class_IRI PRIMARY KEY (class_IRI ASC)
) ;

CREATE TABLE DISJOINT_OWL_CLASSES  -- -----------------------------------------
(
   owl_class_IRI     VARCHAR(128) NOT NULL COMMENT 'Name of the active substance.',
   disjoint_group_id INT          NOT NULL COMMENT 'Group identifier.'

--   CONSTRAINT FK_disjoint_class_iri FOREIGN KEY (owl_class_IRI)
--              REFERENCES OWL_CLASSES(class_IRI)
)
COMMENT = 'Classified OWL classes being disjunct, which belong to disjoint group.
           Classes belonging to a group are considered as disjoint.' ;

CREATE TABLE CLASS_ANNOTATIONS  -- --------------------------------------------
(
   classAnn_id         INT           NOT NULL,

   class_IRI           VARCHAR(128)  NOT NULL,
   annotation_type_IRI VARCHAR(128)  NOT NULL,
   language            VARCHAR(32)   NOT NULL,
   annotation          VARCHAR(8192) NOT NULL,

   CONSTRAINT PK_CLASS_ANNOTATIONS PRIMARY KEY (classAnn_id ASC)

--   CONSTRAINT FK_class_iri FOREIGN KEY (class_IRI)
--              REFERENCES OWL_CLASSES(class_IRI)
) ;
ALTER TABLE CLASS_ANNOTATIONS MODIFY COLUMN classAnn_id  INT AUTO_INCREMENT;

CREATE TABLE ONTOLOGY_TRAILER  -- ---------------------------------------------
(
   trailer_id INT          NOT NULL COMMENT 'Defines the sequence in the ontology header.',
   xml_cmd    VARCHAR(512) NOT NULL COMMENT 'XML command of an OWL/XML encoded ontology.',

   CONSTRAINT PK_ontologyTrailer PRIMARY KEY (trailer_id ASC)
) ;
ALTER TABLE ONTOLOGY_TRAILER MODIFY COLUMN trailer_id  INT AUTO_INCREMENT;


CREATE TABLE HTML_HEADER  -- --------------------------------------------------
(
   line_num   INT           NOT NULL COMMENT 'line number providing the correct sequence',
   session_id INT           NOT NULL COMMENT 'session ID: identifies parts of html header',
   html_cmd   VARCHAR(2048) NOT NULL COMMENT 'html command',

   CONSTRAINT PK_header_id PRIMARY KEY (line_num ASC)
) ;
ALTER TABLE HTML_HEADER MODIFY COLUMN line_num INT AUTO_INCREMENT ;


CREATE TABLE HTML_TRAILER  -- -------------------------------------------------
(
   line_num   INT           NOT NULL COMMENT 'line number providing the correct sequence',
   html_cmd   VARCHAR(2048) NOT NULL COMMENT 'html command',

   CONSTRAINT PK_trailer_id PRIMARY KEY (line_num ASC)
) ;
ALTER TABLE HTML_TRAILER MODIFY COLUMN line_num INT AUTO_INCREMENT ;


CREATE TABLE BFO_TREE  -- -----------------------------------------------------
(
   line_num   INT           NOT NULL COMMENT 'line number providing the correct sequence',
   html_cmd   VARCHAR(2048) NOT NULL COMMENT 'html command',

   CONSTRAINT PK_BFO_tree_id PRIMARY KEY (line_num ASC)
) ;
ALTER TABLE BFO_TREE MODIFY COLUMN line_num INT AUTO_INCREMENT ;


CREATE TABLE OBJECT_PROPERTY_TREE  -- -----------------------------------------
(
   line_num   INT           NOT NULL COMMENT 'line number providing the correct sequence',
   html_cmd   VARCHAR(2048) NOT NULL COMMENT 'html command',

   CONSTRAINT PK_OBJPROP_tree_id PRIMARY KEY (line_num ASC)
) ;
ALTER TABLE OBJECT_PROPERTY_TREE MODIFY COLUMN line_num INT AUTO_INCREMENT ;


CREATE TABLE DATA_PROPERTY_TREE  -- -----------------------------------------
(
   line_num   INT           NOT NULL COMMENT 'line number providing the correct sequence',
   html_cmd   VARCHAR(2048) NOT NULL COMMENT 'html command',

   CONSTRAINT PK_OBJPROP_tree_id PRIMARY KEY (line_num ASC)
) ;
ALTER TABLE DATA_PROPERTY_TREE MODIFY COLUMN line_num INT AUTO_INCREMENT ;


CREATE TABLE SO_TREE  -- ------------------------------------------------------
(
   line_num   INT           NOT NULL COMMENT 'line number providing the correct sequence',
   html_cmd   VARCHAR(2048) NOT NULL COMMENT 'html command',

   CONSTRAINT PK_SO_tree_id PRIMARY KEY (line_num ASC)
) ;
ALTER TABLE SO_TREE MODIFY COLUMN line_num INT AUTO_INCREMENT ;


CREATE TABLE OBJECT_PROPERTIES  -- ----------------------------------------
(
    object_property_IRI   VARCHAR(255)  NOT NULL COMMENT 'FK to TRIPLETS',
    annotation_type_IRI   VARCHAR(128)  NOT NULL COMMENT 'typically skos:definition',
    language              VARCHAR(32)   NOT NULL COMMENT 'typically en',
    annotation            VARCHAR(2048) NOT NULL COMMENT 'annotation' ,
    funct                 boolean       NOT NULL COMMENT 'object property attribute' ,
    invFunct              boolean       NOT NULL COMMENT 'object property attribute' ,
    symm                  boolean       NOT NULL COMMENT 'object property attribute' ,
    aSymm                 boolean       NOT NULL COMMENT 'object property attribute' ,
    trans                 boolean       NOT NULL COMMENT 'object property attribute' ,
    refl                  boolean       NOT NULL COMMENT 'object property attribute' ,
    irRefl                boolean       NOT NULL COMMENT 'object property attribute' ,
/*
    CONSTRAINT PK_OBJECT_PROPERTY_ANNOTATIONS PRIMARY KEY (object_property_IRI,
                                                           annotation_type_IRI,
                                                           language  ASC) ,

    CONSTRAINT FK_obj_prop FOREIGN KEY (object_property_IRI)
               REFERENCES OBJECT_PROPERTIES(object_property_IRI)
*/
    CONSTRAINT PK_objectProp PRIMARY KEY (object_property_IRI ASC)
) ;

CREATE TABLE INVERSE_OBJECT_PROPERTIES  -- ----------------------------------------
(
    invObjProp_id                  INT          NOT NULL,

    object_property_IRI            VARCHAR(255) NOT NULL COMMENT 'FK to TRIPLETS',
    inverse_object_property_IRI    VARCHAR(255) NOT NULL COMMENT ' ',

    CONSTRAINT PK_inverseObjProp PRIMARY KEY (invObjProp_id ASC),

    CONSTRAINT FK_obj_prop_iri  FOREIGN KEY (object_property_IRI)
               REFERENCES OBJECT_PROPERTIES(object_property_IRI)
) ;
ALTER TABLE INVERSE_OBJECT_PROPERTIES MODIFY COLUMN invObjProp_id  INT AUTO_INCREMENT ;

CREATE TABLE SUPER_OBJECT_PROPERTIES  -- ----------------------------------------
(
    object_property_IRI            VARCHAR(255) NOT NULL COMMENT 'FK to TRIPLETS',
    super_object_property_IRI      VARCHAR(255) NOT NULL COMMENT ' ',

    CONSTRAINT PK_objectProp PRIMARY KEY (object_property_IRI ASC)
) ;

CREATE TABLE DATA_PROPERTIES  -- ----------------------------------------
(
    data_property_ID              INT           NOT NULL COMMENT 'data properties will be referred by data_property_IRI',
    data_property_IRI             VARCHAR(255)  NOT NULL,
    data_property_type            VARCHAR(128)  NOT NULL,
    data_property_annotationtype  VARCHAR(2047) NOT NULL,

    super_data_property_IRI       VARCHAR(255)  NOT NULL COMMENT 'always owl:topObjectProperty ',

    CONSTRAINT PK_dataProp     PRIMARY KEY (data_property_IRI    ASC)
--  CONSTRAINT PK_dataProperty PRIMARY KEY (data_property_ID ASC)

) ;


-- Core n-ary relation definition
CREATE TABLE N_ARY_RELATIONS
(
    n_ary_relation_id              INT           NOT NULL AUTO_INCREMENT,  -- PK
    predicate_IRI                  VARCHAR(255)  NOT NULL,
    relation_name                  VARCHAR(511)  NOT NULL,
    relation_annotation            VARCHAR(2047) NOT NULL,
    causal_event_individual        VARCHAR(128)  NOT NULL,  -- suffix-numbered, sequentially

    CONSTRAINT PK_n_ary_relations PRIMARY KEY (n_ary_relation_id)
--
--  it can be enabled later
--
--    CONSTRAINT FK_predicate FOREIGN KEY (predicate_IRI)
--        REFERENCES OBJECT_PROPERTIES(object_property_IRI)
);

-- Entities participating in n-ary relations (unified subjects/objects)
CREATE TABLE N_ARY_PARTICIPANTS
(
    participant_id                INT          NOT NULL AUTO_INCREMENT, -- PK
    n_ary_relation_id             INT          NOT NULL,
    individual_name               VARCHAR(128) NOT NULL, -- suffix-numbered, sequentially
    class_IRI                     VARCHAR(255) NOT NULL,
    role_type                     ENUM('SUBJECT', 'OBJECT') NOT NULL,

    CONSTRAINT PK_participants PRIMARY KEY (participant_id)

--    CONSTRAINT FK_n_ary_relation FOREIGN KEY (n_ary_relation_id)
--        REFERENCES N_ARY_RELATIONS(n_ary_relation_id) ON DELETE CASCADE

--
--    some OWL classes do have 2 superclasses: the SO and the BFO ones !
--    class_IRI cannot be index > consequence> ERROR CODE 1822
--
--    CONSTRAINT FK_participant_class FOREIGN KEY (class_iri)
--        REFERENCES OWL_CLASSES(class_IRI)
);

-- Data property assertions for participants
CREATE TABLE N_ARY_DATA_ASSERTIONS
(
    assertion_id                  INT          NOT NULL AUTO_INCREMENT, -- PK
    participant_id                INT          NOT NULL,
    data_property_IRI             VARCHAR(255) NOT NULL,
    data_property_value           VARCHAR(255) NOT NULL,

    CONSTRAINT PK_data_assertions PRIMARY KEY (assertion_id)
/*
    later can be enabled again :)

    CONSTRAINT FK_participant FOREIGN KEY (participant_id)
        REFERENCES N_ARY_PARTICIPANTS(participant_id) ON DELETE CASCADE,

    CONSTRAINT FK_data_property FOREIGN KEY (data_property_IRI)
        REFERENCES DATA_PROPERTIES(data_property_IRI)
*/
);

COMMIT ;

/*  end of SO_create_00_DBFOsociology.sql
 */