/* ---------------------------------------------------- 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 */