schema8.sql

derby database source code.good for you.

        CREATE TABLE SIZ3_P4
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P5
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P6
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P7
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P8
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P9
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P10
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_F
           (P1   CHAR(3) NOT NULL,
            P2   CHAR(3),
            P3   DECIMAL(4),
            P4   CHAR(3),
            P5   DECIMAL(4),
            P6   CHAR(3),
            P7   CHAR(3),
            P8   DECIMAL(4),
            P9   DECIMAL(4),
            P10   DECIMAL(4),
            P11   CHAR(4),
            FOREIGN KEY (P1)
            REFERENCES SIZ3_P1(F1),
            FOREIGN KEY (P2)
            REFERENCES SIZ3_P2(F1),
            FOREIGN KEY (P3)
            REFERENCES SIZ3_P3(F1),
            FOREIGN KEY (P4)
            REFERENCES SIZ3_P4(F1),
            FOREIGN KEY (P5)
            REFERENCES SIZ3_P5(F1),
            FOREIGN KEY (P6)
            REFERENCES SIZ3_P6(F1),
            FOREIGN KEY (P7)
            REFERENCES SIZ3_P7(F1),
            FOREIGN KEY (P8)
            REFERENCES SIZ3_P8(F1),
            FOREIGN KEY (P9)
            REFERENCES SIZ3_P9(F1),
            FOREIGN KEY (P10)
            REFERENCES SIZ3_P10(F1));

        CREATE TABLE DEPT
                (DNO DECIMAL(4) NOT NULL,
                 DNAME CHAR(20) NOT NULL,
                 DEAN CHAR(30),
                 PRIMARY KEY (DNO),
                 UNIQUE (DNAME));
     
        CREATE TABLE EMP
                (ENO DECIMAL(4) NOT NULL,
                 ENAME CHAR(20) NOT NULL,
                 EDESC CHAR(30),
                 DNO DECIMAL(4) NOT NULL,
                 DNAME CHAR(20),
                 BTH_DATE  DECIMAL(6) NOT NULL,
                 PRIMARY KEY (ENO),
                 UNIQUE (ENAME,BTH_DATE),
                 FOREIGN KEY (DNO) REFERENCES
                 DEPT(DNO),
                 FOREIGN KEY (DNAME) REFERENCES
                 DEPT(DNAME));
     
     
        CREATE TABLE EXPERIENCE
                (EXP_NAME CHAR(20),
                 BTH_DATE DECIMAL(6),
                 WK_DATE  DECIMAL(6),
                 DESCR CHAR(40),
                 FOREIGN KEY (EXP_NAME,BTH_DATE) REFERENCES
                 EMP(ENAME,BTH_DATE));
     
     -- The following tables, STAFF_M and PROJ_M reference each other.
     -- Table STAFF_M has a "forward reference" to PROJ_M.
                              
     CREATE TABLE STAFF_M
        (EMPNUM   CHAR(3) NOT NULL,
         EMPNAME  CHAR(20),
         GRADE    DECIMAL(4),
         CITY     CHAR(15),
         PRI_WK   CHAR(3),
         UNIQUE   (EMPNUM));

     
        CREATE TABLE PROJ_M
           (PNUM     CHAR(3) NOT NULL,
            PNAME    CHAR(20),
            PTYPE    CHAR(6),
            BUDGET   DECIMAL(9),
            CITY     CHAR(15),
            MGR   CHAR(3),
            UNIQUE (PNUM),
            FOREIGN KEY (MGR)
            REFERENCES STAFF_M(EMPNUM));

        ALTER TABLE STAFF_M ADD FOREIGN KEY (PRI_WK)
          REFERENCES PROJ_M (PNUM);
     
	--   The following table is self-referencing.

        CREATE TABLE STAFF_C
           (EMPNUM   CHAR(3) NOT NULL,
            EMPNAME  CHAR(20),
            GRADE    DECIMAL(4),
            CITY     CHAR(15),
            MGR   CHAR(3),
            UNIQUE   (EMPNUM),
            FOREIGN KEY (MGR)
            REFERENCES STAFF_C(EMPNUM));
     
     

     CREATE TABLE STAFF_P
        (EMPNUM   CHAR(3) NOT NULL,
         EMPNAME  CHAR(20),
         GRADE    DECIMAL(4),
         CITY     CHAR(15),
         UNIQUE  (EMPNUM));
     
     
     CREATE TABLE PROJ_P
        (PNUM     CHAR(3) NOT NULL,
         PNAME    CHAR(20),
         PTYPE    CHAR(6),
         BUDGET   DECIMAL(9),
         CITY     CHAR(15),
         UNIQUE   (PNUM));
     
     
        CREATE TABLE MID1 (P_KEY DECIMAL(4) NOT NULL UNIQUE,
                         F_KEY DECIMAL(4) REFERENCES MID1(P_KEY));

        CREATE TABLE ACR_SCH_P(P1 DECIMAL(4) NOT NULL UNIQUE,
                               P2 CHAR(4));

--O   CREATE TABLE CHAR_DEFAULT
--O               (SEX_CODE  CHAR(1)  DEFAULT 'F',
--O                NICKNAME  CHAR(20) DEFAULT 'No nickname given',
--O                INSURANCE1 CHAR(5)  DEFAULT 'basic');

--O   CREATE TABLE EXACT_DEF
--O               (BODY_TEMP NUMERIC(4,1) DEFAULT 98.6,
--O                MAX_NUM   NUMERIC(5)   DEFAULT -55555,
--O                MIN_NUM   DEC(6,6)     DEFAULT .000001);

--O   CREATE TABLE APPROX_DEF
--O               (X_COUNT   REAL DEFAULT 1.78E12,
--O                Y_COUNT   REAL DEFAULT -9.99E10,
--O                Z_COUNT   REAL DEFAULT 3.45E-11,
--O                ZZ_COUNT  REAL DEFAULT -7.6777E-7);

--O   CREATE TABLE SIZE_TAB
--O               (COL1 CHAR(75)  DEFAULT 
--O'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyz0123456789012',
--O                COL2 INTEGER   DEFAULT -999888777,
--O                COL3 DEC(15,6) DEFAULT 987654321.123456,
--O                COL4 REAL      DEFAULT -1.048576E22);   


   CREATE TABLE COMMODITY
         (C_NUM INTEGER NOT NULL,
          C_NAME CHAR(7) NOT NULL UNIQUE,
          PRIMARY KEY (C_NUM));

   CREATE TABLE CURRENCY_TABLE
         (CURRENCY CHAR(10) NOT NULL,
          DOLLAR_EQUIV NUMERIC(5, 2),
          PRIMARY KEY (CURRENCY));
                 
   CREATE TABLE MEASURE_TABLE
         (MEASURE CHAR(8) NOT NULL,
          POUND_EQUIV NUMERIC(8,2),
          PRIMARY KEY (MEASURE));

   CREATE TABLE C_TRANSACTION 
         (COMMOD_NO INTEGER,
          TOT_PRICE     DECIMAL(12,2),
          CURRENCY  CHAR(10),
          UNITS     INTEGER,
          MEASURE   CHAR(8),
          T_DATE    INTEGER,
          FOREIGN KEY (COMMOD_NO)
           REFERENCES COMMODITY,
          FOREIGN KEY (CURRENCY)
           REFERENCES CURRENCY_TABLE,
          FOREIGN KEY (MEASURE)
           REFERENCES MEASURE_TABLE);

  CREATE TABLE T6118REF (
       COL1 CHAR(20) NOT NULL, COL2 CHAR(20) NOT NULL,
       COL3 CHAR(20) NOT NULL, COL4 CHAR(20) NOT NULL,
       COL5 CHAR(23) NOT NULL, COL6 NUMERIC (4) NOT NULL,
       STR118 CHAR(118) NOT NULL UNIQUE,
       UNIQUE (COL1, COL2, COL4, COL3, COL5, COL6));

  CREATE TABLE T118(STR118 CHAR(118) NOT NULL UNIQUE,
    FOREIGN KEY (STR118) REFERENCES T6118REF (STR118));

  CREATE TABLE T6 (COL1 CHAR(20), COL2 CHAR(20),
                 COL3 CHAR(20), COL4 CHAR(20),
                 COL5 CHAR(23), COL6 NUMERIC (4),
    FOREIGN KEY (COL1, COL2, COL4, COL3, COL5, COL6)
      REFERENCES T6118REF (COL1, COL2, COL4, COL3, COL5, COL6));

-- ********************** create view statements *****************

  CREATE VIEW TESTREPORT AS
    SELECT TESTNO, RESULT, TESTTYPE
    FROM HU.TESTREPORT;
--O    FROM TESTREPORT;

--O   CREATE VIEW DOLLARS_PER_POUND (COMMODITY, UNIT_PRICE, FROM_DATE, TO_DATE)
--O      AS SELECT COMMODITY.C_NAME, 
--O                SUM(TOT_PRICE * DOLLAR_EQUIV) / SUM(UNITS * POUND_EQUIV),
--O                MIN(T_DATE), MAX(T_DATE)
--O         FROM C_TRANSACTION, COMMODITY, CURRENCY_TABLE, MEASURE_TABLE
--O         WHERE C_TRANSACTION.COMMOD_NO = COMMODITY.C_NUM
--O            AND C_TRANSACTION.CURRENCY = CURRENCY_TABLE.CURRENCY
--O            AND C_TRANSACTION.MEASURE  = MEASURE_TABLE.MEASURE
--O         GROUP BY COMMODITY.C_NAME
--O         HAVING SUM(TOT_PRICE * DOLLAR_EQUIV) > 10000;

-- View COST_PER_UNIT for OPTIONAL test 0403
-- Remove view from schema if it causes errors.

--O   CREATE VIEW COST_PER_UNIT
--O     (COMMODITY, UNIT_PRICE, CURRENCY, MEASURE)
--O      AS SELECT COMMODITY, UNIT_PRICE * POUND_EQUIV / DOLLAR_EQUIV,
--O                CURRENCY, MEASURE
--O            FROM DOLLARS_PER_POUND, CURRENCY_TABLE, MEASURE_TABLE;

        CREATE VIEW STAFF6_WITH_GRADES AS
                          SELECT EMPNUM,EMPNAME,GRADE,CITY
                          FROM STAFF6
                          WHERE GRADE > 0 AND GRADE < 20
  ;
--O                          WITH CHECK OPTION;

-- ************** grant statements follow *************
--O       GRANT SELECT ON SUN.ECCO TO PUBLIC;
        
--O        GRANT INSERT ON TESTREPORT
--O             TO PUBLIC;

--O        GRANT REFERENCES ON ACR_SCH_P TO SULLIVAN
--O              WITH GRANT OPTION;
        
--O        GRANT ALL PRIVILEGES ON PROJ_P
--O             TO SULLIVAN;

--O        GRANT ALL PRIVILEGES ON T6118REF TO FLATER;

--O        GRANT ALL PRIVILEGES ON T118 TO FLATER;

--O        GRANT ALL PRIVILEGES ON T6 TO FLATER;

--  Test GRANT without grant permission below.
--  "WITH GRANT OPTION" purposefully omitted from SUN's GRANT. 
--  Do not insert text "WITH GRANT OPTION"

--O        GRANT REFERENCES ON STAFF_P
--O             TO SULLIVAN;

--O        GRANT REFERENCES (C_NUM) ON COMMODITY TO SCHANZLE;

-- ************* End of Schema *************