bonus (DBA)

call_track (DBA)
contact (DBA)
customer (DBA)

department (DBA)

employee (DBA)
exam_xref_info (DBA)
exam_xref_list (DBA)
examples (DBA)
examples_categories_list (DBA)
examples_groups (DBA)
examples_previews (DBA)

fin_code (DBA)
fin_data (DBA)

ole (DBA)

printer (DBA)
product (DBA)
province (DBA)

sales_order (DBA)
sales_order_items (DBA)
states (DBA)

terminated_employee (DBA)


// DBA . bonus Table

CREATE TABLE DBA.bonus (
   emp_id                         /* PK        */ INTEGER NOT NULL,
   bonus_date                     /* PK        */ DATE NOT NULL,
   bonus_amount                                   NUMERIC ( 9, 2 ) NULL );
// ***** Primary Key for DBA.bonus *****
ALTER TABLE DBA.bonus ADD PRIMARY KEY (
   emp_id,
   bonus_date );

// DBA . call_track Table

CREATE TABLE DBA.call_track (
   id                             /* PK        */ INTEGER DEFAULT autoincrement NOT NULL,
   call_date                                      DATE NOT NULL,
   caller_name                                    VARCHAR ( 30 ) NOT NULL,
   call_notes                                     VARCHAR ( 32765 ) NOT NULL );
// ***** Primary Key for DBA.call_track *****
ALTER TABLE DBA.call_track ADD PRIMARY KEY (
   id );

// DBA . contact Table

CREATE TABLE DBA.contact (
   id                             /* PK        */ INTEGER NOT NULL,
   last_name                                      CHAR ( 15 ) NOT NULL,
   first_name                                     CHAR ( 15 ) NOT NULL,
   title                                          CHAR ( 2 ) NOT NULL,
   street                                         CHAR ( 30 ) NOT NULL,
   city                                           CHAR ( 20 ) NOT NULL,
   state                                          CHAR ( 2 ) NOT NULL,
   zip                                            CHAR ( 5 ) NOT NULL,
   phone                                          CHAR ( 10 ) NULL,
   fax                                            CHAR ( 10 ) NULL );
// ***** Primary Key for DBA.contact *****
ALTER TABLE DBA.contact ADD PRIMARY KEY (
   id );

// DBA . customer Table

CREATE TABLE DBA.customer (
   id                             /* PK        */ INTEGER NOT NULL,
   fname                          /*         X */ CHAR ( 15 ) NOT NULL,
   lname                          /*         X */ CHAR ( 20 ) NOT NULL,
   address                                        CHAR ( 35 ) NOT NULL,
   city                                           CHAR ( 20 ) NOT NULL,
   state                                          CHAR ( 2 ) NOT NULL,
   zip                                            CHAR ( 10 ) NOT NULL,
   phone                                          CHAR ( 12 ) NOT NULL,
   company_name                                   CHAR ( 35 ) NULL );
// ***** Children of DBA.customer *****
// DBA.sales_order

// ***** Primary Key for DBA.customer *****
ALTER TABLE DBA.customer ADD PRIMARY KEY (
   id );
// ***** Non-Unique Index for DBA.customer *****
CREATE INDEX ix_cust_name ON DBA.customer (
   lname ASC,
   fname ASC );


// DBA . department Table

CREATE TABLE DBA.department (
   dept_id                        /* PK        */ INTEGER NOT NULL,
   dept_name                                      CHAR ( 40 ) NOT NULL,
   dept_head_id                   /*    FK     */ INTEGER NULL );
// ***** Parents of DBA.department *****
// DBA.employee

// ***** Children of DBA.department *****
// DBA.employee

// ***** Primary Key for DBA.department *****
ALTER TABLE DBA.department ADD PRIMARY KEY (
   dept_id );
// ***** Foreign Key from DBA.department to Parent DBA.employee *****
ALTER TABLE DBA.department ADD FOREIGN KEY ky_dept_head (
      dept_head_id )
   REFERENCES DBA.employee (
      emp_id ) ON UPDATE RESTRICT ON DELETE SET NULL 


// DBA . employee Table

CREATE TABLE DBA.employee (
   emp_id                         /* PK        */ INTEGER NOT NULL,
   manager_id                                     INTEGER NULL,
   emp_fname                                      CHAR ( 20 ) NOT NULL,
   emp_lname                                      CHAR ( 20 ) NOT NULL,
   dept_id                        /*    FK     */ INTEGER NOT NULL,
   street                                         CHAR ( 40 ) NOT NULL,
   city                                           CHAR ( 20 ) NOT NULL,
   state                                          CHAR ( 4 ) NOT NULL,
   zip_code                                       CHAR ( 9 ) NOT NULL,
   phone                                          CHAR ( 10 ) NULL,
   status                                         CHAR ( 1 ) NULL,
   ss_number                                      CHAR ( 11 ) NOT NULL,
   salary                                         NUMERIC ( 20, 3 ) NOT NULL,
   start_date                                     DATE NOT NULL,
   termination_date                               DATE NULL,
   birth_date                                     DATE NULL,
   bene_health_ins                                CHAR ( 1 ) NULL,
   bene_life_ins                                  CHAR ( 1 ) NULL,
   bene_day_care                                  CHAR ( 1 ) NULL,
   sex                                            CHAR ( 1 ) NULL );
// ***** Parents of DBA.employee *****
// DBA.department

// ***** Children of DBA.employee *****
// DBA.department
// DBA.sales_order

// ***** Primary Key for DBA.employee *****
ALTER TABLE DBA.employee ADD PRIMARY KEY (
   emp_id );
// ***** Foreign Key from DBA.employee to Parent DBA.department *****
ALTER TABLE DBA.employee ADD FOREIGN KEY ky_dept_id (
      dept_id )
   REFERENCES DBA.department (
      dept_id ) ON UPDATE RESTRICT ON DELETE RESTRICT 


// DBA . exam_xref_info Table

CREATE TABLE DBA.exam_xref_info (
   object_ref                     /* PK      X */ VARCHAR ( 40 ) NOT NULL,
   object_ref_type                /*         X */ VARCHAR ( 100 ) NOT NULL,
   event                          /* PK      X */ VARCHAR ( 100 ) NOT NULL,
   referenced_in                  /* PK      X */ VARCHAR ( 40 ) NOT NULL,
   ref_in_type                                    VARCHAR ( 40 ) NOT NULL,
   pbl                                            VARCHAR ( 60 ) NOT NULL,
   application                    /* PK        */ CHAR ( 10 ) NOT NULL,
   scope                                          CHAR ( 1 ) NULL,
   short_event                                    VARCHAR ( 40 ) NULL );
// ***** Primary Key for DBA.exam_xref_info *****
ALTER TABLE DBA.exam_xref_info ADD PRIMARY KEY (
   object_ref,
   event,
   referenced_in,
   application );
// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx1 ON DBA.exam_xref_info (
   object_ref ASC,
   referenced_in ASC );

// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx2 ON DBA.exam_xref_info (
   object_ref_type ASC,
   referenced_in ASC );

// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx3 ON DBA.exam_xref_info (
   referenced_in ASC,
   event ASC );

// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx4 ON DBA.exam_xref_info (
   referenced_in ASC );


// DBA . exam_xref_list Table

CREATE TABLE DBA.exam_xref_list (
   object                         /* PK        */ VARCHAR ( 40 ) NOT NULL,
   refer                          /* PK      X */ VARCHAR ( 40 ) NOT NULL,
   application                    /* PK        */ CHAR ( 10 ) NOT NULL );
// ***** Primary Key for DBA.exam_xref_list *****
ALTER TABLE DBA.exam_xref_list ADD PRIMARY KEY (
   object,
   refer,
   application );
// ***** Non-Unique Index for DBA.exam_xref_list *****
CREATE INDEX xref_list_idx1 ON DBA.exam_xref_list (
   refer ASC );


// DBA . examples Table

CREATE TABLE DBA.examples (
   title                                          CHAR ( 60 ) NOT NULL,
   window                         /* PK        */ CHAR ( 40 ) NOT NULL,
   version                                        CHAR ( 5 ) NULL,
   description                                    CHAR ( 32765 ) NULL,
   technique                                      CHAR ( 32765 ) NULL,
   new                                            SMALLINT NULL,
   enhanced                                       SMALLINT NULL,
   ostype                                         VARCHAR ( 40 ) NULL,
   pbtype                                         VARCHAR ( 17 ) NULL,
   exhelp                                         SMALLINT NULL );
// ***** Children of DBA.examples *****
// DBA.examples_groups
// DBA.examples_previews

// ***** Primary Key for DBA.examples *****
ALTER TABLE DBA.examples ADD PRIMARY KEY (
   window );

// DBA . examples_categories_list Table

CREATE TABLE DBA.examples_categories_list (
   group_id                       /* PK        */ CHAR ( 40 ) NOT NULL );
// ***** Children of DBA.examples_categories_list *****
// DBA.examples_groups

// ***** Primary Key for DBA.examples_categories_list *****
ALTER TABLE DBA.examples_categories_list ADD PRIMARY KEY (
   group_id );

// DBA . examples_groups Table

CREATE TABLE DBA.examples_groups (
   group_id                       /* PK FK     */ CHAR ( 40 ) NOT NULL,
   window                         /* PK FK     */ CHAR ( 40 ) NOT NULL );
// ***** Parents of DBA.examples_groups *****
// DBA.examples
// DBA.examples_categories_list

// ***** Primary Key for DBA.examples_groups *****
ALTER TABLE DBA.examples_groups ADD PRIMARY KEY (
   group_id,
   window );
// ***** Foreign Key from DBA.examples_groups to Parent DBA.examples_categories_list *****
ALTER TABLE DBA.examples_groups ADD FOREIGN KEY examples_group_fk2 (
      group_id )
   REFERENCES DBA.examples_categories_list (
      group_id ) ON UPDATE RESTRICT ON DELETE RESTRICT 

// ***** Foreign Key from DBA.examples_groups to Parent DBA.examples *****
ALTER TABLE DBA.examples_groups ADD FOREIGN KEY examples_groups_fk (
      window )
   REFERENCES DBA.examples (
      window ) ON UPDATE RESTRICT ON DELETE RESTRICT 


// DBA . examples_previews Table

CREATE TABLE DBA.examples_previews (
   window                         /* PK FK U   */ CHAR ( 40 ) NOT NULL,
   screen                                         IMAGE NULL );
// ***** Parents of DBA.examples_previews *****
// DBA.examples

// ***** Primary Key for DBA.examples_previews *****
ALTER TABLE DBA.examples_previews ADD PRIMARY KEY (
   window );
// ***** Foreign Key from DBA.examples_previews to Parent DBA.examples *****
ALTER TABLE DBA.examples_previews ADD FOREIGN KEY examples_previews_fk (
      window )
   REFERENCES DBA.examples (
      window ) ON UPDATE RESTRICT ON DELETE CASCADE 

// ***** Unique Constraint for DBA.examples_previews *****
ALTER TABLE DBA.examples_previews ADD UNIQUE (
   window );

// ***** Unique Index for DBA.examples_previews *****
CREATE UNIQUE INDEX previews_index ON DBA.examples_previews (
   window ASC );


// DBA . fin_code Table

CREATE TABLE DBA.fin_code (
   code                           /* PK        */ CHAR ( 2 ) NOT NULL,
   type                                           CHAR ( 10 ) NOT NULL,
   description                                    CHAR ( 50 ) NULL );
// ***** Children of DBA.fin_code *****
// DBA.fin_data
// DBA.sales_order

// ***** Primary Key for DBA.fin_code *****
ALTER TABLE DBA.fin_code ADD PRIMARY KEY (
   code );

// DBA . fin_data Table

CREATE TABLE DBA.fin_data (
   year                           /* PK        */ CHAR ( 4 ) NOT NULL,
   quarter                        /* PK        */ CHAR ( 2 ) NOT NULL,
   code                           /* PK FK   X */ CHAR ( 2 ) NOT NULL,
   amount                                         NUMERIC ( 9 ) NULL );
// ***** Parents of DBA.fin_data *****
// DBA.fin_code

// ***** Primary Key for DBA.fin_data *****
ALTER TABLE DBA.fin_data ADD PRIMARY KEY (
   year,
   quarter,
   code );
// ***** Foreign Key from DBA.fin_data to Parent DBA.fin_code *****
ALTER TABLE DBA.fin_data ADD FOREIGN KEY ky_code_data (
      code )
   REFERENCES DBA.fin_code (
      code ) ON UPDATE RESTRICT ON DELETE CASCADE 

// ***** Non-Unique Index for DBA.fin_data *****
CREATE INDEX fin_data_idx ON DBA.fin_data (
   code ASC );


// DBA . ole Table

CREATE TABLE DBA.ole (
   id                             /* PK        */ CHAR ( 20 ) NOT NULL,
   object                                         IMAGE NULL,
   description                                    VARCHAR ( 32765 ) NOT NULL );
// ***** Primary Key for DBA.ole *****
ALTER TABLE DBA.ole ADD PRIMARY KEY (
   id );

// DBA . printer Table

CREATE TABLE DBA.printer (
   rep                            /* PK        */ CHAR ( 10 ) NOT NULL,
   quarter                        /* PK        */ CHAR ( 2 ) NOT NULL,
   product                        /* PK        */ CHAR ( 12 ) NOT NULL,
   units                                          INTEGER NOT NULL );
// ***** Primary Key for DBA.printer *****
ALTER TABLE DBA.printer ADD PRIMARY KEY (
   rep,
   quarter,
   product );

// DBA . product Table

CREATE TABLE DBA.product (
   id                             /* PK        */ INTEGER NOT NULL,
   name                           /*         X */ CHAR ( 15 ) NOT NULL,
   description                    /*         X */ CHAR ( 30 ) NOT NULL,
   prod_size                      /*         X */ CHAR ( 18 ) NOT NULL,
   color                          /*         X */ CHAR ( 6 ) NOT NULL,
   quantity                                       INTEGER NOT NULL,
   unit_price                                     NUMERIC ( 15, 2 ) NOT NULL,
   picture_name                                   CHAR ( 12 ) NULL,
   catalog_picture                                IMAGE NULL );
// ***** Children of DBA.product *****
// DBA.sales_order_items

// ***** Primary Key for DBA.product *****
ALTER TABLE DBA.product ADD PRIMARY KEY (
   id );
// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_color ON DBA.product (
   color ASC );

// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_desc ON DBA.product (
   description ASC );

// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_name ON DBA.product (
   name ASC );

// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_size ON DBA.product (
   prod_size ASC );


// DBA . province Table

CREATE TABLE DBA.province (
   province_code                  /* PK        */ VARCHAR ( 2 ) NOT NULL,
   province_name                                  VARCHAR ( 50 ) NOT NULL );
// ***** Primary Key for DBA.province *****
ALTER TABLE DBA.province ADD PRIMARY KEY (
   province_code );

// DBA . sales_order Table

CREATE TABLE DBA.sales_order (
   id                             /* PK        */ INTEGER NOT NULL,
   cust_id                        /*    FK   X */ INTEGER NOT NULL,
   order_date                                     DATE NOT NULL,
   fin_code_id                    /*    FK     */ CHAR ( 2 ) NULL,
   region                                         CHAR ( 7 ) NULL,
   sales_rep                      /*    FK     */ INTEGER NOT NULL );
// ***** Parents of DBA.sales_order *****
// DBA.customer
// DBA.employee
// DBA.fin_code

// ***** Children of DBA.sales_order *****
// DBA.sales_order_items

// ***** Primary Key for DBA.sales_order *****
ALTER TABLE DBA.sales_order ADD PRIMARY KEY (
   id );
// ***** Foreign Key from DBA.sales_order to Parent DBA.customer *****
ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_customer (
      cust_id )
   REFERENCES DBA.customer (
      id ) ON UPDATE RESTRICT ON DELETE RESTRICT 

// ***** Foreign Key from DBA.sales_order to Parent DBA.employee *****
ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_employee_id (
      sales_rep )
   REFERENCES DBA.employee (
      emp_id ) ON UPDATE RESTRICT ON DELETE RESTRICT 

// ***** Foreign Key from DBA.sales_order to Parent DBA.fin_code *****
ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_fincode (
      fin_code_id )
   REFERENCES DBA.fin_code (
      code ) ON UPDATE RESTRICT ON DELETE SET NULL 

// ***** Non-Unique Index for DBA.sales_order *****
CREATE INDEX ix_sales_cust ON DBA.sales_order (
   cust_id ASC );


// DBA . sales_order_items Table

CREATE TABLE DBA.sales_order_items (
   id                             /* PK FK     */ INTEGER NOT NULL,
   line_id                        /* PK        */ SMALLINT NOT NULL,
   prod_id                        /*    FK   X */ INTEGER NOT NULL,
   quantity                                       INTEGER NOT NULL,
   ship_date                                      DATE NOT NULL );
// ***** Parents of DBA.sales_order_items *****
// DBA.product
// DBA.sales_order

// ***** Primary Key for DBA.sales_order_items *****
ALTER TABLE DBA.sales_order_items ADD PRIMARY KEY (
   id,
   line_id );
// ***** Foreign Key from DBA.sales_order_items to Parent DBA.product *****
ALTER TABLE DBA.sales_order_items ADD FOREIGN KEY ky_prod_id (
      prod_id )
   REFERENCES DBA.product (
      id ) ON UPDATE RESTRICT ON DELETE RESTRICT 

// ***** Foreign Key from DBA.sales_order_items to Parent DBA.sales_order *****
ALTER TABLE DBA.sales_order_items ADD FOREIGN KEY id_fk (
      id )
   REFERENCES DBA.sales_order (
      id ) ON UPDATE RESTRICT ON DELETE CASCADE 

// ***** Non-Unique Index for DBA.sales_order_items *****
CREATE INDEX ix_item_prod ON DBA.sales_order_items (
   prod_id ASC );


// DBA . states Table

CREATE TABLE DBA.states (
   state_id                       /* PK        */ CHAR ( 3 ) NOT NULL,
   state_name                                     CHAR ( 24 ) NOT NULL,
   state_capital                  /* PK        */ CHAR ( 24 ) NOT NULL,
   country                                        CHAR ( 3 ) NOT NULL );
// ***** Primary Key for DBA.states *****
ALTER TABLE DBA.states ADD PRIMARY KEY (
   state_id,
   state_capital );

// DBA . terminated_employee Table

CREATE TABLE DBA.terminated_employee (
   emp_id                         /* PK        */ INTEGER NOT NULL,
   emp_fname                                      VARCHAR ( 20 ) NOT NULL,
   emp_lname                                      VARCHAR ( 20 ) NOT NULL,
   start_date                                     DATE NOT NULL,
   termination_date                               DATE NULL,
   dept_id                                        INTEGER NOT NULL,
   street                                         VARCHAR ( 40 ) NOT NULL,
   city                                           VARCHAR ( 20 ) NOT NULL,
   state                                          VARCHAR ( 4 ) NOT NULL,
   zip_code                                       VARCHAR ( 9 ) NOT NULL,
   phone                                          VARCHAR ( 10 ) NULL,
   ss_number                                      VARCHAR ( 11 ) NOT NULL,
   salary                                         NUMERIC ( 20, 3 ) NOT NULL,
   sex                                            VARCHAR ( 1 ) NULL );
// ***** Primary Key for DBA.terminated_employee *****
ALTER TABLE DBA.terminated_employee ADD PRIMARY KEY (
   emp_id );