A Discussion On RDBMS And SQL Server

The main objective of DBMS (Database Management System) is to provide a structured way to store and retrieve information that is both convenient and efficient. By data, we mean known facts that can be recorded and that have embedded meaning. Every software needs to save its data hence, having a database can be laid as the crucial part of any software or website.

Why Use DBMS?

As stated earlier, DBMS is surely one of the easy and convenient ways of managing a database, but this is definitely not a single reason to go for it. There are rather many:

  • To develop software applications In less time,
  • Data independence and efficient use of data,
  • Uniform data administration,
  • Data integrity and security,
  • Concurrent access to data, and data recovery from crashes,
  • To use user-friendly declarative query language.

Where Is Database Management System (DBMS) being Used?

After analyzing the importance of DBMS, it is equally vital to know about its usage. Learning about the how and where of DBMS processing can help further:

  • Airlines: reservations, schedules, etc,
  • Telecom: calls made, customer details, network usage, etc,
  • Universities: registration, results, grades, etc,
  • Sales: products, purchases, customers, etc,
  • Banking: all transactions, etc,
  • Companies: records, etc.

Microsoft developed SQL Server (Structured Query Language) and pronounced as SEQUEL. SQL Server is a relational database management system or RBDMS and as a product, it is a database server with a primary function of storing and retrieving data for a request.

Insert, Select, Update, Delete of database records is done in SQL programming. But, this doesn’t mean SQL cannot do things beyond that.

In fact, it can do a lot of things including, but not limited to, optimization and maintenance of databases.

In reality, there are many Relational databases like MySQL Database, Oracle, MS-SQL Server, Sybase, etc use SQL as their foundation.

What Is a SQL Server Database Management System?

Database Management System (DBMS) is a collection of programs that enable their users to create and access the database, manipulate data, and help with the reporting/representation of data. DBMS is best used for developing .NET based software with a dynamic data flow.

According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. Given below is an example of dynamic data-driven websites in which the database tier is one of the key components.

Dynamic data-driven websites in which database tier is one of the key component

The RDBMS tends to manage the data and the database engine that allows accessed, locked and modified the data. The database schema defines the database’s logical structure. These three foundational elements help provide concurrency, security, data integrity, and uniform administration procedures. The Typical database administration tasks supported by the DBMS include performance monitoring/tuning and backup, change management, and recovery.

The database is a collection of objects such as table, view, stored procedure, function, trigger, etc.

Tables

As per the MSDN definition, tables are database objects that contain all the data in a database. In the tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. Tables are further divided into categories like Partitioned Tables, Temporary Tables, System Tables, and Wide Tables.

Database Table for PersonsView

View is a virtual table, and its content is defined by a query that is returned at the time of its creation. Views are further divided into three categories like Indexed,  Partitioned, System

Database View - Create View for product listsStored Procedure

A stored procedure in SQL Server is a group of one or more Transact-SQL statements or a reference to a Microsoft .NET Framework common runtime language (CLR) method and its a pre-compiled object which means its faster in performance because it doesn’t need to create an execution plan every time because it stores its execution plan of first time and uses it again and again until you modify its definition.Stored Procedure: pre-compiled object

Function

SQL Server functions are routines that do or do not accept parameters, perform an action, such as a complex calculation and return the result of that action as a value. The return value can either be a single scalar value or a result set.Functions: SQL Server function GetSummaryBlobContainers

Types Of Database

Type Of SQL Server Database

In MS SQL Server, there are two types of databases.

  • System databases
  • User Databases

System Databases

MS-SQL Server installation creates System databases automatically on a computer.

Given below are the types of System database:

  1. Master
  2. Model
  3. MSDB
  4. Tempdb
  5. Resource (Introduced in 2005 version)
  6. Distribution (It’s for Replication feature only)

User Databases

create database command

The user creates the database for their usage is called user database.

Description of all System Type Databases

Below is a description of all system type databases.

Master Database

The master database contains all information of the system for SQL Server – credential of users, linked servers, and other system-wide configuration settings. In the master database, SQL Server stores information about the other databases on this instance and the location of files. If the master database is not there, SQL Server cannot start.

Model Database

The model database is used as a template whenever a new user database is created. One can change most database properties, stored procedures, create users, views, tables, etc – whatever you do will be applied to any new databases. The nice thing is that you can create a guaranteed set of users, stored procedures, and options by making changes to the model database. Once those changes are in place, they are applied to every new database.

MSDB Database

The MSDB is an important system database in the Microsoft SQL Server. The MSDB database is mostly known as “the SQL Server Agent database” because it stores information of all SQL Agent jobs as their configuration and their execution histories like maintenance plans, SQL server jobs, service broker, mail, user, and system database backup history, etc. It is also used by the database engine and management studio.

Tempdb Database

The Tempdb system database is regarded as a global resource that is available for all users connected to the instance of SQL Server. Temporary user objects that are explicitly created example global or local temporary tables, temporary stored procedures, table variables, or cursors.

The temp table is stored in Tempdb until the connection is dropped (in the case of global temp tables, the last connection using it is dropped). You can also (and it is a good practice to do so) manually drop the table when you are finished using it with a drop table statement.

Resource Database

Microsoft introduces the Resource database in SQL Server 2005. The Resource database is termed as a read-only database that contains the use of all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Distribution Database

The distribution database stores metadata and history data for all types of replication, and transactions for transactional replication.

Characteristics Of DBMS

Data maintained or organized in file formats is traditionally quite difficult. Now, RDBMS is a new concept of all the research that was done to make it overcome the defects of the traditional style of data management. RDBMS has the following characteristics:

Real-World entity

RDBMS is more realistic and its architecture is a basis of real-world entities. It uses the behavior and attributes too.

Relation-Based Tables

RDBMS allows for maintaining relations among entities to form tables. This is so that the user can understand the architecture of a database just by looking at the tables.

Isolation of Data

In the database systems, the isolation factor determines how transaction integrity is visible to other users and systems.

Less Redundancy

RDBMS allows normalization of databases by splits of relation when any of its attributes are having redundancy in values.

Consistency

Consistency is a state where every relation in a database remains consistent. There exist methods and techniques for this to happen. A DBMS can provide greater uniformity in comparison to earlier forms of data storing applications like file-processing systems.

Query Language

A query language is the equipment of RDBMS, which makes it more efficient to retrieve and manipulate data.

Multiuser

DBMS supports a multi-user environment and allows them to access it.

Views Multiple

DBMS offers multiple views for users. This feature enables the users to have a concentrated view of the database according to their requirements.

ER (Entity-Relationship) Model

The Entity-Relationship model is based on real-world entities and their relations between them. So, pictorial representations of entities and relations are called ER Model. As ER diagram is pictorial, so anyone who has knowledge of databases can easily understand the whole database structure quickly by just watching it.

The ER Model includes :

  1. Entities and their attributes.
  2. Relationships among entities.

The ER Model and what it includes

Entity

An entity is can be one of any object in the real world and its features are unknown as an attribute whether that is a living thing or not. For example, for a hotel database, employee, management, services, and a client is an entity.

Relationship

The organizing entities among each other with a help of relationship. For example, a Doctor works_at a Hospital, a Robert enrolls in a course. Works_at and Enrolls are relationship entities.

There are many types of relations like one-to-one, one-to-many, and many-to-many relationships.

There are many topics under Database Management System which I will discuss in my further blogs.

Any Questions, confusion, or suggestions? Feel Free to add your valuable comments in the same regard!

Follow me!
Latest posts by Imran Saifi (see all)

6 thoughts on “A Discussion On RDBMS And SQL Server”

Leave a Comment