Question

** use your own words, don't copy and paste, don't use handwriting, please. i need your references URL Link please**

What is Denormalization? Explain this technique and discuss the following:
- The advantages and the disadvantage of using this technique in Database design.
- Why, alternatively in some situations, we might use Materialized View instead of Denormailization? Provide an example to support your arguments.

Answer 1

Q1.

Before denormalization, let's discuss normalization. The primary purpose of normalization is to reduce the redundancy and maintain consistent data in the database. A database consists of multiple tables, and each will be mapped with another by referral integrity. So if we need any related data, we would join the related tables and get the records. This will work fast if the database is small and contains a few records. But In the real world, the database will contain lots of records(Imagine millions of records). When we normalize the table we might not think about the growth of data. So when there will be a large number of records, the cost of the query will increase drastically, this is because it has to join multiple tables to get the records and these tables are not smaller. Any smallest query on the table will have to traverse the table till it gets the record.

Any user accessing the database is more interested in quick and correct result than how a database is designed. Hence if we introduce redundancy in the table and if the performance of the query increases, then we can ignore 3NF. This is known as denormalization. In this process, a normalized table will be rejoined to have the redundancy in the table to increase the performance of the query. Denormalization need not be true in all cases. It all depends on the data. In addition, it also depends on the redundant column which we are re-introducing into the table and frequency of this column update.

Advantages:

1.The data is present in the same table so there is no need for any joins, hence the selects are very fast.

2.A single table with all the required data allows much more efficient index usage. If the columns are indexed properly, then results can be filtered and sorted by utilizing the same index. While in the case of a normalized table, since the data would be spread out in different tables, this would not be possible.

Disadvantages:

1.It usually speeds retrieval but can slow updates

2.It is always application-specific and needs to be re-evaluated if the application changes

3.Cost of disk space will be more as we are keeping duplicate records

4.In some instances, denormalization simplifies coding; in others, it makes it more complex.

5.Data anomalies,as It allows duplicate data,so at any modification of data, it should modification of every piece of it's duplicate data which led to large computation and complex report.

Q2.

First let's see what is materialized view :

materialized view are schema objects, it storing the results of a query in a separate schema object(i.e., take up storage space and contain datas). This indicates the materialized view is returning a physically separate copy of the table data.

materialized view is good when you want to aggregate data and get this summary information from large tables frequently and quickly.This thing can be also achieved by denormalization where deonormalize some of your tables and maintain the aggregates via code or repeatedly scan large sets of rows.If you keep the results in a separate tables, you complicate your application code,you will be in charge of making sure that the manually aggregated data is in synch with the original table's data.

An example would be if you need to answer the question "how many employees are in departements ( x, y, z )" frequently. You could have a table of DEPT's and EMP's and join them and count. OR -- you could store in the DEPT table a count of the number of employees in that department -- an EMP_COUNT column -- and every time you added/removed an employee detail record, you would have to maintain this column. Here materialized view can be used for such an operation.

Denormalization TYPICALLY involves "pre-joining" frequently joined data to avoid having to do that at runtime. This typically results in redudant data they either the system (via materialized views) or you must remember to maintain in addition to the raw detail data.