System Information

generated by SchemaCrawler 16.14.1
generated on 2021-03-24 21:13:50.607
database version HSQL Database Engine 2.5.1
driver version HSQL Database Engine Driver 2.5.1

 

 

Tables

PUBLIC.BOOKS.AUTHORS [table]
Contact details for book authors
1 ID INTEGER NOT NULL
2 FIRSTNAME VARCHAR(20) NOT NULL
3 LASTNAME VARCHAR(20) NOT NULL
4 ADDRESS1 VARCHAR(255)
5 ADDRESS2 VARCHAR(255)
6 CITY VARCHAR(50)
7 STATE CHARACTER(2)
8 POSTALCODE VARCHAR(10)
9 COUNTRY VARCHAR(50)
Primary Key
PK_AUTHORS [primary key]
1 ID
Foreign Keys
Z_FK_AUTHOR [foreign key, with no action]
1 ID ←(0..many) PUBLIC.BOOKS.BOOKAUTHORS.AUTHORID
Indexes
SYS_IDX_PK_AUTHORS_10111 [unique index]
1 ID ascending
IDX_B_AUTHORS [non-unique index]
3 LASTNAME ascending
2 FIRSTNAME ascending
IDX_A_AUTHORS [non-unique index]
6 CITY ascending
7 STATE ascending
8 POSTALCODE ascending
9 COUNTRY ascending
Triggers
TRG_AUTHORS [trigger, after delete, per row]
UPDATE BOOKS.PUBLISHERS SET PUBLISHER='Jacob'WHERE PUBLISHER='John'
Table Constraints
CHECK_UPPERCASE_STATE [check constraint]
7 STATE
SYS_CT_10108 [check constraint]
1 ID
SYS_CT_10109 [check constraint]
2 FIRSTNAME
SYS_CT_10110 [check constraint]
3 LASTNAME

 

PUBLIC.BOOKS.AUTHORSLIST [view]
1 ID INTEGER
2 FIRSTNAME VARCHAR(20)
3 LASTNAME VARCHAR(20)

 

PUBLIC.BOOKS.BOOKAUTHORS [table]
Relationship between books and their authors, along with the latest updated information
1 BOOKID INTEGER NOT NULL
2 AUTHORID INTEGER NOT NULL
3 SOMEDATA VARCHAR(30)
Foreign Keys
Z_FK_AUTHOR [foreign key, with no action]
1 AUTHORID (0..many)→ PUBLIC.BOOKS.AUTHORS.ID
SYS_FK_10128 [foreign key, with no action]
1 BOOKID (0..many)→ PUBLIC.BOOKS.BOOKS.ID
Indexes
SYS_IDX_Z_FK_AUTHOR_10134 [non-unique index]
2 AUTHORID ascending
SYS_IDX_SYS_FK_10128_10132 [non-unique index]
1 BOOKID ascending
UIDX_BOOKAUTHORS [unique index]
1 BOOKID ascending
2 AUTHORID ascending
Table Constraints
SYS_CT_10126 [check constraint]
1 BOOKID
SYS_CT_10127 [check constraint]
2 AUTHORID

 

PUBLIC.BOOKS.BOOKS [table]
Details for published books
1 ID INTEGER NOT NULL
Unique (internal) id for book
2 TITLE VARCHAR(255) NOT NULL
Book title
3 DESCRIPTION VARCHAR(255)
Book description (Usually the blurb from the book jacket or promotional materials)
4 PUBLISHERID INTEGER NOT NULL
Foreign key to the book publisher
5 PUBLICATIONDATE DATE
Book publication date
6 PRICE DOUBLE(64, 0)
Current price for the book
7 PREVIOUSEDITIONID INTEGER
Primary Key
PK_BOOKS [primary key]
1 ID
Foreign Keys
SYS_FK_10128 [foreign key, with no action]
1 ID ←(0..many) PUBLIC.BOOKS.BOOKAUTHORS.BOOKID
FK_PREVIOUSEDITION [foreign key, with no action]
1 ID ←(0..1) PREVIOUSEDITIONID
Indexes
SYS_IDX_PK_BOOKS_10119 [unique index]
1 ID ascending
SYS_IDX_U_PREVIOUSEDITION_10125 [unique index]
7 PREVIOUSEDITIONID ascending
SYS_IDX_FK_PREVIOUSEDITION_10123 [non-unique index]
7 PREVIOUSEDITIONID ascending
Table Constraints
U_PREVIOUSEDITION [unique constraint]
7 PREVIOUSEDITIONID
SYS_CT_10116 [check constraint]
1 ID
SYS_CT_10117 [check constraint]
2 TITLE
SYS_CT_10118 [check constraint]
4 PUBLISHERID

 

PUBLIC.BOOKS.COUPONS [table]
1 ID INTEGER NOT NULL
2 DATA CLOB
3 COUPONS INTEGER ARRAY
4 BOOKS VARCHAR(20) ARRAY[10]
Primary Key
PK_COUPONS [primary key]
1 ID
Indexes
SYS_IDX_PK_COUPONS_10093 [unique index]
1 ID ascending
Table Constraints
SYS_CT_10092 [check constraint]
1 ID

 

PUBLIC.BOOKS.CUSTOMERDATA [table]
1 ID INTEGER NOT NULL
2 DATA VALID_STRING(20)
Primary Key
PK_CUSTOMERDATA [primary key]
1 ID
Indexes
SYS_IDX_PK_CUSTOMERDATA_10104 [unique index]
1 ID ascending
Table Constraints
SYS_CT_10103 [check constraint]
1 ID

 

PUBLIC.BOOKS.CUSTOMERS [table]
1 ID INTEGER NOT NULL
2 FIRSTNAME NAME_TYPE NOT NULL
3 LASTNAME NAME_TYPE NOT NULL
4 AGE AGE_TYPE
Primary Key
PK_CUSTOMERS [primary key]
1 ID
Indexes
SYS_IDX_PK_CUSTOMERS_10098 [unique index]
1 ID ascending
Table Constraints
SYS_CT_10095 [check constraint]
1 ID
SYS_CT_10096 [check constraint]
2 FIRSTNAME
SYS_CT_10097 [check constraint]
3 LASTNAME

 

PUBLIC.BOOKS."Global Counts" [table]
1 "Global Count" INTEGER
2 "UPDATE" VARCHAR(20)

 

PUBLIC.BOOKS.PUBLISHERS [table]
List of book publishers
1 ID INTEGER NOT NULL
auto-incremented
Unique (internal) id for book publisher
2 PUBLISHER VARCHAR(255)
Name of book publisher
Primary Key
SYS_PK_10106 [primary key]
1 ID
Foreign Keys
FK_ΒΙΒΛΊΑ_PUBLISHERS [foreign key, with no action]
1 ID ←(0..many) PUBLIC.BOOKS.ΒΙΒΛΊΑ.ΕΚΔΌΤΗΣ
Indexes
SYS_IDX_SYS_PK_10106_10107 [unique index]
1 ID ascending

 

PUBLIC.BOOKS.ΒΙΒΛΊΑ [table]
1 ΜΟΝΑΔΙΚΌΣ SMALLINT NOT NULL
2 ΤΊΤΛΟΣ VARCHAR(255) NOT NULL
3 ΠΕΡΙΓΡΑΦΉ VARCHAR(255)
4 ΕΚΔΌΤΗΣ SMALLINT NOT NULL
Primary Key
PK_ΒΙΒΛΊΑ [primary key]
1 ΜΟΝΑΔΙΚΌΣ
Foreign Keys
FK_ΒΙΒΛΊΑ_PUBLISHERS [foreign key, with no action]
1 ΕΚΔΌΤΗΣ (0..many)→ PUBLIC.BOOKS.PUBLISHERS.ID
Indexes
SYS_IDX_FK_ΒΙΒΛΊΑ_PUBLISHERS_10144 [non-unique index]
4 ΕΚΔΌΤΗΣ ascending
SYS_IDX_PK_ΒΙΒΛΊΑ_10140 [unique index]
1 ΜΟΝΑΔΙΚΌΣ ascending
Table Constraints
SYS_CT_10137 [check constraint]
1 ΜΟΝΑΔΙΚΌΣ
SYS_CT_10138 [check constraint]
2 ΤΊΤΛΟΣ
SYS_CT_10139 [check constraint]
4 ΕΚΔΌΤΗΣ

 

PUBLIC."PUBLISHER SALES".REGIONS [table]
1 CITY VARCHAR(50) NOT NULL
2 STATE VARCHAR(2) NOT NULL
3 POSTALCODE VARCHAR(10) NOT NULL
4 COUNTRY VARCHAR(50) NOT NULL
Primary Key
PK_CUSTOMERS [primary key]
3 POSTALCODE
4 COUNTRY
Foreign Keys
FK_SALES_REGIONS [foreign key, with no action]
1 POSTALCODE ←(0..many) PUBLIC."PUBLISHER SALES".SALES.POSTALCODE
2 COUNTRY ←(0..many) PUBLIC."PUBLISHER SALES".SALES.COUNTRY
Indexes
SYS_IDX_PK_CUSTOMERS_10227 [unique index]
3 POSTALCODE ascending
4 COUNTRY ascending
Table Constraints
SYS_CT_10223 [check constraint]
1 CITY
SYS_CT_10224 [check constraint]
2 STATE
SYS_CT_10225 [check constraint]
3 POSTALCODE
SYS_CT_10226 [check constraint]
4 COUNTRY

 

PUBLIC."PUBLISHER SALES".SALES [table]
1 POSTALCODE VARCHAR(10) NOT NULL
2 COUNTRY VARCHAR(50) NOT NULL
3 BOOKID INTEGER
4 PERIODENDDATE DATE
5 TOTALAMOUNT DOUBLE(64, 0)
6 SALESDATAID INTEGER
Foreign Keys
FK_SALES_SALESDATA [foreign key, with no action]
1 SALESDATAID (0..many)→ PUBLIC."PUBLISHER SALES".SALESDATA.SALESDATAID
FK_SALES_REGIONS [foreign key, with no action]
1 POSTALCODE (0..many)→ PUBLIC."PUBLISHER SALES".REGIONS.POSTALCODE
2 COUNTRY (0..many)→ PUBLIC."PUBLISHER SALES".REGIONS.COUNTRY
Indexes
SYS_IDX_FK_SALES_SALESDATA_10241 [non-unique index]
6 SALESDATAID ascending
SYS_IDX_FK_SALES_REGIONS_10239 [non-unique index]
1 POSTALCODE ascending
2 COUNTRY ascending
Table Constraints
SYS_CT_10234 [check constraint]
1 POSTALCODE
SYS_CT_10235 [check constraint]
2 COUNTRY

 

PUBLIC."PUBLISHER SALES".SALESDATA [table]
1 SALESDATAID INTEGER
2 YEARLYAMOUNT DOUBLE(64, 0)
Foreign Keys
FK_SALES_SALESDATA [foreign key, with no action]
1 SALESDATAID ←(0..many) PUBLIC."PUBLISHER SALES".SALES.SALESDATAID
Indexes
SYS_IDX_UQ_CUSTOMERS_10233 [unique index]
1 SALESDATAID ascending
Table Constraints
UQ_CUSTOMERS [unique constraint]
1 SALESDATAID

 

 

Routines

PUBLIC.BOOKS.NEW_PUBLISHER [procedure, no result]
2 PUBLISHER CHARACTER VARYING, out

 

PUBLIC.BOOKS.NEW_PUBLISHER [procedure, no result]
2 NEWPUBLISHER CHARACTER VARYING, in
3 PUBLISHER CHARACTER VARYING, out

 

PUBLIC.BOOKS.CUSTOMADD [function, does not return a table]
2 ONE INTEGER, in

 

PUBLIC.BOOKS.CUSTOMADD [function, does not return a table]
2 ONE INTEGER, in
3 TWO INTEGER, in

 

 

Sequences

PUBLIC.BOOKS.PUBLISHER_ID_SEQ [sequence]
increment 1
start value 1
minimum value -2147483648
maximum value 2147483647
cycle false