Lesson02 database system architecture

6 September 2019 S M Irteza / Dr Rafi Ullah 1
Lesson 02
Database System
Architecture

3 Levels of Architecture
 Levels of abstraction of a
database
 physical level (internal – how)
 logical level (conceptual – what)
 view level (external – what part)
lower abstraction
higher abstraction

The ANSISPARC architecture is divided into 3
levels
 The Internal Level
 The External Level
 The Conceptual Level

External
Schema 1
External
Schema 1
External
Schema 1
Conceptual
Schema
Internal
Schema

External Level
 This is on the individual user level
 Application programmer
 End user
 The External Schema is written using the external
DDL portion of the user’s data sub-language.

External Level cont.
Sales
Management
H.R

Conceptual Schema
 A representation of the entire information content of
the database.
 e.g. DBA has this view
 All Structural details are known at this level.
 Tables, table contents.
 Table’s connections.
 Views and users.
 Constraints etc

Conceptual Schema cont.
 The conceptual schema is written using conceptual
DDL.
 Conceptual view is the total view of the database.

Internal Schema
 Also called storage view
 A description of how information is stored on physical
devices, for example:
 Hard drives
 Tape devices
 Compact disks, DVD etc. etc.
 The DBA has to know where all the information is stored
physically,i.e. addresses, IP addresses, etc.

Mapping
 Mapping defines the correspondence between the
 External mapping and conceptual mapping
 Conceptual mapping and the internal mapping

Mapping cont.
User A1 User A2 User B1 User B2 User B3
External
view B
External
view A
Conceptual view
Stored Database( Internal View)
External
Schema A
External
Schema B
Conceptualinternal
Mapping
ExternalConceptual
Mapping A
ExternalConceptual
Mapping B
Conceptual
Schema
DBMS
Schemas
and
mappings
built and
maintained
by the
Database
Admin.
(DBA)

Mapping cont.
 Conceptual/Internal Mapping
 Defines correspondence between conceptual and internal
schemas.
 Specifies how conceptual records are represented on the
internal level.
 If structure of the DB is changed
 Conceptual/internal mapping is changed accordingly
 DBA makes these changes
 Effects of such changes are isolated between the two
schemas.

Mapping cont.
 External/Conceptual Mapping
 Defines correspondence b/w external and conceptual
schemas.
 Many conceptual fields can form one external view
etc.

Instances and Schemas
 Each level is defined by a schema, which defines the
data at the corresponding level
 A logical schema defines the logical structure of the
database (set of customers and accounts and the
relationship between them)
 A physical schema defines the file formats and
locations
 A database instance refers to the actual content of
the database at a particular point in time. A
database instance must conform to the
corresponding schema

DBA
 Data Administrator DA, is the person who makes the
strategic and policy decisions regarding the DATA for
the enterprise.
 This is known as LOGICAL , or sometimes called
conceptual database design.
 DBA is the person who is responsible for the overall
control, implementation on the technical level.

DBA’s Responsibilities
 Defining the Conceptual Schema.
 Decides exactly what info is to be kept in the DB.
 Defines conceptual schema by using DDL
 Defining internal schema
 Defines physical database design.
 Creates the internal schema using internal DDL.
 Define mapping b/w the other schemas.

DBA’s Responsibilities cont.
 Liaising with Users
 Ensure users get the data they require.
 Help the users to write external schemas using
external DDL.
 Mapping for external and conceptual schemas.
 Provide consultation for certain application designs.
 Providing technical support and education.

DBA’s Responsibilities cont.
 Defining Integrity and Security Constraints
 Defining Dump And Reload Policies
 Unloading or dumping a backup of database or
database portion on backup devices.
 In case of error or damage to the database ,
reloading the database when necessary from most
recent dumps.
 Monitoring Performances and responding to change
requirements.

DBMS
 It is a software that handles all access to the
database.following things happen :
 A user issues an access request, using some
language (SQL)
 The DBMS intercepts that request and analyze it.
 The DBMS inspects external schema for that user, the
conceptual schema and the mappings etc. and the
internal schema .
 DBMS executes the necessary operations on the
stored database.

Core DBMS Functions
 Data Definition
 Must be able to accept data definitions
 Must include DDL processor or DDL compiler.
 Understand the DDL definitions.
 So it can handle the data manipulation requests etc.

Core DBMS Functions cont.
 Data Manipulation
 DBMS must have DML compile or DML processor so it
can handle requests for data
 Retrieval
 Updates
 Delete
 Add new data etc.
 DML REQUESTS have 2 categories in general
 Planned requests.
 Unplanned requests.

 Planned Requests
 Their need is already predicted.
 DBA will probably have tuned the Physical DB design to
guarantee good performance.
 Characteristic of “applications”, i.e., issued by pre-written
applications usually
 Unplanned Requests
 An Ad Hoc query
 Its need was not predictable.
 The physical DB might not be tuned for optimum performance.
 Characteristic of “decision support”, I.e. issued by query
language processor.

 Optimization and Execution
 DML requests both planned and unplanned are
processed by optimizer component.
 Its purpose is to find an efficient way of
implementing the request.
 Optimized requests are then executed under the
control of run time manager.
 Run-time manager might invoke some file managers
to access the physically stored data.

 Data Security and Integrity
 Allows to define constraints.
 Monitors user requests.
 Rejects any attempt that violates the security and
integrity constraints.
 Defined by the DBA.
 They can be run on compile time or run time or some
where in the middle.

 Data Recovery And Concurrency
 The Transaction manager, or Transaction
processing monitor(TP monitor) deals with these.
 Must enforce some recovery and concurrency control.
 Beyond the scope of this chapter.
 Discussed later.

 Data Dictionary
 DBMS provides a data dictionary function.
 It is a database in its own right.
 Contains data about data.
 Sometimes called “metadata” or descriptors.
 For example, definitions of other objects in the
database, like schemas, mappings, raw data, various
integrity constraints, security constraints. It might
also know what program uses what part of the
database, etc.

 Performance
 DBMS should perform all of the above mentioned
tasks with the highest efficiency.

Data Communication Manager
 Requests from remote user’s workstations might
come to the DBMS in form of communication
messages.
 Similarly, responses from the DBMS to the user
through an online application etc.
 All these are handled by the data communication
manager, which is not actually a part of DBMS itself.

Client/Server Architecture
 Database systems are considered to have two parts
 Server side (back end)
 Client side (front end)
 Server
 Server is the DBMS itself.
 Provides all the basic DBMS functions
 DDL, DML, security, integrity, etc.

Client/Server Architecture cont.
 Client
 Applications that run on top of the DBMS.
Applications
DBMS
Clients
Server
End users
Database

Distributed Processing
 Distributed Processing
 Meaning that Distinct machines can be connected
together into a communication network, e.g internet,
in such a way that single processing task can span
over several machines in the N/W.
 If machines are physically close together than such
an activity is called “Parallel Processing”.
 Communication is handled by some n/w management
software, probably some enhanced DC manager.

Distributed Processing cont.
 Example
 Client and
server running
on different
machines
Applications
DBMS
Clients machine
Server machine
End users
Database
Remote access

Distributed Processing cont
Server Machine
DB
One Server machine, Many Client
machines
Network

Distributed Processing cont
Clients
Server
Clients
Server
Clients
Server
Clients
Server Clients
Server
Network
Each machine runs both client and server.

End
END OF LESSON 02

Lesson02 database system architecture

In this document