De-normalization in Advance Database Management Lecture 1

Denormalization is a strategy used to increase the performance of a database infrastructure by adding redundant data to a normalized database.

Denormalization is used in DSS to bring close, dispersed but related data items together and to improve query performance, which is significantly dependent on the physical data model.

Denormalization improves performance by reducing the number of tables and the reliance on joins, which in turn speeds up performance.

<p>The main difference is that normalization aims to reduce data redundancy and improve data integrity, while denormalization involves adding redundant data to improve performance.</p> Signup and view all the answers

<p>Denormalization reduces the number of tables in a database.</p> Signup and view all the answers

<p>Denormalization is particularly beneficial in Decision Support Systems (DSS).</p> Signup and view all the answers

<ol> <li>Carefully do a cost-benefit analysis (frequency of use, additional storage, join time). 2. Do a data requirement and storage analysis. 3. Weigh against the maintenance issue of the redundant data (triggers used). 4. When in doubt, don’t</li> </ol> Signup and view all the answers

<p>Dealing with the abundance of star schemas, fast access of time series data for analysis, fast aggregate (sum, average etc.) results and complicated calculations, multidimensional analysis (e.g. geography) in a complex hierarchy, dealing with few updates but many join queries.</p> Signup and view all the answers

<ol> <li>Collapsing Tables, 2. Splitting Tables (Horizontal/Vertical Splitting), 3. Pre-Joining, 4. Adding Redundant Columns (Reference Data)</li> </ol> Signup and view all the answers

<p>Enhance security of data, organizing tables differently for different queries, graceful degradation of database in case of table damage, fewer rows result in flatter B-trees and fast data retrieval.</p> Signup and view all the answers

<p>Identify frequent joins and append the tables together in the physical data model. Generally used for 1:M such as master-detail. Referential integrity is assumed to exist.</p> Signup and view all the answers