Table of Contents
A database system provides a data definition language to specify the database
schema and a data manipulation language to express database queries and updates.
In practice, the data definition and data manipulation languages are not two
separate languages; instead they simply form parts of a single database language,
such as the widely used SQL language.
We specify a database schema by a set of definitions expressed by a special
language called a data-definition language (DDL). For instance, the following
statement in the SQL language defines the account table:
create table account
Execution of the above DDL statement creates the account table. In addition, it
updates a special set of tables called the data dictionary or data directory. A data dictionary contains metadata—that is, data about data. The schema of a
table is an example of metadata. A database system consults the data dictionary
before reading or modifying actual data.
We specify the storage structure and access methods used by the database system
by a set of statements in a special type of DDL called a data storage and definition
language. These statements define the implementation details of the database
schemas, which are usually hidden from the users.
The data values stored in the database must satisfy certain consistency
constraints. For example, suppose the balance on an account should not fall below
$100. The DDL provides facilities to specify such constraints. The database systems
check these constraints every time the database is updated.
Data manipulation is
• The retrieval of information stored in the database
• The insertion of new information into the database
• The deletion of information from the database
• The modification of 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
• Procedural DMLs require a user to specify what data are needed and how to
get those data.
• Declarative DMLs (also referred to as nonprocedural DMLs) require a user
to specify what data are needed without specifying how to get those data.
Declarative DMLs are usually easier to learn and use than are procedural DMLs.
However, since a user does not have to specify how to get the data, the database
system has to figure out an efficient means of accessing data. The DML component of
the SQL language is nonprocedural.
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
datamanipulation language synonymously.
This query in the SQL language finds the name of the customer whose customer-id is
where customer.customer-id = 192-83-7465
The query specifies that those rows from the table customer where the customer-id
is 192-83-7465 must be retrieved, and the customer-name attribute of these rows
must be displayed. If the query were run on the table in Figure 1.3, the name
Johnson would be displayed.
Queries may involve information from more than one table. For instance, the
following query finds the balance of all accounts owned by the customer with
from depositor, account
where depositor.customer-id = 192-83-7465 and
depositor.account-number = account.account-number
If the above query were run on the tables in Figure 1.3, the system would find that
the two accounts numbered A-101 and A-201 are owned by customer 192-83-7465
and would print out the balances of the two accounts, namely 500 and 900. There
are a number of database query languages in use, either commercially or
Database Access from Application Programs
Application programs are programs that are used to interact with the database.
Application programs are usually written in a host language, such as Cobol, C, C++,
or Java. Examples in a banking system are programs that generate payroll checks,
debit accounts, credit accounts, or transfer funds between accounts.
To access the database, DML statements need to be executed from the host language.
There are two ways to do this:
• By providing an application program interface (set of procedures) that can
be used to send DML and DDL statements to the database, and retrieve the
The Open Database Connectivity (ODBC) standard defined by Microsoft for
use with the C language is a commonly used application program interface
standard. The Java Database Connectivity (JDBC) standard provides
corresponding features to the Java language.
• By extending the host language syntax to embed DML calls within the host
language program. Usually, a special character prefaces DML calls, and a
preprocessor, called the DML precompiler, converts the DML statements to
normal procedure calls in the host language.