Entity Relationship (ER) diagram

Date: March 15th 2016
Last updated: March 15th 2016

An entity relationship diagram illustrates columns of tables, and the links (references) between tables. ER diagrams are useful to visualise the contents of the database and aid in normalising the tables. Normalisation is the process of removing redundancy (duplication) from the database.

This entry provides a basic workflow for installing and drawing an ER diagram.

Install Java8

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get update
sudo apt-get install oracle-java8-installer

Install Graphviz

sudo apt-get install Graphviz

Install SchemaCrawler

wget -O Downloads/schemacrawler 'https://github.com/ \
sualeh/SchemaCrawler/releases/download/ \
v14.07.03/schemacrawler-14.07.03-main.zip'

unzip Downloads/schemacrawler.zip -d destination_folder

Create bash script (mybashfile.sh)
Note that there are line breaks in this bash script that need to be removed before use.

# !#/bin/bash
# Contents of mybashfile.sh

# The path of the unzipped SchemaCrawler directory
# This path is equal to...
# SchemaCrawlerPATH= destination_folder/schemacrawler/ 
# schemacrawler-14.07.03-main
SchemaCrawlerPATH=/home/ray/
schemacrawler/schemacrawler-14.07.03-main

# The path of the SQLite database
# gitbook.db is created as an example later in this chapter
SQLiteDatabaseFILE=/home/ray/python/databases/
sqlite/gitbook.db

# The type of the database system.
RDBMS=sqlite

# Where to store the image
OutputPATH=/home/ray/python/databases/sqlite/ER.png

# Username and password need to be empty for SQLite
USER=
PASSWORD=

java -classpath $(echo ${SchemaCrawlerPATH}/
_schemacrawler/lib/*.jar | tr ' ' ':') 
schemacrawler.Main -server=${RDBMS}
-database=${SQLiteDatabaseFILE}
-outputformat=png -outputfile=${OutputPATH} 
-command=graph 
-infolevel=maximum 
-user=${USER} 
-password=${PASSWORD}

Run

# make bash file executable
sudo chmod 755 mybashfile.sh

# run
./mybashfile.sh

ER diagram
This diagram contains 5 tables and includes a many-to-many relationship (via the ownerpatient table). The tables of this database are created in foreign keys and subsequent scrapbook entries. Useful resources

results matching ""

    No results matching ""