8

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

Relational Database Schema

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?

11
+150

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 :

Relational Schema

8

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:

enter image description here

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.

enter image description here

  • 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}.

enter image description here

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:

enter image description here

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}
  • 1
    +1 Really nice code and beautiful styling – Andrew May 2 '18 at 6:30
  • Thanks a lot for that interesting contribution. As I wrote, I intended the bounty to reward OP's answer, but I definitely upvoted your answer, and might use it in the future. – Clément May 5 '18 at 19:33
  • There is a surprising bug with your (excellent) code: if the name of the attribute contains a letter with a descender, then it is slightly pulled up. In your example, you can see it at "Salary", for instance. – Clément May 22 '18 at 20:37
  • There is an update, thanks for noticing that bug .. – J Leon V. May 22 '18 at 23:33
  • Wow, that's impressive. Following p. 3 of the ulem doc, you could add \newcommand\whiteuline{\bgroup\markoverwith\textcolor{white}{\rule[-0.5ex]{2pt}{0.4pt}}}\ULon} and then replace \uline with \whiteuline to obtain a white underlining, hence being able to go back to your previous style. (Also, it spells "DEPARTMENT", not "DEPARTAMENT" :-)) Finally, your improvement makes your code way easier to use, thanks a lot! – Clément May 23 '18 at 3:18
1

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}

enter image description here

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

  • Thanks a lot for that interesting contribution. As I wrote, I intended the bounty to reward OP's answer, but I definitely upvoted your answer! – Clément May 5 '18 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.