Management and Modeling of Oracle Data Modeler - Assignment Example

? Database assignment affiliation: Lecturer: CA Erwin Data Modeler versus Oracle SQL Data Modeler Introduction Data modeling involves documentation and formalization of pre-existing events and processes that are generally applicable during the overall development and design of an application. The concerned techniques used for data modeling and the accompanying tools allows for input, translation and conversion of various system designs (both complex and simple) into processes and data flow representations which are easily read and understood . This whole function has brought a breakthrough in the fields of re-engineering, modeling and construction. Considering a data model, it is a flowchart or diagram that brings out the specific relationships present within data values. Effective data management requires that relationships between data be accurately determined and represented. It is therefore a process that should not be ignored. Data models which are efficiently designed allow for identification of errors and misconceptions that might be present in a data range. Data modeling has been given different approaches due to its importance. According to Burbank, these approaches include the following: Physical data modeling This type of modeling makes use of the current facilities and specific constraints of a particular database management system. It provides database specific kind of representation of a data model. Conceptual data modeling This kind of data modeling makes use of concepts and their specific relationships applicable to databases. It explains the nature of a database using a series of assumptions and semantics. Logical data modeling This is a type of data modeling whereby specific data representations of a given company are explained using relationships and entities. Moreover, it is not dependent of any specific technology of data management. Enterprise data modeling In this modeling, graphical models are used to represent data in a company. This implies that outputs like XML Schemas (XSD) and Entity Relationship Diagrams (ERDs) are used to provide data model outputs. (Burbank, 2011) In an attempt to make data modeling more efficient and realistic, several data modeling software have been developed over the past few years. For instance, CA Erwin Data Modeler and Oracle Data Modeler are some of the modeling software that are now widely used all over the world. These two programs have made data modeling more efficient, faster and cheaper than using the conventional methods. This paper will provide deeper insight on both Erwin and Oracle data modeler tools. The two programs will also be compared and their key features and functionalities outlined. Overview of CA Erwin Data Modeler Erwin data modeler is a data modeling tool used to perform the same function by several companies and organizations. There are also several other companies that uses the same data modeler throughout the globe. This program was initially developed by a renowned software developer known as Logic Works. Platinum Technology later purchased it in 1998. In 1999, Computer Associates purchased Platinum and is the same company that directly markets it. As a modern data modeling software, Erwin support a wide variety of database design functions that encompasses the following: Data modeling Forward engineering (This involves the process of creating a specific physical database and database schema model while relying on the grounds of data model) Reverse engineering(Where data models are created in regards to a pre-existing model of data) These functions are applicable in Erwin for various relational database hierarchical structures that cover a wide variety of used databases (Burbank, 2011). Erwin can be used to perform data modeling for different types of databases like information systems, database transactional systems, data marts and several others. Erwin support various displays of diagrams drawn or structured from different perspectives. For instance, it allows for viewing and manipulation of information engineering diagrams accompanied with notations. Other than this, it is important to note that Erwin’s modeling engine is specifically based on an IDEF1X modeling method (Burbank, 2011). This software (Erwin Data Modeler) uses a collaborative and intuitive data modeling environment and graphical user interface that makes it a preferred data modeler for several companies. This approach to data modeling give allows for easy and more efficient creation and editing of data projections and models. Furthermore, Erwin data modeler, through its features, contributes to higher levels of efficiency. This is so since the program allows for a reuse of resources as well as acceptable standards. This is accompanied by increased quality of data as well as a significant reduction in the overall costs associated with data modeling. Moreover, Erwin also uses a very strategic view of various data asserts which includes the use of a unified view. This software can be applied in different work environments that cover both technical and business areas of application. Therefore, stakeholders in both environments are able to use the software in viewing common information by the use of both or either desktop-based data modeling tools or web-based applications that are compatible with the repository of the class model (Burbank, 2011). Erwin data modeler presents major data attributes and definitions using a centralized view scheme. This view allows for better interpretation and analysis of large and corporate data and data models. It manages such data efficiently and at much reduced costs, making it widely used software in business environment as well as other job areas with limited budgets (Burbank, 2011). Due to these attributes, Erwin has gained popularity among many people and companies. Besides, its application in various environments has spread widely and this has contributed to the need to enhance its features. Overview of Oracle Data Modeler Oracle data modeler is a data modeling tools that provides a user-friendly modeling environment. It allows for easy and efficient capture of data, management and modeling of the same (metadata). Its approach to database generation and design is model-based and incorporates several data flow, relational and logical schemas. The software can be used to create, delete and edit various data structures. Oracle uses a combination of tables, diagrams and data classes, all which are based on a user friendly and intuitive interface (Stoyanov & Harper, 2009). The environment provided by the Oracle program is that which achieves user-friendliness through the application of several attributes. These include well-arranged menus with relevant entries and effective data modeling tools and features like the Engineering Status that makes it not only easier to use but also more accurate . Its functionality is drawn from its main Entity Relationship Diagram (ERD) which is used to draw all possible data models and implementations (Palinski, 2002). Consequently, Oracle provides graphical representations of the used entities, virtual tables, inheritances including links available between classes (Stoyanov & Harper, 2009). You are also able to describe specific parts of a model using the subviews option. Likewise, the relations between all included entities and classes are clearly viewed using logical functions that include logical subviews and models. This functionality allows for more efficient analysis of relationships and dependencies available among tables. The software further allows for writing of various relevant scripts concerning the generated tables. This is achieved using the Oracle SQL Developer. Instead of including shutters for instance, Oracle allows for the addition of new functions and triggers among others. These are some of the features that ensure that data modeling efficiency and accuracy is maintained. Unlike other data modeling tools, Oracle uses an existing logical model to create foreign keys and tables (Harper, 2009). This allows users to incorporate all the available data during the writing of new scripts. For this reason, it is very important in developing relational, logical and physical models. These features thus make it very effective and steady. Details of Evaluation Criteria Both Erwin and Oracle data modeling software have diverse features and functionalities that make them relevant to data modeling. The efficiency of the software can only be determined in regard to how efficient and accurate the software performs its functions. The extent of accuracy and completeness to which the software achieve data modeling is important to their efficiency. Moreover, the overall costs and timeliness in data modeling using the software also contribute to the success of their operation. In regard to these, the modeling tools are evaluated using various approaches. This involves the evaluation using the overall features of the two tools and their application to data modeling. In order to achieve a deeper evaluation, the features of both the tools are compared based on their similarities and differences. Multiple Entity Relationships Diagrams are drawn using both tools concurrently using physical mode. The output ERDs are compared and their level of efficiency in data modeling also evaluated using the same diagrams. Different outputs generated using the tools portray different views and have both different and similar methods of solving modeling tasks. The processes involved in producing the ERDs are also evaluated through comparison in regard to their ease of application, accuracy, and time consumption. In order to evaluate the accuracy of the tools (Erwin and Oracle), errors encountered during the modeling process are identified and which program, between the two, that best minimize the errors determined. Limitations encountered during the use of the tools are also noted as a way of evaluating the functionality of the tools. The evaluation criteria also involves evaluating the interfaces of the tools being intuitive, databases supported, flexibility including their documentation and support. Justification of the Evaluation Criteria The relevance and efficiency of data modeling tools depends on the level of accuracy, ease and cost effectiveness that comes along its application. Moreover, this implies that the user documentation, interface, and modeling features that are incorporated in a modeling tool have great impact on their evaluation. Putting these into consideration, it is important to evaluate how best a modeling tool solves modeling tasks and the level of accuracy and completeness involved in the process. Therefore, this evaluation criterion provides explanation to these by examining the functionality of the tools. In order to better explain them, the criteria involve actual application of the tools in drawing multiple Entity Relationship Diagrams thus, providing a very accurate and relevant method of evaluating the modeling tools. Evaluating errors involved in the application of the tools provides an efficient way of determining the software’s application in modeling environments. It also provides a clear outline on the operation of the tools and their supported databases. The evaluation process has been applied to come up with the following varied features. From my analysis, it is clear that Erwin allows for exports of bulk data to metadata and excel. This is achieved using cross-object which provides an interface that resembles an excel spreadsheet. Such an interface and bulk data support makes it a preferred data modeling tool. On the other hand, Oracle is more driven towards exports to data modeler design with limited support to bulk data (Burbank, 2011). The tool (Erwin) provides higher data and objects reuse. This is so since it allows for application and reuse of generation templates, mappings of data types and other transformation schemes. This is contrary to Oracle’s functionalities which limits objects’ reuse. In order to promote compatibility and usability, Erwin data modeler has been engineered to support various other data modeling tools like Oracle. This implies that with Erwin, it is possible to incorporate databases from other database management systems. However, this is not the case with Oracle. Oracle does not fully allow integration with other modeling tools like Erwin. It limits its functionality to Oracle-supported versions of modeling tools and databases. Results from the Application of the Evaluation Criteria CA Erwin modeler This tool tables an intuitive graphical interface used to manage and organize enterprise data based on a collaborative environment. This functionality aids in the enhancement of the re-use of data, reduces modeling time, improves system quality and allows for essential information protection. It supports standard commercial protocols, commercial security regulations and policies. These features allow it to be used by teams from different environments like I.T and business (Burbank, 2011). Different users can, therefore, work collaboratively to enforce modeling standards and provide efficient data modeling and asset management. The program even allows users with little technical knowledge to contribute towards data updating of the corporate metadata. The program comes along with a full user documentation and online support that novice users can access to ease their modeling process and improve the accuracy of the output. Oracle SQL Modeler Oracle SQL Developer provides a quick database modeling and viewing platform with an intuitive graphical user interface. This interface allows for display of complex data structures, editing data models and making modeling projections that help corporate companies realize their organizational plan. The accompanying documentation, supported features and data types incorporates a wide range of accessible features that makes it relevant to various application environments. Here, developers are presented with diagrammatic view of schema that also manages the generated models. However, it does not possess a tight link between the physical and logical models. This implies that a change made of the logical model is not automatically updated in the physical model. It is thus difficult to update any change made on the physical change. A situation where any change made in the logical model is automatically updated in the physical model is preferred (Harper, 2009). The instantaneous synchronization of physical and logical model assist the user to ensure that the message they communicate is consistent and clear for both development team and the business team. However, the procedure for logical sub-model to physical part in oracle is simple. Comparison between Erwin Data Modeler and Oracle Data Modeler Erwin Data Modeler provides better and more advanced data modeling features than Oracle data modeler. Even though the later also encompasses several good features, the modeling features of Erwin Data Modeler, by far, outwits those of Oracle. Oracle Data Modeler supports a wider variety of graphical models including text-based models’ selections. It includes models like logical, relational, data types, multidimensional and data flow, all in the same tool. This is in favor of Oracle over Erwin (Miller, 2010). Erwin Data Modeler, contrary to Oracle, allows for generation of database encryption keys, sparse columns, workload groups and filtered indexes. This makes it easier to achieve effective data organization and reorganization in cases of data importation from external databases (Burbank, 2011). Contrary to Oracle data modeler, Erwin Data Modeler contains Domains which allow users to reuse and define data types based on an optional allowable values and constraints. These are applicable to both logical and relational models. Moreover, Erwin Data Modeler enables its users to model and document details of business supporting design. Erwin allows for generation of reports using several format including HTML, RTF, TXT and PDF. Furthermore, it contains ODBC interface that integrates with external reporting tools. Additionally, it has an SAP Business-Objects Crystal Reports. This provides more efficient output than that of Oracle which is limited to dynamic reports with HTML, PDF and RTF formats (Burbank, 2011). Examples to Illustrate Specific Differences Several specific differences exist between Oracle and Erwin’s functionality and features. For instance; Erwin data modeler is designed to support a target group of both Small and Midsize Businesses and Enterprises. On the other hand, Oracle supports Enterprises only (Miller, 2010). Another example to illustrate specific difference between the two tools is the fact that Erwin incorporates conceptual, logical and physical data models which is not the case with Oracle data modeler. Oracle supports only logical and physical data models which are why Erwin is preferred over it for diverse data models. Concurrently, Erwin supports a large number of databases than Oracle. This makes increases its compatibility over Oracle. An example of this is the supported database management systems of these modeling tools. Erwin supports the following examples of database management systems: Access, Informix, DB2 LUW, Oracle, MySQL, Progress, Teradata, ODBC, SQL Server and Sybase. Oracle, on the other hand, supports only Oracle, ODBC, MS SQL Server and DB2 LUW. ERD Drawn using Erwin Data Modeler 1. Example: Purchasing train ticket using Machine. 2. Example: Withdraw transaction from ATM. ERD Drawn using Oracle Data Modeler 1. Example: Purchasing train ticket using Machine. 2. Example: Withdraw transaction from ATM. Discussion of Evaluation of Recommendations Erwin Data Modeler provides efficient features for both novice and technical users. The user interface is intuitive and has incredible ways of enabling the user to accomplish the modeling task. There are many other features that the Erwin developers integrated that make it extraordinary. For instance, the process is very much straightforward. The users are given the option to create one DDL script or use the oracle wizard that can generate DDL file for each and every object. Though Oracle has the same feature, one has to undergo a lengthy and tedious process to generate DDL (Burbank, 2011). Erwin Data Modeler has more data modeling features than Oracle and therefore provides a more efficient platform for modeling. The fact that it allows derivation and views from several sources as well as their integrated output also puts it well ahead of Oracle. Moreover, Erwin now incorporates several Oracle features and compatibility in a bid to offer efficient data planning and modeling. I therefore recommend Erwin Data Modeler over Oracle. This is attributed to the modeling features found in the program, which highly encourage data security, efficiency, accuracy, cost effectiveness and better planning. In corporate environments, I would also like to recommend the use of Erwin data modeler since unlike Oracle which is cross-platform based; it is supported by all versions of windows. This makes it more compatible and easy to integrate with other widely used systems. Erwin also comes with support of both SMB (Small and Midsize Business) and enterprises. For this reason, I would like to recommend its use over Oracle which only supports enterprises. The summary of the evaluation is given in the table below. The grading is out of ten. Features CA Erwin Oracle Overall usability 10 8 Model layout and formatting 10 8 Creation of logical model 10 10 Creation of physical model 10 10 Forward-engineering 10 9 DDL Creation 8 9 Report generation 9 10 customization 10 8 Online community 10 7 documentation 10 8 Metadata and other tools 9 7 (Boyd, 2011) References Burbank, D. and Hoberman, S. (2011). Data Modeling Made Simple With CA ERwin Data Modeler R8. Technics Publications Llc. Palinski, J. (2002). Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts. Addison-Wesley Professional. Harper, S. (2009). Introducing SQL Developer Data Modeler: Part 1. Available at: http://www.packtpub.com/article/introducing-sql-developer-data-modeler-part1 , (Date Accessed: 2nd of December 2013). Rosenblum, M. (2011). Expert PL/SQL Practices: for Oracle Developers and DBAs. Apress. Stoyanov, P. and Harper, S. (2009). An Introduction to Oracle SQL Developer Data Modeler. Available at: http://www.oracle.com/technetwork/developer-tools/datamodeler/sqldeveloperdatamodeleroverview-167687.html , (Date Accessed: 30th of November 2013). CA technologies (2011), CA ERwin® Data Modeler. Available at: http://support.ca.com/cadocs/0/CA%20ERwin%20Data%20Modeler%20r8-ENU/Bookshelf_Files/PDF/ERwin_Impl.pdf , (Date Accessed: 4th of December 2013). Boyd, C. (2011), A Usability Comparison of the Top Three Data Modeling Tools Available at: http://www.information-management.com/media/pdfs/MySoftForge.pdf , (Date Accessed: 5th of December 2013). Read More