What is Normalization? What are the Different Types of Normalization?

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.

Benefits of Normalizing your database include:

• Avoiding repetitive entries
• Reducing required storage space
• Preventing the need to restructure existing tables to accommodate new data
• Increased speed and flexibility of queries, sorts, and summaries

Note: During an interview, people expect to answer a maximum of three normal forms and that's what is expected practically. Actually you can normalize database to fifth normal form. But believe this book, answering three normal forms will put you in a decent shape during an interview.

The three normal forms as follows:

First Normal Form

For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields.

Figure 1: Repeating groups example

In the above example, city1 and city2 are repeating. In order for these tables to be in First normal form, you have to modify the table structure as follows. Also note that the Customer Name is now broken down to first name and last name (First normal form data should be broken down to the smallest unit).

Figure 2: Customer table normalized to first normal form

Second Normal Form

The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. In other words, each non-key field should be a fact about all the fields in the primary key.

In the above table of customer, city is not linked to any primary field.

Figure 3: Normalized customer table.

Figure 4: City is now shifted to a different master table.

That takes our database to a second normal form.

Third Normal Form

A non-key field should not depend on another Non-key field. The field Total is dependent on Unit price and qty.

Figure 5: Fill third normal form

So now the Total field is removed and is the multiplication of Unit price * Qty.

What is Denormalization?

Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.

Can you Explain Fourth Normal Form?

Note: Whenever the interviewer is trying to go above the third normal form, there can be two reasons, ego or to fail you. Three normal forms are really enough, practically anything more than that is an overdose.

In fourth normal form, it should not contain two or more independent multi-valued facts about an entity and it should satisfy “Third Normal form”.

So let us try to see what multi-valued facts are. If there are two or more many-to-many relationship in one entity and they tend to come to one place, it is termed as “multi-valued facts”.

Figure 6: Multi-valued facts

In the above table, you can see that there are two many-to-many relationships between Supplier / Product and “Supplier / Location (or in short multi-valued facts). In order for the above example to satisfy the fourth normal form, both the many-to-many relationships should go in different tables.

Figure 7: Normalized to Fourth Normal form.

Can you Explain Fifth Normal Form?

Note: UUUHHH if you get this question after joining the company, do ask him if he himself really uses it?

Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.

Example: Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell theProduct should be registered with the Company. So these three entities have a mutual relationship within them.

Figure 8: Not in Fifth Normal Form.

The above table shows some sample data. If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:

• JM Associate should be an authorized dealer of Cadbury
• Sweets should be manufactured by Cadbury company

These two smaller bits of information form one record of the above given table. So in order for the above information to be “Fifth Normal Form” all the smaller information should be in three different places. Below is the complete fifth normal form of the database.

Figure 9: Complete Fifth Normal Form

What is the Difference between Fourth and Fifth normal form?

Note: There is a huge similarity between Fourth and Fifth normal form, i.e. they address the problem of “Multi-Valued facts”.

“Fifth normal form” multi-valued facts are interlinked and “Fourth normal form” values are independent. For instance in the above two questions Supplier/Product and Supplier/Location are not linked. While in fifth form, theDealer/Product/Companies are completely linked.

Have you Heard about Sixth Normal Form?

Note: Arrrrggghhh yes there exists a sixth normal form also. But note guys you can skip this statement. Just in case you want to impress the interviewer...

If you want a relational system in conjunction with time, you use sixth normal form. At this moment SQL Server does not support it directly.

What is the Difference between SQL SERVER 2000 and 2005?

Following are some major differences between the two versions:

• (PG) The most significant change is the .NET integration with SQL SERVER 2005. Stored procedures, user-defined functions, triggers, aggregates, and user-defined types can now be written using your own favorite .NET language (VB.NET, C#, J# etc.). This support was not there in SQL SERVER 2000 where the only language was T-SQL. In SQL 2005, you have support for two languages T-SQL and .NET.
• (PG) SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000. It was a separate installation for SQL Server 2000.
• (PG) SQL SERVER 2005 has introduced two new data types varbinary (max) and XML. If you remember in SQL SERVER 2000, we had image and text data types. Problem with image and text data types is that they assign the same amount of storage irrespective of what the actual data size is. This problem is solved usingvarbinary (max) which acts depending on amount of data. One more new data type is included XML which enables you to store XML documents and does schema verification. In SQL SERVER 2000, developers usedvarchar or text data type and all validation had to be done programmatically.
• (PG) SQL SERVER 2005 can now process direct incoming HTTP request without IIS Web server. In addition, stored procedure invocation is enabled using the SOAP protocol.
• (PG) Asynchronous mechanism is introduced using server events. In Server event model the server posts an event to the SQL Broker service, later the client can come and retrieve the status by querying the broker.
• For huge databases, SQLSERVER has provided a cool feature called “Data partitioning”. In data partitioning, you break a single database object such as a table or an index into multiple pieces. But for the client application accessing the single database object, “partitioning” is transparent.
• In SQL SERVER 2000, if you rebuilt clustered indexes even the non-clustered indexes where rebuilt. But in SQL SERVER 2005 building the clustered indexes does not build the non-clustered indexes.
• Bulk data uploading in SQL SERVER 2000 was done using BCP (Bulk copy program’s) format files. Now in SQL SERVER 2005 bulk, data uploading uses XML file format.
• In SQL SERVER 2000 there were maximum 16 instances, but in 2005 you can have up to 50 instances.
• SERVER 2005 has support of “Multiple Active Result Sets” also called as “MARS”. In previous versions of SQL SERVER 2000 in one connection, you could only have one result set. Now in one SQL connection, you can query and have multiple results set.
• In previous versions of SQL SERVER 2000, system catalog was stored in the master database. In SQL SERVER 2005, it’s stored in a resource database which is stored as sys object. You cannot access the sys object directly as in the older version we were accessing the master database.
• This is one of the hardware benefits which SQL SERVER 2005 has over SQSERVER 2000 – support of hyper threading. WINDOWS 2003 supports hyper threading; SQL SERVER 2005 can take advantage of the feature unlike SQL SERVER 2000 which did not support hyper threading.
  Note: Hyper threading is a technology developed by INTEL which creates two logical processors on a single physical hardware processor.
• SMO will be used for SQL Server Management.
• AMO (Analysis Management Objects) to manage Analysis Services servers, data sources, cubes, dimensions, measures, and data mining models. You can mapm AMO in old SQL SERVER with DSO (Decision Support Objects).
• Replication is now managed by RMO (Replication Management Objects).
  Note: SMO, AMO and RMO are all using .NET Framework.
• SQL SERVER 2005 uses current user execution context to check rights rather than ownership link chain, which was done in SQL SERVER 2000.
  Note: There is a question on this later see for execution context questions.
• In previous versions of SQL SERVER the schema and the user name was same, but in current, the schema is separated from the user. Now the user owns schema.
  Note: There are questions on this, refer “Schema” later.
  Note: Ok below are some GUI changes.
• Query analyzer is now replaced by query editor.
• Business Intelligence development studio will be used to create Business intelligence solutions.
• OSQL and ISQL command line utility is replaced by SQLCMD utility.
• SQL SERVER Enterprise manager is now replaced by SQL SERVER Management studio.
• SERVER Manager which was running in system tray is now replaced by SQL Computer manager.
• Database mirror concept is supported in SQL SERVER 2005, which was not present in SQL SERVER 2000.
• In SQL SERVER 2005 Indexes can be rebuilt online when the database is in actual production. If you look back in SQL SERVER 2000, you cannot do insert, update, and delete operations when you are building indexes.
• (PG) Other than Serializable, Repeatable Read, Read Committed, and Read Uncommitted isolation levels, there is one more new isolation level “Snapshot Isolation level”.
  Note: We will see “Snapshot Isolation level” in detail in the coming questions.

Summarizing: The major significant difference between SQL SERVER 2000 and SQL SERVER 2005 is in terms of support of .NET Integration, Snap shot isolation level, Native XML support, handling HTTP request, Web service support and Data partitioning. You do not have to really say all the above points during an interview. A sweet summary and you will rock.

What is a Trigger in SQL Server?

A Trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs.

What are the two types of Triggers in SQL Server?

1. After Triggers : Fired after Insert, Update and Delete operations on a table.

2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.

What are the special tables used by Triggers in SQL Server?

Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted table and the data that is being updated is contained in deleted table.

Give a real time example for triggers usage?

It is recomended to avoid triggers in a real time environment. There is one scenario I can think of why you may want to use triggers in a real time environment. Let us use an example to understand this.

I have 2 tables, tblPerson and tblGender as shown below. GenderId is the foriegn key in tblPerson table.

Now create a view based on the above 2 tables as shown below.


Select * from vWPersons will give us the result as shown below.
Now update the view the following query. This will change the Gender Text to Female in tblGender table for Id = 1. This is not what we have expected.

Update tblGender Set Gender='Female' where Id=1


The base tables are updated incorrectly. So, Select * from vWPersons will give us the result as shown below.

To update the base tables correctly, you can create an INSTEAD OF trigger on the view as shown below.

Now run the query below which will update the underlying base tables correctly.
Update vWPersons Set Gender='Female' Where Id=1

Select * from vWPersons, will show the correct result set as shown below. The INSTEAD OF trigger has correctly updated the GenderId in tblPerson table.
So, Instead of triggers can be used to facilitate updating Views that are based on multiple base tables.

What is the difference between a User Defined Function (UDF) and a Stored Procedure (SP) in SQL Server

1. Stored Procedure support deffered name resolution where as functions do not support deffered name resolution.

2. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement.

3. UDF's cannot return Image, Text where as a StoredProcedure can return any datatype.

4. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic.

5. UDF should return a value where as Stored Procedure need not.

6. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters.

7. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables.

8. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL.

9. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

web user controls

What are ASP.NET Custom controls?

Custom controls extend the tools available to Web developers. Using custom controls, you can encapsulate key aspects of the visual interface and program logic that you want to reuse throughout your application, or throughout your organization.

What are the 3 types of custom controls in ASP.NET?
Microsoft Visual Studio .NET provides three types of custom control for use on Web forms.
1. Web user controls
These combine existing server and HTML controls by using the Visual Studio .NET Designer to create functional units that encapsulate some aspect of the user interface. User controls reside in content files, which must be included in the project in which the controls are used.

2. Composite custom controls
These create new controls from existing server and HTML controls. Although similar to user controls, composite controls are created in code rather than visually, and therefore they can be compiled into an assembly (.dll), which can be shared between multiple applications and used from the Toolbox in Visual Studio .NET.

3. Rendered custom controls
These create entirely new controls by rendering HTML directly rather than using composition. These controls are compiled and can be used from the Toolbox, just like composite controls, but you must write extra code to handle tasks that are performed automatically in composite controls.

What are the limitations of user controls in ASP.NET?
As the user controls are not compiled into assemblies, they have the following limitations:
1. A copy of the control must exist in each Web application project in which the control is used.
2. User controls can’t be loaded in the Visual Studio .NET Toolbox; instead, you must create them by dragging the control from Solution Explorer to the Web form.
3. User control code is initialized after the Web form loads, which means that user control property values are not updated until after the Web form’s Load event.

What are the steps to follow for creating and using a user control in a Web application?
1. Add a Web user control page (.ascx) to your project.
2. Draw the visual interface of the control in the designer.
3. Write code to create the control’s properties, methods, and events.
4. Use the control on a Web form by dragging it from Solution Explorer to the Web form on which you want to include it.
5. Use the control from a Web form’s code by declaring the control at the module level and then using the control’s methods, properties, and events as needed within the Web form.

How do you identify user controls?
User controls are identified by their .ascx file extensions.

What is the base class from which user controls derive?
User controls derive from System.Web.UI.UserControl base class. This base class provides the base set of properties and methods you use to create the control.

What are the steps to follow to create properties and methods for the user control that you can use from a Web form?
To create properties and methods for the user control that you can use from a Web form, follow these steps:
1. Create the public property or method that you want to make available on the containing Web form.
2. Write code to respond to events that occur for the controls contained within the user control. These event procedures do the bulk of the work for the user control.
3. If the property or method needs to retain a setting between page displays, write code to save and restore settings from the control’s ViewState.

What happens when you drag a user control from solution explorer and drop it on a web form?
When you drag a user control from solution explorer and drop it on a web form, Visual Studio .NET generates a @Register directive and HTML tags to create the control on the Web form.

Kill All Active Connections To A Database.

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DB_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END