SQL Server And Relational Database - Part One

Client-Server System

If you look to the past, you’ll get to know that in the early 1980s, the applications running on PCs could access the local data files only. In today's world, this creates a problem because multiple users won’t be able to share the data. As things evolved, the databases began to be installed on the Servers to enable multiple users to share the data on the network. Then, we needed something to manage the data. That time, the relational database system came into the picture. The RDBMS can do so with the help of Structured Query Language (SQL). In this simple text, they get passed to the server from the client application. The DBMS, in turn, takes care of all the files known necessary to perform the operations that the users request, such as - CRUD operation etc.

N-Tier Systems

Now, these client-server systems evolved in the complicated scenarios architecturally. Here, we found the need to host our database on the Web Server to be available on all the other places as well. Now, the request comes to the Application Server and then our application requests the data to the Database Server and the result set comes back with a response. Then, we consume this data in the application and the View shows up with data and after applying the operation in the browser or I would say in the client application if it is a desktop application.

This evolved even further with the internet application. And here, the Web App Server returns the HTML or JS to the Client PC.

Relational Model

Relational Model is based on Mathematics.

• Set Theory
• First Order Predicate Logic

But it doesn’t mean that you should be a mathematician to use relational model in your database. But having some knowledge of these underlying concepts can help and understand some of the typical behavior of relational databases. In a relational database, data is stored in one or more tables. In relational theory, rows are tuples, columns are attributes and tables are relations.

 

With the help of the relational model, records are now no longer implemented in terms of physical storage. Instead, connection is the only basis on the logical basis by adding the primary key of the customer in the storage area for an order. The 2^nd important difference is that the rules for relational database only describe the behavior of the database, not the implementation. 

Relational Database

• Relational Database breaks the data into multiple different tables.
• Tables are made up of Columns and Rows.
• Column defines datatypes like varchar, nvarchar, integers, images etc.
• Rows are uniquely identified by the Primary Key Column.
• Rows in one table are related to rows in another table using a special Foreign Key Column. The foreign key is almost like a lookup.
• This approach reduces the redundant data storage.
• You can imagine if we are trying to store all of the information of a customer and orders in a single spreadsheet, we would wind up duplicating for each order with its place.
• You don’t have to worry about file input-output. All this is handled by RDBMS.
• They even have indexes which can be applied to different columns. This makes the dramatically speed up retrieval of data. You can feel the importance of Index with the help of an example of a book. If we use the Index to find something in a book, then it will be so much easier otherwise we have to read out the complete book to find something from the book.
• There are also many popular Relational Databases, like Oracle, DB2 (IBM), MySQL (Open Source), SQL Server.

Structured Query Language

Whenever we talk about the SQL, we need to understand that it is an ANSI Standard used to communicate with the database. So, even though there are multiple different Relational Databases, they always follow some of the ANSI fashion standards. The type of SQL is called Declarative Language that means we explain to the database what we want. We don’t tell the database how to do the data retrieval.

• All Relational Databases use Common Sub Language Keywords:
  • Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
  • Data Definition Language (CREATE, ALTER, DROP)

• Data Control Language (GRANT, DENY, REVOKE)
  It allows us to manage the Permissions for users in the Database to access objects

GRANT       -        To Allow the User to Do Something

DENY         -        To Explicitly Deny the User

REVOKE    -        It is Something Neutral

• Is it possible that we write the Query on SQL Server and run on Oracle or DB2?
  
  Well, it is more difficult. You see each of the database renders have different dialogues for SQL. So, there are minor differences between them also there are different built-in functions that make it difficult to move from one database to another.

It usually requires some rewriting at one level or another to be able to move it.

Database Design (Data Modelling)

When you need to create a database to store information for a business, it is important to plan the database first.

But how many tables do we need? What column goes into each table? And how do they relate? This is called Database Design or Data Modelling.

But models can be made at various abstraction level.

• Physical
• Logical
• Conceptual

Conceptual Data Model (Conceptual Schema)

It is mainly intended to assess formalized all the information that is relevant for the business. A conceptual data model can be used as a starting point to later implement to a database that it doesn’t have to. A good conceptual modeling language uses terminology that is as close as possible to the everyday language of the people in the business.

Logical Data Model

It defines how the business information should be stored in a database. The schema is designed for a specific database technology i.e. hierarchical, relational or object-oriented. If a concept of the logical database has been made, it usually is used as a start point of a logical data model. In our series, we’ll focus on the logical data model and its implementation in the relational database.

Physical Data Model

It defines the exact implementation of the logical data model and it is optimized for a specific implementation. It should be derived from the logical data model. Sometimes the physical data model will be equal to the logical data model but more often it will not. Creating the physical data model and optimizing it take advantage of the specific performance criteria of the various available relational database management systems. 

SQL Server

SQL Server is nothing but an implementation of the relational database. A big pile of data that a lot of people can access at once -- some may be reading, some may be writing it, some may be changing it. It's a highly scalable implementation of the relational database. SQL Server itself is a collection of Services, Applications, Libraries. And when we put them all together, we call these SQL Server.

You might know about SSMS (SQL Server Management Studio) which is the GUI Application and it is used to develop an application for SQL Server and to manage it. SSMS to SQL Server to what Visual Studio is to .NET Applications. Working with GUI, sometimes, is a very productive way to create applications but other times, it is just more convenient to work from the command line. And SQL Server gives us a couple of applications we can use to work from the command line SQLCMD and the other is called PowerShell. And PowerShell is simply a name of a command shell. The principal difference between it and PowerShell is it processes the objects, not text, as Unix Shell does. PowerShell isn’t the part of SQL Server directly but SQL server includes an extension to PowerShell called a PowerShell provider. Sqlcmd is a command line application that allows you to access the SQL Server from the command line.

There are some other applications as well.

• SQL Server Profiler
  It allows you to make a trace of all the commands that were sent to the SQL Server. You can capture the log and you can save this log to a regular file. You can save it to a table or save it to a table on a different machine. It is also useful when you want to optimize the performance of the SQL server.

• Database Engine Tuning Advisor
  It is just used to optimize the performance of the SQL server. And it does this by analyzing the commands being sent to the SQL server in figuring out where the best place is to put index and stuff like that. In fact, typically, you collect a trace of what’s going on in your server in real operations using SQL Server profile.

Database Engine

If we want to connect to the database table's different users and different roles, then we need to connect with Database Engine. When you’re creating a new development type database, click on Databases > New Database > Name the Database.

And make sure to change the recovery model to Simple because if you don’t, SQL Server is going to build up a very large transaction log probably you don’t care about. And you’ll end up having this database stop running until you clean up that log. Typically, for development purposes, we always use the Simple recovery model.

 

When you’re writing the SQL in SQL Server, we call that T-SQL.

Similarly, we can create the database in SSMS with GUI. Whenever you reference a table, you can reference it by 4 parts.

• Specify the Server
• Specify the Database
• Specify the schema
• And Table Name

So, the easiest way is,

Select the database from Object Explorer and press New Query. It saves my typing of specifying the above credentials. And if we directly go into New Query, the system master database is automatically selected.

But we’ll work with Chinook database which is a readymade database to work with. It has built-in tables and data inside. I’ve attached the database file. Just open this file in SSMS and execute it. All the query statements will successfully be executed and your schema will be ready with data.

If you want to move with sqlcmd, then you can go ahead and start your learning. Here, we’ve some important links for that.

• Sqlshack
• Sqlcmd Utility

And if you want to know about SQL PowerShell, then we’ve another tool called sqlps. Just search in your Windows OS and you’ll find that. If you want to explore this, then try to explore them your own. Because most of the time, developers just prefer to use SSMS. But if you’re really nerdy about learning new things, then I’veshown you the direction and explore it.

It is not necessary that you should know about the SQL queries or how to implement DML, DDL operations, and things like that but you can do everything with GUI. Most of the time, when a developer is experienced about  databases, then he tries to perform tasks with GUI with the help of drag and drop, database diagram, object explorer like tools in SQL Server Management Studio.

This is how we generate the SQL script to recreate the things with Object Explorer. Here, we’ve multiple options to create any kind of query like Create, Drop, Select, Insert etc.

SMO (SQL Server Management Object)

Besides the ADO.NET for making a connection and interacting with SQL server, there is another library available to you called the SQL Server Management Object library. And it is actually the core of SQL Server Management Studio and sqlps (SQL PowerShell) command shell and in fact, both of those are really just thin wrappers around SMO but certainly SSMS, the main job it does is to present the GUI. All the things we have done by SMO at the backend. SMO is the set of .NET classes which represent the object you can find in SQL server and has all the intelligence about the hierarchy of the objects.

The script which we generate is SSMS from the tables is actually done by SMO. If you’re writing an application which generates its own tables and things like that SMO might be a very handy way to go and do that.

Analysis And Reporting

We said SQL server is a highly scalable implementation of a relational database. Sometimes when we say something (database) then it might mean that Online Transaction Processing System (OLTP) that is each of the operations done to it individually take far less than a millisecond to accomplish. So, when we design the schema for online transaction processing system, we’ve to be very careful the operations we do to it can run as fast as possible.

And here we wanna do some kind of analysis, some kind of reporting may be output is just the simple printed report, maybe in excel spreadsheet, maybe you wanna put it on the web.

So, here we’ve 2 things.

• OLTP (Online Transaction Processing)
  Here the data comes from daily basis processing like whose the customer came here, how much time the delivery takes. Everyday tasks have come inside here. Day to day operations comes here. It is an operational processing. It is transaction oriented. The users of OLTP are Clerk, DBA, and Database Professionals. It focuses on to capture the data. It is an ER based – application oriented. The data we use here is current, guaranteed and up to date. The main purpose of OLTP is to perform a short and simple transaction. Here, we’ve access to Reading and Write both. The size is from MBs to GBs. It is high performance, high availability.

• OLAP (Online Analytical Processing)
  OLAP means how many sales were completed this day, how much loss we sustained today, which product is selling successfully and which product is not selling. This kind of information comes here. It is an informational processing. It is analysis oriented. It is used by business owners and knowledge workers because it helps them to guide how they can increase the business. It focuses on Information out, different ways to give better results. We use star/snowflake here, it is subject oriented. It uses historical data. It performs the complex query. Here, we mostly read the data. The size of OLAP is from GBs to TBs. It shows highly flexible data and here is end user autonomy.

So, here we need a reporting infrastructure to report the things and SQL Server Reporting Services provide this feature. And obviously the most important point is OLTP and OLAP can’t really be located on the same database server because one would be taking too much power from the other. So what we do to make the OLAP database separately is copy the data out of an OLTP database and transform it into the form that OLAP database needs in order to be able to answer questions like in reporting. And for SQL Server that particular product is called SQL Server Integration Services.

SSAS

SQL Server Analysis Services is just plain old SQL Server but the way we use tables in OLTP is different here because it keeps track of facts. These are small pieces of information; i.e., time etc. It also works with dimensions  in terms of things like time, weeks, hours, years. The product is also another dimension; i.e., each of your SKU are different units in that particular dimension. 

SSAS has a special language called Multi-dimensional Expression language (MDX) which is used to produce something called cubes.

SSIS

In order to use SSIS, you need some way to transform the data you’ve got in OLTP database to an OLAP database, SSIS is the product that does that. It is actually a very general purpose product which helps to move and transform the data from anywhere else to anywhere even if no databases are involved. Moving data is  easy like dragging  and dropping but getting the data in the database through the form needs a program (lines of code). So, SSIS takes the source of data and transforms it how you want, SSIS is the development framework for creating packages and a SQL Server Service from managing packages. Data movement in SSIS called package.

SSRS

There are two major parts to SQL Server Reporting Services.

• Development Framework
  It is used to develop reports. You might think because it's SQL Server reporting services that the source of data of all those reports would be an SQL server database, but that’s not the case at all. The data might be coming from the floppy disk, hard drive to the source of the data can be anything

• Runtime Service
  It takes those reports and generates output and output can be all kinds of different things. You might output excel spreadsheet, might be in pdf files, it might be HTML to display on the web page

Conclusion

We’ve been looking at SQL Server from the point of view of a developer who develops applications which use SQL Server.

And we’ve seen SQL Server consists of collection of services like Database Engine (used for relational database and uses T-SQL internally), SSAS (implements OLAP databases used to do sophisticated analysis of the data working with), SSIS (to move and transform data from OLTP to OLAP) and SSRS (to generate the reports and show to the user).

There are a number of applications that a developer usually works with like SSMS, sqlcmd (to work on command line and execute the queries on cmd) and we can use the object model that SSMS presents of SQL Server on the command line by using sqlps, Profiler (allows you to trace all the things going on under the database), and tuning advisor (to advice well and fact tuning up your database for better performance).

There are a number of libraries that applications use like ADO.NET where we perform connections, commands and get back results and they  can be used to send SQL queries from an application to SQL server and get the results back. Linq to SQL is where we use C# code to generate the SQL queries and we deal with them at compile time and handle all kind of exceptions on compile time. And SMO does all the operations under the hood which we might think have done by SSMS or sqlcmd or sqlps.