Lec 3

DBMS
DBMS_Week 3-4
Three-Schema Architecture
– Internal schema (one view)
• describes physical storage structures
• access paths, indexes used
• Typically uses a physical data model
– Conceptual schema at the conceptual level ( one view)

• describes the logical structure and constraints for the whole database for a
community of users
• Uses a conceptual or an logical data model
– External schemas (many view)

• Many views describe how users see data
Information about schemas is stored in the system catalog
Three-Schema Architecture (Contd.)
• Mappings among schema levels are needed to transform requests and data.
Programs refer to an external schema, and are mapped by the DBMS to the
internal schema for execution  data independence
Three-Schema Architecture
(Contd.)
• Proposed to support DBMS characteristics of:

• Program-data independence.
• Support of multiple views of the data.
Mappings among schema levels are needed to

transform requests and data. Programs refer to
an external schema, and are mapped by the
DBMS to the internal schema for execution. -
data independence
Conceptual schema
• Describes the stored data in terms of the data

model of the DBMS
• In a relational DBMS, the conceptual schema

describes all relations that are stored in the
database
First Year 5
Physical schema
• Describe storage details.

• Summarizes how the relations described in the
conceptual schema are actually stored on secondary
storage devices such as disks and tapes.
• Decide what file organizations used to store the
relations.
• Create indexes to speed up data retrieval operations.
6
External schemas
• Allow data access to be customized (and

authorized) at the level of individual users or
groups of users.
• Any given database has exactly one conceptual

schema and one physical schema because it has
just one set of stored relations, but it may have
several external schemas.
First Year 7
Data Independence
• data independence fully support in DBMS,
– When a schema at a lower level is changed .
• Only the mappings need to be changed between this schema
and higher-level schema(s).
• The higher-level schemas themselves are unchanged.
• The application programs need not be changed since they refer

to the external schemas.
Data Independence (Contd.)
• Logical Data Independence: The capacity to change

the conceptual schema without having to change
the external schemas and their application
programs.
• Physical Data Independence: The capacity to change

the internal schema without having to change the
conceptual schema.
DBMS Languages
• Data Manipulation language(DML)

• Data Definition Language(DDL)
• Data Control Language(DCL)
• Transaction Control Language(TCL) –this is also another type
Data Definition Language (DDL)
• A data definition language or data description

language (DDL) is a syntax similar to a
computer programming language for defining data structures,
especially database schemas.
• For DBA and database designers
• to specify the conceptual schema
• also used to define internal and external schemas (views).
• In some DBMSs, separate storage definition language (SDL) and
view definition language (VDL) are used to define internal and
external schemas.
Data Definition Language
(DDL)
Used to create and modify database objects

– Create
– Drop
– Alter
Data Manipulation Language (DML):
• A data manipulation language (DML) is a family of syntax elements

similar to a computer programming language used for inserting,
deleting and updating data in a database. Performing read-only
queries of data is sometimes also considered a component of DML.
• DML commands can be embedded in a general-purpose programming

language (host language), such as COBOL, C, Java or an Assembly Language.
• Alternatively, stand-alone DML commands can be applied directly (query

language).
Data Manipulation Language (DML)
Used to create, modify and retrieve data

– Insert
– Select
– Update
– Delete
Data Control Language
• A data control language (DCL) is a syntax similar to a

computer programming language used to control
access to data stored in a database. In particular, it is
a component of Structured Query Language (SQL).
• Examples of DCL commands include:
• GRANT to allow specified users to perform specified
tasks.
• REVOKE to cancel previously granted or denied
permissions.
Transaction Control Language
• Transaction Control Language
(TCL) - Transaction control commands
manage changes made by DML
commands. These SQL commands are used for
managing changes affecting the data. These
commands are COMMIT, ROLLBACK, and
SAVEPOINT.
DBMS Languages - SQL
Structured Query Language
SQL - (also pronounced SEQUEL)
• Used as DML,DDL and DCL (TCL)
• Used in ORACLE and other DB systems
• Non-procedural - i.e. Specify what you want
not how to get it
• Used with RELATIONAL DBMS
• Simple to use
Example SQL Queries
• select branchNo, city Variables
from branch;
File name
• select *
from branch
where branchNo = ‘B003’;
• select branchNo, name

from branch, staff
where branch.branchNo = staff.branchNo;
Database Architectures
• Centralized DBMS:
• combines everything into single system

including- DBMS software, hardware,
application programs and user interface
processing software.
Classification of DBMSs
• Based on the data model used:
• Traditional: Relational, Network, Hierarchical.
• Emerging: Object-oriented, Object-relational.
• Other classifications:
• Single-user (typically used with micro- computers) vs.
multi-user (most DBMSs).
• Centralized (uses a single computer with one database)
vs. distributed (uses multiple computers, multiple
databases)
Classification of DBMSs
Distributed Database Systems
come to be known as client server based

database systems
because they do not support a totally distributed

environment, but rather a set of database servers
supporting a set of clients.
QUESTIONS?

Lec 3

Uploaded by

Copyright:

Available Formats

Lec 3

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lec 3

Uploaded by

Copyright:

Available Formats

DBMS

– Conceptual schema at the conceptual level ( one view)

– External schemas (many view)

• Proposed to support DBMS characteristics of:

Mappings among schema levels are needed to

• Describes the stored data in terms of the data

• In a relational DBMS, the conceptual schema

• Describe storage details.

• Allow data access to be customized (and

• Any given database has exactly one conceptual

• The higher-level schemas themselves are unchanged.

• The application programs need not be changed since they refer

• Logical Data Independence: The capacity to change

• Physical Data Independence: The capacity to change

• Data Manipulation language(DML)

• A data definition language or data description

Used to create and modify database objects

• A data manipulation language (DML) is a family of syntax elements

• DML commands can be embedded in a general-purpose programming

• Alternatively, stand-alone DML commands can be applied directly (query

Used to create, modify and retrieve data

• A data control language (DCL) is a syntax similar to a

• select branchNo, name

• combines everything into single system

Distributed Database Systems

come to be known as client server based

because they do not support a totally distributed

You might also like