schema8.sql

derby database source code.good for you.

-- SQL Test Suite, V6.0, Schema Definition, schema8.smi
-- 59-byte ID
-- TEd Version #
-- date_time print
-- ***************************************************************
-- ****** THIS FILE SHOULD BE RUN UNDER AUTHORIZATION ID SUN *****
-- ***************************************************************

-- This file defines the base tables used in most of the CDR tests.

-- This non-standard schema definition is provided so that
-- implementations which require semicolons to terminate statements,
-- but which are otherwise conforming, can still execute the
-- remaining tests.


  CREATE SCHEMA
--O      AUTHORIZATION SUN;
      SUN;
  set schema SUN;


  CREATE TABLE SUN.ECCO (C1 CHAR(2));
--O  CREATE TABLE ECCO (C1 CHAR(2));

  CREATE TABLE STAFF
   (EMPNUM   CHAR(3) NOT NULL,
    EMPNAME  CHAR(20),
    GRADE    DECIMAL(4),
    CITY     CHAR(15));


  CREATE TABLE PROJ
   (PNUM     CHAR(3) NOT NULL,
    PNAME    CHAR(20),
    PTYPE    CHAR(6),
    BUDGET   DECIMAL(9),
    CITY     CHAR(15));


  CREATE TABLE WORKS
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5));

  CREATE TABLE STAFF3
   (EMPNUM   CHAR(3) NOT NULL,
    EMPNAME  CHAR(20),
    GRADE    DECIMAL(4),
    CITY     CHAR(15),
    UNIQUE (EMPNUM));


  CREATE TABLE PROJ3
   (PNUM     CHAR(3) NOT NULL,
    PNAME    CHAR(20),
    PTYPE    CHAR(6),
    BUDGET   DECIMAL(9),
    CITY     CHAR(15),
    UNIQUE (PNUM));


  CREATE TABLE WORKS3
   (EMPNUM   CHAR(3) NOT NULL,
    PNUM     CHAR(3) NOT NULL,
    HOURS    DECIMAL(5),
    FOREIGN KEY (EMPNUM) REFERENCES STAFF3(EMPNUM),
    FOREIGN KEY (PNUM) REFERENCES PROJ3(PNUM));


        CREATE TABLE STAFF4 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20) DEFAULT NULL,
--O                GRADE DECIMAL(4) DEFAULT 0,
--O                CITY   CHAR(15) DEFAULT '               ');
                GRADE DECIMAL(4) ,
                CITY   CHAR(15) );


        CREATE TABLE STAFF14 (EMPNUM    CHAR(3) NOT NULL,
--O                EMPNAME  CHAR(20) DEFAULT USER,
                EMPNAME  CHAR(20) ,
        -- EMPNAME CHAR precision may be changed to implementation-defined
        --              precision for value of USER
                GRADE DECIMAL(4),
                CITY   CHAR(15));


        CREATE TABLE STAFF5 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (GRADE > 0 AND GRADE < 20));


        CREATE TABLE STAFF6 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4) CHECK (GRADE > 0 AND GRADE < 20),
                CITY   CHAR(15));

        CREATE TABLE STAFF7 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (GRADE BETWEEN 1 AND 20));


        CREATE TABLE STAFF8 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (EMPNAME IS NOT NULL));


        CREATE TABLE STAFF9 (EMPNUM    CHAR(3) NOT NULL PRIMARY KEY,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                CHECK (EMPNAME NOT LIKE 'T%'));


        CREATE TABLE STAFF10 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (GRADE NOT IN (5,22)));


        CREATE TABLE STAFF11 (EMPNUM    CHAR(3) NOT NULL PRIMARY KEY,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                CHECK (GRADE NOT IN (5,22) 
                            AND EMPNAME NOT LIKE 'T%'));


        CREATE TABLE STAFF12 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (NOT GRADE IN (5,22) 
                            AND NOT EMPNAME LIKE 'T%'));


        CREATE TABLE STAFF13 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20),
                GRADE DECIMAL(4),
                CITY   CHAR(15),
                PRIMARY KEY (EMPNUM),
                CHECK (NOT EMPNAME IS NULL));

        CREATE TABLE STAFF15 (EMPNUM    CHAR(3),
                EMPNAME  CHAR(20) NOT NULL,
                GRADE DECIMAL(4),
                CITY   CHAR(15));
   
        CREATE TABLE STAFF16 (EMPNUM    CHAR(3) NOT NULL,
                EMPNAME  CHAR(20) DEFAULT NULL,
                GRADE DECIMAL(4) NOT NULL CHECK (GRADE IN (100,150,200)),
                CITY   CHAR(15), PRIMARY KEY (GRADE,EMPNUM));
                
        CREATE TABLE SIZ1_P
           (S1   CHAR(3) NOT NULL,
            S2   CHAR(3) NOT NULL,
            S3   DECIMAL(4) NOT NULL,
            S4   CHAR(3) NOT NULL,
            S5   DECIMAL(4) NOT NULL,
            S6   CHAR(3) NOT NULL,
            R1   CHAR(3),
            R2   CHAR(3),
            R3   DECIMAL(4),
            UNIQUE   (S1,S2,S3,S4,S5,S6));
        
        
        CREATE TABLE SIZ1_F
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(3),
            F3   DECIMAL(4),
            F4   CHAR(3),
            F5   DECIMAL(4),
            F6   CHAR(3),
            R1   CHAR(3),
            R2   DECIMAL(5),
            R3   DECIMAL(4),
           FOREIGN KEY   (F1,F2,F3,F4,F5,F6)
           REFERENCES SIZ1_P(S1,S2,S3,S4,S5,S6));
        
        
        
        CREATE TABLE SIZ2_P
           (P1   CHAR(3) NOT NULL,
            P2   CHAR(3) NOT NULL,
            P3   DECIMAL(4) NOT NULL,
            P4   CHAR(3) NOT NULL,
            P5   DECIMAL(4) NOT NULL,
            P6   CHAR(3) NOT NULL,
            P7   CHAR(3) NOT NULL,
            P8   DECIMAL(4) NOT NULL,
            P9   DECIMAL(4) NOT NULL,
            P10   DECIMAL(4) NOT NULL,
            P11   CHAR(4),
            UNIQUE (P1),
            UNIQUE (P2),
            UNIQUE (P3),
            UNIQUE (P4),
            UNIQUE (P5),
            UNIQUE (P6),
            UNIQUE (P7),
            UNIQUE (P8),
            UNIQUE (P9),
            UNIQUE (P10));
        
        
        CREATE TABLE SIZ2_F1
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P1));
        
        CREATE TABLE SIZ2_F2
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P2));
        
        CREATE TABLE SIZ2_F3
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P3));
        
        CREATE TABLE SIZ2_F4
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P4));
        
        CREATE TABLE SIZ2_F5
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P5));
        
        CREATE TABLE SIZ2_F6
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P6));
        
        CREATE TABLE SIZ2_F7
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P7));

        CREATE TABLE SIZ2_F8
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P8));
        
        CREATE TABLE SIZ2_F9
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P9));
        
        CREATE TABLE SIZ2_F10
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
            FOREIGN KEY (F1) 
            REFERENCES SIZ2_P(P10));
        
        
        CREATE TABLE SIZ3_P1
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P2
           (F1   CHAR(3) NOT NULL,
            F2   CHAR(8),
            UNIQUE (F1));
        
        CREATE TABLE SIZ3_P3
           (F1   DECIMAL(4) NOT NULL,
            F2   CHAR(8),
           UNIQUE (F1));