Share article SQL Server Database Design Interview questions: What is normalization and what are the be ...
This is an interesting topic and yes it’s the most discussed one when it comes to SQL Server interviews.
In SQL Server interviews database design conversation goes in to two wide discussions one is Normalization and the other is de-normalization.
So in normalization section interviewer can ask you questions around the 3 normal forms i.e. 1st normal form, second normal form and 3rd normal form. This looks to be a very simple question but you would be surprised to know even veteran database designers forget the definition thus giving an impression to the interviewer that they do not know database designing.
Irrespective you are senior or a junior everyone expects you to answer all the 3 normal forms. There are exceptions as well where interviewer has asked about 4th and 5th normal form as well but you can excuse those if you wish. I personally think that’s too much to ask for.
When it comes to database designing technique interviewer can query the other side of the coin i.e. de-normalization. One of the important questions which interviewers can query is around Difference between de-normalization and normalization. The expectation from most of the interviewers when answering the differences is from the perspective of performance and type of application.
As people discuss ahead there is high possibility of getting in to OLTP and OLAP discussions which can further trigger discussions around database designing techniques Star and Snow flake schema.
See the following video on SQL server interview question: - Difference between unique and primary keys?
It’s a database design technique to avoid repetitive data and maintain integrity of the data.
Note: - After that sweet one line, I can bet the interviewer will ask you to clarify more on those two words repetitive and integrity word.
Let’s first start with repetitive. Let’s say you have a simple table of user as shown below. You can see how the city is repeated again and again. So you would like to improve on this.
So to solve the problem, very simple you apply normalization. You split that repetitive data in to separate table (city master) and put a reference foreign key as shown in the below figure.
Now the second word “Data integrity”. “Data integrity” means how much accurate and consistent your data is.
For instance in the below figure you can see how the name of the country is inconsistent. “Ind” and “India” means the same thing, “USA” and “United States” means the same the thing. This kind of inconsistency leads to more complication and problems in maintenance.
One of the most important thing in technical interviews like SQL , .NET , Java etc is that you need to use proper technical vocabulary. For example in the above answer the word “Data integrity” attracts the interviewer than the word “Inaccurate”. Right technical vocabulary will make you shine as compared to people who use plain English.
See the following video on SQL server interview question: - Difference between Union and Union All?
For in-depth explanation you can also see the video :- Can you explain First, Second & Third Normal forms in database? provided in the DVD.
Denormalization is exact opposite of normalization. Normalization is good when we want to ensure that we do not have duplicate and inconsistent data. In other words when we want to do operational activities like insert, update, delete and simple reports normalization design is perfectly suited. Putting in other words when data comes in to the system a normalized design would be the best suited.
But what if the we want to analyze historical data, do forecasting, do heavy calculations etc. For these kinds of requirements normalization design is not suited. Forecasting and analyzing are heavy operations and with historical data it becomes heavier. If your design is following normalization then your SQL needs to pull from different tables making the select process slow.
Normalization is all about reducing redundancy while denormalization is all about increasing redundancy and minimizing number of tables.
In the above figure you can see at the left hand side we have a normalized design while in the right hand side we have denormalized design. A query on the right side denormalized table will be faster as compared to the left hand side because there are more tables involved.
You will use denormalized design when your application is more meant to do reporting, forecasting and analyzing historical data where read performance is more important.
See the following SQL Server interview question video on Difference between subquery and co-related queries ?
Both OLTP and OLAP are types of IT systems.OLTP (Online transaction processing system) deals with transactions ( insert, update , delete and simple search ) while OLAP (Online analytical processing) deals with analyzing historical data, forecasting etc.
Below is a simple table which chalks out the differences.
|Design||Normalized. (1st normal form, second normal form and third normal form).||Denormalized (Dimension and Fact design).|
|Motive||Faster insert, updates, deletes and improve data quality by reducing redundancy.||Faster analysis and search by combining tables.|
|SQL complexity||Simple and Medium.||Highly complex due to analysis and forecasting.|
Normalization is best suited for OLTP systems (faster transactions) while denormalizations are best suited for OLAP systems (faster queries and analysis).
The most important goal of OLAP application is analysis on historical data. The most important thing in any analysis are “NUMBERS”. So with OLAP application we would like to get those numbers , forecast them, analyze them for better business growth. These numbers can be total sales, number of customers etc.
These numbers are termed as “Measures” and measures are mostly stored in “Fact” tables. “Dimension” describes what these measures actually mean. For example in the below table you can see we have two measures 3000 units and 1500 $. One measure is “ProductWiseSales” and the other measure is “AgewiseSalary’.
Dimensions are stored in dimension table.
Watch the following awesome video on SQL Server interview questions: - Find the second highest from a table?
Star schema consists of fact and dimension tables. The fact tables have the measures and dimension tables give more context to the fact tables.
In the below figure “Star design” you can see we have four dimension tables and each one of them are referencing the fact tables for measure values. The references between dimension and fact tables are done using simple foreign key relationships.
Figure: - Star design
Snow flake design is very much similar to star design. The exception is the dimension table. In snow flake dimension tables are normalized as shown in the below figure “Snow flake design”. The below design is very much similar to the star design shown previously but the products table and vendor tables are separate tables.
The relationship is more of a normalized format. So summing in other words Star design is pure denormalized design while snow flake can have normalized dimension tables.
Figure: - Snow flake design
|Snowflake Schema||Star Schema|
|Normalization||Can have normalized dimension tables.||Pure denormalized dimension tables.|
|Maintenance||Less redundancy so less maintenance.||More redundancy due to denormalized format so more maintenance.|
|Query||Complex Queries due to normalized dimension tables.||Simple queries due to pure denormalized design.|
|Joins||More joins due to normalization.||Less joins.|
|Usage guidelines||If you are concerned about integrity and duplication.||More than data integrity speed and performance is concern here.|
See the following video on SQL server interview question: - Why do we need denormalization in SQL Server?
See for more stuffs on SQL Server Interview question
Click to view more from author's on SQL Server Interview question
Also buy SQL Server interview question book from here.
ADO.NET interview questions videos
Windows Phone Application Questions Videos
MVC Interview questions and answers Article
Explain the concept of View Model in MVC?
Exception Handling in MVC
Explain MVC application life cycle?
100 MVC Interview question PDF download released , Enjoy
Learn Microsoft Business intelligence step by step – Day 1
Can you create TempBag just like ViewBag?( MVC Interview questions)
MVC Unit Testing Unleashed
MVC and .NET learning tutorials for components of MVC and how do you perform unit testing in MVC?
Explain MVC model binders ? ( ASP.NET MVC interview questions)
Explain the difference between layout and master pages ? (MVC Razor interview questions)
WCF Interview questions videos
WPF Interview question with answers videos
C# Interview Questions & Answers Article
C# design pattern (UNIT of Work Design Pattern)
C# design pattern interview questions – What is Dependency injection ?
C# interview questions and answers: - What is the difference between “==” and .Equals()?
C# Architecture interview questions with answers
C# Mock testing interview questions and answers: – What is Mock testing and how can we implement Mocking?
Write a simple regex for email validation? (C# interview questions with answers)
C#.NET interview questions: - What is the difference between Build Vs Rebuild Vs Clean solution menu? What is the
difference “Rebuild” and if you do “Clean” + “Build”?
C# and .NET interview questions around JIT and NGEN
What is the difference between throw vs throw ex ?( C# Interview questions with answers)
C#/.NET interview questions: - What are the steps included to get a collection of records from database in C#?
Learn C# in Hindi video tutorials
WCF INTERVIEW QUESTIONS AND ANSWERS ARTICLE
QUESTIONS WITH ANSWERS :- WHAT IS DATACONTRACTSERIALIZER AND HOW IT’S DIFFERENT FROM XMLSERIALIZER
What is SOA and what is WCF? ( WCF interview questions with answers)
Six important WCF interview questions with answers
7 WCF SOA interview questions with answers
What are the various ways of consuming WCF services?(WCF C# interview questions with answers)
What is the difference between Serialization and Encoding in WCF?( WCF interview questions with answers)
Why returning dataset or data table from WCF service is not a good practice?(WCF service interview questions with answers)
WCF transaction interview questions: - How transaction works in
WCF Transaction interview questions.
.NET WCF(Windows Communication Foundation) interview questions: - Distinguish between Web Services and Remoting | WCF and Web services
WCF interview questions: - What exactly do you mean by Single in WCF instancing? How to set and what is by default set value of instance context mode?
Explain WCF Duplex services? ( WCF Interview questions)
QuestPond: Interview Questions and Answers on learning WCF
WCF Interview Questions: - What are the different ways of doing WCF concurrency and WCF instancing?
QuestPond: Interview Questions and Answers on learning WCF (Windows Communication Foundation)
QuestPond: Interview Questions and Answers on learning WCF (Windows Communication Foundation)
QuestPond: Interview Questions and Answers on WCF(Windows Communication Foundation)
QuestPond: Interview Questions and Answers on learning
WCF (Windows Communication Foundation)
QuestPond’s Interview Questions & Answers on learning data contract serializer and XML serializer
QuestPond’s Interview Questions & Answers on learning serialization events
QuestPond’s Interview Questions & Answers on learning WCF(Windows Communication Foundation)
QuestPond’s Interview Questions & Answers on WCF(Windows Communication Foundation)
WPF Interview Questions And Answers Article
How does “UpdateSourceTrigger” affect bindings?( C#
WPF interview questions with answers)
WPF Interview questions :- Which are the different namespaces and classes in WPF ?
What are resources in WPF? (C# WPF Interview questions with answers)
Explain multi binding and multivalue converters? (.NET WPF Interview questions with answers)
Explain the difference between static and dynamicresource? (.NET WPF interview questions)
18 important WPF interview questions asked in WPF interviews.
.NET WPF(Windows Presentation Foundation) interview questions: - What is WPF? What are the advantages of WPF over Winforms?
Explain WPF relative binding / relative resource? (.NET WPF Interview questions with answers)
Explain Ancestor relative source binding in WPF ? ( WPF Interview questions with answers)
Explain the need of binding and commands?(WPF Interview questions)
Can you explain WPF command with an example? ( WPF Interview questions with answers)
Explain the need of “INotifyPropertyChanged” interface? ( C# WPF Interview questions with answers)
What is MVVM? (WPF MVVM Interview questions)
What is the importance of command and bindings in MVVM pattern? ( WPF MVVM Interview questions)
What is the difference between MVVM and 3 layer architecture? ( WPF MVVM Interview questions with answers)
Explain delegate command? (WPF MVVM interview questions)
WPF Interview questions with answers
Explain WPF styles?(C# WPF Interview questions with answers)
Explain Multitrigger concept in WPF?( C# WPF Interview questions with answers)
How are WPF observable collections different from simple .NET collections? (.NET WPF Interview questions)
What is the difference between control templates and data templates? ( .NET WPF Interview questions)
What are various ways of doing alignment in WPF?( WPF Interview questions with answers)
.NET INTERVIEW QUESTIONS & ANSWERS ARTICLE
What are ACID Properties( .NET Interview questions with answers)?
.NET interview questions: – What are the areas that need to be concentrated for senior(9+ years experience)persons in .NET technologies?
.NET interview questions: - Does code contract only do dynamic
ADO.NET interview questions: - How can we fire a simple SQL Statement using ADO.NET?
.NET interview questions: - What basic steps are needed to display a simple report in crystal?
.NET interview questions: - What is UML? How many types of diagrams are there in UML?
.Net Interview Questions - What is MVC, MVP and MVVM pattern?
.NET interview questions: - How can you define a property read only for external world and writable in the same assembly?
Explain the implicit and explicit conversion of data types with examples?( .NET interview questions on data type)
.NET interview questions: - What is Reflection its implementation and practical uses?
What is downcasting and upcasting? (.NET interview questions with answers)
.NET interview questions: - How to decide on the design consideration to take a Data grid, data list or repeater?
.NET interview questions : - How can we improve performance of .NET and show the difference between .NET 1.X,
2.0, 3.0, 3.5 and 4.0?
How to convert string to ENUM value ?( .NET interview questions with answers)
.NET architecture interview
questions series: - What is mean by modeling language?
.NET architecture interview questions : - What are benefits and Limitation of using Viewstate, hidden frames and Cookies?
.NET interview questions: - What is GAC (Global Assembly Cache)?
Why do we need HTML 5 server-sent
events? (ASP.NET HTML interview questions)
How can we add and remove data from local storage?(ASP.NET HTML 5 interview questions)
What is local storage concept in HTML 5? ( ASP.NET HTML 5 interview questions)
Explain the term cascading in style
sheet ? ( CSS Interview questions)
What is difference between DIV vs SPAN in HTML ?( ASP.Net Interview questions)
HTML 5 Canvas Interview questions: - Canvas in HTML 5.
Dotnetspider Questions And Answers Article
What is the difference between String vs string? ( C# interview questions) ?