Designing our Oracle JET Application – Creating our tables in the Oracle Database Cloud Service

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

Import the Data Model into Oracle SQL Developer

Choose your Data Model Design File

Choose your Data Model Design File

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

The Logical Data Model

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

The Engineer to Relational Model option

Ooops… what’s this?

Warning Engineering to Relational Model - No Relational Model created

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

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

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 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

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

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 Generation Options dialog

The DDL was generated, but with some errors

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

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

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

Creating a connection for the OJETBLOG db user

Execute the DDL script with the OJETBLOG user

Execute the DDL script with the OJETBLOG user

So if you do a simple


you can see that the table is already created.

Get data from the recently created table

Get data from the recently created table

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

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

The application Data Model

Designing our Oracle JET application – The Data Model

Welcome to the 3rd article on our new Blog series about Oracle JET and Oracle Cloud.

Today we’ll start designing our application, starting with its Data Model. For that, well be focusing on Oracle’s SQL Developer Data Modeler as our tool and design the application’s underlying data model.

So, without further ado, let’s dig right in.

Data Modeling Workflow

Let’s start our SQL Developer and go right into the Data Modeler.

Open up the model browser and save the existing design with a proper, understandable name. I chose “OJetBlog-DataModel”.

Acessing the Data Modeler Browser

Accessing the Data Modeler Browser

Save the design to give it a proper name

Save the design to give it a proper name

Once you have done this, you can start working on your Logical Model. As you know, there are several models to represent your data model, from the most high level (not bound by the RDBMS) to the Physical Model that is totally dependent on the RDBMS.

For our exercise, we’ll model our application in our Logical Model, pass it through to the Relational Model, and the Physical Model, through the generations of specific DDL for our Oracle Cloud Database. Any changes that we need to make in our database will be performed at the Logical level and then, using the SQL Developer tools, passed through our workflow and finalized in a DDL that will be executed on our DB. Keeping this workflow ensures coherence in your designs and a properly documented and maintained DB.

Creating our Logical Data Model

So, using the Data Modeler browser, locate the Logical Model, right-click it and then choose “Show”. This will show your Logical Model diagram, which should be a blank canvas by now.

You Logical Data Model - right now, still a blank canvas

You Logical Data Model – right now, still a blank canvas

The image above highlights the tools available to create your logical data model. You can create Entities, Views and relationships, place notes and images, etc… For this exercise, I’ll create our entities and their corresponding relationships.

First, let’s think about what entities should be involved in our application and their relationships in terms of cardinality. As a quick summary of our project’s goal, we’ll be able to create an Evaluation of a Worker’s performance during a Project Sprint. A project will have a lot of sprints, and each sprint will have a series of evaluations, one per each worker. Of course, workers will perform several sprints also, sometimes even overlapping (working in two distinct sprints at once, on different projects). So, we’ll have:

  • Entities
    • Worker – one of our company’s employees involved in a given sprint.
    • Project – one of our company’s projects.
    • Sprint – a project sprint with a given start and end date that roughly equates to a set of work items being performed by several workers within a project scope.
    • Evaluation – an evaluation of a given worker on a given sprint.
  • Relationships
    • Worker (N) – Sprint (M)  – One sprint relates to the collection of project work done by several workers on a given period. On the other hand, a worker necessarily works on more than one sprint. So this equates to an N:M relationship between these two entities. As you know, N:M relationships are typically bad and we should try to avoid them. I try to resolve this issues immediately in the Logical Model. I think it has a lot of advantages tackling this as soon as possible. So, I’ll create a relationship entity to transform this N:M relationship into two 1:N relationships. To see more on this, check out this article on database normal forms, specifically the 3NF.
    • Project(1) – Sprint (N) – Each project will have multiple sprints, but each sprint belongs to only one project. A simple, classic 1:N relationship.
    • Worker (1) – Evaluation(N) – Each worker will have multiple evaluations, but each evaluation will refer to only one worker.
    • Sprint(1) – Evaluation(N) – Each sprint will have multiple evaluations, but each evaluation will refer to only one sprint.

So, let’s create our entity. Select the appropriate icon in the toolbar, and then draw the entity in the canvas. You’ll be presented with the Entity Properties dialog.

Create a new Entity - Icon in the toolbar

Create a new Entity Icon

Create a new Entity - Entity Properties dialog

Create a new Entity – Entity Properties dialog

From here, you can specify your entities to the tiniest detail, but we’ll focus on the first two sections: General and Attributes.

In the General Section, I’ll just specify the name of the Entity, in this case, Project. Then I’ll go into the Attributes section and specify the attributes of a project, as seen in the dialog below.

Create a new Entity - Project Entity Attributes

Create a new Entity – Project Entity Attributes – Notice the creation and editing toolbar (highlighted)

Attributes can be created using the toolbar. In this case, I created the project ID, project name, and customer name. If this would be used for anything else, I would probably have Customer as an ID to a Customer entity, but for our exercise, this is enough. Please notice that in the case of the ID, I checked the “Primary UID” checkbox, which should set this attribute as the primary identifier of this entity. When you check it, it automatically also checks the “Mandatory” checkbox. So this takes care of the primary keys.

But when you have relationships between entities, you should also have Foreign Keys. I bit on that later, when we create our relationships.

Go ahead and create the Project entity and the Sprint entity as well, using something like these attribute definitions:

Create a new Entity - Using Domains

Create a new Entity – Using Domains

Most of the attributes have a Logical Data Type. Logical Data Types are the ones which are not associated with a specific business domain. You can think of them as the usual types, such as numeric, boolean, date, string (VarChar), etc… As you can see, the first three attributes are of logical data type (Numeric and Date), but the fourth one isn’t. It’s a Domain-based attribute, which means that it has a specific structure specific to a business context. We want our Sprint Status to take only a given set of possible values and we do this by setting a specific Domain type.

Specifying Domain Types

To manage your domain types, go to the Domain Administration Tool. In there, you can create your own domain types, as I did for the Sprint Status. Let me show you how.

Manage your Domains using the Domains Administration Tool

Manage your Domains using the Domains Administration Tool

Select the option to add a new Domain type, give it the name you want and then specify to which logical data type will this map to. In our case, we chose the VARCHAR data type, with Char unit and a size of 20, which is enough to hold a sprint status.

Manage your Domains - The Sprint Status properties

Manage your Domains – The Sprint Status properties

Once the logical type is set, we’ll define the list of all possible values by going to the Value List option. In there we add our Sprint statuses, as shown in the next image.

Manage your Domains - The Sprint Status list of all possible values

Manage your Domains – The Sprint Status list of all possible values

And that’s all there is to it. Ah… don’t forget to save it. Your domain type is created and ready to be used when defining your attributes.

Entity Relationships

Entities have relationships between them, as I mentioned before. We even characterized them so now it’s time to create them in our diagram.

Creating relationships is very simple. You select the type of relationship you want to create between two entities by clicking on the respective icon in the toolbar. Then you click on the Source entity and then on the target entity and the Relation Properties dialog appears for you to specify its details.

Let’s take the example of the Project and the Sprint entities. Create a relationship between the Project and the Sprint entities.

Creating a 1-N relationship between Project and Sprint

Creating a 1-N relationship between Project and Sprint

The relation properties dialog appear and you can fine-tune your relationship. I just click ok. 🙂

Creating a 1-N relationship - The Relation Properties dialog

Creating a 1-N relationship – The Relation Properties dialog

You’ll notice that there’s a new attribute in the Sprint entity called ID1. This corresponds to a Foreign Key to the Project Entity and is automatically added when you create a 1:N relationship on the N side.

I go to the Sprint properties dialog (double-click on the entity in the diagram), Attributes and double-click on the ID1 attribute from the list of attributes. I then change the name of the attribute in the newly opened dialog to ProjectID, as it refers to the Project ID in the Project entity.

Creating a 1-N relationship - The Foreign Key attribute

Creating a 1-N relationship – The Foreign Key attribute

And this is how you create your relationships. Time to create our complete Logical Model.

The full Logical Model

So now, you only need to create your logical model using the instructions I explained earlier. For the sake of time, let me just take my ready-made pie from the oven, which is to say, show you my complete Logical Model. Here it is in all its glory. I use the Bachman notation, but SQL Developer also supports Barker and Information Engineering notations. Try them out and see which is better for you. This article on Wikipedia is an excellent starting point to understand the different notations.

Full Logical Data Model - Bachman Notation

Full Logical Data Model – Bachman Notation

Full Logical Data Model - Barker Notation

Full Logical Data Model – Barker Notation

From these two diagrams, you can understand the Entities, the Relations and the respective attributes involved. The only thing missing is the list of values associated with the Project_Role domain type that you can see in the Worker Sprint entity. Here it is:

Project Role Domain

Project Role Domain



Now all you have to do is create this model yourself.

I’m pretty sure it can be improved, as I only have some basic database modeling knowledge. Feel free to place suggestions in the comments

Wrap up

Our Database model is designed. Next time, we’ll go through the Relational and Physical Models and put the actual database artifacts in our Cloud database. Stay tuned!

P.S: next week there will be no article, as I’ll be enjoying some Carnival holidays! 🙂

José Rodrigues, a.k.a. Maverick


Post image by Jon Olav Eikenes

Oracle JET Blog Series

Getting Started With Oracle JET and Oracle Cloud

Hi everyone,

We’ll kick off this year with a new blog series in Red Mavericks, devoted to a more pure development thread with Oracle development tools (broadly speaking).

I was most impressed with Oracle’s own JET MOOC, which provided with a nice introduction of the toolkit, and allowed me to clean up those spider webs from my programming background and returned to the good old keyboard bashing routine. This posed a significant difference from what I’ve been doing in the last 10 years, which were mainly filled with Workflow and BPM projects.

The Oracle JET MOOC also helped me return to a language I only grasped some 20 years ago… JavaScript. And since JavaScript is all the rage nowadays, it was the perfect excuse to (re)learn it using today’s programming patterns.

Finally, the Oracle JET MOOC ended up with a very important message: give something back to the community and help others! So it only seemed fitting that I would take some of my time to set up something that could help others that, like me, are not (or no longer) into programming, and particularly into JavaScript. So this new blogging series is born.

My goal is to publish a new article every 2 weeks, but if I manage to get a bit more time I’ll try to reduce it to a week’s interval.

The Oracle JET

Oracle JET is a toolkit released by (surprise…) Oracle, which addresses the need to build Enterprise applications in JavaScript. Its main focus is the frontend, with backend services being used mainly via REST web services.

As mentioned several times by Oracle, JET is not a framework, but rather a toolkit, a collection of frameworks that have been put together, tested and enhanced to develop and deliver high-quality enterprise applications. So JET is not a direct substitute for AngularJS or React. It uses its components, such as JQuery and KnockoutJS, to address the same needs as those two JavaScript frameworks.

Because the target is to build enterprise applications, JET incorporates thoroughly tested components that have been on the market for quite some time and are mature. This contrasts with the “Java Framework flavor of the week” approach, in which people adopt the newest framework because it’s the best thing since the invention of the wheel. Don’t get me wrong: going for the newest coolest stuff can be great. There are loads of applications in which using one of these newer frameworks can greatly reduce your work and deliver very good results. But, within organizations, there are several things to consider when choosing frameworks, such as how many resources are available in the market with that kind of knowledge (something that typically is very low on recent frameworks), what kind of community support is available, how do frameworks work with each other to accelerate development (and what side effects or bugs are there), etc… Organizations go for stability and maturity above all, because that ensures quality and risk reduction.

How to set things up

There are a few things you need to set up in order to start. We’ll guide you through each one of these steps. If you already have some of these items installed and setup, you can skip those steps. Here’s a list of everything you need to prepare:

  • Install Node.JS (version 5+)
  • Install the Oracle JET CLI (Command Line Interface)

Install Node.JS

The first thing you need to do is to install Node.JS. You need this to have the NPM installer, which is needed to install the Oracle JET CLI.

Go to download page, choose your OS and the LTS version, and download the respective pre-built installer. Then, run the installer, as shown in the images (these refer to the Windows version. Other OS may differ slightly).

Download Node from site

Download Node from site


Run the Node Installer

Run the Node Installer


Review and accept the License Agreement

Review and accept the License Agreement


Choose the installation folder

Choose the installation folder


Just leave the default options to be installed - Installs everything

Just leave the default options to be installed (Installs everything)


Proceed with the installation

Proceed with the installation


...Installation in progress...

…Installation in progress…


... and the installation is completed!

… and the installation is completed!

After the installation is completed, you can verify that everything is working according to the plan by using the following command in a command line window (Command Prompt or Powershell in Windows, Terminal window in MacOS or Linux), to verify the current version of nodeJS in your system:

prompt> node -v

You can also check the Node Package Manager (NPM), which is what will guarantee the installation of Oracle JET and all its dependencies.

prompt> npm -v 

Done! On to the next part.

Install Oracle JET CLI (Command Line Interface)

We now have everything we need to install a very special tool that was made available with Oracle JET 4.0.0: the CLI. This tool will allow you to create and manage your Oracle JET projects, as well as help you through the entire development process. I could explain you all the details, but it’s better if I just show you hands-on how to use it and the benefits of it.

To install the Oracle JET CLI just run this on your command line or terminal:


prompt> npm -g install @oracle/ojet-cli

If you are behind a corporate proxy, you may need to set additional configurations to npm, so that it can fetch and install the necessary packages. These are:

prompt> npm config set proxy
prompt> npm config set https-proxy

After the Oracle JET Cli is installed, you can use the ojet command, which we will use to create, build and run our Oracle Jet projects. Try it out by typing ojet help. The outcome should be something like this.

prompt> ojet help
Oracle JET CLI

ojet <command> [<scope>] [<parameter(s)>] [<options>]

Available commands:

add ........................... Adds platforms, plugins and more to a JET app

build ......................... Builds a JET app

clean ......................... Cleans build output from a JET app

create ........................ Creates a new JET app, custom theme, or component

help .......................... Displays command line help
Commands: [add|build|clean|configure|create|list|remove|restore|serve|strip]

list .......................... Lists platforms, plugins and more within a JET app

remove ........................ Removes platforms, plugins and more from a JET app

restore ....................... Restores missing dependencies, plugins, and libraries to a JET app

serve ......................... Serves a JET app to an emulator, device or the browser

strip ......................... Strips all non source files from a JET app

Detailed help:

ojet help <command> [<scope>]

You now have Oracle JET properly installed on your system! But before we go and get our hands dirty, there’s still one piece of the puzzle left… the IDE. Enter Netbeans!

Installing NetBeans IDE

You can, of course, use any text editor to create your javascript code. However, that’s the hard way.

The easy way is to use an IDE or text editor that offers some creature comforts, such as code completion, syntax highlighting. Oracle recommends the use of NetBeans IDE (and I do also), a free and open-source IDE, because it features already a tight integration with Oracle JET, making it easier for you to develop Oracle JET projects.

So, go on to the Netbeans site and download and install the latest version (at the time of writing, it’s 8.2). You’ll only need the HTML5/Javascript bundle, but as it is such a great IDE for development in other technologies, I opted for the “All” package.

Download your preferred bundle - I went for All

Download your preferred bundle – I went for All

Netbeans Installation - In my case, everything was already installed. Just follow the instructions.

Netbeans Installation – In my case, everything was already installed. Just follow the instructions.

Now you’re ready to start developing projects using Oracle JET. Hurray!

An Oracle JET application high-level architecture

So we now have almost everything we need to start developing our applications. Nevertheless, we need to take into account what’s the typical high-level architecture of an Oracle JET application. Oracle has a very good diagram that explains this quite well, as well as what is the Model-View-ViewModel architectural design pattern.

The Oracle JET Architecture

The Oracle JET Architecture


the Model-View-ViewModel (MVVM) architectural design pattern

The Model-View-ViewModel (MVVM) architectural design pattern

For now, just have these diagrams present in your memory. As we unfold and go through the next chapters of this blog series, all of this will become clearer and second nature to you, while developing your applications.

What will we be developing?

Through my years as a developer, and now in management, there was always the need to build some tools to help in the development process. Right now, Agile development seems to be the norm and although there are a lot of tools out there to help with this “methodology”, there are always somethings that don’t fit quite well with the way you do your stuff.

So, I decided to tackle that need with the development of an Agile Toolbox, which addresses my particular needs. All of the exercises and blog entries will revolve around building these tools, and I’ll cover everything, from the idea, through application design, requirements definition, development, building and testing, all the way up to packing and deploying the application in a cloud provider (Oracle Application Container Cloud Service?).

As of now, the tools that we’ll develop are:

  • Team Evaluation Tool – A tool that will allow a team member to evaluate the performance of his colleagues during a sprint. The application will get data about projects where the person has been involved, check the sprint team, and allow a quantitative evaluation (1-5), plus a top and bottom performer election.
  • User Story and Backlog creation – A tool to create user stories and manage them in terms of story lifecycle (written, in backlog, in development, in testing, released)
  • Planning Poker – A tool to allow planning poker on the user stories created. Will allow not only voting but also discrepancy discussion, etc…
  • Work Report – A reporting worksheet in which each team member will report the time they spent on a given User Story

We’ll  start with the Team Evaluation Tool in the next blog article.

The Server Side Stuff

Referring to the architecture diagram above, for our case, I decided to implement all Server Side logic and data using Oracle Database Cloud Service (DBCS). For me, it’s the easiest way to handle the data and associated business logic, while having the possibility to expose these as REST services, using ORDS (Oracle REST Data Services). Of course, I could set up a local database for this, but it wouldn’t be as much fun and it’s a way for some of you guys to start your journey in the (Oracle) cloud. We’ll set up a trial account and guide you through each step in the next article of this blog series.

That doesn’t mean that we’ll be implementing everything we need directly in the browser (although we could). We’ll also use the Oracle SQLDeveloper. including the SQL Data Modeler, to plan and design our application data as well as some business logic.

You can download it from here. There’s no installation process. You just uncompress it to a directory and run directly from it. You’ll need an up to date Java Runtime to run it, though.

Additional Resources

Before we wrap up the first blog article in the series, I would like to recommend a set of additional resources that will prove valuable throughout this adventure.

Wrap up

So we set up most of the tools we’ll need to start our adventure with Oracle JET and we established what we’ll be doing in the next weeks/months. I hope you’ll continue this journey with us and follow our (bi-)weekly articles, building your own tools or the ones we’re proposing.

All code will be available on Github. Everyone is welcome to contribute to it.

So, until next week.

José Rodrigues, a.k.a. Maverick

This post has been cross-posted by Link Consulting. Check out their other articles at