DROP TABLE Attributes;
DROP TABLE AttrTypes;
DROP TABLE Texts;
DROP TABLE Elements;
DROP SEQUENCE ElementsSeq;
DROP SEQUENCE AttributesSeq;

CREATE TABLE Elements (
    id NUMBER(3) CONSTRAINT Elements_PK PRIMARY KEY,          -- must be > 0
    parent NUMBER (3) NULL,
    name VARCHAR2(20) NOT NULL
);

ALTER TABLE Elements 
ADD CONSTRAINT Elements_parent_FK_Elements
FOREIGN KEY (parent) REFERENCES Elements ON DELETE CASCADE;


CREATE TABLE AttrTypes (
    id NUMBER(3) CONSTRAINT AttrTypes_PK PRIMARY KEY,
    name VARCHAR2(20) UNIQUE NOT NULL
);

CREATE TABLE Attributes (
    id NUMBER(3) CONSTRAINT Attributes_PK PRIMARY KEY,
    owner NUMBER(3) CONSTRAINT Attributes_owner_FK_Elements REFERENCES Elements
                    ON DELETE CASCADE NOT NULL,
    name VARCHAR2(20) NOT NULL,
    value VARCHAR2(40) NULL,
    atype NUMBER(3) CONSTRAINT Attributes_type_FK_AttrTypes REFERENCES AttrTypes
                    ON DELETE CASCADE NOT NULL
);

CREATE TABLE Texts (
    id NUMBER(3) CONSTRAINT Texts_PK PRIMARY KEY,
    parent NUMBER(3) CONSTRAINT Texts_FK_Elements REFERENCES Elements ON DELETE CASCADE,
    value VARCHAR2(255) NOT NULL
);

CREATE SEQUENCE ElementsSeq START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE AttributesSeq START WITH 1 INCREMENT BY 1;

INSERT INTO AttrTypes VALUES (1, 'String');
INSERT INTO AttrTypes VALUES (2, 'Number');
INSERT INTO AttrTypes VALUES (3, 'Date');
INSERT INTO AttrTypes VALUES (4, 'URI');
INSERT INTO AttrTypes VALUES (5, 'ID');