Subd1_8sem

-- Удаляем таблицы при их наличии
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE Software cascade constraints ';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Table Software does not exist');
END;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE Applications cascade constraints ';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Table Applications does not exist');
END;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE SystemSoft cascade constraints ';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Table SystemSoft does not exist');
END;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE Computer_System cascade constraints ';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Table Computer_System does not exist');
END;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE Applications_SystemSoft cascade constraints ';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Table Applications_SystemSoft does not exist');
END;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE Software_Computer_System cascade constraints ';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Table Software_Computer_System does not exist');
END;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE CS_Component cascade constraints ';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Table CS_Component does not exist');
END;

-- Теперь создадим непосредственно сами таблицы
-- В Software PK будет Code, а Name просто Unique
CREATE TABLE Software (
    PRIMARY KEY (Code),
    Code INTEGER CHECK(Code > 0),
    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(50),
    Purpose VARCHAR2(50) NOT NULL
);
CREATE TABLE SystemSoft (
    PRIMARY KEY (Code),
    FOREIGN KEY (Code) REFERENCES Software(Code),
    Code INTEGER CHECK ( Code > 0 ),
    Hardware VARCHAR2(50)
);
-- Таблица множественной ассоциии для 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 )
);
CREATE TABLE Computer_System (
    PRIMARY KEY (Code),
    Code INTEGER CHECK ( Code > 0 ),
    Description VARCHAR2(50),
    Purpose VARCHAR2(50) NOT NULL
);
-- Таблица множественной ассоциации для Software и Computer_System
CREATE TABLE Software_Computer_System (
    PRIMARY KEY (Software_Code, CS_Code),
    FOREIGN KEY (Software_Code) REFERENCES Software(Code),
    FOREIGN KEY (CS_Code) REFERENCES Computer_System(Code),
    Software_COde INTEGER CHECK ( Software_COde > 0 ),
    CS_Code INTEGER CHECK ( CS_Code > 0 )
);
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
);