Question

I need a response in your opinion, from this answer

Q1.

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 its duplicate data which led to large computation and complex report.

Q2.

First let's see what is materialized view :

The concrete view is a chart object, where you store query results in a separate chart object (for example, occupy storage space and contain data). This indicates that the concrete view returns 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.

Answer 1

Q:1

/* 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 */

instead of above sentence you can write, Denormalization is a database optimization technique in which we add redundant data to one or more tables .Which will decrease the cost of query which was due to heavy joins between tables. This is done to speed up database access by moving from higher to lower form of normalization.

Other part of answer is Ok.

Q.2

in Materialized view you can write definition as following .

we can the frequently used query and store it under some name! This is similar to view but here we are executing the query and storing the results of query under name. This type of view is called materialized view .