complexity/details of ERD

What is the relationship between the complexity/details of ERD and the level of normalization?

thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
k_murli_krishnaCommented:
complexity/details of ERD:

1) How complex the design is
2) What modeling tool you use:
a) Simple: ERD is simple but few features and difficult to understand
b) Advanced: ERD is complex but can be easy/difficult to understand
3) If one does not use modeling tool well can lead to complexity
4) Details of ERD means table structure, constraints, domains, relationships, views

level of normalization:

1) More level of normalization, more tables, more joins, less SELECT performance, more relationships, less redundancy, more consistency
2) Less level of normalization: Exactly the opposite

So, both are independent in one sense but dependent since normalization level decides the ERD.

Less normalization leads to within table complexity & more leads across table complexity. Cheers.
0
 
anushahannaAuthor Commented:
Thanks k_murli_krishna for the detailed points.

*Just by looking at an ERD, will you be able to tell if it is a OLTP or OLAP db?
*Is Visio a simple or advanced tool compared to ERWIN?
0
 
schwertnerCommented:
The deep you normalize the ERD the simplier logically it get.
This is so, because the normalization process in most cases
divides one table in more simplier tables and definesthe way(s)
they are connected.

From the other hand the execution time of the queries increases because
the tables involved in the SELECTs and DMAs has to be joined.

In most cases OLAP (datawarehouses) tries to use not deep normalized ERD to allow fast processing (in the same time the DMLs get slower).
OLTP uses higly normalized ERD because they often insert and update rows and so they need to work as fast as possible.

Erwin should be more sophisticated tool for ERD as Visio, but this my statement should be checked.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
k_murli_krishnaCommented:
*Just by looking at an ERD, will you be able to tell if it is a OLTP or OLAP db?

Yes, in OLTP, you will have master tables to top left and transaction tables to bottom right and thumb rule is "all crows fly south-east" i.e. in crows feet notation, transaction tables records are derived from master table ones. Within transaction tables also tables will be split to follow thumb rule of single theme tables.

In OLAP, since it is mostly read only for analysis, reports and decision making using data mining, only SELECT is fired apart from when migration from OLTP to OLAP takes place to create the data warehouse. Hence, OLAP is very less normalized since the question of redundancy and ensuing inconsistency is not there. One will have central transaction fact table referencing all around master dimension tables it is a star schema.

*Is Visio a simple or advanced tool compared to ERWIN?

I have used both. ERWin is having more features and simple to use if knows the concepts well. Visio has less rich RDBMS data modeling features but appears to be more intricate. Advantage is in ERWin, conceptual/logical/physical models are clearly demarcated. But in Visio, you can have accompanying flow charts, sort of UML diagram and so many others.

ERWin is quite close to ER-Studio but simpler. But all 3 of them are less complex than Rational DB Designer or Silver Run.
0
 
anushahannaAuthor Commented:
k_murli_krishna, is "crows feet notation" apply to OLAP also or only to OLTP?
0
 
k_murli_krishnaCommented:
It is applicable to any design i.e. OLTP, OLAP etc. as long as you choose to represent relations with foreign keys. It is one of the many notations in logical model. The feet represents many.  You have lot more notations in conceptual model diagram than logical/physical.
0
 
anushahannaAuthor Commented:
k_murli_krishna, can you pl take a look at the attached ERD. You don't see any notation/feet in it, do you? I made this from visio. Is it OK design for OLAP model?


erd.jpg
0
 
k_murli_krishnaCommented:
I repeat:
In OLAP, since it is mostly read only for analysis, reports and decision making using data mining, only SELECT is fired apart from when migration from OLTP to OLAP takes place to create the data warehouse. Hence, OLAP is very less normalized since the question of redundancy and ensuing inconsistency is not there. One will have central transaction fact table referencing all around master dimension tables if it is a star schema.

Whether you use start schema or not in OLAP and in OLTP master tables to top left and transaction to bottom right OR not depends on situation to situation. But these are more often used than most for most situations.

Maybe MS-Visio does not support crows feet notation or even if it does you have not used it. It does not matter which notation you use or what layout you follow as long as the design is readable, maintainable, functionally suitable, performance oriented, adequate consistency or single theme or normalized enough tables without INSERT/UPDATE/DELETE anomalies, flexible, reusable and extensible. This applies to all 3 i.e. conceptual/logical/physical design/modeling. Cheers.
0
 
anushahannaAuthor Commented:
Thanks for your detailed and helpful post.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.