SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications<You are Here>

Continuing from my previous post on this series, If you have missed any link please visit link below

We are going to Cover the Following Points in this article

• Database Languages and Interfaces
• DBMS Component Modules

Database Languages and Interfaces

§ As a database supports a number of user groups, DBMS must have languages and interfaces that support each of these user groups.

DBMS Languages

DDL – the data definition language, used by the DBA and database designers to define the conceptual and internal schemas.

§ The DBMS has a DDL compiler to process DDL statements in order to identify the schema constructs, and to store the description in the catalogue.

§ In databases where there is a separation between the conceptual and internal schemas, DDL is used to specify the conceptual schema, and SDL, storage definition language, is used to specify the internal schema.

§ For true three-schema architecture, VDL, view definition language, is used to specify the user views and their mappings to the conceptual schema. But in most DBMSs, the DDL is used to specify both the conceptual schema and the external schemas.

Data Manipulation Languages (DMLs)

§ Data Manipulation Language (DML) is a family of computer languages used by computer programs or database users to retrieve, insert, delete and update data in a database.

§ Currently, the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database.

§ Other forms of DML are those used by IMS/DL1, CODASYL databases(such as IDMS), and others.

§ Data manipulation languages were initially only used by computer programs, but (with the advent of SQL) have come to be used by people as well.

§ Data manipulation languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are "select", "insert", "update", and "delete".

§ Data manipulation languages tend to have many different "flavors" and capabilities between database vendors.

§ There has been a standard established for SQL by ANSI, but vendors still "exceed" the standard and provide their own extensions.

Two main types of DML: High-level/Non procedural

§ Can be used on its own to specify complex database operations.

§ DMBSs allow DML statements to be entered interactively from a terminal, or to be embedded in a programming language.

§ If the commands are embedded in a general purpose programming language, the statements must be identified, so they can be extracted by a pre-compiler and processed by the DBMS.

§ High-level DMLs, such as SQL can specify and retrieve many records in a single DML statement, and are called ‘set at a time’ or ‘set oriented DMLs’.

§ High-level languages are often called declarative, because the DML

often specifies what to retrieve, rather than how to retrieve it.

Low Level/Procedural

§ Must be embedded in a general purpose programming language.

§ Typically retrieves individual records or objects from the database and processes each separately.

§ Therefore it needs to use programming language constructs such as loops.

§ Low-level DMLs are also called ‘record at a time DMLS’ because of this.

DBMS Interfaces

Types of interfaces provided by the DBMS include: Menu-Based Interfaces for Web Clients or Browsing:

§ Present users with lists of options (menus)

§ Lead user through formulation of request

§ Query is composed of selection options from menu displayed by system.

Forms-Based Interfaces:

§ Displays a form to each user.

§ User can fill out a form to insert new data or fill out only certain entries.

§ Designed and programmed for naïve users as interfaces to canned transactions.

Graphical User Interfaces:

§ Displays a schema to the user in diagram form. The user can specify a query by manipulating the diagram. GUIs use both forms and menus.

Natural Language Interfaces:

§ Accept requests in written English or other languages and attempt to understand them.

§ Interface has its own schema, and a dictionary of important words. Uses the schema and dictionary to interpret a natural language request.

Interfaces for Parametric Users:

§ Parametric users have a small set of operations they perform.

§ Analysts and programmers design and implement a special interface for each class of naïve users.

§ Often a small set of commands are included to minimize the number of keystrokes required. (I.e. function keys)

Interfaces for the DBA:

§ Systems contain privileged commands only for DBA staff.

§ Includes commands for creating accounts, setting parameters, authorizing accounts, changing the schema, reorganizing the storage structures etc.

DBMS Component Modules

Stored Data Manager:

· Most important component of a DBMS

· Controls access to DBMS information stored on disk

· It is responsible for interfacing with file system.

· Converts User’s queries coming directly through the query processor to a physical file system.

· Synchronizing of the simultaneous operations performed by concurrent users is controlled by the data manager.

· It maintains consistency and integrity of the data

· It is responsible for back up and recovery, Concurrency control, security and integrity.

Query processor:

· It is used to convert online user’s query into an efficient series of operations and send it to the data manager for execution

· It uses data dictionary to find the structure of schema object [tables, index, and stored procedures].

DDL compiler:

· It processes schema definitions specified in the DDL and stores descriptions of the schema [Metadata] in the DBMS catalog.

· The catalogue includes information such as names of the files, data items, storage details of each file and constraints.

Run-time data base processor:

· It handles database access at run time.

· It receives retrieval or update operations and carries them out on the database.

Pre-compiler:

· DML commands from an application program written in a host programming language are extracted from a Pre-Compiler.

· These commands are sent to the DML compiler for compilation into object code for database access. The rest of the program is sent to the host language compiler.

Database System Utilities – DBA in managing the database system.

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |