

Tables
========================================================================



PUBLIC.BOOKS.AUTHORS                                             [table]
------------------------------------------------------------------------
Contact details for book authors
  FIRSTNAME                         VARCHAR(20) NOT NULL        
  LASTNAME                          VARCHAR(20) NOT NULL        
  ADDRESS1                          VARCHAR(255)                
  ADDRESS2                          VARCHAR(255)                
  CITY                              VARCHAR(50)                 
  STATE                             CHARACTER(2)                
  POSTALCODE                        VARCHAR(10)                 
  COUNTRY                           VARCHAR(50)                 
                                    default 'USA'               

Primary Key

PK_AUTHORS                                                 [primary key]

Indexes

PK_AUTHORS                                                [unique index]
  ID                                ascending                   

IDX_B_AUTHORS                                         [non-unique index]
  LASTNAME                          ascending                   
  FIRSTNAME                         ascending                   

IDX_A_AUTHORS                                         [non-unique index]
  CITY                              ascending                   
  STATE                             ascending                   
  POSTALCODE                        ascending                   
  COUNTRY                           ascending                   

Triggers

TRG_AUTHORS                                                    [trigger]
  after delete, per row
                                                      [action statement]
UPDATE BOOKS.PUBLISHERS SET PUBLISHER='Jacob'WHERE PUBLISHER='John'

Table Constraints

CHECK_UPPERCASE_STATE                                 [check constraint]
  STATE                                                         

SYS_CT_10105                                          [check constraint]

SYS_CT_10106                                          [check constraint]
  FIRSTNAME                                                     

SYS_CT_10107                                          [check constraint]
  LASTNAME                                                      



PUBLIC.BOOKS.AUTHORSLIST                                          [view]
------------------------------------------------------------------------
  FIRSTNAME                         VARCHAR(20)                 
  LASTNAME                          VARCHAR(20)                 



PUBLIC.BOOKS.BOOKAUTHORS                                         [table]
------------------------------------------------------------------------
Relationship between books and their authors, 
along with the latest updated information
  BOOKID                            INTEGER NOT NULL            
  AUTHORID                          INTEGER NOT NULL            
  SOMEDATA                          VARCHAR(30)                 

Foreign Keys

Z_FK_AUTHOR                                [foreign key, with no action]
  AUTHORID (1..many)--> PUBLIC.BOOKS.AUTHORS.ID                              

SYS_FK_10121                               [foreign key, with no action]
  BOOKID (1..many)--> PUBLIC.BOOKS.BOOKS.ID                              

Indexes

Z_FK_AUTHOR                                           [non-unique index]
  AUTHORID                          ascending                   

SYS_FK_10121                                          [non-unique index]
  BOOKID                            ascending                   

UIDX_BOOKAUTHORS                                          [unique index]
  BOOKID                            ascending                   
  AUTHORID                          ascending                   

Table Constraints

SYS_CT_10119                                          [check constraint]
  BOOKID                                                        

SYS_CT_10120                                          [check constraint]
  AUTHORID                                                      



PUBLIC.BOOKS.BOOKS                                               [table]
------------------------------------------------------------------------
Details for published books
  TITLE                             VARCHAR(255) NOT NULL       
                                    Book title                  
  DESCRIPTION                       VARCHAR(255)                
                                    Book description
(Usually the blurb from the book jacket or promotional materials)
  PUBLISHERID                       INTEGER NOT NULL            
                                    Foreign key to the book publisher
  PUBLICATIONDATE                   DATE                        
                                    Book publication date       
  PRICE                             DOUBLE(64, 0)               
                                    Current price for the book  
  PREVIOUSEDITIONID                 INTEGER                     

Primary Key

PK_BOOKS                                                   [primary key]

Foreign Keys

FK_PREVIOUSEDITION                         [foreign key, with no action]
  ID <--(0..1) PREVIOUSEDITIONID                                

Indexes

PK_BOOKS                                                  [unique index]
  ID                                ascending                   

U_PREVIOUSEDITION                                         [unique index]
  PREVIOUSEDITIONID                 ascending                   

FK_PREVIOUSEDITION                                    [non-unique index]
  PREVIOUSEDITIONID                 ascending                   

Table Constraints

U_PREVIOUSEDITION                                    [unique constraint]
  PREVIOUSEDITIONID                                             

SYS_CT_10112                                          [check constraint]

SYS_CT_10113                                          [check constraint]
  TITLE                                                         

SYS_CT_10114                                          [check constraint]
  PUBLISHERID                                                   



PUBLIC.BOOKS."Celebrities"                                       [table]
------------------------------------------------------------------------
  "Id"                              INTEGER NOT NULL            
  NAME                              VARCHAR(20)                 

Primary Key

"PK_Celebrities"                                           [primary key]
  "Id"                                                          

Foreign Keys

SYS_FK_10131                               [foreign key, with no action]
  "Id" <--(1..1) PUBLIC.BOOKS."Celebrity Updates"."Celebrity Id"                              

Indexes

"PK_Celebrities"                                          [unique index]
  "Id"                              ascending                   

Table Constraints

SYS_CT_10128                                          [check constraint]
  "Id"                                                          



PUBLIC.BOOKS."Celebrity Updates"                                 [table]
------------------------------------------------------------------------
  "Celebrity Id"                    INTEGER NOT NULL            
  "UPDATE"                          VARCHAR(20)                 

Primary Key

"PK Celebrity Updates"                                     [primary key]
  "Celebrity Id"                                                

Foreign Keys

SYS_FK_10131                               [foreign key, with no action]
  "Celebrity Id" (1..1)--> PUBLIC.BOOKS."Celebrities"."Id"                              

Indexes

"PK Celebrity Updates"                                    [unique index]
  "Celebrity Id"                    ascending                   

SYS_FK_10131                                          [non-unique index]
  "Celebrity Id"                    ascending                   

Table Constraints

SYS_CT_10130                                          [check constraint]
  "Celebrity Id"                                                



PUBLIC.BOOKS.COUPONS                                             [table]
------------------------------------------------------------------------
  DATA                              CLOB                        
  COUPONS                           INTEGER ARRAY               
                                    default ARRAY[]             
  BOOKS                             VARCHAR(20) ARRAY[10]       

Primary Key

PK_COUPONS                                                 [primary key]

Indexes

PK_COUPONS                                                [unique index]
  ID                                ascending                   

Table Constraints

SYS_CT_10091                                          [check constraint]



PUBLIC.BOOKS.CUSTOMERDATA                                        [table]
------------------------------------------------------------------------
  CUSTOMERID                        INTEGER NOT NULL            
  DATAID                            INTEGER NOT NULL            
  DATA                              VALID_STRING(20)            

Primary Key

PK_CUSTOMERDATA                                            [primary key]
  CUSTOMERID                                                    
  DATAID                                                        

Foreign Keys

FK_CUSTOMER                                [foreign key, with no action]
  CUSTOMERID (1..many)--> PUBLIC.BOOKS.CUSTOMERS.ID                              

Indexes

FK_CUSTOMER                                           [non-unique index]
  CUSTOMERID                        ascending                   

PK_CUSTOMERDATA                                           [unique index]
  CUSTOMERID                        ascending                   
  DATAID                            ascending                   

Table Constraints

SYS_CT_10100                                          [check constraint]
  CUSTOMERID                                                    

SYS_CT_10101                                          [check constraint]
  DATAID                                                        



PUBLIC.BOOKS.CUSTOMERS                                           [table]
------------------------------------------------------------------------
  FIRSTNAME                         NAME_TYPE NOT NULL          
  LASTNAME                          NAME_TYPE NOT NULL          
  AGE                               AGE_TYPE                    

Primary Key

PK_CUSTOMERS                                               [primary key]

Indexes

PK_CUSTOMERS                                              [unique index]
  ID                                ascending                   

Table Constraints

SYS_CT_10093                                          [check constraint]

SYS_CT_10094                                          [check constraint]
  FIRSTNAME                                                     

SYS_CT_10095                                          [check constraint]
  LASTNAME                                                      



PUBLIC.BOOKS.PUBLISHERS                                          [table]
------------------------------------------------------------------------
List of book publishers
  PUBLISHER                         VARCHAR(255)                
                                    Name of book publisher      

Primary Key

SYS_PK_10127                                               [primary key]

Indexes

SYS_PK_10127                                              [unique index]
  ID                                ascending                   



PUBLIC.BOOKS.ΒΙΒΛΊΑ                                              [table]
------------------------------------------------------------------------
  ΜΟΝΑΔΙΚΌΣ                         SMALLINT NOT NULL           
  ΤΊΤΛΟΣ                            VARCHAR(255) NOT NULL       
  ΠΕΡΙΓΡΑΦΉ                         VARCHAR(255)                
  ΕΚΔΌΤΗΣ                           SMALLINT NOT NULL           

Primary Key

PK_ΒΙΒΛΊΑ                                                  [primary key]
  ΜΟΝΑΔΙΚΌΣ                                                     

Foreign Keys

FK_ΒΙΒΛΊΑ_PUBLISHERS                       [foreign key, with no action]
  ΕΚΔΌΤΗΣ (1..many)--> PUBLIC.BOOKS.PUBLISHERS.ID                              

Indexes

FK_ΒΙΒΛΊΑ_PUBLISHERS                                  [non-unique index]
  ΕΚΔΌΤΗΣ                           ascending                   

PK_ΒΙΒΛΊΑ                                                 [unique index]
  ΜΟΝΑΔΙΚΌΣ                         ascending                   

Table Constraints

SYS_CT_10134                                          [check constraint]
  ΜΟΝΑΔΙΚΌΣ                                                     

SYS_CT_10135                                          [check constraint]
  ΤΊΤΛΟΣ                                                        

SYS_CT_10136                                          [check constraint]
  ΕΚΔΌΤΗΣ                                                       



PUBLIC."PUBLISHER SALES".REGIONS                                 [table]
------------------------------------------------------------------------
  CITY                              VARCHAR(50) NOT NULL        
  STATE                             VARCHAR(2) NOT NULL         
  POSTALCODE                        VARCHAR(10) NOT NULL        
  COUNTRY                           VARCHAR(50) NOT NULL        

Primary Key

PK_CUSTOMERS                                               [primary key]
  POSTALCODE                                                    
  COUNTRY                                                       

Foreign Keys

FK_SALES_REGIONS                           [foreign key, with no action]
  POSTALCODE <--(1..many) PUBLIC."PUBLISHER SALES".SALES.POSTALCODE                              
  COUNTRY <--(1..many) PUBLIC."PUBLISHER SALES".SALES.COUNTRY                              

Indexes

PK_CUSTOMERS                                              [unique index]
  POSTALCODE                        ascending                   
  COUNTRY                           ascending                   

Table Constraints

SYS_CT_10207                                          [check constraint]
  CITY                                                          

SYS_CT_10208                                          [check constraint]
  STATE                                                         

SYS_CT_10209                                          [check constraint]
  POSTALCODE                                                    

SYS_CT_10210                                          [check constraint]
  COUNTRY                                                       



PUBLIC."PUBLISHER SALES".SALES                                   [table]
------------------------------------------------------------------------
  POSTALCODE                        VARCHAR(10) NOT NULL        
  COUNTRY                           VARCHAR(50) NOT NULL        
  BOOKID                            INTEGER                     
  COUPON_ID                         INTEGER                     
  PERIODENDDATE                     DATE                        
  TOTALAMOUNT                       DOUBLE(64, 0)               
  SALESDATAID                       INTEGER                     

Foreign Keys

FK_SALES_BOOK                              [foreign key, with no action]
  BOOKID (0..many)--> PUBLIC.BOOKS.BOOKS.ID                              

FK_SALES_SALESDATA                         [foreign key, with no action]
  SALESDATAID (0..many)--> PUBLIC."PUBLISHER SALES".SALESDATA.SALESDATAID                              

FK_SALES_REGIONS                           [foreign key, with no action]
  POSTALCODE (1..many)--> PUBLIC."PUBLISHER SALES".REGIONS.POSTALCODE                              
  COUNTRY (1..many)--> PUBLIC."PUBLISHER SALES".REGIONS.COUNTRY                              

Indexes

FK_SALES_BOOK                                         [non-unique index]
  BOOKID                            ascending                   

FK_SALES_SALESDATA                                    [non-unique index]
  SALESDATAID                       ascending                   

FK_SALES_REGIONS                                      [non-unique index]
  POSTALCODE                        ascending                   
  COUNTRY                           ascending                   

Table Constraints

SYS_CT_10216                                          [check constraint]
  POSTALCODE                                                    

SYS_CT_10217                                          [check constraint]
  COUNTRY                                                       



PUBLIC."PUBLISHER SALES".SALESDATA                               [table]
------------------------------------------------------------------------
  SALESDATAID                       INTEGER                     
  YEARLYAMOUNT                      DOUBLE(64, 0)               

Foreign Keys

FK_SALES_SALESDATA                         [foreign key, with no action]
  SALESDATAID <--(0..many) PUBLIC."PUBLISHER SALES".SALES.SALESDATAID                              

Indexes

UQ_CUSTOMERS                                              [unique index]
  SALESDATAID                       ascending                   

Table Constraints

UQ_CUSTOMERS                                         [unique constraint]
  SALESDATAID                                                   

