# FROM TEST: Database Schema Diagram

## PUBLIC.BOOKS

### AUTHORS (table)
Contact details for book authors

### Columns
- **ID** (INTEGER)
- FIRSTNAME (VARCHAR)
- LASTNAME (VARCHAR)
- ADDRESS1 (VARCHAR)
- ADDRESS2 (VARCHAR)
- CITY (VARCHAR)
- STATE (CHARACTER)
- POSTALCODE (VARCHAR)
- COUNTRY (VARCHAR)

### Primary Key
- PK_AUTHORS (ID) 

### Indexes
- IDX_B_AUTHORS (LASTNAME, FIRSTNAME)
- IDX_A_AUTHORS (CITY, STATE, POSTALCODE, COUNTRY)



### BOOKS (table)
Details for published books

### Columns
- **ID** (INTEGER)    
Unique (internal) id for book
- TITLE (VARCHAR)    
Book title
- DESCRIPTION (VARCHAR)    
Book description
    (Usually the blurb from the book jacket or promotional materials)
- PUBLISHERID (INTEGER)    
Foreign key to the book publisher
- PUBLICATIONDATE (DATE)    
Book publication date
- PRICE (DOUBLE)    
Current price for the book
- *PREVIOUSEDITIONID* (INTEGER)

### Primary Key
- PK_BOOKS (ID) 

### Indexes
- U_PREVIOUSEDITION (PREVIOUSEDITIONID) (unique index)
- FK_PREVIOUSEDITION (PREVIOUSEDITIONID)

### Foreign Keys
- FK_PREVIOUSEDITION (*PREVIOUSEDITIONID* --> **BOOKS.ID**)



### Celebrities (table)


### Columns
- **Id** (INTEGER)
- NAME (VARCHAR)

### Primary Key
- PK_Celebrities ("Id") 

### Indexes



### COUPONS (table)


### Columns
- **ID** (INTEGER)
- DATA (CLOB)
- COUPONS (PUBLIC.BOOKS."INTEGER ARRAY")
- BOOKS (PUBLIC.BOOKS."VARCHAR(20) ARRAY[10]")

### Primary Key
- PK_COUPONS (ID) 

### Indexes



### CUSTOMERDATA (table)


### Columns
- **ID** (INTEGER)
- DATA (PUBLIC.BOOKS.VALID_STRING)

### Primary Key
- PK_CUSTOMERDATA (ID) 

### Indexes



### CUSTOMERS (table)


### Columns
- **ID** (INTEGER)
- FIRSTNAME (PUBLIC.BOOKS.NAME_TYPE)
- LASTNAME (PUBLIC.BOOKS.NAME_TYPE)
- AGE (PUBLIC.BOOKS.AGE_TYPE)

### Primary Key
- PK_CUSTOMERS (ID) 

### Indexes



### PUBLISHERS (table)
List of book publishers

### Columns
- **ID** (INTEGER)    
Unique (internal) id for book publisher
- PUBLISHER (VARCHAR)    
Name of book publisher

### Primary Key
- SYS_PK_10124 (ID) 

### Indexes



### BOOKAUTHORS (table)
Relationship between books and their authors, 
along with the latest updated information

### Columns
- *BOOKID* (INTEGER)
- *AUTHORID* (INTEGER)
- SOMEDATA (VARCHAR)

### Indexes
- Z_FK_AUTHOR (AUTHORID)
- SYS_FK_10118 (BOOKID)
- UIDX_BOOKAUTHORS (BOOKID, AUTHORID) (unique index)

### Foreign Keys
- Z_FK_AUTHOR (*AUTHORID* --> **AUTHORS.ID**)
- SYS_FK_10118 (*BOOKID* --> **BOOKS.ID**)



### Celebrity Updates (table)


### Columns
- **Celebrity Id** (INTEGER)
- UPDATE (VARCHAR)

### Primary Key
- PK Celebrity Updates ("Celebrity Id") 

### Indexes

### Foreign Keys
- SYS_FK_10128 (*Celebrity Id* --> **"Celebrities"."Id"**)



### ΒΙΒΛΊΑ (table)


### Columns
- **ΜΟΝΑΔΙΚΌΣ** (SMALLINT)
- ΤΊΤΛΟΣ (VARCHAR)
- ΠΕΡΙΓΡΑΦΉ (VARCHAR)
- *ΕΚΔΌΤΗΣ* (SMALLINT)

### Primary Key
- PK_ΒΙΒΛΊΑ (ΜΟΝΑΔΙΚΌΣ) 

### Indexes
- FK_ΒΙΒΛΊΑ_PUBLISHERS (ΕΚΔΌΤΗΣ)

### Foreign Keys
- FK_ΒΙΒΛΊΑ_PUBLISHERS (*ΕΚΔΌΤΗΣ* --> **PUBLISHERS.ID**)



### AUTHORSLIST (view)


### Columns
- ID (INTEGER)
- FIRSTNAME (VARCHAR)
- LASTNAME (VARCHAR)



## PUBLIC."PUBLISHER SALES"

### REGIONS (table)


### Columns
- CITY (VARCHAR)
- STATE (VARCHAR)
- **POSTALCODE** (VARCHAR)
- **COUNTRY** (VARCHAR)

### Primary Key
- PK_CUSTOMERS (POSTALCODE, COUNTRY) 

### Indexes



### SALESDATA (table)


### Columns
- SALESDATAID (INTEGER)
- YEARLYAMOUNT (DOUBLE)

### Indexes
- UQ_CUSTOMERS (SALESDATAID) (unique index)



### SALES (table)


### Columns
- *POSTALCODE* (VARCHAR)
- *COUNTRY* (VARCHAR)
- *BOOKID* (INTEGER)
- COUPON_ID (INTEGER)
- PERIODENDDATE (DATE)
- TOTALAMOUNT (DOUBLE)
- *SALESDATAID* (INTEGER)

### Indexes
- FK_SALES_BOOK (BOOKID)
- FK_SALES_SALESDATA (SALESDATAID)
- FK_SALES_REGIONS (POSTALCODE, COUNTRY)

### Foreign Keys
- FK_SALES_BOOK (*BOOKID* --> **BOOKS.ID**)
- FK_SALES_SALESDATA (*SALESDATAID* --> **SALESDATA.SALESDATAID**)
- FK_SALES_REGIONS (*POSTALCODE* --> **REGIONS.POSTALCODE**)
- FK_SALES_REGIONS (*COUNTRY* --> **REGIONS.COUNTRY**)



