System Information

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

 

 

Tables

AUTHORS [table]
Contact details for book authors
ID INTEGER NOT NULL
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)
Primary Key
PK_AUTHORS [primary key]
ID
Foreign Keys
Z_FK_AUTHOR [foreign key, with no action]
ID ←(0..many) BOOKAUTHORS.AUTHORID
Indexes
SYS_IDX_PK_AUTHORS_10111 [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]
UPDATE BOOKS.PUBLISHERS SET PUBLISHER='Jacob'WHERE PUBLISHER='John'
Table Constraints
CHECK_UPPERCASE_STATE [check constraint]
STATE
SYS_CT_10108 [check constraint]
ID
SYS_CT_10109 [check constraint]
FIRSTNAME
SYS_CT_10110 [check constraint]
LASTNAME

 

AUTHORSLIST [view]
ID INTEGER
FIRSTNAME VARCHAR(20)
LASTNAME VARCHAR(20)

 

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 (0..many)→ AUTHORS.ID
SYS_FK_10128 [foreign key, with no action]
BOOKID (0..many)→ BOOKS.ID
Indexes
SYS_IDX_Z_FK_AUTHOR_10134 [non-unique index]
AUTHORID ascending
SYS_IDX_SYS_FK_10128_10132 [non-unique index]
BOOKID ascending
UIDX_BOOKAUTHORS [unique index]
BOOKID ascending
AUTHORID ascending
Table Constraints
SYS_CT_10126 [check constraint]
BOOKID
SYS_CT_10127 [check constraint]
AUTHORID

 

BOOKS [table]
Details for published books
ID INTEGER NOT NULL
Unique (internal) id for book
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]
ID
Foreign Keys
SYS_FK_10128 [foreign key, with no action]
ID ←(0..many) BOOKAUTHORS.BOOKID
FK_PREVIOUSEDITION [foreign key, with no action]
ID ←(0..1) PREVIOUSEDITIONID
Indexes
SYS_IDX_PK_BOOKS_10119 [unique index]
ID ascending
SYS_IDX_U_PREVIOUSEDITION_10125 [unique index]
PREVIOUSEDITIONID ascending
SYS_IDX_FK_PREVIOUSEDITION_10123 [non-unique index]
PREVIOUSEDITIONID ascending
Table Constraints
U_PREVIOUSEDITION [unique constraint]
PREVIOUSEDITIONID
SYS_CT_10116 [check constraint]
ID
SYS_CT_10117 [check constraint]
TITLE
SYS_CT_10118 [check constraint]
PUBLISHERID

 

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

 

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

 

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

 

"Global Counts" [table]
"Global Count" INTEGER
"UPDATE" VARCHAR(20)

 

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

 

ΒΙΒΛΊΑ [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]
ΕΚΔΌΤΗΣ (0..many)→ PUBLISHERS.ID
Indexes
SYS_IDX_FK_ΒΙΒΛΊΑ_PUBLISHERS_10144 [non-unique index]
ΕΚΔΌΤΗΣ ascending
SYS_IDX_PK_ΒΙΒΛΊΑ_10140 [unique index]
ΜΟΝΑΔΙΚΌΣ ascending
Table Constraints
SYS_CT_10137 [check constraint]
ΜΟΝΑΔΙΚΌΣ
SYS_CT_10138 [check constraint]
ΤΊΤΛΟΣ
SYS_CT_10139 [check constraint]
ΕΚΔΌΤΗΣ

 

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 ←(0..many) SALES.POSTALCODE
COUNTRY ←(0..many) SALES.COUNTRY
Indexes
SYS_IDX_PK_CUSTOMERS_10227 [unique index]
POSTALCODE ascending
COUNTRY ascending
Table Constraints
SYS_CT_10223 [check constraint]
CITY
SYS_CT_10224 [check constraint]
STATE
SYS_CT_10225 [check constraint]
POSTALCODE
SYS_CT_10226 [check constraint]
COUNTRY

 

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

 

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

 

 

Routines

NEW_PUBLISHER [procedure, no result]
PUBLISHER CHARACTER VARYING, out

 

NEW_PUBLISHER [procedure, no result]
NEWPUBLISHER CHARACTER VARYING, in
PUBLISHER CHARACTER VARYING, out

 

CUSTOMADD [function, does not return a table]
ONE INTEGER, in

 

CUSTOMADD [function, does not return a table]
ONE INTEGER, in
TWO INTEGER, in

 

 

Sequences

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