Build a Logical Data Model

with ER/Studio Data Architect

Building a Logical Data Model

In this video, we're going to build a logical data model. The steps we'll take are as follows first, we'll capture our high level requirements as a conceptual data model. Then we'll populate with the entities, the core concepts of the model three to those entities. We'll then add in the properties, starting off with the key properties. How do we identify those entities? And then we'll add the non-key properties. Then finally we'll have some relationships between the entities. So let's go look at the tool.

ER/Studio Data Architect

So this is AR studio, data architects. First step, we can create a new project, the options we've got, we can create a new blank project, or we can reverse engineer an existing database, or we can import from an external files. So we're going to create a new model. Over on the left-hand side. We've got the Explorer, which gives us views of the model itself. The data dictionary, the lineage, showing data movements and macros.

Logical and Physical Data Models

So in our model, we can break it down into the logical part of the model, and then we can create physical models. So he asked UDL allows you to create one logical model and you can break it down into sub models from the main model or any individual sub model, which might represent a particular concept or topic or project. We can then generate many physical models for different physical database products. So we're, first of all, we're going to create a new sub model. It's going to call this training. The next step is to add a title block.

Business Data Objects

We can then add our business data objects. So we're going to create business data objects for employee skill department training course and agency. So this model represents a data store for an HR system. Thanks that we can lay out the business data objects on the screen.

Conceptual Data Model

So this effectively represents our conceptual data model. These are the core concepts in our model.

Data Entities

So the next step is to populate these with the entities. So we'll start off with an employee and we can create different subtypes of employees. So we'll have a full-time employee, we'll have a contractor. We can then create other entities for department, agency, training, history, training, course scale and employee skill matrix. And again, we can lay them out nicely on the canvas notice throughout we've used business. So this helps us communicate and confirm our requirements with the business community. Now we can add our institutes to our business data objects. So we select a number of entities within the business data objects. We can right click and add them to the business data objects. We can then edit the business data objects. We can even provide a description of the business data objects, and we can see all the entities that are contained within the business data objects. Now, one of the entities here is, has got a higher priority than all the others. So employees the most important entity in the business data objects. So we'll set that as the anchor object and we can do the same thing for all of our other institutes can now start adding properties to our instance.

Primary Keys

So we're going to add our primary keys and then the non key properties. So we have shifts. We can click above the line here and start typing in the primary key for the objects. So we'll create employee number. If a clink below the line, we can then create some non key properties. So our love employee type. Start date. I can then edit the entity directly either by double clicking or right click edit. So here we can see various properties of the entity itself, and we can apply things like a note to the entity. We've also got a business definition for the NC. So here we might want to say, employee must have an employment contract. We can also look at the attributes and start to edit the attributes. So yeah, employee number again, double click or click, and then click the edit button. And we can see all the properties of each of the attributes and the attributes themselves. We can apply notes and definitions, et cetera. We can also add things like reference values. So for employee number, we're going to specify the data type. Now here is the empty list of logical data types that are universal to all data assets. Later on. When we convert this model to a physical model, a data architect we'll use data time mappings for each database products from this ANSI logical data type, we'll create the appropriate products related physical data type. We're going to set this to be character data, and we'll have a 12 character employee number because it's a key allow. NOLs is always set to know, and we do the same for our other attribute types. So our employee type will be character. Let's make it one single character allow NOLs. Nope, we need to have an employee type again. This is where we can employ the reference value. So might want to say what are the different employee types and provide that list start date. We can set that to be a date property. Okay. And again, allow NOLs. We'll set that to know, And then we can add attributes all of our other entities. Okay. So we've now created our business data objects. We've populated those with entities, okay. To those entities, to the business data objects, we've created primary keys for all of our entities and we've set all of our non-key properties as well. So notice over on the left-hand side, our Explorer has been populating as we go along. So there's all our business data objects within employee. We've got all the entities that I click on the objects and the Explorer, and notice that they're highlighted on the diagram, which is useful to find things. Okay.

Data Relationships

So next job is to populate our model where the relationships between the institutes. So as you'd expect and data architects, we can create the usual types of relationships. So super Serb identifying non identifying and nonspecific. So our first job we're going to create a super set relationship between our employee and a full-time employee and our contractor. So we can slacks from the, the toolbar, the cluster, and we can draw from the employee to the full-time employee for our contracts. So these two are part of an exclusive relationship. So an employee can either be full-time or contract. So we can then draw from the discriminator down to the contractor. We can edit the super sub cluster and set prophecies of the super sub relationship. So membership type it's exclusive, one of the other and the discriminator we can use as the employee type. So the employee type different defiant defines which of the sub entities it is. And then we can reposition online. Now we can create a relationship from address to employee. So we'll select non identifying mandatory relationship or draw from address to employee. Now we can specify some properties of bounce, the relationship. So let's set the verb phrases. So an address, locates employees and an employee is located at an address. And this, as we type the forward and reverse phrases, then we get a nice sentence that puts the two entities into context, which is really helpful. We'll create another one from departments to employee here. The phrase is Devon contains employees and an employee is part of the department. And nature says we've been creating these relationships. Then the foreign key properties have been propagating to our employee entity.

Foreign Keys

If we create another relationship and this one will create an optional relationship from department to employee the tool pop up until there's, we've got a duplicate relationship here. It's going to be populate the same foreign key. We'll be using the ID attribute as the foreign key. So here, we're going to specify a role name, which allows us to segregate. So the two foreign keys. So at this one, the relationship's going to be manager. And if we move the lines apart, we can then edit and set the verb phrase managed by employee managers department. And we can also set a role name for our first relationship and we'll call this logical role name member. And now our two foreign keys for our department or member and manager. So the tool has really helped us out here or create another one from departments to employee. Hear the phrase is Davon contains employees. And an employee is part of the department and niches. As we've been creating these relationships, then the foreign key properties have been propagating to our employee entity. If we create another relationship and this one will make a optional relationship from an employee through a department, great, an optional relationship from department to employee the tool, pop up, tell us we've got a duplicate relationship here. It's going to be populates the same foreign key. We'll be using the ID attribute as the foreign key. So here, we're going to specify a role name, which allows us to segregate. So the two foreign keys. So at this one, the relationship's going to be manager. And then if we move the lines apart, we can then edit and set the verb phrase managed by employee managers department. And we can also set a role name for our first relationship and we'll call this girl name member. And now our two foreign keys for our department, our member and manager to the tools really helps us out here. You can narrow it in some identifying relationships. So full-time employee identifies the training history and training course also identify as a training history. And again, notice the primary keys are populated, lost relationship. We'll add in a nonspecific relationship between training course and skills. So training courses can deliver skills and skills can be delivered by training courses. And we'll see that resolved later on. Again, we can edit the forward and reverse phrases.

Conclusion

So to recap, we started off creating a conceptual data model, pulling out some business data objects. We then populated the entities within those data objects. We then added properties, both key and non-key and then we built relationships between them for more information, go to idera.com forward slash contact sales.