DBMS Implementation and Administration

By Liyakathunisa, SJCE, Mysore

Prerequisites
Data Structures Algorithm Any Programming Language Discrete Math etc.

Introduction
Data:
Known facts that can be recorded and have an implicit meaning. E.g names, telephone numbers, addresses of people. You may have recorded this data in an book, or stored on a disk.

Users Application DBMS

Data Base:

This collection of related data with an implicit meaning is called a database

Database

What is Database?
Essentially, a database is a set of data, or a collection of information. These data are related. Examples:

Student Information human genome sequence company products information

More Examples
Airline

Reservation Systems

Reservations by a single customer on a single flight, including such information as assigned seat or meal preference Information about the flight, the airports thy fly from and to, the departure and arrival times, etc. Ticket prices, requirements, and availability

Banking

Systems

Customers, accounts, loans and the balances Deposit or withdraw money

Introduction

Database Management System (DBMS):

A collection of programs that enables users to create and maintain DB. A software system to facilitate the creation and maintenance of a computerized database.

Database System:

Include the DBMS software, the databases, and the applications.

Whats DBMS?

DataBase Management System (DBMS)

A powerful tool for creating and managing large amounts of data efficiently and allowing it to persist over long periods of time, safely. It also provides efficient accesses to multiple users. It is used to manage databases.
7

Example of a Database

database

relations (or tables)

attributes tuples (or records)

Example of a Database (with a Conceptual Data Model)

Some mini-world relationships:

SECTIONs are of specific COURSEs STUDENTs take SECTIONs COURSEs have prerequisite COURSEs INSTRUCTORs teach SECTIONs COURSEs are offered by DEPARTMENTs STUDENTs major in DEPARTMENTs

Note: The above could be expressed in the ENTITY-RELATIONSHIP data model.

9

DBMS capabilities
Persistent Storage Programming Interface Transaction management

10

DBMS capabilities (Cont.)

Allow users to create databases and specify their schema, using data-definition language. Allow users to query and modify the data, using data-manipulation language. Support the storage of very large amount of data over a long period. Data consistency and failure recovery.

11

File System

Why not using file system?

Can be used to store data information for a long period as well. Consistency and failure recovery capability no query language, no efficient access for a data item support of the creation of database is limited consistency control is not adequate

Problems:

Management of Data is complex

12

DBMS Evolution
File Systems Hierarchical Model (Tree-based) Network Model (Graph-based) Relational Model Object Oriented Model Object/Relational Model

13

Relational Database System

Ted Codd 1970 The view of data: relations

relations, tuples, attributes tables, rows, columns

Queries could be expressed in high level language, it is simple and efficient It is widely used in most commercial systems

14

Example 1.1
Bank Account Information (Accounts) AccountNo Balance Type 12345 67890 1000.00 2846.92 Savings checking
15

Example 1.1 (Cont.)

Check the balance

SELECT balance FROM Accounts WHERE accountNo= 67890

Check the accountNo

SELECT accountNo From Accounts WHERE type=savings AND balance < 0
16

Major DBMS Products

Oracle IBM: DB2, Informix Microsoft: SQL Server, Access Sybase MySQL Postgres

All are "relational" (or "object-relational") database systems at their core.

17

Schema versus data

Schema describes how data is to be structured - defined at set-up time, rarely changes (part of the "metadata") Data is actual "instance" of database, may change rapidly Comparable to types and variables in programming languages

18

Data Definition Language (DDL)

Commands for setting up schema of database Process of designing schema can be complex, may use design methodology and/or tool

19

Data Manipulation Language (DML)

Commands to manipulate data in database: RETRIEVE, INSERT, DELETE, MODIFY Also called "query language"

20

People
DBMS Implementer: builds system Database Designer: establishes schema Database Administrator: loads data, keeps whole thing running Database User: queries/modifies data

21

DBMS Evolution Trends

Size
using personal computer, similar to applications such as spreadsheet and word processing

smaller and smaller

bigger and bigger
terabytes or even petabytes data, using tertiary storage and parallel computing techniques.

Architecture
database Server application server database server
22

Client-server architecture (Two-Tier)

client client

Multi-Tier architecture:

DBMS Trends (Cont.)

Versatile Data type

integers, characters
small storage requirement

videos, audios
large storage requirement

Integration
Data warehouses, data mining

23

Advanced DBMS

Starting from now Something completely different!

24

DBMS Architecture

25

Role of DBA: Data Definition Lang commands

DDL commands are simpler to process

E.g. the DBA for a university registrars DB might decide that there should be a table or relation with columns

SName Course Grade

The DBA decides that the only allowable grades are A,B,C,D and E.
26

DBA needs special authority to execute schema altering commands

These have profound effect on the DB.

These Schema altering commands are parsed by DDL processor (DDL Complier) and passed to the execution engine, Which then goes through the index/file/record manager to alter the Metadata, i.e. Schema information for DB

27

Main Memory Buffers and Buffer manager

The data of a DB normally resides in secondary storage in computer system.

Secondary storage means magnetic disks.

To perform any useful operation on data , that data must be in MMemory. Thus a DBMS component called buffer manager is responsible for portioning the available MM into buffers

Which are page sized regions into which disk blocks can be transferred.

28

Main Memory Buffers and Buffer manager

Thus all the DBMS components that need information from disk will interact with the buffers and the buffer manager ,

either directly or through the execution engine.

29

Query Processing
A user or an application program initiates some actions that does not affect the schema of the DB, but may affect the content of the DB (modification) or will extract data from DB (query). There are two paths along which user actions affect the DB.

30

Answering the Query

The query is optimized and parsed by a query compiler. The resulting query plan , or sequence of actions to be taken to answer the query , is passed to the execution engine. The execution engine issues a sequence of request for small pieces of data,

Typically records or tuples of a relation., to a resource manger that knows about data files holding relations, The format and size of records in these files, and index files , which help find elements of data files quickly. These requests are passed to buffer manager.

The request for data are translated into pages ,

31

The role of buffer manger is to bring appropriate portion of the data from secondary storage where it is kept permanently, to main memory buffers. The buffer manger communicates with a storage manager to get data from disk.

32

Query Processor

It is represented by two components

Query compiler and Execution engine

The query compiler , which translates the query into an internal from called a query plan
Query Plan is a sequence of operations to be performed on the data.

The query compiler consists of three major units

A query parse , which builds a tree structure from the textual from of the query. A query preprocessor which performs semantic checks on the query. A query optimizer, which transforms the initial query plan into best available sequence of operations on the actual data

33

Query Processor (cont..)

The query compiler uses meta data and statics about data to decide which sequence of operations is likely to be fastest.

34

Query Processor

Execution Engine

Has the responsibility for executing each of the steps in the chosen query plan . The execution engine interacts with most of the other components of the DBMS, either directly or through the buffer. It must get the data from the DB into buffers in order to manipulate that data. It needs to interact with the scheduler to avoid accessing data that is locked , and with log manager to make sure that all DB changes are properly logged.
35

Transaction Processing
Queries and other actions are grouped into transactions The transaction manager accepts transaction commands from an application,

Which tell the transaction manager when transaction begin and end ,as well as information about expectation of the application.

36

Transaction Processing

The transaction processor perform following Tasks: Logging

In order to assure durability , every change in the DB is logged separately on the disk. Log manager follows one of the several policies designed to assure when a system failure or crash occurs . A recovery manager will be available to examine the log of changes and restore the DB to some consistent state
37

Overview of DBMS

DDL commands
DBA DDL Complier Execution engine

Query Processing
Answering the query User Query Compiler Execution engine Transaction processing

38

Storage and Buffer Management

Buffer manager Storage manager

Data: the contents of the database Metadata (Schema) : structure of the database Statistics: data properties Indexes

39

Transaction Processing

Transaction manager
Logging Concurrency control Deadlock resolution

40

Query Processor

Query complier
Query parser Query preprocessor Query optimizer

Execution engine

41

Outline of Database System

Database design
Determine the database structure

Database programming
Perform database operations

Database system implementation

Build the database

42

End of Chapter 1

43