Data Modelling using SQL Developer Data Modeler

Overview:->

Data modeling is a process used to define and analyze data requirements needed to support the business processes within the scope of corresponding information systems in organizations. Therefore, the process of data modeling involves professional data modelers working closely with business stakeholders, as well as potential users of the information system.

Why is Data Modeling Important?

Data modeling is probably the most labor intensive and time-consuming part of the development process. Why bother especially if you are pressed for time? A common response by practitioners who write on the subject is that you should no more build a database without a model than you should build a house without blueprints.

The goal of the data model is to make sure that all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language, it can be reviewed and verified as correct by the end-users.

The data model is also detailed enough to be used by the database developers to use as a “blueprint” for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning, you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user’s requirements.

Introduction to SQL Developer Data Modeler :->

Oracle SQL Developer Data Modeler is a free graphical tool that enhances productivity and simplifies data modeling tasks. Using Oracle SQL Developer Data Modeler users can create, browse and edit, logical, relational, physical, multi-dimensional, and data type models. The Data Modeler provides forward and reverse engineering capabilities and supports collaborative development through integrated source code control. Following is the link to download this tool.

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

 

Data modeling using SQL Developer Data Modeler:->

The Data Modeler supports a number of graphical models and a selection of text-based models. During this discussion, we are going to cover following two most important data models.

  • Logical—this is the entity relationship model or Entity Relationship Diagram (ERD), and comprises entities, attributes, and relationships.
  • Relational—this is the schema or database model and is comprised of tables, columns, views, and constraints. In SQL Developer Data Modeler, these models are database independent and need to be associated with the physical model to support database specific DDL.

Oracle SQL Developer Data Modeler -Key Features:->

In this article, We will try to mention only those features which can be relevant to our work.

1) Logical Model supported features:->

  • ER diagramming.
  • Entity hierarchies with strategies for engineering to relational models
  • Box-in-box presentation for hierarchies (supertypes and subtypes)
  • Support for mutually exclusive relationships (arcs)
  • Engineering to and from relational models (create, compare and synchronize changes)
  • Compare and merge with logical model in another design

2) Relational Model supported features

  • Define sensitive and personally identifiable information
  • Mutually exclusive (arcs), mandatory (optional) and transferable foreign keys
  • Automatic propagation and synchronization of foreign key columns
  • Import DDL scripts or import from database dictionary
  • Compare and/ or merge to models in other designs, a status of database dictionary or status in DDL scripts.

3) Naming Standards supported features

  • Glossary for permitted prime, class, modifier and qualifier words
  • Name structure for elements in Logical and Relational models
  • Model level restrictions for – name length, possible characters, used letter case
  • Name translation during engineering between logical and relational models
  • Naming templates for table constraints and indexes
  • Prefix management

4) Design Rules supported features

Sets of predefined design rules can be used to validate the correctness of information in all models.

  • Create user-defined Design Rules.
  • Group collections of design rules in Design Rule sets.
  • Build libraries of Design Rules.

 

Case Study:

As per my understanding, data modeling consists of following activities.

1) Decide logical names of entities and attributes.

2) Develop the logical model

3) Develop the relational model

4) decide abbreviations for words and apply them to the relational model.

5) Apply Design rules and correct warnings

6) Generate DDL

7) Save the design

 

1) Decide logical names of entities and attributes :-> once the requirement is base lined and entities are decided then we should decide the logical names of entities and attributes. All names should be in sentence letter.

table1

Let’s try to develop data models for above entities.

2) Develop the logical model :->

a) Create Entities:->

Create entities and attributes as follows.

  • In the main area (right side) of the SQL Developer Data Modeler window, click the Logical tab.
  • Click the New Entity icon.
  • Click in the logical model pane in the main area; and in the Logical pane press, diagonally drag, and release the mouse button to draw an entity box. The Entity Properties dialog box is displayed.
  • Click General on the left, and specify as follows:
  • Click Attributes on the left, and use the Add (+) icon to add the following attributes, one at a time.
  • (For data types, select data type as logical type and specify further)
  • Kindly mention comments while creating entities and attributes.

b) create a relationship between entities

  • Click the New 1: N Relation icon.
  • Click first in the Departments box, then in the Employees box. A line with an arrowhead is drawn from Departments to Employees.
  • Optionally, double-click a line (or right-click a line and select Properties) and view the Relation Properties information.

Logical

3) Develop the relational model :->

a) Generate relational model

  • Develop the relational model as follows:
  • With the logical model selected, click Design, then Engineer to Relational Model. The Engineering dialog box is displayed.
  • Accept all defaults (do not filter), and click Engineer. This causes the Relational_1 model to be populated with tables and other objects that reflect the logical model.
  • Optionally, expand the Relational Models node in the object browser on the left side of the window, and expand Relational_1 and nodes under it that contain any entries (such as Tables and Columns), to view the objects created.

Relational_1

b) create a schema and add relevant objects

  • In the left panel, expand relationship model then select a schema.
  • right click on schema then select the new schema.
  • give a proper name to the schema and assign all DB objects to the schema.

Relational_2

4) Decide abbreviations for words and apply them to relational model

  • Create a new CSV file, which should have details of actual word and corresponding abbreviation. in this scenario, It should have following entries.

table2

  • in the top panel, click on tools, then name abbreviations, then select the .csv file and click OK.
  • after applying above steps, relationship diagram should look like below.

Relational_3

This is a standard practice. A major benefit in my perception is, abbreviation will be same across the databases if the same sheet is used while data modeling.

5) Apply Design rules and correct warnings

  • in the top panel, click on tools, further click on “Design Rules And Transformation”, then click on Design rules.
  • After applying design rules we can verify whether standard practices are not followed or not. it is not mandatory to follow all practices.

6) Generate DDL

  • Click File, then Export, then DDL File.
  • Select the database type (for example, Oracle Database 11g) and click Generate. The DDL Generation Options dialog box is displayed.
  • Accept all defaults, and click OK. A DDL file editor is displayed, with SQL statements to create the tables and add constraints. (Although you can edit statements in this window, do not edit any statements for this tutorial exercise.)
  • Click Save to save the statements to a .sql script file (for example, create_library_objects.sql) on your local system.
  • Later, run the script (for example, using a database connection and SQL Worksheet in SQL Developer) to create the objects in the desired database.
  • Click Close to close the DDL file editor.

7) Save the design

Save the design by clicking File, then Save.

 

Posted in: