Introduction

erd-script is a system to easily draw Entity-Relation (ER) diagrams.

In this case 'easily' means without needing to pay attention to the drawing part itself. This implies that the writer only needs to think about specifying the right entities, relations, attributes and identifiers.

It uses graphviz to actually draw to diagrams.

You can try it out in the browser as described here.

Sample

// Modelled after https://graphviz.org/Gallery/undirected/ER.html

entity course
  attribute name
  id code

entity institute
  attribute name

entity student
  attribute name
  attribute number
  attribute grade

relation CI(C-I)
  one required institute
  multiple optional course

relation SI(S-I)
  one required institute
  multiple required student

relation SC(S-C)
  multiple optional course
  multiple optional student

Online version

You can use erd-script in your browser without needing to install anything.

  • Creating an ER model can be done at this url
  • Creating a physical model can be done at this url

Syntax

In Entity-Relation diagrams the following concepts exist:

  • Entities
  • Relations
  • Attributes
  • Identifying attributes
  • (more exist, but are not yet supported)

General info

  • A name can only contain alphanumerical characters.
  • A label can contain any character.

Comments

You can add comments to the erd code by preceding it with //.

// This is a comment

Entities

An entity is defined with the keyword entity followed by a name.

entity course

Relations

A relation is defined with the keyword relation followed by a name. This relation definition needs to be followed by at least 2 lines specifying the members.

Members

The syntax for a member is of the form <cardinality> <optionality> <entity_name>

Valid values are:

  • cardinality: one (maximum one), multiple (can be several), exactly(n) (exactly n times)
  • optionality: required or optional
relation CI
  one required institute // A course has to be taught in an institute and can only be taught in one institute
  multiple optional course // An institute can teach multiple courses but does not need to teach one

It is possible to add a label to a relation by placing it between round brackets.

relation CI(C-I)
  one required institute
  multiple optional course

Attributes

Attributes can be added to entities and relations.

The syntax for an attribute is of the form <type> <name> where type can be id or attribute.

entity course
  attribute name
  id code

entity institute
  attribute name

relation CI(C-I)
  one required institute
  multiple optional course
  attribute year

Syntax

erd-script supports converting an ER-diagram to physical structure by generating the sql code to create the tables and constraints.

To use this feature, extra syntax is needed.

The following concepts are used:

  • Tables
  • Datatypes

Datatypes

Columns in a table need to have a datatype, therefore you need to add a type to each attribute in the ER-diagram.

entity Person
  id id type autoincrement
  attribute name type varchar(100)
  attribute alive type boolean

The following datatypes are supported

  • autoincrement: an integer that is filled in automatically
  • boolean: a boolean true / false value
  • varchar(n): a text of maximum length n
  • integer: an integer
  • float: a floating comma value
  • datetime: a date and time

Tables

Entities

For each entitiy a table needs to be created with a command of the form table <table_name> from entity <entity_name>.

table person from entity Person

Relations

Foreign keys

Relations of type 1:1 or 1:n can be implemented by adding a foreign key to the right table.

table person from entity Person
foreign key father for Father
foreign key mother for Mother

Extra table

Relations of type m:n need to be implemented by creating an extra table with a command of the form table <table_name> from relation <relation_name>.

table ownership from relation Ownership

Example

entity Person
  id id type autoincrement
  attribute name type varchar(100)
  attribute alive type boolean

relation Father
  one optional Person // For now, this order is important, the one with 'one' should be the first for unary relations
  multiple optional Person

relation Mother
  one optional Person
  multiple optional Person

relation Friends(Is friends with)
  multiple optional Person
  multiple optional Person

entity Car
  id id type autoincrement
  attribute color type varchar(20)
  attribute price type float

relation Ownership(Is owner)
  multiple optional Person
  multiple optional Car
  attribute since type datetime

table person from entity Person
foreign key father for Father
foreign key mother for Mother

table car from entity Car

table ownership from relation Ownership

table friendship from relation Friends

Importing the SQL in your dbms

LibreOffice Base

  • Start the program
  • Create a new (or open a) database of type HSQLDB Embedded
  • Click in the toolbar on Tools -> SQL...
  • Paste the SQL code in the upper input field
    • Make sure you selected LibreOffice Base as dbms when generating the SQL code.
  • Click on Execute
    • The status should be 1: Command successfully executed.
  • Close the SQL view (by clicking on Close in the right bottom corner)
  • Click on Tables in the left menu
  • Refresh the tables by clicking on View -> Refresh Tables
    • All tables should be there

You can also check the relations by clicking on Tools -> Relationships

MS Access

  • Start the program
  • Create a new (or open a) database
  • Click in the toolbar on Create -> Query Design
  • Click in the right bottom corner on SQL
    • Or in the left upper corner
  • Paste the SQL code here, command by command
    • MS Access does not support running multiple queries at once, so you will need to run each command (the things separated by ;) by hand
    • You execute the query by clicking on Execute in the left upper corner
  • Close the query (without saving it)
  • The tables should be created

You can also check the relations by clicking on Database Tools -> Relationships.