Chapter 2 :Overview of

Module 1 Database Languages

and Architectures
Outline :
Data Models and Their Categories

History of Data Models

Schemas, Instances, and States

Three-Schema Architecture

Data Independence

DBMS Languages and Interfaces

The Database System Environment

 2.1 Data Models
Data Model:

• A set of concepts to describe the structure of a database, the operations for manipulating these
structures, and certain constraints that the database should obey.

Data Model Structure and Constraints:

• Constructs are used to define the database structure

• Constructs typically include elements (and their data types) as well as groups of elements (e.g.
entity, record, table), and relationships among such groups
• Constraints specify some restrictions on valid data; these constraints must be enforced at all times

Data Model Operations:

• These operations are used for specifying database retrievals and updates by referring to the
constructs of the data model.
• Operations on the data model may include basic model operations (e.g. generic insert, delete,
update) and user-defined operations (e.g. compute_student_gpa, update_inventory)
 1. Conceptual (high-level, semantic) data models:
• Provide concepts that are close to the way many users perceive
data.
• (Also called entity-based or object-based data models.)
2. Physical (low-level, internal) data models:
• Provide concepts that describe details of how data is stored in the
computer. These are usually specified in an ad-hoc manner
through DBMS design and administration manuals
Categories of Data 3. Implementation (representational) data models:
Models • Provide concepts that fall between the above two, used by many
commercial DBMS implementations (e.g. relational data models
used in many commercial systems).
4. Self-Describing Data Models:
• Combine the description of data with the data values. Examples
include XML, key-value stores and some NOSQL systems.
 ➢ Database Schema:
• The description of a database
• Includes descriptions of the database structure, data types, and the
constraints on the database.
➢ Schema Diagram:
• An illustrative display of (most aspects of) a database schema.
➢ Schema Construct:
2.2 Schemas • Each object in schema is called a schema construct, e.g., STUDENT,
and Instances COURSE.
➢ Database State:
• The actual data stored in a database at a particular moment in
time. This includes the collection of all the data in the database.
• Also called database instance (or occurrence or snapshot).
• The term instance is also applied to individual database
components, e.g. record instance, table instance, entity
instance
 ➢ Database State:
• Refers to the content of a database at a moment in time.
➢ Initial Database State:
• Refers to the database state when it is initially loaded into the
system.
2.2 Schemas ➢ Valid State:
And • A state that satisfies the structure and constraints of the
database.
Instances ➢ Distinction
• The database schema changes very infrequently.
• The database state changes every time the database is updated.
• Schema is also called intension.
• State is also called extension.
Example of a
Database
Schema
Example of a
database
state
2.3 Three-Schema Architecture

Proposed to support DBMS characteristics of:

• Program-data independence.
• Support of multiple views of the data.

This Architecture defines DBMS schemas at three levels:

1. Internal schema at the internal level to describe physical storage structures and access paths
(e.g indexes).
• Typically uses a physical data model.
2. Conceptual schema at the conceptual level to describe the structure and constraints for the
whole database for a community of users.
• Uses a conceptual or an implementation data model.
3. External schemas at the external level to describe the various user views.
• Usually uses the same data model as the conceptual schema.
 The three-
schema
architecture
Three-Schema Architecture

Mappings :
• The process of transforming requests and results between levels are called
mappings.
• Programs refer to an external schema and are mapped by the DBMS to the
internal schema for execution.
• Data extracted from the internal DBMS level is reformatted to match the
user’s external view (e.g. formatting the results of an SQL query for display
in a Web page)
Data Independence

• Defined as the capacity to change the schema at one level without changing schema at next
level.
• There are 2 types of data independence.
i. Logical Data Independence:
• The capacity to change the conceptual schema without having to change the
external schemas and their associated application programs.
ii. Physical Data Independence:
• The capacity to change the internal schema without having to change the
conceptual schema.
• For example, the internal schema may be changed when certain file structures are
reorganized or new indexes are created to improve database performance
• When a schema at a lower level is changed, only the mappings between this schema and
higher-level schemas need to be changed in a DBMS that fully supports data independence.
2.4 Database DBMS Languages :
Languages 1. Data Definition Language (DDL) :
and Interfaces • Used by DBA and database designer to define schemas.
• DDL compiler – process DDL statements.
• Used to specify conceptual schema only.
2. Storage Definition Language (SDL) :
• Used when clear separation is maintained between the conceptual
and internal levels.
• Used to specify the internal schema.
3. View Definition Language (VDL) :
• Used to specify user views and their mappings to the conceptual
schema.
4. Host Language :
• When DML commands are embedded in a general-purpose
programming language , we call it is as Host language.
2.4 Database
4. Data Manipulation Language (DML) :
Languages • Used to perform manipulation operation like retrieval, Insertion,
and Interfaces deletion, modification of data.
Types of DML :
• High Level or Non-procedural Language:
• Called as “Set-oriented” or “set-at-a-time” DML’s.
• Used to specify what data to retrieve rather than how to retrieve it.
• Also called declarative languages.
• Low Level or Procedural Language:
• Retrieve data one record-at-a-time;
• Constructs such as looping are needed to retrieve multiple records,
along with positioning pointers.
.
2.4 Database
Languages DBMS Interfaces :
and Interfaces 1. Menu-based Interfaces for Web clients or Browsing
2. Apps for Mobile Devices – banking ,Reservation
3. Forms-based Interfaces – displays form to user
4. Graphical User Interfaces
5. Natural Language Interfaces – accepts req written in English lang
6. Keyword-based database Search – web search engine(index)
7. Speech Input and Output – limited applications(ex:airlines)
8. Interfaces for Parametric Users – bank tellers
9. Interfaces for the DBA – privileged commands(creating
account,setting parameters,granting access)
2.5 The Database System Environment

2.5.1 DBMS Component Modules :

• It consists of 2 parts.
• Top part – refers to various users of DBMS
• Lower part – refers to internal part of DBMS (storage and transactions).
• Components :
• DDL Compiler → compiles schema definitions
• Interactive query interfaces → information from the database for casual user
• Query compiler → compiles query for casual user
• Query optimizer → rearrangement and reordering of opeations
• Precompiler → extract DML commands from app written in host lang.
 • Host language compiler.
• Runtime database processor :
(1) the privileged commands

(2) the executable query plans, and

(3) the canned transactions with runtime parameters.

• Stored data manager.

• Concurrency control and backup and recovery systems.

2.5.2 Database System 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.
• Performance monitoring utilities.
• Report generation utilities.
• Other functions, such as sorting, user monitoring, data compression, etc.
Component
Modules of
DBMS
2.5.3 Other Tools :
• 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.
• Passive data dictionary is accessed by users/DBA only.
• Application Development Environments and CASE (computer-aided software engineering) tools:
• Examples:
• PowerBuilder (Sybase)
• JBuilder (Borland)
• JDeveloper 10G (Oracle)