Lesson Overview
In the lesson about the Domain Model you learned how to create Entities with Attributes and Relationships. This is an example of the Domain Model for a "CRM" Web application. It's a pretty simple model in which we have basic information about a company and its employees. Let's suppose that you want to add some more information about both the "Company" and the "Employee" entity. For example, let’s suppose you want to add an Attribute containing how many employees each company has; another Attribute containing the employee full name and an attribute containing the name of the company in which the employee works. In addition, you might also want to group a subset of companies that are in the same country.
E-R Derivation
All this additional information can be inferred and thus created using the elements contained in the Domain Model. This is the concept of derivation. You can create derived information on Entities, Attributes or Relationships. In order to be able to express the way in which the derived information is built, you have to use the "Derivation Wizard". The wizard is available by pressing the "Edit" button next to the "Derivation" property in the Properties View of the desired element. The steps of the wizard differ depending on the kind of information you want to add. Let’s see some examples of derived information for a "CRM" Web application.
Derived Attribute
A Derived Attribute is an abstract concept that represents a specific "Entity" property. In this case, the value of the property can be inferred from other Attributes (either of the same Entity or other Entities) or from Relationships. Not all the Derived Attributes must be mapped on the physical database; the imported ones are retrieved at runtime while the others are mapped on a Database View. Derived attributes are shown differently from normal attributes: the name is written in italics and they have a colored flag that appears next to the attribute icon. If the flag is blue the attribute must not be synchronized, otherwise the flag is yellow.
Simple Imported Attribute
Let’s suppose you want to add an attribute to the "Employee" entity containing the name of the company for which the employee works. This information is already known since the company for which the employee works is the company instance connected to the specific employee instance through the related Relationship. What you want to do is to use this information as a quick reference in the "Employee" entity. Since the Attribute is a reference to another Attribute, you must specify the "master" attribute you want to reference. For the same reason, it's not necessary to synchronize the new Attribute. At runtime the Web application is able to retrieve the Attribute value. Every time you need to create an Attribute similar to this example, you must choose the "Simple Imported Attribute" option from the Derivation Wizard. Let's see how to create this Attribute in practice.
How to Add a Simple Imported Attribute
Right-click on the "Employee" entity and choose "Add" and then "Attribute" option to add a new Attribute to the Entity. Then write a name for the Attribute, such as "Company". Select the attribute and move to the Properties View. Press the "Edit" button next to the "Derivation" property to open the Derivation Wizard. Select the "Simple Imported Attribute" option and press the "Next" button. Now you have to specify the way in which the Attribute value is calculated. In this case you have to set the path that allows reaching the desired Attribute. Expand the "Employee" entity node and select the "Company" entity. Then select the desired Attribute, which in this case is the company name. Press "Finish" to confirm. As you can see, the attribute is shown in italics and has a blue flag icon. This representation reminds you that it is not necessary to synchronize this information.
Complex Imported Attribute
In some cases you may want to refer to specific information only when a condition is verified. Let’s suppose we have a section of the Domain Model like this. You have the "Employee" entity, which is connected to the "Email" Entity, with the meaning that an employee can have different email addresses. Only one email is the "primary" one, which means that is the employee’s preferred address for receiving communications. Let’s suppose that you want to extract the primary email and to have an Attribute on the "Employee" entity containing this information. This case is different from the previous one since there’s a condition to apply in order to choose the "primary email". So, when opening the "Derivation Wizard" we are going to choose the "Complex Imported Attribute" option. Since the "Complex Imported Attribute" option with respect to the "Simple Imported" includes an additional conditional derivation query, this means that the Attribute needs the synchronization process in order to be fully defined, and it becomes a database view. Let’s see how to create this attribute in practice.
How to Add a Complex Imported Attribute
Right-click on the "Employee" Entity and choose "Add" and then "Attribute" option to add a new Attribute to the Entity. Then write a name for the attribute, such as "Primary Email". Select the attribute and move to the Properties View. Press the "Edit" button next to the "Derivation" property to open the Derivation Wizard. Select the "Complex Imported Attribute" option and press the "Next" button. Now you have to specify the way in which the attribute value is calculated. As you did in the previous example, you have to set the path that allows reaching the desired Attribute. Expand the "Employee" entity node and select the "Email" entity. Then select the desired Attribute, which in this case is the email, and press the "Next" button. At this time you have to build the condition that must be verified by the email instance in order to be considered as primary and thus used as the value for the derived attribute. The expression you have to build is "primary equals to true". The dialog you see is composed of three different sections. On the left you can select the first term of the expression, which in this case is the "primary" attribute of the "Email" entity. In the middle you can select the operator used for the condition, in this case "equal". Finally, on the right you can select the second term of the expression, which can also be a constant value. In this example you need a constant value, which is true. Let’s write "true" in the "Constant" field. Press "OK" and then "Finish" to complete the procedure. As you can see, the attribute is shown in italics and has a green triangle icon with a question mark on it. This representation reminds you that you have to synchronize this information.
Calculated Attributes
It may be that the information you want to add to the Domain Model is obtained through a mathematical expression. An example for a "CRM" Web application is to have the number of employees working for a specific company. In this case, when opening the "Derivation Wizard" you are going to choose the "Calculated Attribute" option. This also means that the attribute needs the synchronization process in order to be fully defined, and it becomes a database view. Let’s see how to create this attribute in practice.
How to Add a Calculated Attribute
Right-click on the "Company" entity and choose "Add" and then "Attribute" option to add a new Attribute to the Entity. Then write a name for the Attribute. Select the Attribute and move to the Properties View. Press the "Edit" button next to the "Derivation" property to open the Derivation Wizard. Select the "Calculated Attribute" option and press the "Next" button. Now you have to specify the way in which the attribute value is calculated. To obtain the number of employees working for a specific company you have to count the "Employee" entity instances connected to the specific company instance. So, in this case you have to select the path that allows you to reach the "Employee" entity through the desired relationship and then to select the "Count" option in the "Aggregate" field. Press "OK" and then "Finish" to complete the procedure. As you can see, the attribute is shown in italics and has a green triangle icon with a question mark on it. This representation reminds you that you have to synchronize this information.
Other ways to calculate attributes
There are two other ways in which you can build the mathematical expression. The first one is to build a mathematical expression for obtaining information, such as the "Partial Amount" and the "Total Amount" of a purchase order. The second one is to use the calculation to combine string Attributes to provide new information. An example is the employee "Full Name" obtained with the concatenation of the employee "First Name" and "Last Name".
Mathematical Expressions
Let’s suppose that we want to obtain the total amount for each purchase made. Right-click on the "Purchase" entity and choose "Add" and then "Attribute" option to add a new Attribute to the Entity. Then write a name for the attribute, like "Total Amount". Select the attribute and move to the Properties View. Press the "Edit" button next to the "Derivation" property to open the Derivation Wizard. Select the "Calculated Attribute" option and press the "Next" button. Now you have to specify the way in which the attribute value is calculated. To obtain the total amount of a purchase you have to sum the partial amounts of purchase items that are connected to the specific purchase. So, in this case you have to select the path that allows you to reach the "Purchase Item" entity through the desired relationship and then to select the "Sum" option in the "Aggregate" field. Press "OK" and then "Finish" to complete the procedure. As you can see, the attribute is shown in italics and has a green triangle icon with a question mark on it. This representation reminds you that you have to synchronize this information.
Combining Strings
Let’s suppose you want to create the "Full Name" attribute for an Employee. Right-click on the "Employee" entity and choose "Add" and then "Attribute" option to add a new Attribute to the Entity. Then write "Full Name" as the name for the Attribute. Select the Attribute and move to the Properties View. Press the "Edit" button next to the "Derivation" property to open the Derivation Wizard. Select the "Calculated Attribute" option and press the "Next" button. Now you have to specify the way in which the attribute value is calculated. To obtain the full name of an employee you have to concatenate the name, a whitespace, and the surname. So, in this case you have to directly select the Attributes from the Entity itself. Select the "Name" attribute and press "OK" to add it to your expression. Use the button "Add a new Term after the current selection" to open once again the "Edit expression term" dialog. Add the whitespace by typing a space surrounded by quotes in the "Constant" field. Then press "OK" to add it. Finally, use the button "Add a new Term after the current selection" to add the "Surname" attribute. Choose it and press "OK" to add it. Change the operators between strings from "+" to "||" in order to concatenate strings. Press the "Finish" button to confirm the expression. As you can see, the attribute is shown in italics and has a green triangle icon with a question mark on it. This representation reminds you that you have to synchronize this information.
Derived Entity
Let’s suppose you want to group all the companies that spent a lot of money in buying your products. In this case what you are doing is defining a new Entity in the Domain Model whose instances are company instances satisfying a specific condition. The prerequisite for defining a Derived Entity is that the new Derived Entity must be connected to another entity through a generalization relation (IS-A), which establishes inheritance between the two. Only in that case can you create the derivation selecting the "Derived Entity" option from the "Derivation Wizard". Let’s see how to create this entity in practice.
How to Add a Derived Entity
Let’s add the "Best Customer" derived entity. It groups only the instances of the "Company" entity that spent at least $1 million in purchases. Drag the Entity icon from the toolbar to the work area. Type the name as "Best Customer". The "Best Customers" are a subset of all the customers (companies). You can model this by setting up a IS-A hierarchy with "Company". There is no reason the keep the "oid" attribute. Remove it by selecting the attribute with a mouse left-click and pressing the delete button. Right-click on the "Best Customer" entity and choose the "Add" and then "Generalization" command, then click on the "Company". The derivation property for "Best Customer" is now active. Press on the "Edit" button to open the Derivation Wizard. Choose "Derived Entity" from the dialog and press "Next". The "Edit activation expression" dialog offers two areas to choose the attributes and the rules to derive the company instances. From the area of the first operand, browse the available attributes to find the "Total Expenses" attribute of the Super Entity, select it and choose the ">=" sign among the comparators. Type in the left side of the dialog, inside the "Constant" input the value 1 million. Press "OK" to confirm your choice and press "Finish" to confirm. The Entity now is derived and its name is written in italics, with the green triangle icon to remind you that it needs to be mapped to the database.
Derived Relationships
A Derived Relationship describes one between entities that can be defined starting from an existing Relationship, typically by specifying conditions upon the involved Entities. A Derived Relationship is shown with a dashed line inside the Domain Model. From the Outline View, you can find the triangular flag above the Relationship icon. There are three different ways to Derive Relationships: by restriction, by concatenation, or as new Relationships.
Relationship by Restriction
A Relationship Derived by restriction is defined by specifying some conditions on an existing Relationship. The instances of the Relationship are a subset of the master Relationship. An example of Relationship by restriction is using the one-to-many Relationship of the "CRM" web project between the Employee and its phone numbers to extract the primary phone number. The idea is to have a shortcut to the primary phone number of the employee.
How to Add a Relationship by Restriction
Starting from the Basic Domain Model, it’s possible to add the restriction by placing another Relationship between the "Employee" and the "Phone Number" entity. You can add the new connection by selecting the "Relationship" icon from the toolbar and clicking on the source and then on the target. Select this Relationship and change its name to "EmployeePrimaryPhone". Change the roles names accordingly. Then press the "Edit" button next to the derivation property and choose "Imported Relationship" from the "Derivation" dialog. Press on "Next", and from the new dialog choose the path to connect the desired Entities. In this case you have to browse from the "Employee" to the "Phone Number" Entity and then press on "Next" to build the expression. From the "Activation Expression" dialog, choose the "Primary" attribute of the Phone Number and select the operator "IS TRUE". Press "OK" to confirm your choice and press "Finish" to confirm, and the relationship is now derived. Remember to synchronize to map the new Relationship to the database.
Relationship by Concatenation
A Relationship Derived by concatenation is used to compose existing Relationships. An example of derived relationship by concatenation for the "CRM" web project may be the one connecting a Sales Manager with all the Employees of its companies.
How to Add a Relationship by Concatenation
A Relationship Derived by concatenation is used to compose existing relationships. An example of derived relationship by concatenation for the "CRM" web project may be the one connecting a sales manager with all the employees of its companies. Start adding a Relationship between the "Employee" and the "Sales Manager". Choose the "Relationship" icon from the Domain Model and click on the "Employee" entity and then on the "Sales Manager". Select the new Relationship and go on its Properties View, press the "Edit" button next to the "Derivation" property and from the dialog choose the option "Imported Relationship", then press "Next" to confirm. From the new dialog build the path by browsing the available relationships, starting from the "Sales Manager" Entity and reaching the "Employee" entity. Press "Finish" to confirm. Remember to synchronize the Domain Model to map the new Derived Relationship.