```{r init, echo=FALSE}
#  AOUG Presentation knitr script for relation visualisation 
#  adjust the credentials for DB connection
#  and run with:
# 
#  library(knitr)
#  knit2html("RelationVisualisation.rmd")
#
library(RJDBC)
## adjust clasPath, DB Url, user and password
jdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", 
  classPath="c:/oracle/product/12.2.0/ojdbc8.jar")

conn <- dbConnect(jdbcDriver, 
    "jdbc:oracle:thin:@//localhost:1521/service", 
    "usr", "pwd")

options(dec=",") ## decimal point
options(scipen=999) ## remove scientific format
options(digits=8)
```
# Relation Visualisation

*by Jaromir D.B. Nemec*

The supporting functions are defined in the file [rvis_supporting_functions.r](rvis_supporting_functions.r)
```{r sf1, echo=F}
## get supporting functions
source("rvis_supporting_functions.r")
```
The visualisation function *getRelVis* provides two views. The ordered view classifies the keys in four groups

*  -FK invalid FK without PK
*  PK- idle PK key without FKs
*  PK and FK are ordinary paired keys

The keys are ordered based on the group, within the group based on the count of FKs (low count first)

The heat map view is a summary of the ordered view. The two bars on the left show the cardinality of the PK and FK keys
in log scale.

The two bars on the right show via a heat map the frequency of the PK and FK keys.
The possible empty space below means an idle PK key (PK- group) or PK containing a NULL value.

A dark spot on the top of the PK bar (third bar) signalised duplicated keys in PK. (Be carefull while joining such relation!) 
Similar a dark spot on the bottom of the FK bar (fourth bar) shows inconsistent FKs (group -FK) - you possible need a full outer join to get all data from such a relation.    


## Ordered View

```{r gr1,fig.height=8, fig.width=8,echo=F}
p <-  getRelVis(conn,'TABLE_1a','ID','TABLE_2a','FK_ID') 
print(p[[1]])
```

## Heat Map View

```{r gr2,fig.height=8, fig.width=4,echo=F}
print(p[[2]])
```

*last update 15.5.2019*

*Jaromir D.B. Nemec* is a freelancer specializing in data warehouse and integration solutions. He can be reached under [http://www.db-nemec.com](http://www.db-nemec.com) 

This document was generated with [knitr](https://yihui.name/knitr/) using [this source file](RelationVisualisation.rmd)
 
