DBMS, RDBMS and SQL Server

Lets start this tutorial with the chapter “DBMS, RDBMS and SQL Server”. Before starting T-SQL part of the SQL Server tutorial, we will learn basic concepts of database systems. In this chapter, we will explore “Database management systems” and its relational extension “Relational database management systems”. We will also have an introduction to Microsoft SQL Server at the end of the chapter. This chapter has necessary images to explain the concepts in pictured way which will help you to understand the topics with ease.

Database management system (DBMS)

Database management system is a software used to define, record, query, update and manage data in a database. DBMS stores data in a structured way (relational, hierarchical, flat files, objects etc), and executes operations requested by various users on these data. It interacts with user, receives commands, and runs it against the database. DBMS can interact with some other applications, if required. A fourth generation query language (SQL – Structured Query Language) is used to communicate with recorded data in Database management systems. DBMS also provide some standard mechanism to communicate with other DBMS systems or with other applications.

A few application areas of DBMS are Finance, Marketing, Sales, Banking, Manufacturing, Airlines etc. Have a look on the below image which describes the DBMS:

Relational Database management system (RDBMS)

Relational database management system is a type of database management system which stores its data in tables and columns with relationships. It is based on the relational model of Edgar F. Codd well known as E. F. Codd, an English computer scientist. Below image explains the RDBMS:

Codd Rule

Codd introduced these rules in his relational model. Below is the list of Codd rules:

E. F. Codd 12 rules

Rule 0: The Foundation Rule

A RDBMS must manage its data in relational form only. This rule is applied as a base rule to all other other rules.

Rule 1: The Information Rule

All information in a relational database either user data or meta data (data about data), must be stored as table value i.e. in a cell of table.

Rule 2: The Guaranteed Access Rule

Every scalar value in database must be logically addressable using a combination of table name, column name and primary key value of the row.

Rule 3: Systematic Treatment of NULL Values

NULL values must be handled systematically and uniformly. It used to represents “missing value” or “not applicable value” or “data not available”.

Rule 4: Active Online Catalog

Database’s structure must be available in an online active catalog which is accessible using same query language as used to access the database’s data.

Rule 5: The Comprehensive Data Sub-language Rule

DBMS system must support at least one relation language which has linear syntax. This language can be used in direct way to query the database or using some application. Also it must support data definition operations, data manipulation operations and transaction management operations.

Rule 6: The View Updating Rule

All the views of the database that can be updated theoretically, must be updatable by the system.

Rule 7: High Level Insert, Update and Delete Rule

High-level insert, update and delete must be supported by the database system. Insert, update and delete must be supported for any retrievable set and not limited to single row in a single table. Data can be retrieved in terms of sets constructed from multiple rows and tables.

Rule 8: Physical Data Independence

Storage of physical data must be independent of the application which access the data.

Rule 9: Logical Data Independence

Its most difficult rule to apply which states that any change to the logical level of the database must not require a change in applications using this structure.

Rule 10: Integrity Independence

Integrity constraints must be stored in the catalog and provide a mechanism to make required changes to constraints without affecting the existing applications.

Rule 11: Distribution Independence

Distribution of the data to various locations must not be visible to the end user. Distributed version of DBMS system must continue operating with existing applications without any changes.

Rule 12: The Non-subversion Rule

RDBMS system must not allow to bypass the defined integrity constraints. It must not allow modifications in the structure of database except through the database language (SQL).

The above rules are very strict and the most popular RDBMSs of today do not fulfill all of them, especially rules 6, 9, 10, 11 and 12.

Below image describes the Codd Rule in pictured way:

Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. It provides high-end solution to store, retrieve and manage enterprise database with user friendliness of Microsoft. It delivers fast performance, powerful business insights and cloud capabilities to enterprise environments. SQL Server has a powerful administration and programming capability which makes it one of the most industry used RDBMS in today’s era. It is scalable and robust and widely used as a server database system which runs on a central server and allows multiple users simultaneously. Scalability of SQL Server stands it in the top row of most used RDBMSs for small groups as well as for enterprises.

Microsoft introduced first version of SQL Server in 1989 as SQL Server 1.0 followed with subsequent versions such as SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and the latest one is SQL Server 2014. SQL Server 2014 released with built in in-memory OLTP capabilities to boost performance. It delivers mission critical performance across all workloads, faster insights from any data, platform for hybrid cloud scenarios to build, deploy and manage enterprise level solutions for big as well as small organizations.

Microsoft SQL Server runs on T-SQL (Transact SQL) which is a proprietary extension of SQL (Structured Query Language) by Microsoft and Sybase. To communicate with Microsoft SQL Server, we need to send our commands as T-SQL statements. We will focus primarily on T-SQL throughout the chapters in this tutorial. In next chapter “Table Relationships“, we are going to discuss table relationships and their types in RDBMS.