SQL Server training for DBAs : A short tutorial

Introduction

In this three part series, starting with SQL Server training for DBAs, we will look at the most important concept of any application, which is the application architecture. Before a single line of code is written, the architecture of the application is developed. Most of the Enterprise applications consist of 3-tier architecture. In this article we will examine the database layer which is considered the foundation of the whole application.

The Database Layer

The Database layer is considered the most important layer in the application, so the SQL Server training for DBAs tutorial starts with the database itself.

The whole functionality of the application relies on the database layer. Most of the applications are made ground up, meaning the database layer is made first, then the business logic layer, and finally the presentation layer. If we are not able to make a solid foundation and correct database design layer it will be very hard and sometimes impossible to fix those problems in the future.

The Design phase

The design of the database can be started by using a scratch paper and a pencil, or you can use a Case tool.

Later, that design can be exported to a database system such as SQL SERVER 2000. Let's look at some important concepts that should be a part of a good database design:

Primary Keys

First in this SQL Server training for DBAs tutorial, let us take a quick look at the importance of primary and foreign keys. Every table in the database must consist of a column (or set of columns) that uniquely identifies each particular record. This can be done using the primary key constraint.

Each table should have one primary key. Let's look at the problems we face by not using a primary key or choosing a wrong primary key.

No Primary Key or Choosing the wrong Primary Key

The table below does not contain any primary key and hence it is very hard to recognize each record. As you can see, the table below has three records. Since there is no primary key there is no way to uniquely identify each record. Now suppose that I want the record of John. I search the table and I found two people whose names are John. Since there is no primary key, the system will never know which John I am talking about.

Now let us jump to another important fact:

Suppose that we set the "Name" column as the primary key. The problem with "Name" being the primary key is that many people have the same name.

It is not a good idea to set name as the primary key.

Think for a while about something that is unique for each person. What about home address or telephone number?

Well, not a good idea since 3-4 people living in the same house have the same address and the same telephone number.

How about Social Security Number? Tha's a good idea since Social Security Numbers are different for every person. But then, are they? Actually not: I can instantly think of three situations where SSN cannot be used:

1. You don't have one, because you are a foreigner.

2. You have changed sex. (Yes, people do...)

3. You need protection (witness program, or whatever)

Be careful about which candidate key you choose for your primary key.

I will show you another technique to make primary keys which is accomplished by the use of identity columns.

An identity column is a column in a table which increments each time automatically when the data is inserted. This way we will have unique number for every newly inserted record and the best thing is that it's done automatically.

The image below shows the identity column "PersonID" being added, giving unique ID's to individual rows of the table:

Creating relationships using the Foreign Keys

Foreign keys are basically used to connect different tables together. I know it is kind of hard to digest this at first, but let's look at an example which does not use a foreign key. Suppose we want to save the phone numbers of these people:

Look carefully at the table above: Adam has three phone numbers; his phone numbers are causing the repetition of data.

Because of his three phone numbers we are repeating his name and his email address. This is not a good design and it is not normalized properly.

The term Normalization means removing any duplication or repetition in the table (basically). Let us see how we can solve this problem.

In order to solve this problem we need to get another table which will only deal with the "Phone Numbers". So, let us make a table which is used to store phone numbers.

As you might have noticed, we are also storing the PersonID in the Phone table, which identifies whose phone number it is. The first column in the Phone table is "Phone ID" which is also the primary key, and it auto increments. Second column is "Person ID" and the last column is the "Phone Number" which contains the phone number of Person. Could we have used Phone Number as a primary key? We could, but it is not adviseable. What if it changed?

As you can see above, now we are not repeating as much information as we were doing before. Our model is not denormalized anymore.

A good question to ask at this point, is when and how should I know when I need to make another table. Let's look at the three types of relationships that are possible in relational databases:

1) One to one relationship

One to one relationship is used when you have only one table and all the data is in one table. A relationship between a Social Security Number and a person name is one to one. Since one Social Security Number can belong to one person and one person can only have one Social Security Number.

2) One to many relationship

One to many relationships is used when at least two tables are used. A relationship between a Person and his Phone number is one to many since one person can have many phone numbers. This is by far the most used kind of relationship.

3) Many to many relationship

As it sounds like, when many items are related to many items. A good example will be: one product can have many suppliers and one supplier can deliver many products.

For a thorough explanation of all this and much more, get my free eBook on Entity relationship Modeling.

Let's move on to other important aspects in this SQL Server training for DBAs tutorial:

Stored Procedures or Ad-Hoc Queries

Stored procedures are simply a group of statements that perform some functions on the database. These functions can be insertion, updating, selecting or deleting rows on one or more database tables.

Ad-Hoc queries on the other hand serves the same purpose as stored procedures with one big difference. They are assigned to a string and placed embedded in the business logic code.

It is always better to use Stored procedures instead of Ad-Hoc Queries, since stored procedures can be cached and hence they are much faster. Stored procedures are also safe from the SQL Injections.

Let us look at some examples of stored procedures and Ad-Hoc queries:

Selecting all items from the Person table:

String query = "SELECT * FROM Person;"

CREATE PROCEDURE [GetItems]
AS
SELECT * FROM Person
GO

You can see that the stored procedure takes more lines of code but it's cleaner and it is saved in the database layer instead of the business layer.

String query = "INSERT INTO Person
VALUES(@Name,@Email);"

CREATE PROCEDURE [InsertNewPerson]
AS
INSERT INTO Person
VALUES(@Name,@Email)
GO

Stored procedures are also safe from the SQL Injections, which is the biggest threat to the database.

Sql Injections refers to the fact that any user can type anything in the textbox or the input field and that is concatenated within the query.

A Look at the SQL Query Analyzer

SQL Query Analyzer is a tool provided by SQL Server 2000 to test and run your queries. You can run Query Analyzer by selecting Tools and then SQL Query Analyzer.

Let us write a simple query in the query analyzer and see what happens:

As you can see, it provides a graphical user interface to show the result of our queries. The upper pane is used to insert the query, while the bottom pane is used to show the results of the query.

If you are running a stored procedure in the Query Analyzer, you will only need to write the name of the stored procedure and it will execute and show you the results in the bottom pane.

Backing up the database

This is one of the most important tasks in the database development. Always, always make backup of your database. There are cases in which you create one database and did not make the backup of the database, and next day the server crashed and you lost your database along with millions of records in the database. Making a backup of your database in Sql Server 2000 is very simple:

Just right click on your database, which I in this case named "MyDatabase" and select "All Tasks", and then "Backup Database".

Next, you will see a pop up window: Click on Add and enter the name of your Backup File:

Next, click OK and click OK again to create the backup of your whole database.

If you have performed all the operations correctly, you will see a message box saying that your database backup has been made.

Let us browse your hard drive and see if the database backup has been made or not, so we will be sure about it.

And as you can see in the image below the backup is created in the same place where we wanted it:

I hope you have enjoyed this SQL Server training for DBAs tutorial. In the next section we will take a look at the business logic layer which is the heart and soul of any application.

Return to Application Architecture