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)
4 Comments. Leave new
how to remove Escape character from Column in a table?
//Here is the script to remove spaces from column names.
DECLARE @table_name varchar(1000) ,@column_name varchar(1000)
DECLARE changetablename CURSOR — Declare cursor
LOCAL SCROLL STATIC
FOR
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
WHILE @@FETCH_STATUS = 0
BEGIN
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
END
CLOSE changetablename — close the cursor
DEALLOCATE changetablename — Deallocate the cursor
@Safeer nadeem – thanks for your contribution
Hi
Online Analytical Processing Server, also known as OLAP Server, is a kind of engine that primarily supports multi-dimensional data structures.