Posts

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

SELECT * FROM EVALUATION;

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