Databasemagament System - Inroduction, Study notes of Database Management Systems (DBMS)

Download Databasemagament System - Inroduction and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

1

Database

Database

Management System

Management System

2

Database Management System

Database Management System

 Data –Data – InformationInformation – Knowledge– Knowledge

 Chronology: Data & Knowledge EngineeringChronology: Data & Knowledge Engineering

 DBMS Definition & ApplicationDBMS Definition & Application

 Why DBMS? PurposeWhy DBMS? Purpose

 (^) Developing Information Systems

 (^) Transaction ManagementTransaction Management

 DBMS Types & users

 DBMS languagesDBMS languages

 (^) DDL Vs. DMLDDL Vs. DML

 DBMS ArchitectureDBMS Architecture

 (^) Client-server based

 (^) Web-based architecture

 DBMS ImplementationDBMS Implementation

 Data ModelingData Modeling

 (^) E-R Diagram

 From diagram to Tables

 Data Base designData Base design

 Data Querying: Concepts & ConstructsData Querying: Concepts & Constructs

 Query / Report format design, development.Query / Report format design, development.

 Recent trends in DBMSRecent trends in DBMS

Data,

Data,

Information

Information & Knowledge& Knowledge

 Computer processing requires data,data, which is a collection of rawcollection of raw

facts facts, figures and symbols, such as numbers, words, images,

video and sound, given to the computer during the input phase.

 Computer processes the data to create InformationInformation which is

data that is organized, meaningful, and useful.

 During the output Phase, the information that has been created

is put into some form, such as a printed report.

 A DBMS processes the data & provide information.

 Knowledge is not simply the information presented, but is

information furtherfurther processedprocessed withwith intelligentintelligent mechanismmechanism

incorporating experience, domain knowledge and specialized

techniques.

 To generate (business) insight / strategy based on the

Knowledge acquired from IT based systems. KDD ( KnowledgeKnowledge

Discovery in Database) is an significant concept related to data

mining and business intelligence.

5

Data EngineeringChronological Development Data EngineeringChronological Development

Evolutionary steps Evolutionary steps Business queryBusiness query EnablingEnabling

technologies technologies

Product vendors Product vendors CharacteristicsCharacteristics

Data

Collection

(1960s)

“ What was my

total revenue in

the last five

years”

Computers,

tapes, disks

IBM Retrospective,Retrospective,

static data

delivery

Data access

(1980s)

“ What were A.C

unit sales in

New England

last March?

Relational

Databases, SQL,

ODBC

Oracle, Sybase,

Informix,

IBM,Microsoft

Retrospective Retrospective

dynamic data

delivery at

record level.

Data

Warehousing

& Decision

support

systems1990s)

“ What were

A.C.unit sales in

New England

last March?Drill

down to

Boston.”

OLAP, Multi

dimensional

databases, DW

Pilot, Comshare,

Arbor,Cognos,

Microstrategy.

Retrospective, Retrospective,

dynamic data

delivery at

multiple levels.

Data Mining

(Emerging )

“What’s likely to

happen to

Boston’s

A.C.unit sales

next month?

Why?

Advanced

algorithms,

multiprocessor

computers,

massive

databases.

Pilot, Lockheed,

IBM, SGI, Mineset

etc.

Prospective, Prospective,

proactiveproactive

information

delivery.

7

Database Management system Database Management system

 Purpose of Database Systems: Developing Information

system

 View of Data

 Data Models

Data Definition Language

 (^) Data Manipulation Language

 Transaction Management

 Storage Management

 Database Administrator

 Database Users

 Overall System Structure

8

Developing Information systems Developing Information systems

 The purpose of a implementing a database system in any

organization is to develop an effective Information System in

the organization.

What is information system?

 Information system is an “computerization of some existing

manual /system which after automation provides useful

information to the targeted user.

 One of the most popular example of an information system is

computerization of “Railway Reservation Systems”

 Developed by “CRIS” [Centre for Railways Information Systems]

Is providing very valuable information like

1.Availability of trains between two stations.

2. Availability of Tickets in a particular Train, on particular date etc.
3. Booking a Ticket online.

 Being used as Web based system it is sound implementation of

DBMS!

Purpose of Database Systems

Purpose of Database Systems

In the early days, database applications were built directly

on top of file systems with a program. language

 Transaction Management is typical example which

highlights the significance of a DBMS.

 In case of transferring funds from one bank A/C to othertransferring funds from one bank A/C to other

if System failure occurs!

 This failure may occur at any stage & the system

failures (e.g., power failures, operating system crashes)

will result not only as a transaction failure but may be in

serious inconsistencies in database.

 Money withdrawn from one Account ( and withdrawn from one Account ( and network fails network fails))

so so not received in the Other A/C.!

 Also in concurrent transactions how to ensure the

consistency of the database.

 And many other problems….

Purpose of Database Systems Purpose of Database Systems

 Drawbacks of using file systems to store data:

 (^) Data redundancy and inconsistency

 Multiple file formats, duplication of information in

different files

 (^) Difficulty in accessing data

 Need to write a new program to carry out each new task

 (^) Data isolation — multiple files and formats

 (^) Integrity problems

 Integrity constraints (e.g. account balance >0 ) becomeIntegrity constraints

“buriedburied” in program code rather than being stated

explicitly

 Hard to add new constraints or change existing ones

13

Data Base Management System Data Base Management System

Ordinary Users interact Ordinary Users interact

with DBMS via Menu in FrontEnd with DBMS via Menu in FrontEnd

Application like Application like VisualVisual BasicBasic..

Whereas Whereas Sophisticated UsersSophisticated Users

& & Database administratorDatabase administrator

interacts with DBMS interacts with DBMS

via via SQLSQL interface or directlyinterface or directly

with with (^) Oracle / MS SQL Server / IBM DB2Oracle / MS SQL Server / IBM DB

Users Users

Front-endFront-end

Database Database

Application Application

DBMSDBMS

DatabaseDatabase

Database Architecture

Database Architecture

The architecture of a database systems is greatly

influenced by the underlying computer system on

which the database is running:

 Centralized

 Clientserver

 Parallel (multiple processors and disks)

 Distributed

Data Base Application Architecture can be categorized asData Base Application Architecture can be categorized as

TwoTierTwoTier^ and Three Tier ArchitectureThree Tier Architecture

16

Database Users Database Users

 Users are differentiated by the way they expect to

interact with the system

 Application programmers – interact with system through

DML calls

 Sophisticated users –give requests in a database query

language

 Specialized users – write specialized database

applications that do not fit into the traditional data

processing framework

 Naive users – invoke one of the permanent application

programs that have been written previously

 (^) E.g. people accessing database over the web, bank

tellers, clerical staff

17

Database Administrator Database Administrator

 Coordinates all the activities of the database

system;

 The database administrator has a good

understanding of the enterprise’s information

resources and needs.

 Database administrator's duties include:

 (^) Schema definition

 (^) Storage structure and access method definition

 (^) Schema and physical organization modification

 Granting user authority to access the database

 (^) Specifying integrity constraints

 Monitoring performance and responding to changes in requirements

Query Processing in DBMS

Query Processing in DBMS

1. Parsing and translation
2. Optimization
3. Evaluation

Query Processing (Cont.)

Query Processing (Cont.)

 Same query can be framed in more than one way…

 Alternative ways of evaluating a given query

Equivalent expressions

Different algorithms for each operation

 Cost difference between a good and a bad way of

evaluating a query can be enormous

 Need to estimate the cost of operations

Depends critically on statistical information about

relations which the database must maintain

Need to estimate statistics for intermediate results

to compute cost of complex expressions