DATABASE LANGUAGE
For supporting variety of users, a DBMS
must provide appropriate languages and
interfaces for each category of users to
express database queries and updates.
Once the design of the database is
complete and a DBMS is chosen to
implement the database, it is important to
first specify the conceptual and internal
schemas for the database and the
mappings between the two.
Following languages are used to specify
database schemas:
1) Data Definition Language (DDL)
2) Storage Definition Language(SDL)
3) View Definition Language(VDL)
4) Data Manipulation Language(DML)
5) Fourth-generation Language(4GL)
SQL = DDL+VDL+DML
DATA DEFINITION LANGUAGE:IT IS ALSO CALLED AS Data Description
Language.
It is a special language used to specify a
database conceptual schema using set of
definitions.
It supports the definition or declaration of
database objects (or data elements).
It allows the DBA or user to describe and
name
the
entities,
attributes
and
relationships required for the application,
together with any associated integrity and
security constraints.
Theoretically different DDLs are defined
for each schema in the three-level schemaarchitecture.
Various techniques are available for writing
data definition language
One widely used technique is writing DDL
into a text file.
Other methods use DDL Compiler or
interpreter to process the DDL file or
statements.
The result of the compilation of DDL
Statements is a set of tables stored in
specific file collectively called the system
log or data dictionary.
E.g.
CREATE TABLE PRODUCT
(PROD-ID CHAR(6),
PROD-DESC CHAR(20),
UNIT-COST NUMERIC(4));
E.g.
CREATE TABLE CUSTOMER
(CUST-ID CHAR(4),
CUST-NAME CHAR(20),
CUST-STREET CHAR(25),
CUST-CITY CHAR(15),
CUST-BAL NUMERIC(10));
E.g.
CREATE TABLE SALES
(CUST-ID CHAR(4),
PROD-ID CHAR(6),
PROD-QTY NUMERIC(3));
The execution of above DDL Statements will
create PRODUCT, CUSTOMER, AND SALES
tables, as shown in the following diagrams
(a), (b), & (c).
Diagram (a):-
PRODUCT
PROD-ID
PROD-DESC
UNIT-COST
Diagram (b):-
CUSTOMER
CUST-ID CUSTNAME
CUSTSTREET
CUSTCITY
CUSTBAL
Diagram (c):-
SALES
CUST-ID
PROD-ID
PRODQTY
PRODPRICE
Data Storage
(DSDL):-
Definition
Language
It is used to specify the internal schema in
the database.
The mappings between the conceptual
schema (a specified by DDL) and the
internal schema(as specified by DSDL) may
be specified in either on of these languages.
VIEW DEFINITION LANGUAGE(VDL):It is used to specify users view (external
Schema) and their mappings to the
conceptual schema.
In most DBMSs, DDL is used to specify
both conceptual and external schemas.
There are two views of data.
1) Logical View(Programmer)
2) Physical View(that way that data
is actually stored on disk).
DATA MANIPULATION LANGUAGE(DML):It is a mechanism that provides a set of
operations to support the basic data
manipulation operations on the data held in
the database.
It helps in communicating with DBMS.
Data Manipulation applies to all the three
(conceptual, internal, and external) levels of
schema.
The part of DML that provides retrieval is
called query language.
The DML provides following functional access
(or manipulation operations) to the
database.
1)
Retrieve data and/or records from
database files.
2) Delete records from database files.
3) Retrieve records sequentially in the key
sequence.
4)
Retrieve records in the physically
recorded sequence.
5) Rewrite records that have been
updated.
6) Modify data and/or record in the
database files.
PRODUCT
PRODUCT-ID
PRODUCT-DESC
UNIT-COST
A12345
STEEL ALMIRAH
4000
B23412
DRYER
4500
B44332
FREEZE
6000
A98765
STEEL TABLE
3500
A29834
STEEL CHAIR
4800
C11008
IRON MOULDING
5100
CUSTOMER
CUSTID
CUSTNAME
1001
CUSTSTREET
CUSTCITY
CUSTBAL
Waterhous Box 41,
e ltd.
Mumbai
Mumbai
65000.
00
1000
KLY
System
Chicago
40000.
00
1005
Megapoint C-12, Pataya,
s
Goa
Goa
84000.
00
1010
Concept
Shapers
32, Main
Road, Ranchi
Mumbai
10500.
00
1006
Trinity
Agencies
P.O.Box 266,
Tokyo
Delhi
11200.
00
41, 1st street,
Chicago
SALES
CUST-ID
PROD-ID
QTY
UNITPRICE
1001
A12345
100
6,700
1000
B23412
250
4,000
1010
B44332
120
14,000
1005
A98765
110
5,500
1001
A29834
300
12,999
E.g.
SELECT
FROM
WHERE
PRODUCT.PROD-DESC
PRODUCT
PROD-ID=B4432;
OUTPUT IS
B44332
FREEZE
E.g.
SELECT
FROM
WHERE
CUSTOMER.CUST-ID,
CUSTOMER.CUST-NAME
CUSTOMER
CUST-CITY =Mumbai;
OUTPUT IS
1001
Waterhouse Ltd.
1010
Concept Shapers
E.g.
SELECT
CUSTOMER.CUST-NAME
CUSTOMER.CUST-BAL
FROM
SALES.PROD-ID
WHERE
SALES.PROD-ID=B23421
AND
CUSTOMER.CUSTID=SALES.CUST-ID;
OUTPUT IS
KLY System
40000.00
There are two ways of accessing
retrieving) data from the database.
(or
1)
Procedural DML (or Embedded
Statements)
2) Non-procedural DML (or declarative
language).
Fourth-Generation Language (4GL):It is a compact, efficient and nonprocedural programming language that
is used to improve the productivity of
the DBMS.
In 4GL, the user defines what is to
be done and not how it is to be done.
The 4GL depends on higher-level
4GL tools.
The 4GL has the following components
inbuilt in it.
Query Languages
Report generators
Spreadsheets
Application generators
High-level
languages
application program.
to
generate
SQL and QBE are the examples of fourthgeneration language.
