Details of Example Database

 

System Information

generated by SchemaCrawler 15.01.01
generated on 2018-08-15 00:58:24

 

 

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 VARCHAR(2)
POSTALCODE VARCHAR(10)
COUNTRY VARCHAR(50)
Primary Key
[primary key]
ID ascending
Foreign Keys
[foreign key, with no action]
ID ←(0..many) BOOKAUTHORS.AUTHORID
Indexes
[unique index]
ID ascending
[non-unique index]
LASTNAME ascending
FIRSTNAME ascending
[non-unique index]
CITY ascending
STATE ascending
POSTALCODE ascending
COUNTRY ascending
Triggers
[trigger, after delete, per row]
UPDATE BOOKS.PUBLISHERS SET PUBLISHER='Jacob'WHERE PUBLISHER='John'
Table Constraints
[check constraint]
STATE
[check constraint]
ID
[check constraint]
FIRSTNAME
[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
[foreign key, with no action]
AUTHORID (0..many)→ AUTHORS.ID
[foreign key, with no action]
BOOKID (0..many)→ BOOKS.ID
Indexes
[non-unique index]
AUTHORID ascending
[non-unique index]
BOOKID ascending
[unique index]
BOOKID ascending
AUTHORID ascending
Table Constraints
[check constraint]
BOOKID
[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
[primary key]
ID ascending
Foreign Keys
[foreign key, with no action]
ID ←(0..many) BOOKAUTHORS.BOOKID
[foreign key, with no action]
ID ←(0..1) PREVIOUSEDITIONID
Indexes
[unique index]
ID ascending
[unique index]
PREVIOUSEDITIONID ascending
[non-unique index]
PREVIOUSEDITIONID ascending
Table Constraints
[unique constraint]
PREVIOUSEDITIONID
[check constraint]
ID
[check constraint]
TITLE
[check constraint]
PUBLISHERID

 

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

 

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

 

CUSTOMERS [table]
ID INTEGER NOT NULL
FIRSTNAME NAME_TYPE NOT NULL
LASTNAME NAME_TYPE NOT NULL
AGE AGE_TYPE
Primary Key
[primary key]
ID ascending
Indexes
[unique index]
ID ascending
Table Constraints
[check constraint]
ID
[check constraint]
FIRSTNAME
[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
[primary key]
ID ascending
Foreign Keys
[foreign key, with no action]
ID ←(0..many) ΒΙΒΛΊΑ.ΕΚΔΌΤΗΣ
Indexes
[unique index]
ID ascending

 

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

 

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

 

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

 

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

 

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

 

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

 

WRITERS [table]
ID BIGINT NOT NULL
FIRSTNAME VARCHAR(20) NOT NULL
LASTNAME VARCHAR(20) NOT NULL
ADDRESS1 VARCHAR(255)
ADDRESS2 VARCHAR(255) NOT NULL
CITY VARCHAR(50)
STATE VARCHAR(2)
POSTALCODE VARCHAR(10)
COUNTRY VARCHAR(50)
PHONE1 VARCHAR(10)
PHONE2 VARCHAR(15)
EMAIL1 VARCHAR(10)
EMAIL2 INTEGER
FAX VARCHAR(10)
FAX3 INTEGER
HOMEEMAIL11 VARCHAR(10)
HOMEEMAIL12 VARCHAR(10)
PUBLICATION_ID BIGINT NOT NULL
Primary Key
[primary key]
ID ascending
Foreign Keys
[foreign key, with no action]
PUBLICATION_ID (0..many)→ PUBLICATIONS.ID
[foreign key, with no action]
ID ←(0..many) EXTRA_PK.WRITERID
[foreign key, with no action]
ID ←(0..many) PUBLICATIONS.WRITERID
[foreign key, with no action]
ID ←(0..many) PUBLICATIONWRITERS.WRITERID
Indexes
[unique index]
ID ascending
[non-unique index]
PUBLICATION_ID ascending
[non-unique index]
CITY ascending
STATE ascending
[unique index]
EMAIL1 ascending
COUNTRY ascending
[non-unique index]
LASTNAME ascending
FIRSTNAME ascending
[non-unique index]
CITY ascending
STATE ascending
POSTALCODE ascending
COUNTRY ascending
Table Constraints
[check constraint]
STATE
[check constraint]
ID
[check constraint]
FIRSTNAME
[check constraint]
LASTNAME
[check constraint]
ADDRESS2
[check constraint]
PUBLICATION_ID

 

REGIONS [table]
CITY VARCHAR(50) NOT NULL
STATE VARCHAR(2) NOT NULL
POSTALCODE VARCHAR(10) NOT NULL
COUNTRY VARCHAR(50) NOT NULL
Primary Key
[primary key]
POSTALCODE ascending
COUNTRY ascending
Foreign Keys
[foreign key, with no action]
POSTALCODE ←(0..many) SALES.POSTALCODE
COUNTRY ←(0..many) SALES.COUNTRY
Indexes
[unique index]
POSTALCODE ascending
COUNTRY ascending
Table Constraints
[check constraint]
CITY
[check constraint]
STATE
[check constraint]
POSTALCODE
[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
[foreign key, with no action]
SALESDATAID (0..many)→ SALESDATA.SALESDATAID
[foreign key, with no action]
POSTALCODE (0..many)→ REGIONS.POSTALCODE
COUNTRY (0..many)→ REGIONS.COUNTRY
Indexes
[non-unique index]
SALESDATAID ascending
[non-unique index]
POSTALCODE ascending
COUNTRY ascending
Table Constraints
[check constraint]
POSTALCODE
[check constraint]
COUNTRY

 

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