Chapter 2 Database Systems Architecture

1
Chapter 2Database Systems Architecture
2
Outline

• The Three Levels of the Architecture External,
  Conceptual, and Internal Levels
• Mappings
• Database Administrator (DBA)
• Database Management System (DBMS)
• Database Communications
• Client/Server Architecture
• Utilities
• Distributed Processing

3
The Three levels of the Architecture
4
Overview

• A general database concept proposed by ANSI/SPARC
• The three levels not necessarily for a
  relational system
• Internal level (storage level) closest to
  physical storage
• Concerned with the way the data is stored inside
  the system
• One/Implementation level
• External level (individual user logical level)
  closest to users
• Concerned with the way the data is seen by
  individual users
• Many/Model level
• Conceptual level (community logical level)
• Indirection between the above two levels
• One/Model level

5
The Three Levels of the Architecture
6
An Example of the Three Levels
Contain control information such as codes, flags,
or pointers
7
Note for the Example

• The corresponding data items can have different
  names at different points/levels
• The corresponding data items can have different
  types at different points/levels
• The system must be aware of the correspondence ?
  mapping

8
The Three-Level Architecture in Relational Systems

• The conceptual level will definitely be
  relational
• A given external view will typically be
  relational too, or something very close to it
• PL/I and COBOL record declaration in the previous
  example might loosely be regarded as PL/I and
  COBOL analogs of the declaration of a relational
  table in a relational system
• The internal level will not be relational
• The objects at this level will not be just
  (stored) relational tables
• Stored records, pointers, indexes, hashes

9
Detailed System Architecture
10
External Level

• Individual user level
• Each user has a (host) language at his or her
  disposal
• Data sublanguage (DSL) is a subset of the total
  language that is concerned specifically with
  database objects and operations
• Data definition language (DDL) support the
  definition of DB objects
• Data manipulation language (DML) support the
  processing of DB objects
• SQL is one DSL
• An external view is the content of the DB as seen
  by some particular user
• To that user, the external view is the database
• Different users will require different views

11
External Level (Cont.)

• An external view consists of many occurrences of
  many types of external record (or logical record)
• The users DSL is defined in terms of external
  records
• Each external view is defined by means of a
  external schema, which consists basically of
  definitions of each of the various record types
  in that external view
• Written by the external DDL
• Implemented via SQL views
• May support single or groups of users
• Data can be integrated, and shared

12
Conceptual Level

• The abstract entire information content of the DB
• Global logical representation, atemporal, and
  shared by all users
• Consist of many occurrences of many types of
  conceptual record
• Ideally should include security and integrity
  constraints, and many additional features
• Described by conceptual schema, which is written
  by the conceptual DDL
• Should provide physical data independence
• Foundation for database design

13
Internal Level

• Consist of many occurrences of many types of
  internal record (or stored record)
• Stored DB internal view
• Stored DB definition internal schema
• One remove from the physical level (not deal with
  blocks, pages nor with any device-specific
  consideration)
• Described by internal schema, which is written by
  the internal DDL
• Various stored record types, indexes, how stored
  fields are represented, what physical sequence
  the stored records are in
• Includes structures such as hash, heap, B-tree,
  pointers

14
Mappings

• One conceptual/internal mapping and several
  external/conceptual mappings
• Conceptual/internal mapping
• Correspondence between the conceptual view and
  the stored DB
• Example fields with different data types, fields
  and records with different names, combining
  several internal fields into one single
  conceptual field
• Specifies how conceptual records and fields are
  presented at the internal level
• If the structure of the stored DB is changed, the
  conceptual/internal mapping must be changed
  accordingly, so that the conceptual schema can
  remain invariant
• The effects of such changes must be isolated
  below the conceptual level, in order to preserve
  physical data independence

15
Mappings (Cont.)

• External/conceptual mapping
• Correspondence between the conceptual view and a
  particular external view (similar to
  conceptual/internal mapping)
• Key to providing logical data independence
• Users and user programs are immune to changes in
  the logical structure of the DB (changes at the
  conceptual or community logical level)
• If the structure of the conceptual DB is changed,
  the external/conceptual mapping must be changed
  accordingly, so that the external schema can
  remain invariant
• External/external mapping
• Define external views in terms of others

16
Database Administrator

• The DBA is the person who provides the necessary
  technical support for implementing those decision
  made by the DA
• Tasks of the DBA
• Defining the conceptual schema
• Participates in logical (conceptual) DB design
  (with the DA)
• Determines how to implement conceptual schema
• Defining the internal schema
• Decides how the data is to be represented in the
  stored DB physical DB design
• Having done the physical design, the DBA must
  then create the corresponding stored DB
  definition (i.e. the internal schema)
• Defines the associated conceptual/internal
  mapping by conceptual DDL
• Physical design should always be done after
  logical design

17
Database Administrator (Cont.)

• Tasks of the DBA (Cont.)
• Liaising with users
• Ensures that the data user need is available and
  to write (or help the users write) the necessary
  external schemas
• Defines the corresponding external/conceptual
  mappings by external DDL
• Consult on application design, teach users
• Defining security and integrity constraints
• Part of the conceptual schema
• Defining dump/restore schemas unload/reload
  utilities
• Monitoring performance and responding to changing
  requirements
• Performance tuning and reorganize the stored DB

18
Database Management System

• DBMS handles all access to the database
• The overall purpose of DBMS is to provide the
  user interface to the database system
• Scenario
• A user issues an access request, using some
  particular DSL (typically SQL)
• The DBMS accepts that requests and analyzes it
• The DBMS inspects, in turn, the external schema
  for that user, the corresponding
  external/conceptual mapping, the conceptual
  schema, the corresponding conceptual/internal
  mapping, and the stored database definition
• The DBMS executes the necessary operations on the
  stored DB

19
Functions of DBMS

• Data definition DDL processor / compiler
• Data manipulation DML processor / compiler
• Handle planned (scheduled) and unplanned (ad hoc)
  queries
• DBA will probably have tuned the physical DB
  design in such a way as to guarantee good
  performance for planned requests
• Optimization and execution
• Optimizer determine an efficient way of
  implementing the request
• The optimized requests are then executed under
  the control of the run-time manager
• Data security and integrity
• Performance

20
Functions of DBMS (Cont.)

• Data recovery and concurrency
• Transaction manager or TP monitor must enforce
  certain recovery and concurrency controls
• Data dictionary or catalog
• Contains definitions of other objects in the
  system
• All of the various schemas and mappings and all
  of the various security and integrity constraints
  will be kept in the dictionary
• In addition, which program use which parts of the
  DB, which users require which reports, and so on
• Data dictionary can be regarded as a DB in its
  own right
• It is possible to query the dictionary like any
  other DB

21
Major DBMS Functions and Components
22
Data Communications

• DB requests from an end user are actually
  transmitted from the users computer which
  might be physically remote from the DBMS to
  some online application, and thence to the DBMS,
  in the form of communication messages
• Likewise, responses back from the DBMS and online
  application to the users computer are also
  transmitted in the form of such messages
• All such message transmissions take place under
  the control of the data communication manager (DC
  manager)

23
Client/Server Architecture

• The overall purpose of a database system is to
  support the development and execution of database
  applications
• Such a system can be regarded as a client-server
  structure
• Server DBMS
• Client various applications that run on top of
  the DBMS
• User-written applications
• Vendor-provided applications tools
• Assist in the creation and execution of other
  applications

24
Client/Server Architecture

• Vendor-provided tools
• Query language processors
• Report writers
• Business graphics subsystems
• Spreadsheets
• Natural language processors
• Statistical packages
• Copy management or data extract tools
• Application generators
• CASE products
• Data mining and visualization tools

Front ends
Back end
25
Utilities

• Programs designed to help the DBA with various
  administration tasks
• Operate at the external or internal levels
• Utility examples
• Load create the initial database from regular
  data files
• Unload/reload (or dump/restore) unload the
  database or portions to backup storage and to
  reload data from such backup copies
• Reorganization rearrange the data in the stored
  database for various reasons (usually having to
  do with performance)
• Statistical compute various performance
  statistics such as file sizes, value
  distributions, I/O counts, and so on
• Analysis analyze the statistic just mentioned

26
Distributed Processing

• Distributed processing means that distinct
  machines can be connected into a communication
  network such that a single data processing task
  can span several machines in the network
• Communication among the various machines is
  handled by some kind of network management
  software, possibly an extension of the DC
  manager, more likely a separate software
  component
• Client(s) and Server running on different machines

27
Client(s) and Server running on different machines
28
Why Distributed Processing?

• Response time and throughput should be improved
• The server machine might be a custom-built
  machine that is tailored to the DBMS function and
  might thus provide better DBMS performance
• The client machine might be tailored to the needs
  of the end user and thus able to provide better
  interface, high availability, faster responses,
  and overall improved ease of use to the user
• Several different client machines might be able
  to access the same server machine (sharing)

29
One Server Machine, Many Client Machines
A single client machine might be able to access
several different server machines the converse
of this figure
30
Each Machine Runs both Client (s) and Server
Example a bank with several branches
31
Distributed Database System

• Full distributed database support implies that a
  single application should be able to operate
  transparently on data that is spread across a
  variety of different databases, managed by a
  variety of different DBMSs, running on a variety
  of different machines, supported by a variety of
  different OSs, and connected by a variety of
  different communication networks
• Transparency means that the application operates
  from a logical point of view as if the data were
  all managed by a single DBMS running on a single
  machine