Welcome to the 4th article on our not-so-new Blog series about Oracle JET and Oracle Cloud.

Today we’ll take our Logical Data Model design from the last article and create the necessary tables directly on our cloud database instance.

Let’s start the party!

The SQL Developer data model generation tools

Launch the SQL Developer and import the design you worked on the previous article.

Import the Data Model into Oracle SQL Developer

Choose your Data Model Design File

So here’s our Logical Data Model, in all its splendor:

The Logical Data Model

Now we need to create the Relational Model, based on our logical model. Lucky for us, this means pressing a button in SQL Developer. When you open your Logical Model, a specific option called “Engineer to Relational Model” appears, allowing you to produce the Relational Model directly. And the good thing is that there’s also a button to “Engineer to Logical Model”, which means you can make your own small adjustments and changes, and reflect those back into the Logical Model.

Find the SQL Developer Data Modeler Logical Model toolbar and click on the “Engineer to Relational Model” option.

The Engineer to Relational Model option

Ooops… what’s this?

Warning Engineering to Relational Model – No Relational Model created

You need to create the model first (or open one, if you already have one created). Go to your Data Modeler browser, find the Relational Models folder and right-click it to create a new relational model.

Right-click to create a new relational model

Now you can try again to engineer your Logical Model. This time, an Engineering dialog appears. Make sure all checkboxes on the Logical side are selected so that everything in the Logical Model is transformed into Relational Model artifacts. There are a few options that you can use to finetune this process, but we’ll leave everything in default. Click on Engineer. Et voilá! Your Relational Model is now generated.

The Engineer to Relational Model dialog

Here’s our application Relational Data model. Although it’s closer to what we’re going to have on our application (now we talk about Tables and Columns and Primary Keys), it’s still not the actual data model implementation.

The Relational Model

The final step to have our model running inside the cloud database is to produce the actual Physical model and run it, which is to say, generate the DDL and execute that inside our cloud database. The DDL generation is achieved also with the press of a button, based on our relational model.

There’s a specific option to generate the DDL from the relational model

As the DDL code is specific to a given RDBMS, this option asks what specific version of DB engine are we using and from which relational model do we want to use to generate it. Notice that the current SQL Developer version also features DDL generation for DB engines other than Oracle.

DDL generation tool features multiple DB engines

As we choose Oracle DB 12c as our cloud database engine, we’ll choose that option and click on “Generate”. SQL Developer will then show you the DDL generation options specific to that DB engine. Naturally, Oracle DB DDL generation have more options than other engines. For the purpose of this exercise, we’ll just use the default settings and click OK.

The DDL Generation Options dialog

The DDL was generated, but with some errors

Our DDL generation produced some errors, but we can’t see them. SQL Developer says that we should check the Design Rules for more details, so that’s what we’ll do. Press “Generate” again to take you to the DDL generation dialog and click on “Design Rules”, in the lower left corner to check for the errors.

The Design Rules option

It shows up a Design Rules dialog where you can apply the rules and check eventual problems. Doing so shows a few warnings and a lot of errors, but only one seems to stand out: The Evaluation.value Column datatype is unknown.

Checking the Design Rules validations to find the problem with the DDL generation

We seem to have forgotten to set the Evaluation.Value datatype. It should be a Domain value list, so let’s do it, as we did before. The best way is to correct all the way up in the logical model, where the problem is. The good thing is that correcting it is easy and propagating that correction all the way down to the physical level is also quite easy: just generate an updated relational model and then re-run the DDL generation tool.

The error message still exists, but it’s just a matter of having comments associated with the tables, so we can just ignore them. Just save the data model generation code that SQL Developer produced. We’ll now apply it to our cloud database.

Executing our DDL in our Cloud Database

Do you remember the user we created in the second article of the series, OJETBLOG?

We’ll use that to execute our DDL. First, create a DB connection using OJETBLOG to our cloud database and connect to it. Then, open the DDL file you have previously saved and select the OJETBLOG connection from the connection list. Finally, execute the DDL script and commit those changes. Your data model should now be fully deployed in the cloud.

Creating a connection for the OJETBLOG db user

Execute the DDL script with the OJETBLOG user

So if you do a simple

SELECT * FROM EVALUATION;

you can see that the table is already created.

Get data from the recently created table

All the tables created from the relational and logical models, now fully deployed in the cloud

And that’s it! Your data model is fully deployed and running in your Oracle DB Cloud Service.

Wrap up

The data model is done and implemented. Next time, we’ll start building our application, from top (UI/JET) to bottom (NodeJS). Stay tuned.

José Rodrigues, a.k.a. Maverick

Post header image by: nigelpepper