Creating a relational database schema

Question

A database systems book at our school depicts a relational schema like this :

I've been trying to recreate a similar schema using LaTeX, so far I use a whole bunch of single-row tabular environments and have been toying with Tikz in combination with Tikzmarks, but I'm not sure how to make arrows travel in right angles like in the picture. What would be your way of going about this?

Answer 1

Here's a solution based on Arianna's link :

\documentclass[2pt,a4paper]{article}
\usepackage{tikz}
\usetikzlibrary{shapes,positioning,calc}
\colorlet{lightgray}{gray!20}

\begin{document}

\begin{tikzpicture}[relation/.style={rectangle split, rectangle split parts=#1, rectangle split part align=base, draw, anchor=center, align=center, text height=3mm, text centered}]\hspace*{-0.3cm}

% RELATIONS

\node (countrytitle) {\textbf{COUNTRY}};

\node [relation=3, rectangle split horizontal, rectangle split part fill={lightgray!50}, anchor=north west, below=0.6cm of countrytitle.west, anchor=west] (country)
{\underline{ISO\_code}%
\nodepart{two}   Name
\nodepart{three} Description};

\node [below=1.3cm of country.west, anchor=west] (awardtitle) {\textbf{AWARD}};

\node [relation=5, rectangle split horizontal, rectangle split part fill={lightgray!50}, below=0.6cm of awardtitle.west, anchor=west] (award)
{\underline{URI}%
\nodepart{two} Name
\nodepart{three} Description
\nodepart{four}  Date
\nodepart{five}  Country\_ISO\_code};

\node [below=1.1cm of award.west, anchor=west] (booktitle) {\textbf{BOOK}};

\node [relation=5, rectangle split horizontal, rectangle split part fill={lightgray!50}, anchor=north west, below=0.6cm of booktitle.west, anchor=west] (book)
{\underline{URI}%
\nodepart{two}   Name
\nodepart{three} Description
\nodepart{four}  First\_publication\_date
\nodepart{five} Original\_language};

\node [below=1.4cm of book.west, anchor=west] (genretitle) {\textbf{GENRE}};

\node [relation=3, rectangle split horizontal, rectangle split part fill={lightgray!50}, anchor=north west, below=0.6cm of genretitle.west, anchor=west] (genre)
{\underline{URI}%
\nodepart{two}   Name
\nodepart{three} Description};

\node [below=1.5cm of genre.west, anchor=west] (ell1) {...};

\node [below=1.0cm of ell1.west, anchor=west] (winsawardtitle) {\textbf{WINS\_AWARD}};

\node [relation=3, rectangle split horizontal, rectangle split part fill={lightgray!50}, anchor=north west, below=0.6cm of winsawardtitle.west, anchor=west] (winsaward)
{\underline{Award\_URI}%
\nodepart{two}   \underline{Book\_URI}
\nodepart{three} \underline{Genre\_URI}};

% FOREIGN KEYS

\draw[-latex] (award.five south) -- ++(0,-0.2) -| ($(award.five south) + (4,0)$) |- ($(country.one south) + (0.25,-0.50)$) -| ($(country.one south) + (0.25,0)$);

\draw[-latex] (winsaward.one south) -- ++(0,-0.4) -| ($(winsaward.one south) + (11,0)$) |- ($(award.one south) + (0,-0.35)$) -| ($(award.one south) + (0,0)$);

\draw[-latex] ($(winsaward.two south) + (0.00,0)$) |- ++(0,-0.60) -| ($(winsaward.two south) + (5,0)$) |- ($(book.one south) + (0.00,-0.40)$) -| ($(book.one south) + (0.00,0)$);

\draw[-latex] (winsaward.three south) -- ++(0,-0.2) -| ($(winsaward.three south) + (2.63,0)$) |- ($(genre.one south) + (-0.00,-0.35)$) -| ($(genre.one south) + (-0.0,0)$);

\end{tikzpicture}

\end{document}

Which gives :

Answer 2

Well just to challenge me and see how much can be done using functions, here I present my attempt; I automate the creation of frames, possibility of emphasizing ones, facilitating the interconnection of frames by means of named coordinates,add a new property to the text of nodes, for this case the underlining.

Some faults: To define the box style it is necessary to add a space; since the function of pgfmath of word length calculation is used, it is not possible to include text with \bar instructions, such as \_ for underscore. Well it can be a reason for you to ask a question in the forum ...

Here the result:

Here the MWE:

% By J. Leon
\documentclass[border=20pt]{standalone}
\renewcommand\familydefault{\sfdefault} % Default family: serif 
\usepackage[usenames,dvipsnames]{xcolor}
\usepackage{tikz}
\usetikzlibrary{calc} 
\usetikzlibrary{arrows, decorations.markings,positioning,backgrounds,shapes}
\definecolor{WIRE}{HTML}{002FA7} % Klein Blue
\newbox\ubox %Used to define new node property
\begin{document}
\begin{tikzpicture}[
    EMP node/.style={% Style for empatized boxes
        rectangle, line width =1pt,
        anchor=west,
        underline, % new property
        align=center,
        text=white,
        minimum height=.8cm,
        fill=WIRE!50,
        draw=black,
        },
    NOR node/.style={% Style for normal boxes.
        rectangle, line width =1pt,
        anchor=west,
        align=left,
        minimum height=.6cm,
        fill=WIRE!10,
        draw=black,
        },
    underline/.append style={% define new style property
        execute at begin node={%
            \setbox\ubox=\hbox\bgroup
            },
            execute at end node={%
                \egroup\underline{\box\ubox}%
                }
             },
    ] % Uff that is all the configuration for tickzpicture xD

% Define an brute force objet "Frame"
% Variables 1:Position, 2: Identifier, 3: Title of frame 4: Subframe/Boxtype
 \def\Frame(#1)#2[#3]#4{%
  \begin{scope}[shift={(#1)}] 
      \node[font=\bf, anchor=west] (Title) at (-0.2,0.7) {#3}; 
       \edef\k{0}% Variable for box positión
       \edef\x{0}% Variable for named coordinate centering - below box
       \foreach \id/\style in {#4} {%enter sub frame data Name/Boxtype ,Name2/Boxtype | An space before Boxtype is needed 
            \node[\style node] (h) at (\k pt,0) {\id}; %  % Draw a node depending on the variables.
            \pgfmathparse{\k+0.5*width{"\id"}+3.4pt} % Uses the textwidth to calculate named coordinate  
            \xdef\x{\pgfmathresult} % The resul is saved in the variable \x
            \draw (\x pt,-0.4) coordinate (\id#2); %Create a named coordinate concatenated: "sub frame data Name"+"identifier"
            \pgfmathparse{\k+width{"\id"}+6.8pt}% Calculate positión for each subframe box.       
        \xdef\k{\pgfmathresult}% Save the value to be added to the next iteration value.
       }    
  \end{scope}
}% disadvantages: Is not posible to use Frame data Name like: Name_another_desc instead I use Name-another-desc

% Start drawing
% \node[EMP node] (dm) at (0,0) {{Sometext/EMP,another/EMP}};
 \Frame(0,0){1}[EMPLOYEE]{%first frame identified as 1 named EMPLOYEE
    Fname/NOR ,% see that it is necessary to add a space
    Minit/NOR ,
    Lname/NOR ,
    Ssn/EMP ,
    Bdate/NOR ,
    Address/NOR ,
    Sex/NOR ,
    Salary/NOR ,
    Super-Ssn/NOR ,
    Dno/NOR  % and here too
    }; 

 \Frame(0,-2.5){2}[DEPARTAMENT]{
    Dname/NOR ,
    Dnumber/EMP ,
    Mgr-ssn/NOR ,
    Mgr-Start-date/NOR 
    }; 

 \Frame(0,-5){3}[DEPT\_LOCATIONS]{
    Dnumber/EMP ,
    Dlocations/EMP %Here is a space
    };

  \Frame(0,-7.5){4}[PROJECT]{
    Pname/NOR ,
    Pnumber/EMP ,
    Plocation/NOR ,
    Dnum/NOR 
    }; 

  \Frame(0,-10){5}[WORKS ON]{
    Essn/EMP ,
    Pno/EMP ,
    Hours/NOR 
    };  

 \Frame(0,-12.5){6}[DEPENDENT]{
    Essn/EMP ,
    Dependent-Name/NOR ,
    Sex/NOR ,
    Bdate/NOR ,
    Relationship/NOR 
    }; 

% Start drawing arrows:
% In this part I use the named coordinates to draw the arrows.
    \draw[thick,<-,thick,>=latex] % From Essn6 to Ssn1  
        (Ssn1)++(0.1,0) -- ++(0,-.55) -- ++(4.5,0) coordinate (inter) %inter is the name of coordinate register
        -- (Essn6 -| inter) -- ++(0,-0.4) coordinate (inter)  % to calculate intersections.
        -- (Essn6 |- inter) --++(0,0.4); %
     %Essn -- Ssn id 5
     \draw[thick,<-,thick,>=latex]
        (Ssn1)++(-0.1,0) -- ++(0,-.7) -- ++(4.55,0) coordinate (inter) %some shift using (Ssn1)++(shiftx,shifty)
        -- (Essn5 -| inter) -- ++(0,-0.4) coordinate (inter) 
        -- (Essn5 |- inter) --++(0,0.4); %
     \draw[thick,<-,thick,>=latex]
        (Pnumber4) -- ++(0,-.5) -- ++(1,0) coordinate (inter) 
        -- (Pno5 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Pno5 |- inter) --++(0,0.2); %

     \draw[thick,<-,thick,>=latex]
        (Dnumber2) -- ++(0,-.75) -- ++(4,0) coordinate (inter) 
        -- (Dnum4 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Dnum4|- inter) --++(0,0.2); %

     \draw[thick,<-,thick,>=latex]
        (Dnumber2)++(-.2,0) -- ++(0,-.9) -- ++(1.75,0) coordinate (inter) 
        -- (Dnumber3 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Dnumber3 |- inter) --++(0,0.2); %

     \draw[thick,<-,thick,>=latex]
        (Ssn1)++(-0.3,0) -- ++(0,-0.85) -- ++(3.5,0) coordinate (inter) 
        -- (Mgr-ssn2 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Mgr-ssn2 |- inter) --++(0,0.3); %

     \draw[thick,<-,thick,>=latex]
        (Dnumber2)++(0.2,0) -- ++(0,-.6) coordinate (inter) -- (Dno1 |- inter) -- (Dno1); %

     \draw[thick,<-,thick,>=latex]
        (Ssn1)++(0.3,0) -- ++(0,-.4) coordinate (inter) -- (Super-Ssn1 |- inter) -- (Super-Ssn1); %

\end{tikzpicture}
\end{document}

UPDATE AND CORRECTION OF BUGS:

• Issue 1: Typography descender problen in normal boxes. Solution: setting text height=1.5ex and text depth.25ex in node styles for NOR and EMP.

• Issue 2: The same bug apears in emphatized boxes, problem is by \underline command: Solution: package ulem is added, the separation is modified globally, \renewcommand{\ULdepth}{3pt}.

Some improvements

• Requirement to add space at the end of the format declaration. Solution: change innnecesary style declaration EMP node/.style= to EMP/.style={

Known problems

• The underlined lines do not change color, for that reason I commuted the colors

UPDATED RESULT:

UPDATED MWE:

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% By J. Leon
\documentclass[border=20pt]{standalone}
\renewcommand\familydefault{\sfdefault} % Default family: serif 
\usepackage[usenames,dvipsnames]{xcolor}
\usepackage{tikz}
\usepackage{soul}
\usetikzlibrary{calc} 
\usetikzlibrary{arrows, decorations.markings,positioning,backgrounds,shapes}
\definecolor{EMP}{HTML}{77DD77} % Green1
\definecolor{NOR}{HTML}{06500C} % Green2
\usepackage{ulem}
\renewcommand{\ULdepth}{3pt}

\newbox\ubox
\begin{document}
\begin{tikzpicture}[
    EMP/.style={% Style for empatized boxes
        rectangle, line width =1pt,
        anchor=west,
        underline, % new property
        align=center,
        text=Black,
        minimum height=.8cm,
        text height=1.5ex,
            text depth=.25ex,
        fill=EMP,
        draw=black,
        },
    NOR/.style={% Style for normal boxes.
        rectangle, 
        line width =1pt,
        anchor=west,
        align=left,
        minimum height=.6cm,
        text height=1.5ex,
            text depth=.25ex,
            text=white,
        fill=NOR,
        draw=black,
        inner ysep=5pt
        },
    underline/.append style={% define new style property
        execute at begin node={%
            \setbox\ubox=\hbox\bgroup
            },
            execute at end node={%
                \egroup\uline{\box\ubox}%
                }
             },
    ] % Uff that is all the configuration for tickzpicture xD

% Define an brute force objet "Frame"
% Variables 1:Position, 2: Identifier, 3: Title of frame 4: Subframe/Boxtype
 \def\Frame(#1)#2[#3]#4{%
  \begin{scope}[shift={(#1)}] 
      \node[font=\bf, anchor=west] (Title) at (-0.2,0.7) {#3}; 
       \edef\k{0}% Variable for box positión
       \edef\x{0}% Variable for named coordinate centering - below box
       \foreach \id/\style in {#4} {%enter sub frame data Name/Boxtype ,Name2/Boxtype | An space before Boxtype is needed 
            \node[\style] (h) at (\k pt,0) {\id}; %  % Draw a node depending on the variables.
            \pgfmathparse{\k+0.5*width{"\id"}+3.4pt} % Uses the textwidth to calculate named coordinate  
            \xdef\x{\pgfmathresult} % The resul is saved in the variable \x
            \draw (\x pt,-0.4) coordinate (\id#2); %Create a named coordinate concatenated: "sub frame data Name"+"identifier"
            \pgfmathparse{\k+width{"\id"}+6.8pt}% Calculate positión for each subframe box.       
        \xdef\k{\pgfmathresult}% Save the value to be added to the next iteration value.
       }    
  \end{scope}
}% disadvantages: Is not posible to use Frame data Name like: Name_another_desc instead I use Name-another-desc

% Start drawing
% \node[EMP node] (dm) at (0,0) {{Sometext/EMP,another/EMP}};
 \Frame(0,0){1}[EMPLOYEE]{%first frame identified as 1 named EMPLOYEE
    Fname/NOR,% see that it is necessary to add a space
    Minit/NOR,
    Lname/NOR,
    Ssn/EMP,
    Bdate/NOR,
    Address/NOR,
    Sex/NOR,
    Salary/NOR,
    Super-Ssn/NOR,
    Dno/NOR}; 

 \Frame(0,-2.5){2}[DEPARTAMENT]{
    Dname/NOR,
    Dnumber/EMP,
    Mgr-ssn/NOR,
    Mgr-Start-date/NOR}; 

 \Frame(0,-5){3}[DEPT\_LOCATIONS]{
    Dnumber/EMP,
    Dlocations/EMP};

  \Frame(0,-7.5){4}[PROJECT]{
    Pname/NOR,
    Pnumber/EMP,
    Plocation/NOR,
    Dnum/NOR}; 

  \Frame(0,-10){5}[WORKS ON]{
    Esgn/EMP,
    Pno/EMP,
    Hours/NOR};     

 \Frame(0,-12.5){6}[DEPENDENT]{
    Essn/EMP,
    Dependent-Name/NOR,
    Sex/NOR,
    Bdate/NOR,
    Relationship/NOR}; 

% Start drawing arrows:
% In this part I use the named coordinates to draw the arrows.
    \draw[thick,<-,thick,>=latex] % From Essn6 to Ssn1  
        (Ssn1)++(0.1,0) -- ++(0,-.55) -- ++(4.5,0) coordinate (inter) %inter is the name of coordinate register
        -- (Essn6 -| inter) -- ++(0,-0.4) coordinate (inter)  % to calculate intersections.
        -- (Essn6 |- inter) --++(0,0.4); %
     %Essn -- Ssn id 5
     \draw[thick,<-,thick,>=latex]
        (Ssn1)++(-0.1,0) -- ++(0,-.7) -- ++(4.55,0) coordinate (inter) %some shift using (Ssn1)++(shiftx,shifty)
        -- (Esgn5 -| inter) -- ++(0,-0.4) coordinate (inter) 
        -- (Esgn5 |- inter) --++(0,0.4); %
     \draw[thick,<-,thick,>=latex]
        (Pnumber4) -- ++(0,-.5) -- ++(1,0) coordinate (inter) 
        -- (Pno5 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Pno5 |- inter) --++(0,0.2); %

     \draw[thick,<-,thick,>=latex]
        (Dnumber2) -- ++(0,-.75) -- ++(4,0) coordinate (inter) 
        -- (Dnum4 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Dnum4|- inter) --++(0,0.2); %

     \draw[thick,<-,thick,>=latex]
        (Dnumber2)++(-.2,0) -- ++(0,-.9) -- ++(1.75,0) coordinate (inter) 
        -- (Dnumber3 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Dnumber3 |- inter) --++(0,0.2); %

     \draw[thick,<-,thick,>=latex]
        (Ssn1)++(-0.3,0) -- ++(0,-0.85) -- ++(3.5,0) coordinate (inter) 
        -- (Mgr-ssn2 -| inter) -- ++(0,-0.2) coordinate (inter) 
        -- (Mgr-ssn2 |- inter) --++(0,0.3); %

     \draw[thick,<-,thick,>=latex]
        (Dnumber2)++(0.2,0) -- ++(0,-.6) coordinate (inter) -- (Dno1 |- inter) -- (Dno1); %

     \draw[thick,<-,thick,>=latex]
        (Ssn1)++(0.3,0) -- ++(0,-.4) coordinate (inter) -- (Super-Ssn1 |- inter) -- (Super-Ssn1); %

\end{tikzpicture}
\end{document}

Answer 3

Here is just an example how you can automate the relation drawing using to path style.

The style relation has 3 sub-keys :

• right : the distance to deviate on the right,
• source : integer that indicate if it is 1st, 2nd, ... out-going relation,
• target : integer that indicate if it is 1st, 2nd, ... in-going relation.

And can be used in to or edge.

\documentclass[tikz,border=7pt]{standalone}
\tikzset{
  relation/.style={
    to path={
      ([xshift={\sourcedown*(-1ex)}]\tikztostart)
      |- ([xshift={\relationright+\sourcedown*(-1ex)},yshift={\sourcedown*(-1ex)-1ex}]\tikztostart)
      |- ([yshift={\targetdown*(-1ex)-1ex},xshift={\targetdown*(-1ex)}]\tikztotarget)
      -- ([xshift={\targetdown*(-1ex)}]\tikztotarget)
    },
    ->,>=latex,
    relation/.cd,#1
  },
  relation/.cd,
  source/.store in=\sourcedown,
  source = 1,
  target/.store in=\targetdown,
  target = 1,
  right/.store in=\relationright,
  right = 1 cm
}
\begin{document}
  \begin{tikzpicture}
    \draw
      (0,0) edge[relation={right=2cm}] (1,3) % by default this is the first source/target relation
      (0,0) edge[red,relation={right=3cm,source=2}] (2,-3) % second source / first target
      (-.5,1) edge[blue,relation={right=2cm,target=2}] (1,3); % first source / second target
  \end{tikzpicture}
\end{document}

To draw the database fields you can use matrix of nodes for example.