Database Languages

A database system provides a Data-Definition Language (DDL) to specify

the database schema, and a Data-Manipulation Language (DML) to
express database queries and updates. In practice, these languages are
not two separate languages instead they simply form parts of a single
database language, such as the widely used SQL language.
Data-Definition Language (DDL): A database schema is specified by a set
of definitions expressed by a special language called a “Data-Definition
Language”. The result of compilation of DDL statements is a set of
table(s) that is stored in a special file called “data dictionary” or “data
directory”. Srinivas Jangili
Ex: The following statement in the SQL language defines the customer
table: CREATE TABLE customer(
customer_id char(5),
customer_name varchar(20),
customer_street varchar(20),
customer_city varchar(20),
account_number numeric(8,2),
balance numeric(8,2))
Execution of the above DDL statement creates the customer
table.
A data dictionary is a file that contains “meta data” i.e. data
about data. The schema of a table is an example of metadata.
We specify the storage structure and access methods used
by the database system are specified by a set of definitions in a
special type DDL called a “data storage and definition” language.
These statements define the implementation details of the
Srinivas Jangili
database schemas, which are usually hidden from the users.
We can perform the following operations on database using DDL:
* To CREATE the database instance
* To ALTER the structure of database
* To DROP database instances
* To DELETE tables in a database instance
* To RENAME database instances
 All of these commands either defines or update the
database schema that’s why they come under Data
Definition Language.
Data-Manipulation Language (DML):
The DML is used for accessing and manipulating data in a
database. The following operations on database comes
under DML:

Srinivas Jangili
* To RETRIEVE the information stored in the database
* To INSERT the new information into the database
* To DELETE the information from the database
* To MODIFY the information stored in the database
A data-manipulation language (DML) is a language that
enables users to access or manipulate data as organized by
the appropriate data model. There are basically two types
of DMLs:
Procedural DMLs require a user to specify what data are needed
and how to get those data.
Non-procedural DMLs require a user to specify what data are
needed without specifying how to get those data.
Non-procedural DMLs are usually easier to learn and use than are
procedural DMLs. However, since a user doesn't have to specify
how to get the data, these languages may generate code that is
not as efficient as that produced by procedural languages. We can
remedy this difficulty through various optimization techniques,
Srinivas Jangili
several of which will be discussed in the chapter “query
processing”. Nonprocedural languages are also called declarative
languages.
A query is a statement requesting the retrieval of
information. The portion of a DML that involves information
retrieval is called a query language. Although technically
incorrect, it is common practice to use the terms “query
language” and “data-manipulation language” synonymously.
Example: The following query in the SQL language find the name
of the customer whose customer_id is 192-83-7465:
SELECT customer.cuctomer_name
FROM customer
WHERE customer.cuctomer_id=‘192-83-7465’
The above query specifies that those rows FROM the table
customer WHERE the customer_id 192-83-7465 must be
Srinivas Jangili
retrieved, and the customer_name attribute of these rows must
be displayed.
After execution of the above query, the name Johnson would be
displayed. Queries may involve information from more than one
table.
There are a number of database query languages in use, either
commercially or experimentally. We study most widely used
query language SQL in Chapter-4.
 Data Storage, Querying and Time Management
A database system is partitioned into modules that deal with each
of the responsibilities of the overall system. The functional
components of a database system can be broadly divided into the
storage manager, the query processor, the time management
components.
Storage Management
The storage manager is important because databases typically
require a large amount of storage space. Corporate databases
Srinivas Jangili
range in size from hundreds of gigabytes to terabytes of data (for
largest databases). Since the main memory of computers can’t
store this much information, the information is stored on disks.
Data are moved between disk storage and main memory as
needed. Since the movement of data to and from disk is slow
relative to the speed of the CPU, it is imperative that the database
system structure the data so as to minimize the need to move
data between disk and main memory.
A storage manager is a program module of a database system that
provides the interface between the low-level data stored in the database
and the application programs and queries submitted to the system. The
storage manager is responsible for the interaction with the file manager.
The raw data are stored on the disk using the file system, which is
usually provided by a conventional operating system. The storage
manager translates the various DML statements into low-level file
system commands. Thus, storage manager is responsible for storing,
retrieving, and updating data in the database.
Srinivas Jangili
The storage manager components include:
Authorization and integrity manager, which tests for the satisfaction of
integrity constraints and checks the authority of users to access data.
Transaction manager, which ensures that the database remains in a
consistent (correct) state despite system failures, and that concurrent
transaction executions proceed without conflicting.
File manager, which manages the allocation of space on disk storage
and the data structures used to represent information stored on disk.
Buffer manager, which is responsible for fetching data from disk storage
into main memory, and deciding what data to cache in main memory.
The buffer manager is a critical part of the database system, since it
enables the database to handle data sizes that are much larger than the
size of main memory.
The storage manager implements several data structures as part of the
physical system implementation:
Data files, which store the database itself.
Data dictionary, which stores metadata about the structure of the
Srinivas Jangili
database, in particular the schema of the database.
Indices, which provide fast access to data items. Like the index in the
textbooks, a database index provides pointers to those data items that
hold a particular value.
Ex: We use an index to find the instructor record with a particular ID, or
all instructor records with a particular name. Hashing is an alternative to
indexing that is faster in some but not all cases.
Statistical data, which store statistical information about the data in the
database. This information is used by the query processor to select
efficient ways to execute a query.
 The query processor
The query processor is important because it helps the database system
simplify and facilitate access to data. High-level views help to achieve this
goal; with them, users of the system are not burdened unnecessarily with
the physical details of the implementation of the system. However, quick
processing of updates and queries is important. It is the job of the database
system to translate updates and queries written in non-procedural
language, at the logical level, into an efficient sequence of operations at the
physical level.
The query processor components include:
• DDL interpreter, which interprets DDL statements and records the
Srinivas Jangili
definitions in the data dictionary.
• DML compiler, which translates DML statements in a query language into an
evaluation plan consisting of low-level instructions that the query
evaluation engine understands.
A query can usually be translated into any of a number of alternative
evaluation plans that all give the same result. The DML compiler also
performs query optimization; that is, it picks the lowest cost evaluation plan
from among the alternatives.
• Query evaluation engine, which executes low-level instructions generated
by the DML compiler.
 Transaction Management
A transaction is a single logical unit of program execution
that access and possibly updates various data items. Usually,
a transaction is initiated by a user program written in a high
level Data Manipulation Language (SQL) or programming
language (COBOL, C, C++, JAVA, etc.) where it is delimited by
statements begin transaction and end transaction. The
transaction consists of all operations executed between the
Srinivas Jangili
begin transaction and end transaction.
To ensure integrity of the data, we require that the database
system maintain the following properties of the transaction:
(i) Atomicity
(ii) Consistency
(iii) Isolation
(iv) Durability
(i) Atomicity: Either all operations of the transaction are
reflected properly in the database, or none are.
Ex: Suppose in a fund transfer, one account (say A) is debited and
another account (say B) is credited. Clearly, it is essential that
either both the credit and debit occur, or that neither occur. That
is the fund transfer must happen in its entirety or not at all. This
all-or-none requirement is called “atomicity”.
(ii) Consistency: Execution of a transaction in isolation (i.e. No
other transaction executing concurrently) preserves the
Srinivas Jangili
consistency of the database.
Ex: In the above fund transfer, the value of the sum A+B must be
preserved. This correctness requirement is called “consistency”.
(iii) Isolation: Even though multiple transactions may execute
concurrently, the system guarantees that for every pair of
transactions Ti and Tj, it appears to Ti that either Tj finished
execution before Ti started, or Tj started execution after Ti
finished. Thus each transaction is unaware of the other
transactions executing concurrently in the system.
 (iv) Durability: After a transaction completes successfully, the
changes it has made to the database persist, even if there are
system failures.
Ex: After the successful execution of a funds transfer, the new
values of accounts A and B must persist, despite the possibility of
the system failure. This persistency requirement is called
“durability”.
These properties are often called the “ACID” properties. The
acronym is derived from the first letter of each of the FOUR
properties.
Srinivas Jangili
 Transaction-management component ensures the database
remains in a consistent (correct) state despite system failures (ex.
power failures and operating system crashes) and transaction
failures.
 Concurrency-control manager controls the interaction among the
concurrent transactions, to ensure the consistency of the
database.
Database systems designed for use on small personal computers
may not have all these features.
• The concept of a transaction has been applied broadly in
database systems and applications. While the initial use of
transactions was in financial applications, the concept is now
used in real-time applications in telecommunication, as well
as in the management of long-duration activities such as
product design or administrative workflows.

Srinivas Jangili
 Database Users and Administrators
A primary goal of a database system is to provide an environment
for retrieving information from and store new information into
the database. People who work with a database can be
categorized as database users (or) database administrators.
Database Users and User Interfaces:
There are FOUR different types of database system users,
differentiated by the way that they expect to interact with the
system. Different types of user interfaces have been designed for
Srinivas Jangili
the different types of users.
(i) Naïve users are unsophisticated users who interact with the
system by invoking one of the application programs that have
been written previously. Naïve users are lowest level of users.
Ex: For example, a clerk in the university who needs to add a new
instructor to department A invokes a program called new hire.
This program asks the clerk for the name of the new instructor,
his/her new ID, the name of the department (that is, A), and the
salary.
 Srinivas Jangili
(ii) Application Programmers are computer professionals who
write application programs. Application programmers can
choose from many tools to develop user interfaces. These
programmers are actually responsible for writing the
programs/codes where as the naïve users merely using it.
Programmers fully understand the query language and they
know how to develop the database designs.
(iii) Sophisticated users (analysts) interact with the system
without writing programs. Instead, they form their requests
either using a database query language or by using tools such as
data analysis software. Analysts who submit queries to explore
data in the database fall in this category. The analysts are
responsible for the design of database schema, different
constraints, authorizations, etc. They are expert in designing
various kinds of query tools as well. Analysts are higher level of
programmers.
Srinivas Jangili
(iv) Specialized users are sophisticated users who write
specialized database applications. These applications include
computer-aided design (CAD) systems, computer-aided
manufacturing (CAM) systems that store data with complex data
types (for example, graphics data and audio data), and
environment-modeling systems.
 Database Administrator (DBA)
One of the main reasons for using DBMSs is to have central control of
both the data and the programs that access those data. A person who
has such central control over the system is called a database
administrator (DBA).
The functions of a DBA include:
1. Schema definition: The DBA creates the original database schema
by executing a set of data definition statements in the DDL.
2. Schema and physical-organization modification: The DBA carries out
Srinivas Jangili
changes to the schema and physical organization to reflect the
changing needs of the organization, or to alter the physical
organization to improve performance.
3. Granting of authorization for data access: By granting different
types of authorization, the database administrator can regulate which
parts of the database various users can access. The authorization
information is kept in a special system structure that the database
system consults whenever someone attempts to access the data in the
system.
 4. Routine maintenance: Examples of the database administrator’s
routine maintenance activities are:
 Periodically backing up the database, either onto tapes or onto remote
servers, to prevent loss of data in case of disasters such as flooding,
earthquake, thunderstorm, etc.
 Ensuring that enough free disk space is available for normal operations,
and upgrading disk space as required.
 Monitoring jobs running on the database and ensuring that
Srinivas Jangili
performance is not degraded by very expensive tasks submitted by
some users.
 DBA privileged commands include commands for granting and revoking
privileges to individual accounts, users, or user groups and for
performing the following types of actions.
a) Account creation: This action creates a new account and password
for a user or a group of users to enable them to access the DBMS.
b) Privilege granting: This action permits the DBA to grant certain
accounts.
c) Privilege revocation: This action permits the DBA to revoke
(cancel) certain privileges that were previously given to certain
accounts.
d) Security level assignment: This action consists of assigning user
accounts to the appropriate classification level.
The DBA is responsible for the overall security of the
database system. Action 1 in the preceding list is used to control
access to the DBMS as a whole, whereas actions 2 and 3 are used
Srinivas Jangili
to control discretionary database authorizations, and action 4 is
used to control mandatory authorization.
Database Architecture:

Srinivas Jangili