DbSchema Tutorial with Introduction in

SQL Databases
Contents
Connect to the Database and Create First Tables ................................................................................... 2
Create Foreign Keys ............................................................................................................................. 7
Create Indexes ..................................................................................................................................... 9
Generate Random Data ......................................................................................................................... 11
Relational Data Browse ......................................................................................................................... 11
DbSchema Project File ........................................................................................................................... 14
Schema Synchronization ....................................................................................................................... 15
DbSchema Layouts ................................................................................................................................ 16
Visual Query Builder .............................................................................................................................. 17
The SQL Editor ....................................................................................................................................... 18
Forms and Reports ................................................................................................................................ 19
Technical Support .................................................................................................................................. 20
End ......................................................................................................................................................... 20

In this tutorial you will get an introduction into relational databases. Among the tutorial we will use
the DbSchema designer tool. Download, install and open DbSchema from
http://www.dbschema.com. DbSchema can be used on trial base 15 days for free.
Connect to the Database and Create First Tables

Start DbSchema and you should get this screen. Choose ‘Connect to Database’.

Choose this

We are going to connect to a H2 database. We will use this because H2 requires no other software to
be installed. For MySql, MariaDb, Postgresql, etc., the database software should be installed as well.
If you wish to do this please check in DbSchema Help, in the ‘Databases and How to Connect’ there
are some tips, they will help to install the database software end enable network connections so you
can connect DbSchema to the database.

H2 connection to file database

Press ‚new‘ to create a new

database in a folder
In the connection dialog choose first ‘H2’ database. This will prompt to download the driver. Choose
‘Download from DbSchema’.

Press ‘Create New’ to create an empty database. In the next dialog you can choose or create an
empty folder.

Create new folder

Press ‘Ok’ in the connection dialog.

Next step is to choose a schema you will work on. A schema is similar with a group of the tables, so
different applications may use the same database but different schemes. ‘Information_schema’ is an
internal H2 schema, with tables used by H2 itself.

Press ok and we should get a screen like bellow. The database is empty, no table is created. Create a
table by right-clicking the layout (the large free space) and ‘Create table’.
 Right-click the layout and
choose ‘Create table’.

In our tutorial we are going to create two tables: Customers and Employees. Each table will have few
columns.

Customers

 Customerid – a number, to identify each customer. For example a customer ‘Targot’ will have
the customerid 100, and each time we reffer the customerid 100 we know we talk about this
customer. Using this Id’s has the advantage that we can easy rename the customer and all
other tables which point to it won’t be changed.
 Name – some text
 Description - text

Employees

 EmployeeId – similar with customerid, a number to identify each employee

 Firstname – text
 Lastname – test
 Birthdate – date
 Companyid – the id of the company he or she works for

In the table dialog enter the first table name, than add one column at a time by pressing the ‘add’
button.
 Enter the table name

Add columns one by one

When you will enter the table name as well as the column names, you will notice the text is
automatically converted to upper-cases. This because the database is using by default uppercases for
table and column names, and DbSchema convert them to show them as they are looking in the
database.

Let’s add the first column CUSTOMERID. Enter the name, choose the data type ‘Integer’ which means
number. The checkbox ‘Primary Key’ should be on, which means this column will unique identify any
record from this table. Primary keys must always be specified. This means all records should have a
value in this field and therefore the mandatory field is checked and cannot be un-checked.
Similar we add the name and description as varchar (this is text) with maximal number of characters
allowed 100 for name and 500 for description. The name should be mandatory in order to make sure
no company is missing the name.

After adding all columns the table will look like here:

Primary key and ‘x’ for

mandatory symbols

Or like this:

Press this button to see the data type

Similar create the second table EMPLOYEES.

Create Foreign Keys

To prevent having employees with CUSTOMERID values which does not exists in the customer table,
we are going to create a foreign key. The foreign key is a ‘constraint’, which means it is a logical
verification of the data. This verification takes place each time a new record is inserted in the table or
the data is modified.

To create the foreign key, drag and drop the CUSTOMERID from EMPLOYEES over the CUSTOMERID
in CUSTOMERS. We want that each EMPLOYEE.CUSTOMERID exists in the CUSTOMERS.CUSTOMERID.

Drag & drop column

The foreign key dialog will open:

The foreign key requires the referring table (employees) and referred table (customers) plus the
columns for each of them. We can set an action to be taken if a customer is deleted. No action
means nothing will be done. If we will try to drop a customer which has employees we will get back
an error message. We can set ‘on delete cascade’ and all employees belonging to a customer will be
deleted when the company is deleted.

‘Virtual’ means the foreign key is created only in DbSchema. In this case no data verification will be
done in the database. The virtual foreign keys may be used only for design purposes or for relational
data browse which will be explained later.

A foreign key always require an index to exist on the referred columns (CUSTOMERID in customers
table). If this column is a primary key will automatically get an index. Read more about indexes in the
index chapter.

About the foreign key impact on performance

Is a common mistake to skip creating foreign keys because they slow down the database. This is not
true. The foreign keys are a validation of the data which is done only when inserting or deleting new
rows. Usually the ID fields don’t get modified, so the foreign key won’t be triggered. The foreign key
validation won’t be done when updating other columns than the foreign key columns.

Foreign keys are useful as they will prevent having bad data in the database. This is very important!
Foreign keys are healthy!

Create Indexes

Database indexes are used to fasten searching of data by a given criteria. Consider a query SELECT *
FROM EMPLOYEES WHERE FIRSNAME=’JOHN’. Without indexes the database will scan the complete
data before finding the employees with this firstname. This may take time if the table contains many
records. Having an index on FIRSTNAME will fasten the search.

Indexes work similar with a book keyword index, which can be usually found in the end of a book. An
index is a list of values in a column with a reference to the position where the values can be found in
the table.

Indexes duplicate the column data in a separate structure and hold for each distinct value a list of
pointers to the table records. The index is synchronized by the database server each time the column
data is modified, inserted or deleted.

Let’s create an index on EMPLOYEES.FIRSTNAME. Right-click the FIRSTNAME column and choose ‘Add
Index’.
An index can be normal, which means any value can be stored inside. Unique indexes do not allow
duplicate values. When a value is inserted in the table field and that value exists in another record for
the same field, the insert operation will throw an error and the operation is aborted.

Primary Key indexes are the same as unique indexes plus the condition that the column must be
mandatory (does not allow missing or empty values). A table can have at most one primary key, but if
you need more you can create unique indexes and set the columns to be mandatory. This has the
same effect as creating a primary key column.

After creating the index a symbol will show in front of the column.

Index symbol

Indexes require more space in the database and decrease the database performance on inserts and
updates, but increase the performance by selects.

In case of foreign keys with ‘on delete cascade’ is recommended to set-up an index on the referring
column ( customerid in employees ). This will fasten the deletion of any customer from customer
table.

Queries can use at most one index for a table. If for example you want to search an employee by
firstname and lastname and you have one index for firstname and another index for lastname, only
one index will be used. One solution is to create a compound index on two or more columns. Create
an index on firstname and lastname in the same time. This will help on searching by firstname and
lastname, or only by firstname. The index won’t help searching only on lastname - either the first or
first and second columns have to be specified in the search criteria. Having a value only for the
second column won’t use the index.

Generate Random Data

Let’s fill the tables with some random data. For this choose the random data generator from the
menu.

Generate ramdom data

Generate data

In the dialog press ‘Generate’ to start the generator. Optional we can configure other patterns for
the fields as the default settings. Double-click one of the tables to open the patterns editor for each
of the columns.

Relational Data Browse

DbSchema has a dedicated tool for exploring and editing the data. Is called ‘relational’ because can
explore data from multiple tables based on the foreign keys between them.
Right-click on CUSTOMERS table header and press the Relational Data Browse from the popup. The
option is available in the right-click pop-up as well.

Relational Data Browse

The editor will open. We can descend in the EMPLOYEES table by pressing the foreign key arrow in
the table header. Choose ‘Join EMPLOYEES’.

Descend into EMPLOYEES

Now we are browsing two tables. Select a different record in the first editor will refresh the second
with the matching rows based on foreign key, which are the matching employees for the selected
company.
To filter a column data click the column header.

To edit the data double-click any cell.

Double-click cell to edit.

Add or delete row

DbSchema Project File

Our work including the schema, diagrams, SQL Editors and relational data browse can be saved to
project file. The file has the extension .dbs.

Save project to file.

Next time when you restart DbSchema, the project file will be reopened. If you are working with a
dedicated database server and you are in a location where you cannot connect to the database, the
project file can still be opened and the diagrams will show. The relational data browse cannot be
used because the data has to be loaded from the database.
Schema Synchronization

DbSchema is using its own image of the schema. This is what is being saved in the project file.
Whenever you connect to the database you have to press the refresh button to make sure the
internal schema is the same as in the database.

Refresh schema

Online or offline

DbSchema can be used online (connected to database) or offline. Switching to online will require
refreshing schema from the database or creating schema in the database (if you designed a fresh
schema directly in DbSchema offline). After refresh you may decide which differences to take over in
DbSchema internal project or apply in the database.

If you design a schema offline you can connect to any database and create the schema in the
database. You may improve an existing schema, connect and deploy the latest changes on different
databases.
DbSchema Layouts

A layout is the diagram and the associated tools opened inside ( Relational data browse, SQL editor,
Query builder, Database reports, etc. ). Create further layouts from the menu.

Create further layouts

One table may show in different layouts or in none of them. Adding tables to layout can be done by
drag & drop or by pressing the foreign key icon beside columns.

Drag & drop tables to

layout from here
Add further tables to layout by
pressing the foreign key icon
Visual Query Builder

Relational data browse is one tool for accessing the data. Beside it we can use the SQL Editor and the
Query builder. Start the query builder by clicking the table header.

The builder will open with the selected table. We are going to build a query over both tables. We add
the CUSTOMERS table by clicking the foreign key icon.

Foreign key icon

Tick the checkboxes for the columns we want to show in the result. To set a filter (where clause) right
click a column.

Execute the query. The result can be saved to file. The generated query is on the left, in SQL Preview
frame.
 Execute the query

Generated query

The SQL Editor

Use the SQL editor will to edit and execute SQL queries. Text auto-completion is enabled on CTRL-
BLANK keys.
Forms and Reports

Using DbSchema you can create small applications or database reports. Start a report from the
application menu, edit the query in the query builder (called data source, add tables to it by drag &
drop).

After editing and executing the query the

wizard will continue.

One more screen to select the components to add to report:

…and the report is ready to be executed from the menu.

 Run report
Execution mode:
HTML or swing

Execute the report and the web browser will show the data.

On DbSchema website there are dedicated tutorials for learning forms and reports. Read them and
you will discover how easy is to create a master-detail report, do customization, etc.

Technical Support

On DbSchema website there are contact forms where you can write us with any technical questions.
Unregistered users may ask as well. Don’t hesitate to write!

End

Thank you for taking this course. Please give us feedback about DbSchema, your help is appreciated!