SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31

Click here to get free chapters (PDF) in the mailbox

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is OLTP?

OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data At the very instant it is received and has a large number of concurrent users.

What is OLAP?

OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

What is the Difference between OLTP and OLAP?

Data Source

OLTP:         Operational data is from original data source of the data
OLAP:        Consolidation data is from various sources.

Process Goal

OLTP:         Snapshot of business processes which do fundamental business tasks
OLAP:        Multi-dimensional views of business activities of planning and decision making

Queries and Process Scripts

OLTP:         Simple quick running queries ran by users.
OLAP:        Complex long running queries by system to update the aggregated data.

Database Design

OLTP:         Normalized small database. Speed will be not an issue because of a small database, and normalization will not degrade performance. This adopts the entity relationship (ER) model and an application-oriented database design.

OLAP:        De-normalized large database. Speed is an issue because of a large database and de-normalizing will improve performance as there will be less tables to scan while performing tasks. This adopts star, snowflake or fact constellation mode of subject-oriented database design.

Back up and System Administration

OLTP: Regular Database backup and system administration can do the job.
OLAP: Reloading the OLTP data is considered as a good backup option.

What are Normalization Forms?

There are different types of normalization forms such as

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key

BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is ODS?

ODS is the abbreviation of Operational Data Store ‑ a database structure that is a repository for near real-time operational data rather than long-term trend data. The ODS may further become the enterprise-shared operational database, allowing operational systems that are being re-engineered to use the ODS as their operation databases.

What is ER Diagram?

Entity Relationship (ER) Diagrams are a major data modeling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has enabled the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner.

An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables.

List of all the Interview Questions and Answers Series blogs

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 21 of 31
Next Post
SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 23 of 31

Related Posts

No results found.

4 Comments. Leave new

  • dharmendra yadav
    July 26, 2011 4:20 pm

    how to remove Escape character from Column in a table?

    • safeer nadeem
      March 30, 2015 3:30 pm

      //Here is the script to remove spaces from column names.
      DECLARE @table_name varchar(1000) ,@column_name varchar(1000)

      DECLARE changetablename CURSOR — Declare cursor



      SELECT t.name AS table_name,
      c.name AS column_name
      FROM sys.tables AS t
      INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
      ——————————————————-Change the Column Name

      OPEN changetablename — open the cursor

      FETCH NEXT FROM changetablename

      INTO @table_name,@column_name

      — PRINT @table_name + ‘—-‘ + @column_name — print the name


      declare @newcolumnname varchar(1000),@oldcolumnname varchar(1000)
      set @oldcolumnname=@table_name+’.’+@column_name

      set @newcolumnname=replace(@column_name,’ ‘,’_’)

      exec sp_RENAME @oldcolumnname, @newcolumnname , ‘COLUMN’

      FETCH NEXT FROM changetablename

      INTO @table_name,@column_name

      CLOSE changetablename — close the cursor

      DEALLOCATE changetablename — Deallocate the cursor

  • Hi

    Online Analytical Processing Server, also known as OLAP Server, is a kind of engine that primarily supports multi-dimensional data structures.


Leave a ReplyCancel reply

Exit mobile version