FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Publisher reserves the right to serve qualified requesters only.

What is the difference between ER modeling and OLTP modeling? Is the result of ER modeling and OLTP model the same?

Q: What is the difference between ER modeling and OLTP modeling? Is the result of ER modeling and OLTP model the same?

Chuck Kelley's Answer: If you take ER modeling through its full modeling processes, then you might have an OLTP model. ER is about defining Entities and Attributes and the relationships between them. Most of the time, this is the logical design. Then you use that logical model to create a physical model. It may or may not look like the logical design. Since both are ER models; the output of the physical model would be the SQL of the database; the model is to be used as an OLTP application, then the modeling would be and OLTP model.

Not all ER modeling are used in OLTP systems. They could be used in the data warehouse, the staging area and/or a ROLAP data mart.

Larissa Moss' Answer: While many database modelers believe that the terms entity relationship (ER) modeling and online transaction processing (OLTP) modeling are the same, they are not. Let's look at ER modeling first. Contrary to common belief, entity relationship modeling was not invented by Dr. Codd as a new DBMS schema, but by Dr. Peter Chen as a business data modeling technique. Thus, an ER model is a business model, not a database model. Business models are by definition fully normalized models because they accurately reflect the uniqueness of the real world. Thus, all redundancy is eliminated from the ER models (i.e., fully normalized). In addition, entities, attributes, and data relationships are defined in fully spelled out business terms; attribute names have prime, qualifier, and class word components; and so on. Now to OLTP models. Based on the same ER modeling principles, Dr. Codd formalized the six normalization rules (1NF, 2NF, 3NF, BCNF, 4NF, and 5NF) and, based on relational algebra and set theory, invented the first relational DBMS System-R, which was marketed at DB2. By definition, an OLTP model is a database model. It describes the tables, columns, and keys of a database that stores operational data. Almost all database models are denormalized to some extent to increase performance. Denormalization introduces redundancy, which violates the normalization rules. Many times, denormalization involves collapsing two entities that have a 1 to Many relationship on the ER model into one physical table on the OLTP model, which violates first normal form (1NF). In addition, tables, columns, primary and foreign keys, indices, etc. are defined in abbreviated technical terms. While ER models are products of data analysis activities, OLTP models are the products of database design activities.


Larissa Moss is founder and president of Method Focus Inc., a company specializing in improving the quality of business information systems. She has more than 20 years of IT experience with information asset management. Moss is coauthor of three books: Data Warehouse Project Management (Addison-Wesley, 2000), Impossible Data Warehouse Situations (Addison-Wesley, 2002) and Business Intelligence Roadmap: The Complete Project Lifecycle for Decision- Support Applications (Addison-Wesley, 2003). Moss can be reached at methodfocus@earthlink.net.

Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.

For more information on related topics, visit the following channels:



Industry Vendors