subd1

/* Далее идут блоки удаления таблиц если они существуют*/
BEGIN
    EXECUTE IMMEDIATE 'drop table Applications cascade constraints';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'drop table Software cascade constraints';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'drop table SystemSoft cascade constraints';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'drop table Computer_System cascade constraints';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'drop table CS_Component cascade constraints';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'drop table Applications_SystemSoft cascade constraints';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'drop table Software_Computer_System cascade constraints';
EXCEPTION
    WHEN OTHERS THEN NULL;
END;
/

CREATE TABLE Software (
    PRIMARY KEY (Code),
    Code           INTEGER
                   CHECK ( Code > 0 ),
    -- Code уже PK, Name просто Unique
    Name           VARCHAR2(50) NOT NULL UNIQUE,
    License        VARCHAR2(30),
    Cost_per_month REAL
                   CHECK ( Cost_per_month >= 0.0 )
);

CREATE TABLE Applications (
    PRIMARY KEY (Code),
    FOREIGN KEY (Code) REFERENCES Software (Code),
    Code        INTEGER
                CHECK ( Code > 0 ),
    Description VARCHAR2(500),
    Purpose     VARCHAR2(30) NOT NULL
);

CREATE TABLE SystemSoft (
    PRIMARY KEY (Code),
    FOREIGN KEY (Code) REFERENCES Software (Code),
    Code     INTEGER
             CHECK ( Code > 0 ),
    Hardware VARCHAR2(50
);

CREATE TABLE Computer_System (
    PRIMARY KEY (Code),
    Code        INTEGER
                CHECK ( Code > 0 ),
    Description VARCHAR2(500),
    Purpose     VARCHAR2(30) NOT NULL
);

-- Таблица ComputerSystem Component
CREATE TABLE CS_Component (
    PRIMARY KEY (ComponentID),
    FOREIGN KEY (Computer_System_Code) REFERENCES Computer_System(Code),
    FOREIGN KEY (NextComponent) REFERENCES CS_Component(ComponentID),
    -- Суррогатный первичный ключ
    ComponentID     NUMBER GENERATED ALWAYS AS IDENTITY,
    Computer_System_Code         INTEGER
                    CHECK ( Computer_System_code > 0 ),
    Accountable_Emp VARCHAR2(100) NOT NULL,
    -- Критичность будет определяться целым числом
    Critical        INTEGER
                    CHECK ( Critical >= 0 ),
    NextComponent     NUMBER -- Ссылка на CS_Component верхнего уровня
);

-- Таблица множественной ассоциации для CS и Software
CREATE TABLE Software_Computer_System (
    PRIMARY KEY (Code_Software, Code_CS),
    FOREIGN KEY (Code_Software) REFERENCES Software(Code),
    FOREIGN KEY (Code_CS) REFERENCES Computer_System(Code),
    Code_Software INTEGER
                  CHECK ( Code_Software > 0 ),
    Code_CS       INTEGER
                  CHECK ( Code_CS > 0 )
);

-- Таблица множественной ассоциации для Applications и SystemSoft
CREATE TABLE Applications_SystemSoft (
    PRIMARY KEY (Applications_Code, SystemSoft_Code),
    FOREIGN KEY (Applications_Code) REFERENCES Applications(Code),
    FOREIGN KEY (SystemSoft_Code) REFERENCES SystemSoft(Code),
    Applications_Code        INTEGER,
                    CHECK ( Applications_Code > 0 ),
    SystemSoft_Code INTEGER
                    CHECK ( SystemSoft_Code > 0 )
);