Enterprise Business Intelligence IS6120

1
Enterprise Business Intelligence (IS6120)

• 10 December, 2009
• DBMS Components

Csaba Csáki Business Information Systems (based
on slides by G.Kiely and ideas from the book by
Connolly/Begg/Strachan)
2
Recap DBMS

• Logical view of data
• A view that shows the logical relationship(s)
  between different pieces of data in a database
• Physical view of data
• A view that shows how and where data are
  physically stored in a storage medium

3
DBMS

• A database management system (DBMS) is a complex
  piece of software
• Its purpose is to store and retrieve large
  volumes of data in the most efficient way
  possible
• A DBMS normally consists of one or more program
  modules that each provide some part of the
  overall functionality of the system
• These modules include the
• language processors,
• the query processor,
• the database manager,
• the data dictionary manager, and
• the file manager.

4
DBMS

• For most DBMSs there are three major types of
  input
• (1) application programs that change data in the
  database,
• (2) queries that retrieve data from the database
  (usually in response to a users request), and
• (3) commands to change the database schema.
• Some of the functions of the DBMS may be provided
  by the underlying operating system

5
App. Programs
Queries
dB Schema
6
DB languages

• There are many languages in a DBMS
• Data Definition Language
• Data Manipulation Language
• Data Query Language

7
Language Processor

• Components of the Language Processor
• Data Manipulation Language Pre-processor
• Data Definition Language Compiler
• The Language Processor is responsible for
• Checking that all commands to the DBMS are
  correct
• Translating commands into machine readable
• Working with the query processor to produce the
  most efficient method of answering queries
• Data manipulation commands are often stored in
  application programs
• The language processor actually consists of a
  variety of different processes
• The data manipulation language pre-processor is
  responsible for converting manipulation commands
  (e.g. SQL insert command) into commands that may
  be executed by the database manager (thus into
  machine readable form)
• The data definition language compiler is
  responsible for converting commands that define
  the structure of the database into entries in the
  data dictionary

8
App. Programs
Queries
dB Schema
DML preprocessor
Program Object code
9
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
Program Object code
10
Data definition, data schema

• When the DBMS receives a DDL command it
• 1. Creates or changes the underlying file
  structures that are used to implement the
  database. For example, a file might be created
  for each new relation created by a CREATE TABLE
  DDL command
• 2. Changes the data dictionary to record the
  change that has been made to the database
  structure. For example, a new record may be added
  to the data dictionary describing the structure
  of a newly created relation.

11
Three-level DDL schema

• There are three different data definition
  languages
• 1. A view definition language for creating
  entities and relationships at the external level
  and mapping them to the conceptual schema.
• 2. A data definition language for creating
  entities and relationships at the conceptual
  level and mapping them to the internal level.
• 3. A storage definition language for creating
  file and index structures at the internal level.

12
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
Dictionary Manager
13
Data Dictionary

• The data dictionary is a database that stores
  information about all the data stored in the DBMS
• Describes in detail the characteristics of the
  data
• Descriptions of the tables and attributes
• Location of the data (in what file the data are
  located)
• Size of the data (how many bytes)
• Range of acceptable values for each field
• Type of data (number, character, audio, etc.)
• Source of the data (where the data originated)
• Usage (who uses the data)
• Ownership (who has the right to view or modify
  the data)
• Methods for accessing and securing data

14
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
15
Dictionary Manager

• Responsible for
• Updating the data dictionary when the database
  schema changes, for example, when a new table is
  added
• Providing information about the database schema
• Providing the database manager with information
  about the content of the database
• Storing integrity constraints
• Storing authorisation permissions (of users)
• This allows the database manager to fulfil its
  role of enforcing the integrity and security
  constraints of the database administrator

16
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
dB Manager
17
Database Manager

• The database manager is
• responsible for accepting commands from the user
• ensuring that commands are valid
• calculating the most effective way to execute
  commands
• executing commands
• Some call the database manager the run-time
  database processor

18
Database Manager

• The database manager has the following major
  responsibilities
• Authorisation control
• Command processor
• Integrity checking
• Query optimisation
• Transaction Recovery management

19
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
dB Manager
20
Authorisation control

• Or Access control
• All commands from the user are checked to ensure
  that the user is allowed to execute them
• This process will involve checking the security
  permissions that have been given to the user and
  the restrictions that have been placed on the
  data requested

21
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
dB Manager
22
DBM functionality

• Command processor
• When a command has been authorised it is carried
  out by the command processor
• Carrying out a command involves selecting the
  best method of executing the command using the
  query optimiser and the transaction manager
• Integrity checking
• All commands that change the contents of the
  database must be checked to ensure that they do
  not introduce errors into the database.
• Integrity constraints are created by the database
  administrator
• Query optimisation
• The most efficient method of executing a query
  must be identified
• This is done by analysing a variety of possible
  plans and selecting the best

23
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
scheduler
Transaction manager
dB Manager
24
Transaction Recovery management

• Large DBMSs use transaction processing to manage
  very large changes to the database
• Logging or
• shadow pages

25
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
scheduler
Transaction manager
Recovery manager
dB Manager
26
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
scheduler
Transaction manager
Recovery manager
Buffer manager
dB Manager
27
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
scheduler
Transaction manager
Recovery manager
Buffer manager
dB Manager
File manager
28
File Manager

• Responsible for
• Allocating disc storage
• Maintaining files and Indexes
• Managing system buffers in main memory
• Transferring blocks between discs and buffers
• Functionality may be provided by the underlying
  operating system.
• Also called the stored data manager

29
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
scheduler
Transaction manager
Recovery manager
Buffer manager
dB Manager
File manager
Access methods
30
OS

• Some of the functions of the DBMS may be provided
  by the underlying Operating System
• for example
• the file manager or
• the system buffers

31
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
scheduler
Transaction manager
Recovery manager
Buffer manager
dB Manager
File manager
Access methods
System buffer
32
App. Programs
Queries
dB Schema
DML preprocessor
Qry Processor
DDL Compiler
Program Object code
DBMS
Dictionary Manager
Authorisation control
Integrity checker
Command processor
Qry optimiser
scheduler
Transaction manager
Recovery manager
Buffer manager
dB Manager
File manager
Access methods
dB and System catalog
System buffer
33
DBMS

• DBMS Interfaces
• Menus
• Forms
• DBMS Utilities
• Loaders
• Backup
• File Reorganisation
• Performance Monitoring
• Classifications of DBMSs
• Number of Users
• Distribution
• Cost