Details of Example Database

 

System Information

generated by SchemaCrawler 15.01.06
generated on 2018-11-11 05:25:40

 

 

Tables

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 VARCHAR(2)
8 POSTALCODE VARCHAR(10)
9 COUNTRY VARCHAR(50)
Primary Key
[primary key]
1 ID ascending
Foreign Keys
[foreign key, with no action]
1 ID ←(0..many) BOOKAUTHORS.AUTHORID
Indexes
[unique index]
1 ID ascending
[non-unique index]
3 LASTNAME ascending
2 FIRSTNAME ascending
[non-unique index]
6 CITY ascending
7 STATE ascending
8 POSTALCODE ascending
9 COUNTRY ascending

 

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

 

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
[foreign key, with no action]
1 AUTHORID (0..many)→ AUTHORS.ID
[foreign key, with no action]
1 BOOKID (0..many)→ BOOKS.ID
Indexes
[non-unique index]
2 AUTHORID ascending
[non-unique index]
1 BOOKID ascending
[unique index]
1 BOOKID ascending
2 AUTHORID ascending

 

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
[primary key]
1 ID ascending
Foreign Keys
[foreign key, with no action]
1 ID ←(0..many) BOOKAUTHORS.BOOKID
[foreign key, with no action]
1 ID ←(0..1) PREVIOUSEDITIONID
Indexes
[unique index]
1 ID ascending
[unique index]
7 PREVIOUSEDITIONID ascending
[non-unique index]
7 PREVIOUSEDITIONID ascending

 

COUPONS [table]
1 ID INTEGER NOT NULL
2 DATA CLOB
3 COUPONS INTEGER ARRAY
4 BOOKS VARCHAR(20) ARRAY[10]
Primary Key
[primary key]
1 ID ascending
Indexes
[unique index]
1 ID ascending

 

CUSTOMERDATA [table]
1 ID INTEGER NOT NULL
2 DATA VALID_STRING(20)
Primary Key
[primary key]
1 ID ascending
Indexes
[unique index]
1 ID ascending

 

CUSTOMERS [table]
1 ID INTEGER NOT NULL
2 FIRSTNAME NAME_TYPE(100) NOT NULL
3 LASTNAME NAME_TYPE(100) NOT NULL
4 AGE AGE_TYPE
Primary Key
[primary key]
1 ID ascending
Indexes
[unique index]
1 ID ascending

 

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

 

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
[primary key]
1 ID ascending
Foreign Keys
[foreign key, with no action]
1 ID ←(0..many) ΒΙΒΛΊΑ.ΕΚΔΌΤΗΣ
Indexes
[unique index]
1 ID ascending

 

ΒΙΒΛΊΑ [table]
1 ΜΟΝΑΔΙΚΌΣ SMALLINT NOT NULL
2 ΤΊΤΛΟΣ VARCHAR(255) NOT NULL
3 ΠΕΡΙΓΡΑΦΉ VARCHAR(255)
4 ΕΚΔΌΤΗΣ SMALLINT NOT NULL
Primary Key
[primary key]
1 ΜΟΝΑΔΙΚΌΣ ascending
Foreign Keys
[foreign key, with no action]
1 ΕΚΔΌΤΗΣ (0..many)→ PUBLISHERS.ID
Indexes
[non-unique index]
4 ΕΚΔΌΤΗΣ ascending
[unique index]
1 ΜΟΝΑΔΙΚΌΣ ascending

 

EXTRA_PK [table]
Extra table with just a primary key
1 WRITERID BIGINT NOT NULL
2 PUBLICATIONID INTEGER NOT NULL
3 ID INTEGER NOT NULL
Primary Key
[primary key]
3 ID ascending
Foreign Keys
[foreign key, with no action]
1 PUBLICATIONID (0..many)→ PUBLICATIONS.ID
[foreign key, with no action]
1 WRITERID (0..many)→ WRITERS.ID
Indexes
[non-unique index]
1 WRITERID ascending
[unique index]
3 ID ascending
[non-unique index]
2 PUBLICATIONID ascending

 

"Global Counts" [table]
1 "Global Count" INTEGER

 

PUBLICATIONS [table]
1 ID INTEGER NOT NULL
2 TITLE VARCHAR(255) NOT NULL
Publication title
3 DESCRIPTION VARCHAR(255)
4 WRITERID BIGINT NOT NULL
5 PUBLICATIONDATE DATE
6 PRICE DOUBLE(64, 0)
7 "UPDATE" CLOB
8 PRESS_RELEASE CLOB
Primary Key
[primary key]
1 ID ascending
Foreign Keys
[foreign key, with no action]
1 ID ←(0..many) EXTRA_PK.PUBLICATIONID
[foreign key, with no action]
1 ID ←(0..many) PUBLICATIONWRITERS.PUBLICATIONID
[foreign key, with no action]
1 ID ←(0..many) WRITERS.PUBLICATION_ID
[foreign key, with no action]
1 WRITERID (0..many)→ WRITERS.ID
Indexes
[non-unique index]
4 WRITERID ascending
[unique index]
1 ID ascending

 

PUBLICATIONWRITERS [table]
1 PUBLICATIONID INTEGER NOT NULL
2 WRITERID BIGINT NOT NULL
Foreign Keys
[foreign key, with no action]
1 PUBLICATIONID (0..many)→ PUBLICATIONS.ID
[foreign key, with no action]
1 WRITERID (0..many)→ WRITERS.ID
Indexes
[non-unique index]
2 WRITERID ascending
[non-unique index]
1 PUBLICATIONID ascending

 

SELFREFERENCE [table]
1 ID INTEGER NOT NULL
2 TITLE VARCHAR(255) NOT NULL
Primary Key
[primary key]
1 ID ascending
Foreign Keys
[foreign key, with no action]
1 ID ←(0..1) ID
Indexes
[unique index]
1 ID ascending
[non-unique index]
1 ID ascending

 

WRITERS [table]
1 ID BIGINT NOT NULL
2 FIRSTNAME VARCHAR(20) NOT NULL
3 LASTNAME VARCHAR(20) NOT NULL
4 ADDRESS1 VARCHAR(255)
5 ADDRESS2 VARCHAR(255) NOT NULL
6 CITY VARCHAR(50)
7 STATE VARCHAR(2)
8 POSTALCODE VARCHAR(10)
9 COUNTRY VARCHAR(50)
10 PHONE1 VARCHAR(10)
11 PHONE2 VARCHAR(15)
12 EMAIL1 VARCHAR(10)
13 EMAIL2 INTEGER
14 FAX VARCHAR(10)
15 FAX3 INTEGER
16 HOMEEMAIL11 VARCHAR(10)
17 HOMEEMAIL12 VARCHAR(10)
18 PUBLICATION_ID BIGINT NOT NULL
Primary Key
[primary key]
1 ID ascending
Foreign Keys
[foreign key, with no action]
1 PUBLICATION_ID (0..many)→ PUBLICATIONS.ID
[foreign key, with no action]
1 ID ←(0..many) EXTRA_PK.WRITERID
[foreign key, with no action]
1 ID ←(0..many) PUBLICATIONS.WRITERID
[foreign key, with no action]
1 ID ←(0..many) PUBLICATIONWRITERS.WRITERID
Indexes
[unique index]
1 ID ascending
[non-unique index]
18 PUBLICATION_ID ascending
[non-unique index]
6 CITY ascending
7 STATE ascending
[unique index]
12 EMAIL1 ascending
9 COUNTRY ascending
[non-unique index]
3 LASTNAME ascending
2 FIRSTNAME ascending
[non-unique index]
6 CITY ascending
7 STATE ascending
8 POSTALCODE ascending
9 COUNTRY ascending

 

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
[primary key]
3 POSTALCODE ascending
4 COUNTRY ascending
Foreign Keys
[foreign key, with no action]
1 POSTALCODE ←(0..many) SALES.POSTALCODE
2 COUNTRY ←(0..many) SALES.COUNTRY
Indexes
[unique index]
3 POSTALCODE ascending
4 COUNTRY ascending

 

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
[foreign key, with no action]
1 SALESDATAID (0..many)→ SALESDATA.SALESDATAID
[foreign key, with no action]
1 POSTALCODE (0..many)→ REGIONS.POSTALCODE
2 COUNTRY (0..many)→ REGIONS.COUNTRY
Indexes
[non-unique index]
6 SALESDATAID ascending
[non-unique index]
1 POSTALCODE ascending
2 COUNTRY ascending

 

SALESDATA [table]
1 SALESDATAID INTEGER
2 YEARLYAMOUNT DOUBLE(64, 0)
Foreign Keys
[foreign key, with no action]
1 SALESDATAID ←(0..many) SALES.SALESDATAID
Indexes
[unique index]
1 SALESDATAID ascending