ERD directly producing 5NF relational schema – is this desirable

database-designerdnormalization

I am given a case study of Event Management to develop an entity-relationship diagram (ERD), and based on it check if the resulting relational schema meets the different normal forms. Now, I have developed my ERD based on the SRS that I have developed.

The question that I am having is: when I converted my ERD to the relational schema I got my tables already normalized. Because of the ERD I had nothing left to accomplish with respect to the normalization process, so I got confused.

Another question that arose in my mind is whether or not this is a desirable situation, I mean when I am getting my all the relations normalized from the ERD itself then why do I need to do normalization at all?

To put it differently, if we get the database already in 5NF from the ERD then why do we need to think of normalization?

Best Answer

If your tables are already in 5NF then you don't need to think much more about further normalization.

Note however that the principle of full normalization (POFN) is no panacea. There may remain non-obvious update anomalies.

Database constraints will need to be applied. In my experience, designers who start with an ER diagram are great at implementing primary keys and foreign keys but other constraints can be easily overlooked. Other design approaches may result in better constraints e.g. I have a little experience of object role modelling which seemed to work well.

Further normalization should only be considered in conjunction with constraints because 6NF often makes constraints more difficult to implement using today's industrial-strength technologies e.g. none support Standard SQL's CREATE ASSERTION nor CHECK constraints that may include a subquery.

Also consider the principle of orthogonal design (POOD).

Related Question