Details of Example Database

 

System Information

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

 

 

Tables

AUTHORS [table]
Contact details for book authors
ADDRESS1 VARCHAR(255)
ADDRESS2 VARCHAR(255)
CITY VARCHAR(50)
COUNTRY VARCHAR(50)
FIRSTNAME VARCHAR(20) NOT NULL
ID INTEGER NOT NULL
LASTNAME VARCHAR(20) NOT NULL
POSTALCODE VARCHAR(10)
STATE VARCHAR(2)
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]
FIRSTNAME ascending
LASTNAME ascending
[non-unique index]
CITY ascending
COUNTRY ascending
POSTALCODE ascending
STATE ascending

 

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

 

BOOKAUTHORS [table]
Relationship between books and their authors, along with the latest updated information
AUTHORID INTEGER NOT NULL
BOOKID 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]
AUTHORID ascending
BOOKID ascending

 

BOOKS [table]
Details for published books
DESCRIPTION VARCHAR(255)
Book description (Usually the blurb from the book jacket or promotional materials)
ID INTEGER NOT NULL
Unique (internal) id for book
PREVIOUSEDITIONID INTEGER
PRICE DOUBLE(64, 0)
Current price for the book
PUBLICATIONDATE DATE
Book publication date
PUBLISHERID INTEGER NOT NULL
Foreign key to the book publisher
TITLE VARCHAR(255) NOT NULL
Book title
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

 

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

 

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

 

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

 

"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
ΜΟΝΑΔΙΚΌΣ SMALLINT NOT NULL
ΠΕΡΙΓΡΑΦΉ VARCHAR(255)
ΤΊΤΛΟΣ VARCHAR(255) 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

 

EXTRA_PK [table]
Extra table with just a primary key
ID INTEGER NOT NULL
PUBLICATIONID INTEGER NOT NULL
WRITERID BIGINT 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

 

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

 

PUBLICATIONS [table]
DESCRIPTION VARCHAR(255)
ID INTEGER NOT NULL
PRESS_RELEASE CLOB
PRICE DOUBLE(64, 0)
PUBLICATIONDATE DATE
TITLE VARCHAR(255) NOT NULL
Publication title
"UPDATE" CLOB
WRITERID BIGINT NOT NULL
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

 

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

 

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

 

WRITERS [table]
ADDRESS1 VARCHAR(255)
ADDRESS2 VARCHAR(255) NOT NULL
CITY VARCHAR(50)
COUNTRY VARCHAR(50)
EMAIL1 VARCHAR(10)
EMAIL2 INTEGER
FAX VARCHAR(10)
FAX3 INTEGER
FIRSTNAME VARCHAR(20) NOT NULL
HOMEEMAIL11 VARCHAR(10)
HOMEEMAIL12 VARCHAR(10)
ID BIGINT NOT NULL
LASTNAME VARCHAR(20) NOT NULL
PHONE1 VARCHAR(10)
PHONE2 VARCHAR(15)
POSTALCODE VARCHAR(10)
PUBLICATION_ID BIGINT NOT NULL
STATE VARCHAR(2)
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]
COUNTRY ascending
EMAIL1 ascending
[non-unique index]
FIRSTNAME ascending
LASTNAME ascending
[non-unique index]
CITY ascending
COUNTRY ascending
POSTALCODE ascending
STATE ascending

 

REGIONS [table]
CITY VARCHAR(50) NOT NULL
COUNTRY VARCHAR(50) NOT NULL
POSTALCODE VARCHAR(10) NOT NULL
STATE VARCHAR(2) NOT NULL
Primary Key
[primary key]
COUNTRY ascending
POSTALCODE ascending
Foreign Keys
[foreign key, with no action]
POSTALCODE ←(0..many) SALES.POSTALCODE
COUNTRY ←(0..many) SALES.COUNTRY
Indexes
[unique index]
COUNTRY ascending
POSTALCODE ascending

 

SALES [table]
BOOKID INTEGER
COUNTRY VARCHAR(50) NOT NULL
PERIODENDDATE DATE
POSTALCODE VARCHAR(10) NOT NULL
SALESDATAID INTEGER
TOTALAMOUNT DOUBLE(64, 0)
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]
COUNTRY ascending
POSTALCODE ascending

 

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