The SQL Server Training Series: Key Database Concepts In The Context Of SQL Server 2016

SQL Server 2016 presents a different take on database management, in terms of use-case, syntax and environment, as compared to other Relational Database Management Systems.

In this article, we will discuss some of the key database concepts, within SQL Server 2016, to better understand the management system functions.

What is a Database?

Simply put, a database is where you store and process structured information. They allow manipulation as well as retrieval of data that exists in usable form. There are different types of databases that organize information with the help of different structures. You may have heard in your SQL server training course that the Microsoft SQL Server is a database-management system, or DBMS.

DBMS is software that provides the ability to create and maintain databases, and allows one to organize information through various structures.

It is important you know that the database management system is not the same as a database, because DBMS is software. When it comes to SQL server, the databases that one has are relational databases, which is the reason why the product is sometimes called a relational database management system or RDBMS.

Learning about Database Types

There are three types of databases; namely flat-file databases, hierarchical databases and relational databases.

• Flat file databases are simple and have a two-dimensional design. Some examples of this are comma-separated value files, as well as Excel spreadsheets.
• Hierarchal databases play a very important role in the structure. An example is the folder structure that exists in a hard drive.
• Relational databases are more commonly known. They are like flat file databases that are linked together with the help of references.

Since Microsoft SQL Server is relational database software, we will be exploring this in detail.

Relational Database Concepts

Relational databases are the most common database type, and are used for general purposes. It has one or more tables that are similar to the flat file database. These tables are made up of one or more domain i.e. columns which describe how the data is recorded. If you have a database containing information, you can include employees and their job types.

This is an example of structured data that would be held separately as it has diverse information. Other than separating the data, the database makes it possible for relationships to be created. A relationship is a link that exists between different data types, and this link depends on the information held. For example, in the database, there may be a relationship between employees and the kind of work they perform, which may indicate that more than one employee has the same job title.

Understanding the Elements of a Relational Database Schema

There are different elements to a relational database that has been created with Microsoft’s SQL Server. These forms help in creating the database’s schema. Schema is defined as a logical data model that helps in finding the information that needs to be stored and the way to arrange it. Let’s have a look at some basic terminology that will help you grasp the concept better.

Tables

This is a very important integral concept of a relational database, as this is where the information in the database exists. You can think of a table as a grid that has information. In a grid, the column defines the elements of data required and the row is an individual record. So, columns and rows are used to describe data as well as schema of the table.

Normalization

Normalization is the process of removing duplications as well as redundancies in the days. Suppose you have a table that talks about the number of children people have. You have 20 people on the list, 10 of which have one child, and ten who have two children. In order to normalize the data, you will create a table and name it ‘children’. In the table, you will give a single child the value of 1, and two children, the value of 2. The people table will then be updated this way regarding the number of children.

Index

An index is used to get fast access to key columns of data in a table that helps in processing queries at a faster rate.

Transaction Logs

Transaction logs are used to detect what changes have been made since the page was last updated. This helps in protecting the logs. If there is a power failure, SQL server replays the changes back into the memory.

Database Data Types

You can store data in different forms, such as numbers, images, dates etc. All of these affect the size of the database. To save up space, it is important that you use small data types.

Binary Large Objects (BLOBs)

BLOBs are images, sounds, attachments as well as images that can be stored on separate files.

The SQL Language

The SQL language has three parts. These include Data Manipulation Language (DML), Data Definition Language (DDL), and Control Language.

Data Manipulation Language (DML)

Some of the commands in DML include delete, insert, update, and select.

Data Definition Language (DDL)

DDL gives command control on the database. Some of the commands include create, use, and drop.

Control Language

This includes logical structures and repetition.

Server Management Studio

This is a tool introduced by Microsoft that helps in managing SQL, and can be installed through a disk or ISO. If you want to explore its features, you can do so from the sample database known as AdventureWorks.

Tables

These can be created in SQL Server Management Studio. In the latest version of SQL Server, they can be found in views.

Views

This is a virtual table that can show rows and columns.

Stored Procedures

These are similar to functions in a script and have a security feature. This means that they can only be accessed by administrators or users that have already been logged in.

Key database concepts are an integral aspect of SQL Server training, and can help you understand how databases function in SQL Server 2016.