Respond to the following Although normalization results in a

Respond to the following: Although normalization results in a number of advantages for relational databases, such as eliminating data anomalies, it also poses some disadvantages. Identify both the advantages and disadvantages of normalization. Review the information on Normalization and identify the circumstances when normalization might not be preferred. What are the situations when one should de-normalize tables? What are the situations when one should pursue 3NF instead of BCNF? Please use real-world examples to support your arguments.

type with 150 words

Solution

Advantages:

Normalized databases fair very well under conditions where the applications are write-intensive and the write-load is more than the read-load. This is because of the following reasons:

Disadvantages: The main cause of concern with fully normalized tables is that normalized data means joins between tables. And this joining means that read operations have to suffer because indexing strategies do not go well with table joins.

Circumstances when normalization might not be preferred:

The thumb rule is to Denormalize when it\'s OLAP operations, normalize when OLTP.

Databases intended for online transaction processing (OLTP) are typically more normalized than databases intended for online analytical processing (OLAP). OLTP applications are characterized by a high volume of small transactions such as updating a sales record at a supermarket checkout counter. The expectation is that each transaction will leave the database in a consistent state. By contrast, databases intended for OLAP operations are primarily \"read mostly\" databases. OLAP applications tend to extract historical data that has accumulated over a long period of time. For such databases, redundant or \"denormalized\" data may facilitate business intelligence applications. Specifically, dimensional tables in a star schema often contain denormalized data. The denormalized or redundant data must be carefully controlled during extract, transform, load (ETL) processing, and users should not be permitted to see the data until it is in a consistent state. The normalized alternative to the star schema is the snowflake schema. In many cases, the need for denormalization has waned as computers and RDBMS software have become more powerful, but since data volumes have generally increased along with hardware and software performance, OLAP databases often still use denormalized schemas.

Denormalization is also used to improve performance on smaller computers as in computerized cash-registers and mobile devices, since these may use the data for look-up only (e.g. price lookups). Denormalization may also be used when no RDBMS exists for a platform (such as Palm), or no changes are to be made to the data and a swift response is crucial.

Situations in which it is advantageous to pick 3NF over BCNF is if we can normalize a schema to an equivalent 3NF schema but not to an equivalent BCNF schema without introducing inter-relational functional dependencies.