This mind map represents the following hierarchy of topics we used in a previous post (this one is small and inactive but with the visNetwork graph visualization R package, it will possible to develop animated and interactive mind maps) :
Root Relational theory First-order predicate logic Syntax Rules of inference Deductive systems Semantics Three-valued logic Normal forms Functional dependencies Multivalued dependencies 1NF 2NF 3NF BCNF 4NF 5NF DKNF SQL Types and domains PKs & FKs Relations Operators Restriction Projection Joins Cross join Inner join Left outer join Right outer join Full outer join Union Intersection Difference Divide Aggregations Constraints Views
We changed the data structure for the implementation of Adjacency List Model for trees with relational operators.
tree(node_id, node_up_id, topic) where node_up_id is a reference (FK) to node_id
we use a two-tables representation of a tree :
nodes(node_id, topic) edges(from_node, to_node) where from_node and to_node are references (FKs) to nodes.node_id
It’s always better to use normalized data model. It takes often a bit of effort but the queries will be easier to design.
We implement in PostgreSQL this model :
create sequence node_sequence; create table nodes ( node_id integer not null default nextval('node_sequence') primary key ,topic varchar(128) ); create table edges ( from_node integer not null references nodes(node_id) on update cascade ,to_node integer not null references nodes(node_id) check (from_node != to_node) ,primary key(from_node, to_node) );
The visualization of the mind_map with the visGraph R library is now straightforward.
1. We load the libraries and we make connection to the database :
require(RPostgreSQL) require(visNetwork) driver ← dbDriver("PostgreSQL") connexion ← dbConnect(driver, ,dbname="higayone" ,host="localhost", port=5432 ,user="mchl", password="secret")
2. We populate nodes and edges, the R data structure is a direct mapping of the relational structure (a R data frame is relational table) :
nodes ← dbGetQuery(connexion, " select node_id as id ,topic as label from nodes order by id_topic;") edges ← dbGetQuery(connexion, " select from_node as from ,to_node as to from edges order by to_node, from_node;")
3. We call the graphic interface, the visNetwork data structure is direct mapping of the R data structure (visNetwork function waits nodes identified by id and label and edges identified by from and to) :
visNetwork(nodes, edges) %>% visNodes(font = '10px', shape = 'ellipse')