In this paragraph we will create a Relational Data Generator (RDG) project on a simple database, and fill it with generated values.

We will use the Chinook sqlite database. The Chinook data model represents a digital media store, including tables for artists, albums, media tracks, invoices and customers.

Download Chinook here

Create a connection to your database

When you start RDG, the Database connections page is opened by default.

Click on the "+" button, then set the following attributes:

• Name: chinook
• Driver: SQLite
• JDBC URL: jdbc:sqlite:c:/path/to/Chinook_Sqlite.sqlite
  For instance, if you have saved the file in c:/tmp, the JDBC URL is jdbc:sqlite:c:/tmp/Chinook_Sqlite.sqlite
• User: leave blank
• Password: leave blank

RDG needs a specific file to connect with each database vendor, the JDBC driver.
In the driver attributes, click on "Download driver from internet". This will download the Sqlite driver. To make sure the connection is OK, click on "Test". Then click "Connect".

Explore the database model

After creating a new project, the list of tables is automatically read from the database.

To read the other metadata from the database, including foreign keys and constraints, click on the "Refresh metadata" button from the toolbar (shortcut F5).

Go to the "Diagram" page. You should be able to explore the relations between tables. Tables are represented as boxes, foreign keys as links between them.

In this tutorial we will fill the following tables:

• Artist
• Album
• Genre
• Track

Set up cardinalities

On the "Diagram" page, click on the table Artist. In the table attributes, set "Target row count": 20

Then click on the link between Artist and Album. We can specify how many albums each artist will have. The default value is 0..* where the star means "no upper limit".

Set the cardinality to 1..3 so that each artist will own at least 1 album and at most 3:

Do the same for the other tables:

• Album table: set "Target row count": 50
• Genre table: set "Target row count": 4
• Track table: set "Target row count": 500
• Album-Track relation: set "Cardinality": 4..20

For the Genre-Track and Album-Track relations, leave the cardinality to the default value (0..*).

Set up generators

Time to save the project ! Click on "Save", and set the file name to tutorial.json.

Go to the "Tables" page. Check the 4 tables that we want to fill (Artist, Album, Genre, Track).

Set up the Artist table

We will start by configuring the Artist table. Click on Artist > column ArtistId. In the column attributes, click "Select..." > "Numerical sequence".

This generator is suitable for Ids: the first row will have the value 1, the second row 2, etc.

Click on column Name > "Select..." > "Full name"

In the Preview pane, you can see a sample of the values that will be inserted into this column:

Set up the Album table

Go to Album table > AlbumId column > Set the generator to "Numerical sequence"

Go to Title column > Set the generator to "Pick from list"

This generator picks values randomly from a list. The list can be user-defined, or we can reference an existing list. Relational Data Generator comes bundle with a set of built-in lists that can be used for this purpose.

Click on "Reference existing list" > Type in "music_albums".

This built-in variable contains Rolling Stone Magazine's list of "The 500 Greatest Albums of All Time." The values in this list do not represent a single field but a full object, with the following fields: Artist, Genre, Subgenre, Album name, etc.

For this reason we need to select the Album field. Click on "+ Transform/filter" > "Select object field" > Type in "Album". You should see album names in the "Preview" pane:

Note: to see all built-in variables and create your own, go to the "Variables" page.

You should not be able to edit the ArtistId column since it is the target of a foreign key. The column will be automatically filled with Ids coming from Artist.ArtistId.

Preview

To see how things look so far, go to the "Preview/Execution" page then click on "Album".

A preview of the data that will be inserted into the table is displayed.

Now click on "Artist" to see a preview of this table as well.

Set up the Genre table

• GenreId column: in Sqlite, all integer primary keys can auto-increment: when they are not referenced in an insert statement, each row will be assigned an unused value. To use this native auto-increment feature, we will set the generator to "Default value for column".
  In this case the preview pane shows the value (default) but this is not the value that will be inserted in the database.

• Name column: create a "Pick from list" generator > "Create list" > type in the following values (one per line):
  Rock
  Country
  Reggae
  Pop
  

Set up the Track table

Set up the Track table the following way:

• TrackId: default value for column
• Name: Random string with min length=10 and max length = 10.
  Add a transformation: Join with > String > "My awesome track #" > first.
• Composer: Constant value > NULL
• Milliseconds: Random number with distribution = Normal, mean = 180000 and variance = 60000.
  Add a transformation: Round > Nearest > to 0 decimals.
• Bytes: Reference column > "Milliseconds".
  Add a transformation: script: x * 12

Check for problems

Go to the "Problems" page. This page lists any problem that could be found in the project.

It is a good practice to check this page after any change to the project.

Fill your database with generated values

Go to the "Preview/Execution" page. Click on "Fill checked tables".

The database is filled according to our project. Warning: all contents from the selected tables will be overwritten.

A notification is displayed at the end of the execution, with a link to the execution report.

To see the data that has been inserted, click on "Real data". This toggle switches between preview data and the data currently existing in the database.

Show execution report

Go to "Execution history" and selected the first report in the list.