Entity Relationship Modeler

Hello. Hello and welcome to this, Aqua Data Studio advanced Er modeler session. Aqua Data Studio, a universal database IDE with visual analytics, productivity, and for It professionals working with data and databases. Aqua Data Studio you to develop and manage your databases and access, import, export, and visually analyze them. With this, Aqua Data Studio supports over 30 different database platforms and works on a variety of operating systems such as Windows, Max, OS, and Linux. Aqua Data Studio supports relational, NoSQL, and cloud based data sources, making your data easily and quickly accessible. The agenda that we have for today is going to talk about the Er modeler, specifically more advanced features of the Er modeler component. In Aqua David Studio, we will learn how to forward engineer a model. We will open up an existing data model and generate a script from it. Using the existing data model, we will go ahead and generate Dan HTML report an image.

ER Model

Once we’ve done that, we’ll go ahead and compare this model with a different data model that we have. We can also go ahead and compare this with a live database. Finally, we’ll go ahead and convert this to a different database platform. So with that, let’s get started. Here I have the Aqua Data Studio application open. What you’re looking at is the sample Er model that I have generated. Here you can see on the left hand side are your tables, your views, and your relationships and any notes that you might have. Here onto your right hand side, at the center of the screen is the canvas. This is where you would have your Er Model objects being present, such as views, tables, etc. Here you can see we have the relationships lines. Here we talk about the different types of relationships on the tool palette that you can actually go ahead and work with.

Data Model

Our first overview is going to be how we can go ahead and forward engineer from a brand new database into a data model. Here I’m going to go ahead and pick Microsoft SQL Server as my source. I’m going to pick 2017, and this gives me a blank sketch board, if you will, to actually go ahead and start building my data model. What I can do is I can first click on go to the palette and click on the entity on the table and go ahead and drag and drop the multiple ones. I click on it if I want one more, let’s go ahead and do it like so next what I can do is I can go ahead and double click on the table one, I’m going to call it as Clients. Here we can pick whichever database we want in question or what schema we plan on using.

Here I’ll just call it a Department, call it Client ID, and we will go ahead and just leave it at this. And I’ll go ahead and click okay. Now if you want to go ahead and view some of the advanced options, you can right click on the table and go through any of these options. Here, for example, if I want to add a new primary key constraint, I can just go ahead and add that. Here I can choose primary key for this particular column which has a not null value. One of the cool things about Aqua Data Studio is that it gives you the ability to go under the Sequel preview tab and look at these options. So we have this. Now let’s take a look of an existing data model. I’m going to go to my Tools menu file open Recent and I’m going to open up this mSQL healthcare model.

Generate Script for Data Models

Here we have an already reverse engineered data model into our canvas. What we will do now is go ahead and generate a script for this particular data model. I will go tools and I’ll click on Generate script. Here you have the option of picking which of the databases you want. Here I’ll pick the healthcare and here I can pick with schema. I’ll pick all the schemas, pick the tables and you can see that I have from the database Healthcare. I have the schema DBL and the table names. Go ahead and choose next. Here you have multiple ways of generating scripts from a diagram. The first option is a preview tab. I can actually go ahead and choose preview the SQL script right within the application. Or I can put all my scripts into a single file and save it as I wish. Plus I can also go ahead and create individual files per object.

For example, I can have an individual file for tables, individual files for views, so on and so forth. I can actually go ahead and choose. For the purposes of this demonstration, I will pick the preview tab. I’ll go ahead and click Next and there you have it. Here you have a sample list of script that you can go ahead and copy and paste it in the query analyzer and run the scripts from there, the next option we’ll be looking at is generating an HTML report. Now, an HTML report would basically go ahead and save this entire script. Here I can actually go ahead and save it. Here I have a folder that I have already gone ahead and saved it. Here if I go to my Internet Explorer, if I go to the navigate under that folder and navigate to the index HTML page I’m brought to this HTML view.

Here I can just go ahead and click on Tables. It gives me the list of tables along with the metadata as well. Same thing goes for views, click on this. I can get the view information including the SQL statement it generated. Next, let’s take a look at how we can go ahead and save this as an image. In order to do so, we’ll navigate back to the Tools menu and choose Save US image. In the Save as image we can choose a variety of formats. Here I have JPEG as well as PNG. I can just choose PNG. Here I can save it under the same report Healthdb. If I go ahead and open it up, I have this view that I can look and I can magnify it with my favorite image viewer that I have within the operating system. To save all as images will actually go ahead and save it all the groups of images in a common folder.

Compare Models

You can also go ahead and do so now what we’ll do is we’ll compare this with a different model. Now the purpose of this is to compare if you’re working on a test development project as well as a production based project, you can compare the production with the development project. I’ll go oct and choose compare with model. Here I have an Oracle At and C model and I can choose what are the database objects that I’m interested in comparing. Here we can choose the alignment, include database names, key, my name, Et, Peter. We’ll choose next. This brings us to the Compare editor. Here you can actually see what’s present in the Oracle XC model, but not present in the SQL Server model. Finally, let’s take a look at how we can compare this with the database. Here I have my set of databases and I can go ahead and choose my production database if I will.

Import Database Reverse Engineering

I’ll pick my tables that I’m interested in. Choose Next. Here again, you can see that there’s some key difference, whereas in the production here it says in the database it says non clustered, but in the diagram it says clustered. There’s a little bit of difference here. Here if you look that these two objects remain the same. Again, there are certain table objects that are visible in the diagram, but not there in the table in the database. Excuse me necessarily. Next we will go ahead and choose how we can import a database. Importing a database is basically reverse engineering it from scratch. Here for example, if I were to go to my production database, I picked this, then I’ll pick a different schema, I’ll pick the sales schema here, pick the table, I don’t want everything. I’ll just pick a couple of these. Choose next. And there you go.

Connect to Different Database Platforms

Here you can see I have my tables from my sales schema and plus I brought in these as well. Plus you can see I have the column comments and table level comments as well. If I were to double click, it brings moving to the metadata of this particular tablet, including giving me the SQL preview as well, now the last part is to convert an existing DBMS platform to a different one without having to rewrite any of that logic. Here, for example, I can actually go ahead and convert this to a snowflake model. So I’ll choose next. And there you have it. Now I have all the snowflake related data types. Everything’s been converted, including the DDL being generated as well. So, in summary, Aqua Data Studio provides extensive capabilities to improve productivity for developers, analysts and DBAs alike. Helps you to connect to 30 plus different database platforms.

Gives you the ability to perform SQL query development, data visualization, database administration, and, as you’ve seen here, er modeling to a greater scale.