Database Design With ER/Studio

ER/Studio Data Architect

And this recording, we're going to create and deploy a database design. So the steps are going to take, we're going to start with the logical data model that we created earlier on for our design specified by the business. As we know, the logical model is expressed in business language, but the physical world has various conventions that we need to apply to it. So you going to set up those naming conventions within the tool. So then we can generate a physical model from that logical. And then we're going to elaborate that model with specific elements for the database products that we're going to choose. And then finally we'll deploy that design to the database itself. So let's kinda have a look at the tool.

Logical Data Model

So this is the logical model we're going to start from.As you can see, it's expressed in business language, which isn't suitable for deployment databases. So what we're going to do is use a naming standards template, which allows us to specify some of the rules of how to convert between logical and physical names and vice versa. It's going to go across to our data dictionary. So the day addiction is really useful. It allows us to build a library of reusable components, such as attached properties, rules, domains, data types, triggers, and procedures.

Creating Naming Conventions

And in this demonstration, we're going to create a new naming standards template. Now, normally, if were connected to the central repository, that naming standards template would already be in place here, we're in a standalone mode modifying MD one file. But we're going to do is we're going to load into our standalone model, our corporate standards, naming templates. Okay.

So there's various different rules for naming that can be applied. The main area that we're gonna look at here is where got a list of logical words and their physical contractions and equivalents. We can also specify for the tool, how to understand the separation of logical words in a name. So it'll detect word separations by character or by case changes, et cetera. And then how is it going to apply to in the physical world? Likewise, it also allow you to remove special characters from a string. Then you can specify the behavior or what you want to replace it with. Okay. So there's our naming standards applied.

Physical Data Model

Okay. So before we generate our physical model, just notice here between training course and skill, we've got a many to many relationship, a non-specific relationship. So the model isn't yet in third normal form.

So this wouldn't work as a physical model. We couldn't deploy this to a database. So we're going to see next is how the wizards we're hope is resolved that, okay, so let's run the wizard to, we can read, run it from our Explorer. We can right click on the logical model generate physical model, or we can go to the menu at the top. So the first job is to name the physical model that we're going to create. So we're going to call it HR and discord DB. We can specify what type of physical model is relational dimensional.

We're going to select the database platform that we're going to generate two, and this is really important. So every physical model has properties and information associated with the target products that you're going to deploy to.

Entities and Business Data Objects

So there's a whole bunch of settings we can now apply. The first job is to decide, well, what's going to go into the physical model so we can select entities by business data objects, or what tables we're going to generate. So we'll select everything here.

Apply Naming Conventions

Next thing is the naming standards template that we selected earlier. So let's apply that we can also apply other naming standards like prefixes and suffixes were attached to each table name or a column name.

So earlier on, were talking about our many to many relationship. We had that nonspecific a relationship. So this is where the tool we can tell it all, exactly how to deal with that. So we can specify to create here an associative entity or not bother with the relationship at all. Then we click finish and generate the model.

Logical Data Model vs. Physical Data Model

So there's our physical model generated, and we can do this a number of times. So from a single logical model, we can generate multiple physical models out one for each different database product.

So the first thing to notice is the nonspecific relationship between our training course and our skill here has now been resolved. Many to many relationship has been replaced by an associative entity, nicely named based on the two entities. So we can now go through each of the tables in the design and look at the, in detail at the generated model. So for our employee table called EMP, we've got a lot of different properties we can set for the of the table. So here's the list of columns. And we can go through each individual column and look at the properties that the tool is allocated to it.

So the first thing to look at is the data type. So the NC data types that we saw in the logical model and now been replaced with the database specific products. So for Microsoft SQL server here, all the specific data types, the length has been set, allow NOLs, et cetera. And here we've got properties specific to the database products that we can set for different database products, live different features. And many of these features are available through the data architect. The DDL tab contains all the SQL codes that will generate the database. Again, specific to the database products, which is SQL server here. We've also got pre and post sequel. So you, we can add in before that DDL code or after the DDL code, your own custom code, there's lots of other things we can set for the table.

So we can set indexes, for instance, security requirements, set permissions for the table, for the model as a whole, we could move on and we could set things like functions and procedures, synonyms triggers, and the user side of the database as well

Deploy to Database

So the design is now complete next. We can deploy it to the database server. So we've got a menu option here for database generate database and off we go. So there's a little wizard here that will take us through the process. We can generate objects, the database connections, or connect to our database, and there's various connection mechanisms. So we can either use it's a different database products that have different connection mechanisms.

We can connect via ODBC driver through a direct connection mechanism, or if we've using teams server within the team server product, we can store lists of different database connections, and we can slate from one of those. So in this one, we're going to connect directly to the database. So there's the name of our server. And we can put in the password and we could store this back in teams, server, and reuse it later on. So we connected to the database and we now specify, do we connect to an existing database or create a new one in this case, we're going to create a new one. And then we can specify exactly what do we want to add to our database? So we can select individual tables.

If we wanted to, we've got various different rules and properties here that we can apply. And this one we'll keep it simple. There's, it's not going to give us a summary of all of that and finish and off we go. So that's now connected directly to our database and has deployed. So we go to our Microsoft SQL server management studio. Then we can see if we refresh that refresh of the databases, there's our HR database and all of the tables all nicely created exactly as per our model.

Logical Data Models with ER/Studio

So to summarize, we took a, a logical model, all expressed in business language. We applied a naming convention file, and then using that, we generated a new physical data model from the logical we then went through and we saw that the non-specific relationship had been resolved with creating an associated table within added some extra information specific to our SQL server database. And then we deployed it directly to our database server, more information, go to idera.com forward slash contact sales. Thanks, Alyssa.