/* ----------------------------------------------------
   Generated by Enterprise Architect Version 15.2
   Title        : create_dbfontomaestro.sql
   Created On   : 21-nov-2024 3:04:16
   DBMS         : MySql 8.0.30
   Workbench    : MySQLWorkbench 8.0.32
                  Monterey compatible. Dec 14. 2022
   Designed by  : Dr Hlaszny, Edit
   ----------------------------------------------------
*/

DROP DATABASE   IF EXISTS     DBFOntoMaestro ;
CREATE DATABASE IF NOT EXISTS DBFOntoMaestro ;

USE                           DBFOntoMaestro ;

SET FOREIGN_KEY_CHECKS=0 ;
SET SQL_SAFE_UPDATES=0;

/*  Drop Tables
 */
DROP TABLE IF EXISTS BFO_ANNOTATIONS               CASCADE ;
DROP TABLE IF EXISTS CLASS_ANNOTATIONS             CASCADE ;
DROP TABLE IF EXISTS DATA_PROPERTY_ANNOTATIONS     CASCADE ;
DROP TABLE IF EXISTS DISJOINT_OWL_CLASSES          CASCADE ;
DROP TABLE IF EXISTS IAO_ANNOTATIONS               CASCADE ;
DROP TABLE IF EXISTS OBJECT_PROPERTY_ANNOTATIONS   CASCADE ;
DROP TABLE IF EXISTS ONTOLOGY_HEADER_ANNOTATIONS   CASCADE ;
DROP TABLE IF EXISTS ONTOLOGY_HEADER               CASCADE ;
DROP TABLE IF EXISTS ONTOLOGY_TRAILER              CASCADE ;
DROP TABLE IF EXISTS OWL_CLASSES                   CASCADE ;
DROP TABLE IF EXISTS OWL_DATA_PROPERTIES           CASCADE ;
DROP TABLE IF EXISTS OWL_INVERSE_OBJECT_PROPERTIES CASCADE ;
DROP TABLE IF EXISTS OWL_OBJECT_PROPERTIES         CASCADE ;
DROP TABLE IF EXISTS OWL_SUPER_OBJECT_PROPERTIES   CASCADE ;
DROP TABLE IF EXISTS TRIPLETS                      CASCADE ;

/*  Create Tables
 */
CREATE TABLE BFO_ANNOTATIONS  -- ----------------------------------------------
(
   bfo_entity_name VARCHAR(128)  NOT NULL COMMENT 'BFO 1.0 annotation name.',
   bfo_literal     VARCHAR(2048) NOT NULL COMMENT 'Elucidation, example, URL in the
                   OBO library, source of information.',
   language        VARCHAR(16)  NOT NULL COMMENT 'Language of the annotation
                   (e.g.: ''en'', it'', ''nl'').',

   CONSTRAINT PK_bfo_entity_name PRIMARY KEY (bfo_entity_name ASC)

)
COMMENT = 'The table contains 33 BFO (Basic Formal Ontology)
           annotations (en). Name, detailed elucidation, and
           origin build the content.' ;

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(2048) NOT NULL,

   CONSTRAINT PK_CLASS_ANNOTATIONS PRIMARY KEY (classAnn_id ASC),

   CONSTRAINT FK_class_iri FOREIGN KEY (class_IRI)
              REFERENCES OWL_CLASSES(class_IRI),

   CONSTRAINT FK_iaoa_ann_type FOREIGN KEY (annotation_type_IRI)
              REFERENCES IAO_ANNOTATIONS(abbreviated_IRI)
) ;
ALTER TABLE CLASS_ANNOTATIONS MODIFY COLUMN classAnn_id  INT AUTO_INCREMENT;

CREATE TABLE DATA_PROPERTY_ANNOTATIONS  -- ------------------------------------
(
   datPropAnn_id       INT           NOT NULL,

   data_property_IRI   VARCHAR(128)  NOT NULL,
   annotation_type_IRI VARCHAR(128)  NOT NULL,
   language            VARCHAR(32)   NOT NULL,
   annotation          VARCHAR(2048) NOT NULL,

   CONSTRAINT PK_datPropAnn_id PRIMARY KEY (datPropAnn_id ASC),

   CONSTRAINT FK_dat_prop FOREIGN KEY (data_property_IRI)
              REFERENCES OWL_DATA_PROPERTIES(data_property_IRI)
) ;
ALTER TABLE DATA_PROPERTY_ANNOTATIONS MODIFY COLUMN datPropAnn_id  INT AUTO_INCREMENT;

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 IAO_ANNOTATIONS  -- ----------------------------------------
(
   abbreviated_IRI          VARCHAR(128)  NOT NULL COMMENT 'abbreviated IRI of the annotations.',
   annotation_property_type VARCHAR(32)   NOT NULL COMMENT 'Such as ''definition'', ''definition source''.',
   annotation_literal       VARCHAR(2048) NOT NULL COMMENT 'Memonical content of the annotation (e.g.: ''elucidation'').',
   language                 VARCHAR(16)   NOT NULL COMMENT 'Language of the annotation (e.g.: ''en'', it'', ''nl'').',

   CONSTRAINT PK_iao_annotation PRIMARY KEY (abbreviated_IRI ASC)
) ;

CREATE TABLE OBJECT_PROPERTY_ANNOTATIONS  -- ----------------------------------
(
   object_property_IRI VARCHAR(128)  NOT NULL,
   annotation_type_IRI VARCHAR(128)  NOT NULL,
   language            VARCHAR(32)   NOT NULL,
   annotation          VARCHAR(2048) NOT NULL,

   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 OWL_OBJECT_PROPERTIES(object_property_IRI)

) ;

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 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 OWL_CLASSES  -- --------------------------------------------------
(
    class_IRI              VARCHAR(128) NOT NULL COMMENT ' ',
    superclass_of_IRI      VARCHAR(128) 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).',

    CONSTRAINT class_IRI PRIMARY KEY (class_IRI ASC)
) ;

CREATE TABLE OWL_DATA_PROPERTIES  -- ------------------------------------------
(
    data_property_IRI              VARCHAR(128) NOT NULL COMMENT 'FK to TRIPLETS',
    data_property_type_IRI         VARCHAR(128) NOT NULL COMMENT ' ',
    super_data_property_IRI        VARCHAR(128) NOT NULL COMMENT ' ',

    annotated                      BOOL         NOT NULL DEFAULT false COMMENT 'There is annotation for this entity (in one or more languages).',
    already_processed              BOOL         NOT NULL DEFAULT false COMMENT 'avoiding multiple declarations.',

    CONSTRAINT PK_datProp PRIMARY KEY (data_property_IRI ASC)

) ;


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

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

    CONSTRAINT PK_inverseObjProp PRIMARY KEY (invObjProp_id ASC),

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

CREATE TABLE OWL_OBJECT_PROPERTIES  -- ----------------------------------------
(
    object_property_IRI            VARCHAR(256) NOT NULL COMMENT 'FK to TRIPLETS',
    annotated                      BOOL         NOT NULL DEFAULT false COMMENT 'There is annotation for this entity (in one or more languages).',

    CONSTRAINT PK_objectProp PRIMARY KEY (object_property_IRI ASC)
) ;

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

    CONSTRAINT PK_objectProp PRIMARY KEY (object_property_IRI ASC)
) ;

CREATE TABLE TRIPLETS  -- -----------------------------------------------------
(
   triplet_id                   INT          NOT NULL,

   subject_IRI                  VARCHAR(256) NOT NULL,
   predicate_IRI                VARCHAR(256) NOT NULL,
   object_IRI                   VARCHAR(256) NOT NULL,

   subject_data_property_IRI    VARCHAR(256) NOT NULL,
   object_data_property_IRI     VARCHAR(256) NOT NULL,

   subject_data_property_value  VARCHAR(64) NOT NULL,
   object_data_property_value   VARCHAR(64) NOT NULL,

   CONSTRAINT PK_triplet_id PRIMARY KEY (triplet_id ASC),

   CONSTRAINT FK_subject FOREIGN KEY (subject_IRI)
              REFERENCES OWL_CLASSES(class_IRI) ,

   CONSTRAINT FK_object FOREIGN KEY (object_IRI)
              REFERENCES OWL_CLASSES(class_IRI) ,

   CONSTRAINT FK_predicate FOREIGN KEY (predicate_IRI)
              REFERENCES OWL_OBJECT_PROPERTIES(object_property_IRI),

   CONSTRAINT FK_subject_dat_prop FOREIGN KEY (subject_data_property_IRI)
              REFERENCES OWL_DATA_PROPERTIES(data_property_IRI),

   CONSTRAINT FK_object_dat_prop FOREIGN KEY (object_data_property_IRI)
              REFERENCES OWL_DATA_PROPERTIES(data_property_IRI)
)
COMMENT = 'The table contains definitions of triplets (SUBJECT-PREDICATE-OBJECT),
          or with other terminology: domain-object property-range; and their
          SUBJECT- and OBJECT data property type- and value data.' ;

ALTER TABLE TRIPLETS MODIFY COLUMN triplet_id  INT AUTO_INCREMENT;

SET FOREIGN_KEY_CHECKS=1 ;

COMMIT ;

/*  end of create_dbfontomaestro.sql
 */
