Dimension Table vs Fact Table

     Dimension table and fact table are mainly used in data warehousing. The fact table mainly consists of business facts and foreign keys that refer to primary keys in the dimension tables. A dimension table consists mainly of descriptive attributes that are textual fields.
      Dimension tables provide descriptive or contextual information for the measurement of a fact table. On the other hand, fact tables provide the measurement of an enterprise. A dimension table contains a surrogate key, natural key, and a set of attributes. On the contrary, a fact table contains a foreign key, measurements, and degenerated dimensions.
     When comparing the size of the two tables, the fact table is bigger than the dimensional table. In a comparison table, more dimensions are presented than the fact tables. In a fact table, less numbers of facts are observed. In a dimension table, the values are text representations or numeric. In a fact table the values are in integer form or numeric.
     A fact table normally contains two columns – one that contains the facts and the other foreign keys to the dimension table. Dimension tables are also called reference tables.
     When dimension tables can be loaded directly, it is not possible with a fact table. In a fact table, the dimension table has to be loaded first. While loading the fact tables, one should have to look at the dimension table. This is because the fact table has measures, facts, and foreign keys that are the primary keys in the dimension table.

Dimension Table features :
1. It provides the context /descriptive information for a fact table measurements.
2. Provides entry points to data.
3. Structure of Dimension - Surrogate key , one or more other fields that compose the natural key (nk) and set of Attributes.
4. Size of Dimension Table is smaller than Fact Table.
5. In a schema more number of dimensions are presented than Fact Table.
6. Surrogate Key is used to prevent the primary key (pk) violation(store historical data).
7. Values of fields are in numeric and text representation.

Fact Table features :
1. It provides measurement of an enterprise. 
2. Measurement is the amount determined by observation. 
3. Structure of Fact Table - foreign key (fk), Degenerated Dimension and Measurements. 
4. Size of Fact Table is larger than Dimension Table. 
5. In a schema less number of Fact Tables observed compared to Dimension Tables. 
6. Compose of Degenerate Dimension fields act as Primary Key. 
7. Values of the fields always in numeric or integer form. 

Difference between dimension and fact tables: 

1.dimension data is de-normalized where as fact table contains normalized data 2.dimension table contains many columns where as fact table contains less columns only 2 in fact 
3. the data in the dimension tables are less compared to the data in the fact tables 
4. The data in the dimension table is static and descriptive in nature where as the fact table contains numeric and will change regularly 
5. dimension tables generally called as look up or reference table as well. facts tables are the key performance indicators of the business 

Summary:
     The fact table mainly consists of business facts and foreign keys that refer to primary keys in the dimension tables. A dimension table consists mainly of descriptive attributes that are textual fields.
     A dimension table contains a surrogate key, natural key, and a set of attributes. On the contrary, a fact table contains a foreign key, measurements, and degenerated dimensions.
     Dimension tables provide descriptive or contextual information for the measurement of a fact table. On the other hand, fact tables provide the measurements of an enterprise.
     When comparing the size of the two tables, a fact table is bigger than a dimensional table. In a comparison table, more dimensions are presented than the fact tables. In a fact table, less numbers of facts are observed.
     The dimension table has to be loaded first. While loading the fact tables, one should have to look at the dimension table. This is because the fact table has measures, facts, and foreign keys that are the primary keys in the dimension table.

Nolock mechanism in MySQL

    Before going to Nolock mechanism,first we learn some basic terms in common SQL,listed here,

Lock: it allows different type of resource to be lock by the transaction.

When user accessing data, data get locks so that other user cannot modify or delete data that someone is reading.

A transaction is unit of work submitted as hole to database for processing.

Database Anomalies and how to Overcome those Anomalies:

                In our day-to-day life, we come across data stored in various formats and structure. The most common form is a flat-file structure or commonly known as "spreadsheet-like" structure. This means nothing but data stored in rows and columns with the first row representing the headers under which the relevant data is stored. However, as the amount of data i.e. the number of records and the number of fields go on increasing the format becomes difficult to manage, analyze & maintain.

Open Northwind Database Explained:

 Northwind Database Explained:

                Northwind Database is a sample database that is shipped along with Microsoft Access application. Basically, the database is about a company named "Northwind Traders". The database captures all the sales transactions that occurs between the company i.e. Northwind traders and its customers as well as the purchase transactions between Northwind and its suppliers.