Database Language and Interfaces
By
Database Language
▪
Data definition language (DDL)
•
Defines both schemas
▪
Storage definition language (SDL)
•
Specifies the internal schema
▪
View definition language (VDL)
•
Specifies user views/mappings to conceptual
schema
▪
Data manipulation language (DML)
Database Language
Once the design of the database is completed and a DBMS is chosen to implement the database, the first step is to specify conceptual and
internal schemas for the database and any mappings between the two.
In many DBMS where no strict separation of level is maintained, one language called the “Data definition language” is used by the DBA and the database designer to define both the schema(conceptual and internal).
Database Languages (cont.)
In DBMS where a clear separation is maintained between the conceptual and internal levels. The DDL is used to specify the conceptual schema only.
Another language, the storage definition language (SDL), is used to specify the internal schema.
For a true three-schema architecture, we would need a third language, the view definition language (VDL),to specify user views and their mappings to the conceptual schema.
In most DBMSs the DDL is used to define both conceptual and external schemas.
Database Languages (cont.)
Once the database schema are compiled and the database is populated with data, user must have some means to manipulate the database
(insertion, deletion and modification).
The DBMS provide a set of operation or a language called the “Data Manipulation Language”.
There are two type of DML languages:
High Level or Nonprocedural Language.
DML- Data Manipulation Language.
High Level or Nonprocedural:
This kind of DML is to specify the complex database operations concisely.
Low Level or Procedural:
Database Interfaces
Menu Based Interfaces and Web Clients or Browsing
These interfaces provide the user with the lists of options called (menus).
Pull down menus are very popular technique in Web-based Interfaces.
Lead user through formulation of request
Database Interfaces
Forms-Based Interfaces
Displays a form to each user.
User can fill out 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.
Database Interfaces
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 small set of operations they perform.
Analysts and programmers design and implement a special interface for each class of naïve users.
Database Interfaces
Interfaces for the DBA
Systems contain privileged commands only for DBA staff.
The Database System Environment
•
A higher-level stored data manager module of the
DBMS controls access to DBMS information stored
on the disk, whether it is part of the database or the
catalog.
The Database System Environment
•
The run-time database processor handles database
accesses at run time; it receives retrieval or updated
operations and carries them out on the database.
The Database System Environment
The
pre-compiler
extracts DML commands from an
application program written in a host programming language.
These commands are sent to the
DML compiler
for
Description of the DBMS components
The top half of the figure show how different users uses the database environment and interfaces.
The lower half of the figure show the internals of the DBMS responsible for storing the data and processing the transactions.
The database and DBMS catalogue are usually stored on disk. The
allocation of disk input/output devices is done by the Operating System (OS).
Catalogue include information such as names, size of file and data types of data items etc.
Database Language and Interfaces
The top half of the figure show how DBA staff and Casual users uses the interface to formulate the queries.
Application programmer who program using some host languages.
Parametric users who do data entry by supplying parameter to predefined transaction.
Once the DDL statement is generated, it is sent to DDL compiler.
Casual user and person with occasional need for information
from the database uses “Interactive Query”, interface.
These queries parsed and analysed for the correctness of the
operations for the model, the names of the data element. These
process is done by “Query Compiler”.
Rearrangement, reordering of operations, elimination of
redundancy is done by the “Query Optimizer”.
Application Programmer write the program into different languages and than submitted into the pre-compiler.
The pre-compiler extracts DML commands.
These commands are sent to DML compiler for compilation into object code for database access.
The rest of the program is sent to host language compiler.
DML commands and rest of the program are linked forming a command transaction whose executable code includes calls to the runtime database processor.
Database Utilities
•
To perform certain functions such as:
• Loading data stored in files into a database. Includes data conversion
tools.
• Backing up the database periodically on tape.
• Reorganizing database file structures.
• Report generation utilities.
• Performance monitoring utilities.
Other tool
•
Data dictionary / repository
:
• Used to store schema descriptions and other information such as design decisions, application program descriptions, user information, usage standards, etc.
• Active data dictionary is accessed by DBMS software and users/DBA.
Application Environment
Application Development Environments and CASE (computer-aided software engineering) tools:
Examples – Power builder (Sybase), JBuilder (Borland)
