Section 4
Oracle SQL Developer Data Modeler
Use Oracle SQL Developer Data Modeler to create:
• Entities, attributes, and UIDs with correct optionality and cardinality
• Supertype and subtype entities
• Arc, hierarchical, barred, and recursive relationships
Oracle SQL Developer Data Modeler offers a range of data and database modeling capabilities, enabling
you to:
• Capture business rules and information
• Create process, logical, relational, and physical models
• Store metadata information in XML files
• Synchronize the relational model with the data dictionary
Building an ERD by Using Oracle SQL Developer Data Modeler
1
,CASE Scenario
Faculty: Sean, I would like you to create a simplified library database to manage the number of reference
books in our department. As a first step, can you build a logical model using Oracle SQL Developer Data
Modeler that we have installed in our student machines?
Student: Glad to. I’ll start by identifying the entities and their attributes. After that, I can use the
Oracle SQL Developer Data Modeler tool to build the logical model.
1. Identifying entities
2. Building an Entity Relationship Diagram
I. Navigate to the Logical tab
The first step in building an ERD in Oracle SQL
Developer Data Modeler is to click the Logical tab.
Notice that the toolbar changes to display tools
specifically for working with ERDs
If you do not see the Logical tab, then perform the
following steps:
a) Right-click the Logical Model in the
browser
b) Select Show
2
,II. Create an entity
To create an entity, perform the following steps:
a) On the toolbar, click the New Entity tool, and
then click anywhere in the white space of the
Logical pane. The Entity Properties window
appears
b) In the Entity Properties window, enter the
name of the entity. For the example in the
slide, the entity name is "BOOK". Do not close
the window after you have entered the entity
name
III. Add attributes to the entity
To add attributes to the entity, perform the following steps:
a) Select Attributes in the navigator of the Entity Properties window
b) Click the Add an Attribute icon
c) In the Name field, enter a name for the attribute
d) In the Data type field, select Logical, then required type. from the dropdown list
e) Enter the attribute size
f) If the attribute is mandatory, check the box
g) Click OK
Although Data types are not required in a logical model (they will not be shown on the ERD)
adding them now will allow Data Modeler to convert them to actual SQL Data types when we
engineer to the Physical (Relational) Model
Set Preferred data types
You can set commonly used Logical data types as preferred by performing the following steps:
a) Select Tools > Preferences > Data Modeler, and select the Model node
b) Select the types from the All Logical Types section and move them to the preferred area
by clicking the arrow
c) Click OK
To view only the preferred data types, select the Preferred check box. This will limit the options
displayed in the dropdown list for logical types
3
,IV. Set Primary and Secondary UIDs
To set the Primary UID for the entity, perform the following steps:
a) Select Attributes in the left navigator of the Entity Properties window
b) Select the attribute that you want to assign as the Primary UID
c) Select the Primary UID check box
The attribute that you assign as primary UID is automatically also set to mandatory
To set Secondary UIDs for the entity, perform the following steps:
a) Select Unique Identifiers in the left navigator of the Entity Properties window
b) Click the Add icon to add another UID
c) Click the Properties icon
d) Enter a name to identify the Secondary UID
e) Select Attributes and Relations from the left navigator of the Key Properties window
f) Select the attribute to set as a Secondary UID
g) Click the arrow icon to move the attribute to the right-hand pane
h) Click OK
V. Define the relationships between the entities
The relationships available in Oracle SQL Developer are:
• 1:1 (one-to-one)
• 1:N (one-to-many)
• 1:N Identifying Relationship (one-to-many barred relationship)
• M:N (many-to-many)
4
, VI. Set the source and target values for the relationship
CASE Scenario
Faculty: Sean, I was wondering if we could include new types of membership categories such as:
Student Membership, Faculty Membership, Corporate Membership
Student: This can definitely be achieved. I can create a common entity that would hold membership
details that are common to all the three membership categories. This would be a supertype entity.
The specific membership categories would inherit the properties of the supertype entity, in addition
to their own specific attributes. Hence, the specific membership category would be a subtype entity.
1. Creating the Supertype Entity
To define an entity as a subtype in Oracle SQL Developer Data Modeler you need to ensure that the
supertype exists. In this example, the super type is MEMBER. Perform the following steps to create the
FACULTY subtype:
a) Double-click the entity that you want to make a subtype. For the example in the slide, you want
to make FACULTY a subtype of the MEMBER supertype. Double-click FACULTY
b) Select the MEMBER supertype entity from the Super Type list, and click OK. The FACULTY entity is
now a subtype of the MEMBER supertype and will inherit all the attributes of the supertype
2. Creating the Arc Relationship
To create an exclusive relationship in Oracle SQL Developer Data Modeler, perform the following steps:
a) Hold the ctrl key and select the intersecting entity and both relationships on which you want to
create the Arc relationship
b) Click the New Arc icon in the toolbar. The exclusive relationship is created with the arc
5
,3. Creating the Barred Relationship
To add a barred relationship select Identifying Relationship from the toolbar, and click the source and
target entities to add the relationship between the entities
4. Creating the Hierarchical Relationship
• Hierarchical data can be represented as a set of 1:N relationships (or 1:N
Identifying)
• In this example, a University has a hierarchical structure:
o A FACULTY can consist of one or more DEPARTMENTS
o A DEPARTMENT can offer one or more COURSES
• The UIDs for a set of hierarchical entities can be propagated through
multiple relationships by making the relationships Identifying
5. Creating the Recursive Relationship
To add a Recursive Relationship, select the required relationship from the toolbar as
normal, then click on the entity to make it the source, and click on the same entity a
second time to make it the target
Convert a Logical Model to a Relational Model
Data Modeling Approaches
There are three approaches to data modeling:
• Top-down modeling is the approach taken for designing a
new database
• Bottom-up modeling is the approach taken for creating a
database based on extracting metadata from an existing
database or using the data definition language (DDL) code
obtained from an implementation of an existing database
• Target modeling is best suited for adapting a database to new requirements
Engineering a Data Model
Forward engineering is the process of transforming a logical data model to a relational model.
− In Oracle SQL Developer Data Modeler a physical model is represented by a Relational Model
− Reverse engineering is the process of creating a conceptual or logical model by extracting the
information from an existing data source
6
, Benefits of Creating a Relational Model
A relational model:
• Is closer to the implementation solution
• Facilitates discussion
• Forms the basis for the physical database design
• The ideal model can be adapted to a relational database management system (RDBMS) model
Terminology
CASE Scenario: Building a Relational Model
Faculty: Sean, is it possible to create a relational model from an existing logical model by using the Oracle
SQL Developer Data Modeler tool?
Student: Absolutely. Let me list the steps involved in forward engineering a logical model to a
relational model by using the Oracle SQL Developer Data Modeler tool.
1. Simplified Library Database ERD
Sean has put together the logical model (ERD) for a simplified Library database. Now he needs to engineer
this logical model to a relational model by using the Oracle SQL Developer Data Modeler tool.
7
Oracle SQL Developer Data Modeler
Use Oracle SQL Developer Data Modeler to create:
• Entities, attributes, and UIDs with correct optionality and cardinality
• Supertype and subtype entities
• Arc, hierarchical, barred, and recursive relationships
Oracle SQL Developer Data Modeler offers a range of data and database modeling capabilities, enabling
you to:
• Capture business rules and information
• Create process, logical, relational, and physical models
• Store metadata information in XML files
• Synchronize the relational model with the data dictionary
Building an ERD by Using Oracle SQL Developer Data Modeler
1
,CASE Scenario
Faculty: Sean, I would like you to create a simplified library database to manage the number of reference
books in our department. As a first step, can you build a logical model using Oracle SQL Developer Data
Modeler that we have installed in our student machines?
Student: Glad to. I’ll start by identifying the entities and their attributes. After that, I can use the
Oracle SQL Developer Data Modeler tool to build the logical model.
1. Identifying entities
2. Building an Entity Relationship Diagram
I. Navigate to the Logical tab
The first step in building an ERD in Oracle SQL
Developer Data Modeler is to click the Logical tab.
Notice that the toolbar changes to display tools
specifically for working with ERDs
If you do not see the Logical tab, then perform the
following steps:
a) Right-click the Logical Model in the
browser
b) Select Show
2
,II. Create an entity
To create an entity, perform the following steps:
a) On the toolbar, click the New Entity tool, and
then click anywhere in the white space of the
Logical pane. The Entity Properties window
appears
b) In the Entity Properties window, enter the
name of the entity. For the example in the
slide, the entity name is "BOOK". Do not close
the window after you have entered the entity
name
III. Add attributes to the entity
To add attributes to the entity, perform the following steps:
a) Select Attributes in the navigator of the Entity Properties window
b) Click the Add an Attribute icon
c) In the Name field, enter a name for the attribute
d) In the Data type field, select Logical, then required type. from the dropdown list
e) Enter the attribute size
f) If the attribute is mandatory, check the box
g) Click OK
Although Data types are not required in a logical model (they will not be shown on the ERD)
adding them now will allow Data Modeler to convert them to actual SQL Data types when we
engineer to the Physical (Relational) Model
Set Preferred data types
You can set commonly used Logical data types as preferred by performing the following steps:
a) Select Tools > Preferences > Data Modeler, and select the Model node
b) Select the types from the All Logical Types section and move them to the preferred area
by clicking the arrow
c) Click OK
To view only the preferred data types, select the Preferred check box. This will limit the options
displayed in the dropdown list for logical types
3
,IV. Set Primary and Secondary UIDs
To set the Primary UID for the entity, perform the following steps:
a) Select Attributes in the left navigator of the Entity Properties window
b) Select the attribute that you want to assign as the Primary UID
c) Select the Primary UID check box
The attribute that you assign as primary UID is automatically also set to mandatory
To set Secondary UIDs for the entity, perform the following steps:
a) Select Unique Identifiers in the left navigator of the Entity Properties window
b) Click the Add icon to add another UID
c) Click the Properties icon
d) Enter a name to identify the Secondary UID
e) Select Attributes and Relations from the left navigator of the Key Properties window
f) Select the attribute to set as a Secondary UID
g) Click the arrow icon to move the attribute to the right-hand pane
h) Click OK
V. Define the relationships between the entities
The relationships available in Oracle SQL Developer are:
• 1:1 (one-to-one)
• 1:N (one-to-many)
• 1:N Identifying Relationship (one-to-many barred relationship)
• M:N (many-to-many)
4
, VI. Set the source and target values for the relationship
CASE Scenario
Faculty: Sean, I was wondering if we could include new types of membership categories such as:
Student Membership, Faculty Membership, Corporate Membership
Student: This can definitely be achieved. I can create a common entity that would hold membership
details that are common to all the three membership categories. This would be a supertype entity.
The specific membership categories would inherit the properties of the supertype entity, in addition
to their own specific attributes. Hence, the specific membership category would be a subtype entity.
1. Creating the Supertype Entity
To define an entity as a subtype in Oracle SQL Developer Data Modeler you need to ensure that the
supertype exists. In this example, the super type is MEMBER. Perform the following steps to create the
FACULTY subtype:
a) Double-click the entity that you want to make a subtype. For the example in the slide, you want
to make FACULTY a subtype of the MEMBER supertype. Double-click FACULTY
b) Select the MEMBER supertype entity from the Super Type list, and click OK. The FACULTY entity is
now a subtype of the MEMBER supertype and will inherit all the attributes of the supertype
2. Creating the Arc Relationship
To create an exclusive relationship in Oracle SQL Developer Data Modeler, perform the following steps:
a) Hold the ctrl key and select the intersecting entity and both relationships on which you want to
create the Arc relationship
b) Click the New Arc icon in the toolbar. The exclusive relationship is created with the arc
5
,3. Creating the Barred Relationship
To add a barred relationship select Identifying Relationship from the toolbar, and click the source and
target entities to add the relationship between the entities
4. Creating the Hierarchical Relationship
• Hierarchical data can be represented as a set of 1:N relationships (or 1:N
Identifying)
• In this example, a University has a hierarchical structure:
o A FACULTY can consist of one or more DEPARTMENTS
o A DEPARTMENT can offer one or more COURSES
• The UIDs for a set of hierarchical entities can be propagated through
multiple relationships by making the relationships Identifying
5. Creating the Recursive Relationship
To add a Recursive Relationship, select the required relationship from the toolbar as
normal, then click on the entity to make it the source, and click on the same entity a
second time to make it the target
Convert a Logical Model to a Relational Model
Data Modeling Approaches
There are three approaches to data modeling:
• Top-down modeling is the approach taken for designing a
new database
• Bottom-up modeling is the approach taken for creating a
database based on extracting metadata from an existing
database or using the data definition language (DDL) code
obtained from an implementation of an existing database
• Target modeling is best suited for adapting a database to new requirements
Engineering a Data Model
Forward engineering is the process of transforming a logical data model to a relational model.
− In Oracle SQL Developer Data Modeler a physical model is represented by a Relational Model
− Reverse engineering is the process of creating a conceptual or logical model by extracting the
information from an existing data source
6
, Benefits of Creating a Relational Model
A relational model:
• Is closer to the implementation solution
• Facilitates discussion
• Forms the basis for the physical database design
• The ideal model can be adapted to a relational database management system (RDBMS) model
Terminology
CASE Scenario: Building a Relational Model
Faculty: Sean, is it possible to create a relational model from an existing logical model by using the Oracle
SQL Developer Data Modeler tool?
Student: Absolutely. Let me list the steps involved in forward engineering a logical model to a
relational model by using the Oracle SQL Developer Data Modeler tool.
1. Simplified Library Database ERD
Sean has put together the logical model (ERD) for a simplified Library database. Now he needs to engineer
this logical model to a relational model by using the Oracle SQL Developer Data Modeler tool.
7
