Database Management System

Introduction

A database is an organized collection of logical data. DBMS or Database Management System is the system software that stores and sustains the database. Database Management System is a window that allows users to interact with the database.

Major industries like Telecom, Hotel, Airlines, Textiles, Health, Education, Insurance uses some or the other DBMS software. There are wide varieties of DBMS software available in the market. Database systems like MS-SQL Server, DB2, SAP, CouchDB, PostgreSQL, SQLite, Sybase, RethinkDB, Berkeley, Redis, mongoDB, MySQL, Oracle, MS-Access are popular DBMS available in the market.

History

In the period of 1960s, Charles Bachman designed the first general purpose DBMS named as 'Integrated Data Source' which formed the basis of Network Data Model. He received ACM's Turing Award for his work in 1973.

In late 1960s, IBM developed Information Management System (IMS) DBMS for NASA's Apollo space program. This formed the basis of Hierarchical data model.

In 1970, Edgar Codd, at IBM, introduced Relational Data Model. The relational DBMS benefits and utilities were universally accepted by the industries and the corporate world for storing the data. Relational DBMS is more user-friendly and easy to use as compared to network and hierarchical data model.

Structured Query Language in DBMS

SQL is an ANSI and ISO standard language, developed to interact with the database. Earlier, SQL was popular as SEQUEL. SQL includes language elements like clauses, expressions, predicates, statements, and queries. SQL communicates with the database via DDL, DML, DCL, and TCL. The data structures and schemes are defined under DDL or Data Definition Language. DML or Data Manipulation Language includes insert, update, select and deletion of records. DCL or Data Control Language authorizes data access to users by Grant and Revoke statements. TCL or Transaction Control Language includes data transactions.

Elements Of DBMS

1. Physical database
2. Database engine
3. Database scheme
4. Schema objects
5. Tables
6. Fields
7. Records
8. Keys
9. Relationships
10. Data types

Advantages of DBMS

1. Data security and consistency are very important when multiple users access the database simultaneously.
2. Data retrieval is fast.
3. Developing and maintaining DBMS is easier as compared to the file system.
4. There is an inbuilt utility of backup and recovery when the system crashes.

Disadvantages of DBMS

1. Complexity increases with increase in the size of data.
2. Maintenance is expensive. 

What is the difference between executing a dynamically built SQL statement @SQL (NVARCHAR(4000) with “EXEC” statement as opposed to the “sp_executesql” statement

- sp_executesql gives the possibility to use parameterised statements, EXECUTE does not.
- Parameterised statements gives no risk to SQL injection and also gives advantage of cached query plan.
- The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters used.
- When you use thesp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.
- sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.