Advertisement
saleks28

Subd1_8sem

Sep 23rd, 2020
824
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. -- Удаляем таблицы при их наличии
  3. BEGIN
  4.     EXECUTE IMMEDIATE 'DROP TABLE Software cascade constraints ';
  5. EXCEPTION
  6.     WHEN OTHERS THEN
  7.         DBMS_OUTPUT.PUT_LINE('Table Software does not exist');
  8. END;
  9. BEGIN
  10.     EXECUTE IMMEDIATE 'DROP TABLE Applications cascade constraints ';
  11. EXCEPTION
  12.     WHEN OTHERS THEN
  13.         DBMS_OUTPUT.PUT_LINE('Table Applications does not exist');
  14. END;
  15. BEGIN
  16.     EXECUTE IMMEDIATE 'DROP TABLE SystemSoft cascade constraints ';
  17. EXCEPTION
  18.     WHEN OTHERS THEN
  19.         DBMS_OUTPUT.PUT_LINE('Table SystemSoft does not exist');
  20. END;
  21. BEGIN
  22.     EXECUTE IMMEDIATE 'DROP TABLE Computer_System cascade constraints ';
  23. EXCEPTION
  24.     WHEN OTHERS THEN
  25.         DBMS_OUTPUT.PUT_LINE('Table Computer_System does not exist');
  26. END;
  27. BEGIN
  28.     EXECUTE IMMEDIATE 'DROP TABLE Applications_SystemSoft cascade constraints ';
  29. EXCEPTION
  30.     WHEN OTHERS THEN
  31.         DBMS_OUTPUT.PUT_LINE('Table Applications_SystemSoft does not exist');
  32. END;
  33. BEGIN
  34.     EXECUTE IMMEDIATE 'DROP TABLE Software_Computer_System cascade constraints ';
  35. EXCEPTION
  36.     WHEN OTHERS THEN
  37.         DBMS_OUTPUT.PUT_LINE('Table Software_Computer_System does not exist');
  38. END;
  39. BEGIN
  40.     EXECUTE IMMEDIATE 'DROP TABLE CS_Component cascade constraints ';
  41. EXCEPTION
  42.     WHEN OTHERS THEN
  43.         DBMS_OUTPUT.PUT_LINE('Table CS_Component does not exist');
  44. END;
  45.  
  46. -- Теперь создадим непосредственно сами таблицы
  47. -- В Software PK будет Code, а Name просто Unique
  48. CREATE TABLE Software (
  49.     PRIMARY KEY (Code),
  50.     Code INTEGER CHECK(Code > 0),
  51.     Name VARCHAR2(50) NOT NULL UNIQUE,
  52.     License VARCHAR2(30),
  53.     Cost_per_month REAL CHECK ( Cost_per_month >= 0.0 )
  54. );
  55. CREATE TABLE Applications (
  56.     PRIMARY KEY (Code),
  57.     FOREIGN KEY (Code) REFERENCES Software(Code),
  58.     Code INTEGER CHECK ( Code > 0 ),
  59.     Description VARCHAR2(50),
  60.     Purpose VARCHAR2(50) NOT NULL
  61. );
  62. CREATE TABLE SystemSoft (
  63.     PRIMARY KEY (Code),
  64.     FOREIGN KEY (Code) REFERENCES Software(Code),
  65.     Code INTEGER CHECK ( Code > 0 ),
  66.     Hardware VARCHAR2(50)
  67. );
  68. -- Таблица множественной ассоциии для Applications и SystemSoft
  69. CREATE TABLE Applications_SystemSoft (
  70.     PRIMARY KEY (Applications_Code, SystemSoft_Code),
  71.     FOREIGN KEY (Applications_Code) REFERENCES Applications(Code),
  72.     FOREIGN KEY (SystemSoft_Code) REFERENCES SystemSoft(Code),
  73.     Applications_Code INTEGER CHECK ( Applications_Code > 0 ),
  74.     SystemSoft_Code INTEGER CHECK ( SystemSoft_Code > 0 )
  75. );
  76. CREATE TABLE Computer_System (
  77.     PRIMARY KEY (Code),
  78.     Code INTEGER CHECK ( Code > 0 ),
  79.     Description VARCHAR2(50),
  80.     Purpose VARCHAR2(50) NOT NULL
  81. );
  82. -- Таблица множественной ассоциации для Software и Computer_System
  83. CREATE TABLE Software_Computer_System (
  84.     PRIMARY KEY (Software_Code, CS_Code),
  85.     FOREIGN KEY (Software_Code) REFERENCES Software(Code),
  86.     FOREIGN KEY (CS_Code) REFERENCES Computer_System(Code),
  87.     Software_COde INTEGER CHECK ( Software_COde > 0 ),
  88.     CS_Code INTEGER CHECK ( CS_Code > 0 )
  89. );
  90. CREATE TABLE CS_Component (
  91.     PRIMARY KEY (ComponentID),
  92.     FOREIGN KEY (Computer_System_Code) REFERENCES Computer_System(Code),
  93.     FOREIGN KEY (NextComponent) REFERENCES CS_Component(ComponentID),
  94.     ComponentID NUMBER GENERATED ALWAYS AS IDENTITY,
  95.     Computer_System_Code INTEGER CHECK ( Computer_System_Code > 0 ),
  96.     Accountable_EMP VARCHAR2(100) NOT NULL,
  97.     Critical INTEGER CHECK ( Critical >= 0 ),
  98.     NextComponent NUMBER
  99. );
  100.  
  101.  
  102.  
  103.  
  104.  
Advertisement
RAW Paste Data Copied
Advertisement