System Information

generated by SchemaCrawler 16.9.2
generated on 2020-06-20 14:57:35
database version HSQL Database Engine 2.5.0
driver version HSQL Database Engine Driver 2.5.0

 

 

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
minimum value -2147483648
maximum value 2147483647
cycle false

 

 

Row Count

Row Count
AUTHORS 20 rows
AUTHORSLIST 20 rows
BOOKAUTHORS 20 rows
BOOKS 20 rows
COUPONS 10 rows
CUSTOMERDATA empty
CUSTOMERS empty
"Global Counts" 10 rows
PUBLISHERS 20 rows
ΒΙΒΛΊΑ empty
REGIONS 20 rows
SALES 20 rows
SALESDATA empty

 

 

Dump

AUTHORS
ID FIRSTNAME LASTNAME ADDRESS1 ADDRESS2 CITY STATE POSTALCODE COUNTRY
1 Oren Shaffer 900-2273 Integer Avenue Farmer City MD 37032 Cook Islands
2 Octavia Burke 955-7635 Natoque St. Kalispell MS 66863 Cape Verde
3 Aiko Stafford Ap #554-7132 Dictum Street Kankakee ID 59675 Brunei Darussalam
4 Christine Aguilar Ap #698-4255 Sodales Av. Riverton MA 24828 Ghana
5 Kenneth Curry P.O. Box 305, 8335 Cras St. Somersworth ME 36619 Kazakhstan
6 Lillian Nieves P.O. Box 827, 1923 Ornare Avenue Sierra Vista MA 12684 Mali
7 Damian Donaldson 5047 Eu Rd. LaGrange UT 64681 Thailand
8 Jordan Woodward Ap #486-6702 Iaculis Road Glen Cove OH 18011 Libyan Arab Jamahiriya
9 Adele Holmes 7585 Magna. Ave Villa Park ME 00619 Cayman Islands
10 Jillian Mckee 1185 Per Ave Rapid City LA 11671 New Caledonia
11 Mikayla Knight Ap #375-6798 Auctor St. Sault Ste. Marie CO 26558 Lesotho
12 Denise Dominguez 324-4875 Vitae Av. Hudson SD 41933 Russian Federation
13 Ira Dunn 8869 Velit Rd. Lincoln IA 74182 Zambia
14 Elvis Blevins Ap #629-9258 In St. Abilene WY 19269 Saint Vincent and The Grenadines
15 Theodore Gilliam 6293 Enim, Ave Baltimore UT 86549 Saudi Arabia
16 Jared White P.O. Box 806, 6212 Adipiscing, Road Thibodaux KS 90172 China
17 Melyssa Willis Ap #467-9359 Ipsum Rd. Roanoke OH 45706 Niger
18 Hilary Marks Ap #361-1093 Vitae Street Fort Lauderdale ID 10855 Venezuela
19 Frances Ellis Ap #458-4569 Sem. Ave Durham MS 50037 Gambia
20 Lenore Cooley P.O. Box 937, 3415 Nulla Ave Half Moon Bay OR 51967 Canada

 

AUTHORSLIST
ID FIRSTNAME LASTNAME
1 Oren Shaffer
2 Octavia Burke
3 Aiko Stafford
4 Christine Aguilar
5 Kenneth Curry
6 Lillian Nieves
7 Damian Donaldson
8 Jordan Woodward
9 Adele Holmes
10 Jillian Mckee
11 Mikayla Knight
12 Denise Dominguez
13 Ira Dunn
14 Elvis Blevins
15 Theodore Gilliam
16 Jared White
17 Melyssa Willis
18 Hilary Marks
19 Frances Ellis
20 Lenore Cooley

 

BOOKAUTHORS
BOOKID AUTHORID SOMEDATA
1 7 This is the latest update
2 4 NULL
2 6 Some update
3 1 NULL
3 4 No updates since a week
3 5 NULL
3 6 NULL
4 9 NULL
5 5 NULL
5 7 NULL
6 1 NULL
6 8 NULL
7 8 NULL
7 9 NULL
8 6 NULL
9 3 NULL
9 6 NULL
9 7 NULL
10 1 NULL
10 9 NULL

 

BOOKS
ID TITLE DESCRIPTION PUBLISHERID PUBLICATIONDATE PRICE PREVIOUSEDITIONID
1 Neque. In Ornare Sagittis Dis Parturient Montes, Nascetur Ridiculus Mus. 18 2000-10-27 69.99 1
2 Duis Dignissim Tempor Arcu. Eu, Euismod Ac, Fermentum Vel, 20 2002-05-09 27.99 2
3 Lacinia Orci, Consectetuer Euismod Est Sem Ut Cursus Luctus, Ipsum Leo Elementum Sem, Vitae Aliquam Eros Turpis Non Enim. 15 2006-04-14 37.99 3
4 Vitae Sodales Rhoncus. Nullam Velit Dui, Semper Et, Lacinia Vitae, Sodales At, 1 2007-05-21 64.99 4
5 Posuere Cubilia Curae Phasellus Ornare. Risus A Ultricies Adipiscing, Enim Mi Tempor Lorem, Eget Mollis Lectus Pede 7 2010-05-14 83.99 5
6 Tincidunt Aliquam Arcu. Tristique Pellentesque, Tellus Sem Mollis Dui, 13 2009-07-17 31.99 6
7 Auctor Quis, Tristique Vel, Faucibus Id, Libero. Donec Consectetuer Mauris Id Sapien. Cras Dolor Dolor, Tempus Non, Lacinia 7 1999-06-09 76.99 7
8 Sem. Nulla Interdum. Curabitur Turpis. Nulla Aliquet. Proin Velit. Sed Malesuada Augue Ut 7 2009-01-24 30.99 8
9 Lobortis Ultrices. Vivamus Rhoncus. Est, Mollis Non, Cursus Non, Egestas A, Dui. Cras Pellentesque. Sed Dictum. 1 2004-06-25 69.99 9
10 Quisque Purus Sapien, Gravida Et Ultrices Posuere Cubilia Curae Phasellus Ornare. Fusce Mollis. Duis Sit Amet Diam Eu Dolor Egestas Rhoncus. 19 2005-11-14 34.99 10
11 Phasellus Egestas. Duis Ac Arcu. Nunc Mauris. 5 2003-10-19 89.99 11
12 Non Massa Non Est Mauris, Rhoncus Id, Mollis 5 2002-05-04 64.99 12
13 Gravida Mauris Ut Mi. Duis Fusce Mollis. Duis Sit Amet Diam Eu Dolor Egestas Rhoncus. Proin Nisl Sem, Consequat Nec, 8 2005-08-03 25.99 13
14 Auctor Vitae, Suspendisse Dui. Fusce Diam Nunc, Ullamcorper Eu, Euismod Ac, Fermentum Vel, Mauris. Integer Sem Elit, Pharetra 2 2009-02-09 84.99 14
15 Ultrices Posuere Cubilia Elit, Pellentesque A, Facilisis Non, Bibendum Sed, Est. Nunc Laoreet Lectus Quis Massa. Mauris Vestibulum, Neque Sed 20 2001-08-28 77.99 15
16 Vitae, Aliquet Nec, Imperdiet Cursus Purus. Nullam 6 2003-08-30 47.99 16
17 Parturient Montes, Nascetur Ridiculus Ornare, Lectus Ante Dictum Mi, Ac Mattis Velit Justo Nec Ante. Maecenas Mi Felis, Adipiscing Fringilla, Porttitor Vulputate, Posuere Vulputate, 17 2005-06-24 36.99 17
18 Lacus. Etiam Bibendum Facilisi. Sed Neque. Sed 18 2008-01-28 63.99 18
19 Luctus Lobortis. Rutrum. Fusce Dolor Quam, Elementum At, Egestas A, Scelerisque Sed, Sapien. 5 2006-08-23 67.99 19
20 Dui, In Sodales Elit Erat Sociis Natoque Penatibus Et Magnis Dis Parturient 5 2002-09-29 43.99 20

 

COUPONS
ID DATA COUPONS BOOKS
1 <binary> ARRAY[] NULL
2 <binary> ARRAY[] NULL
3 <binary> ARRAY[] NULL
4 <binary> ARRAY[] NULL
5 NULL ARRAY[] NULL
6 <binary> ARRAY[] NULL
7 <binary> ARRAY[] NULL
8 NULL ARRAY[] NULL
9 <binary> ARRAY[] NULL
10 <binary> ARRAY[] NULL

 

CUSTOMERDATA
ID DATA

 

CUSTOMERS
ID FIRSTNAME LASTNAME AGE

 

"Global Counts"
Global Count UPDATE
1 Abbot
2 Alexis
3 Jasmine
4 NULL
5 Teagan
6 Stella
7 Alden
8 Daniel
9 NULL
10 Yetta

 

PUBLISHERS
ID PUBLISHER
1 Dalton Lewis
2 Macaulay Bryant
3 Nash Fox
4 Leonard Bradshaw
5 Brendan Kramer
6 Tobias Mosley
7 Zachary Tate
8 Derek Lee
9 Neil Tyson
10 Shad Robinson
11 Justin Schroeder
12 Leonard Atkinson
13 Hyatt Caldwell
14 Neil Fletcher
15 Ferdinand Tucker
16 Barrett Mclaughlin
17 Hayden Potts
18 Neville Cardenas
19 Allistair Gilbert
20 Cooper Chapman

 

ΒΙΒΛΊΑ
ΜΟΝΑΔΙΚΌΣ ΤΊΤΛΟΣ ΠΕΡΙΓΡΑΦΉ ΕΚΔΌΤΗΣ

 

REGIONS
CITY STATE POSTALCODE COUNTRY
Arvada KY 17131 USA
Baltimore AK 34702 USA
Clairton MA 42875 USA
Del Rio CA 24179 USA
Duluth ND 82539 USA
Effingham TX 44207 USA
El Segundo VA 13078 USA
Fayetteville MS 41233 USA
Hopkinsville IN 54618 USA
Kent OH 91624 USA
Lake Forest AK 26131 USA
Marlborough WV 88272 USA
Martinsburg KY 76672 USA
Nashua SC 27829 USA
Ogdensburg AZ 50786 USA
San Juan MD 30552 USA
Tacoma PA 70472 USA
Warren VA 42210 USA
Watertown SC 20239 USA
Woburn NV 27392 USA

 

SALES
POSTALCODE COUNTRY BOOKID PERIODENDDATE TOTALAMOUNT SALESDATAID
13078 USA 1 2001-07-18 18257.0 NULL
13078 USA 1 2001-09-29 14895.0 NULL
13078 USA 6 2003-12-28 6083.0 NULL
13078 USA 6 2006-06-11 7840.0 NULL
17131 USA 2 2002-04-30 17887.0 NULL
17131 USA 2 2004-11-13 16317.0 NULL
17131 USA 7 2000-03-08 6951.0 NULL
17131 USA 7 2003-03-21 18431.0 NULL
20239 USA 3 2004-08-28 18747.0 NULL
20239 USA 3 2005-07-27 6712.0 NULL
20239 USA 8 2008-03-29 11013.0 NULL
20239 USA 8 2008-08-15 17514.0 NULL
24179 USA 4 2005-12-24 11103.0 NULL
24179 USA 4 2009-02-14 5885.0 NULL
24179 USA 9 2000-05-16 13595.0 NULL
24179 USA 9 2004-04-10 14069.0 NULL
26131 USA 5 2001-12-02 14798.0 NULL
26131 USA 5 2004-11-03 15348.0 NULL
26131 USA 10 2000-09-20 14476.0 NULL
26131 USA 10 2005-12-03 17069.0 NULL

 

SALESDATA
SALESDATAID YEARLYAMOUNT