De-normalization in Advance Database Management Lecture 1

Choose a study mode

Play Quiz

Study Flashcards

Spaced Repetition

Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is denormalization?

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

Why is denormalization used in DSS (Decision Support Systems)?

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.

How does denormalization improve performance?

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

What is the main difference between normalization and denormalization?

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

What is the impact of denormalization on the number of tables in a database?

Denormalization reduces the number of tables in a database. Signup and view all the answers

In what type of database is denormalization particularly beneficial?

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

What are the four guidelines for denormalization?

<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

What are the areas for applying de-normalization techniques?

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. Signup and view all the answers

What are the five principal de-normalization techniques?

<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

What are the advantages of horizontal splitting of tables?

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. Signup and view all the answers

What is the purpose of pre-joining in de-normalization?

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. Signup and view all the answers