Database Denormalization

Denormalization:

Constraints:Constraints in DBMS. Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table

Denormalization techniques:

1. Materialized views

2. Star schema:

Fact and Dimension tables: https://www.youtube.com/watch?v=6k3nwXXpnMY

Fact tables:
Defined by related dimensions
Resolves many to many relations
Does not contain granular information.
Provides Foreign keys to relate to Dimension tables to pull granular information.

Dimension tables:
Table containing actual business elements
Filelds contain element descriptions
Referenced by multiple fact tables

Example: 

3. Snowflake schema: Extension to Star schema - https://en.wikipedia.org/wiki/Snowflake_schema
The snowflake schema is similar to the star schema. However, in the snowflake schema, dimensions are normalized into multiple related tables, whereas the star schema's dimensions are denormalized with each dimension represented by a single table. A complex snowflake shape emerges when the dimensions of a snowflake schema are elaborate, having multiple levels of relationships, and the child tables have multiple parent tables ("forks in the road").

Data Normalization

or simply normalization, is the process of organizing the columns (attributes) and tables (relations) of a relational database to reducedata redundancy and improve data integrity.

Questions:

1. Differences between views and materialized views?

Materialized views are disk based and are updated periodically based upon the query definition.

Views are virtual only and run the query definition each time they are accessed.

A materialized view takes a different approach: the query result is cached as a concrete ("materialized") table (rather than a view as such) that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of extra storage and of some data being potentially out-of-date. Materialized views find use especially in data warehousing scenarios, where frequent queries of the actual base tables can be expensive.