Database Management Systems

Introduction

1
 Objectives
• What is a database?
• Major components of a DBMS?
• Advantages of using a DBMS?
• How are databases used to build applications?
• How have DBMS changed over time?

2
 Some Preliminaries
• An organized, machine-readable collection of symbols, to be
interpreted as a true account of some enterprise.

• … so a database is also a collection of tables/relations.

 Basic Definition
• Data: raw facts
– Constitute building blocks of information
– Known facts that can be recorded and that have implicit
meaning
• Information: is produced by processing data and
reveals meaning of data
– timely, relevant information key to decision making
– decision making key to organizational survival
• Database: a collection of Data
– End user data (Operational Data) - Stored in Table
– Metadata – Stored in System Catalog/Data Dictionary
 Database Definition
• Database
– A collection of logically inter-related
organized persistent shared data stored in a
standardized format, designed to be shared by
multiple users.

5
 DBMS Definition
A collection of programs that manages the database
structure and controls access to the data stored in the
database. DBMS Offers facilities..
• Data Definition
• Data Construction
• Data Manipulation
• Data Management

• Possible to share data among multiple applications or

users
Example: bank and its ATM machines
• Makes data management more efficient and
effective
End users have better access to more and better-managed
data
 DBMS: Database Management System
• Database Management System (Goals)
– Software that defines a database, stores the data,
supports a query language, produces reports, and
creates data entry screens.
– To provide an efficient as well as a convenient
environment for accessing data in a database
– Enforce information security: database security,
concurrency control, crash recovery

7
 Database File Structure
Data
Raw Facts
Data Field/ Attribute/ Column
Group of characters with specific meaning
Record / Row / Tuple
Logically connected fields that describe a
person, place, or thing
Data File / Table / Relation
Collection of related records
 “Organised Collection of
Symbols”
For example:

StudentId Name CourseId

S1 Anne C1
S1 Anne C2
S2 Boris C1
S3 Cindy C3

The symbols are organised into rows and columns, thus forming a table.
One of the rows is different in kind from the others.

9
 “To Be Interpreted as a True
Account”
For example (from the table just shown):

StudentId Name CourseId

S1 Anne C1

Perhaps those green symbols, organised as they are with respect to

the blue ones, are to be understood to mean:

“Student S1, named Anne, is enrolled on course C1.”

10
 “Collection of Data Values”
ENROLMENT

StudentId Name CourseId

S1 Anne C1
S1 Anne C2
S2 Boris C1
S3 Cindy C3
S4 Devinder C1

ENROLMENT is a relation. If so, it (relation) has been updated since then

(the row for S4 has been added).

11
What Is a Relational Database?
A database whose symbols are organised into a collection of
relations. Here is a relation, shown in tabular form:

StudentId Name CourseId

S1 Anne C1
S1 Anne C2
S2 Boris C1
S3 Cindy C3
S4 Devinder C1
Might be the value currently assigned to ENROLMENT, a relation
variable.

12
Anatomy of a Relation
 Anatomy of a Relation
StudentId Name CourseId
S1 Anne C1

attribute attribute n-tuple, or tuple.

name values This is a 3-tuple.
The tuples
constitute the
body of the
Heading (a set of attributes) relation.
The degree of this heading is 3, The number of
which is also the degree of the relation. tuples in the body
is the cardinality
of the relation.

14
 Two Types of Data in Database
• Operational Data:
– Data is a set of facts and statistics can that be operated, referred or
analyzed.
– Business Data to describe the activities of an enterprise
– Stored as records in data file/table/relation
– Data may or may not have been processed.
• Metadata:
– Metadata describes relevant information about the data.
– Is also known as the System Catalog.
– It describes how, when and by whom certain data was collected as
well as the format and context of the data.
– A data dictionary is a centralized collection of data information
– Metadata is always a processed data.
Operational Data
Converting Operational Data to Information
Meta Data
Database Manages Interaction
 Web Front-End
SQL I/f
Forms

SQL Commands

Plan Executor Parser

SQL Engine
Operator Evaluator Optimizer

Tx Files & Access

Manager Recovery
Buffer Manager Manager
Lock
Manager Disk Space Manager DBMS

Meta Datab
Data File ase
Data
 DBMS Features/Components
• Database engine • Report writer
– Storage • Forms generator (input
– Retrieval screens)
– Update
• Application generator
• Query Processor • Communications
• Data dictionary • 3GL Interface
• Utilities
• Security
22
 DBMS Components
All Data

Database Engine
Data Dictionary
Communication 3GL
Security
Network Connector

Query Processor
Form Report
Builder Writer Program

Application
Generator

23
DBMS Engine, Security, Utilities
Product
OrderItemID Description
887 Dog food
OrderID ODate
Customer 946 Cat food
9874 3-3-97 Data
CustomerID Name
1195
9888 3-9-97
Jones Tables
2355 Rojas

Product Database
ItemID
Customer Integer, Unique Engine
Description
CustomerID Text, 100
Integer, char
Unique
Name Text, 50 char Data
Dictionary

User Identification Concurrency and

Security
Access Rights Lock Manager

Backup and Administration

Utilities
Recovery

24
Database Tables (Access)

25
Database Tables (Oracle)

26
 DBMS Query Processor
All Data

Database Engine
Data Dictionary

Animal Query Processor

AnimalID
Name
Category Category CountOfAnimalID
Breed
Dog 100
Field Category AnimalID Cat 47
Table Animal Animal Bird 15
Totals Group By Count Fish 14
Sort Descending Reptile 6
Criteria Mammal 6
Or Spider 3
27
 DBMS Report Writer
All Data

Database Engine
Data Dictionary

Query Processor
Report Writer

Report
Format
and Query

28
Report Writer (Oracle)

29
 DBMS Input Forms
All Data

Database Engine
Data Dictionary

Query Processor
Form Builder

Input
Form
Design

30
People Who Work with Databases
• Database Implementers / Designer /Back End
Developer
• End Users / Naïve User / Data Entry Operator
• Application Programmers / Front End
Developer
• DBA / System Manager
 End Users
• Casual users
These are people who use the database occasionally.
• Naive users
These are users who constantly querying and
updating the database.
Eg. Reservation Clerks of Airline, Railway, Hotel, etc.
Clerks at receiving station of Courier service,
Insurance agencies, etc.
• Sophisticated Users
People who use for their complex requirements.
Eg. Engineers, Scientists, Business analysts…
• Standalone Users
Who maintain database for personal use.
 DBA / System Manager
• Managing resources
• Creation of user accounts
• Providing security and User authorization
• Managing poor system response time
• System Recovery
• Tuning the Database
• Specifying the database structure
 Is a File System a
= DBMS?
• Thought Experiment 1:
– You and your project partner are editing the same file.
– You both save it at the same time.
– Whose changes survive?

A) Yours B) Partner’s C) Both D) Neither E) ???

•Thought Experiment 2: Q: How do you write
programs over a
–You’re updating a file.
subsystem when it
–The power goes out. promises you only “???” ?
–Which of your changes survive? A: Very, very
carefully!!
A) All B) None C) All Since Last Save D) ???
 History of DBMS
• 1960 – First DBMS designed by Charles Bachman at
GE. IBMs Information Management System (IMS)
• 1970 – Codd introduced the RDBMS
• 1980 – Relational model became popular and
accepted as the main database paradigm. SQL, ANSI
SQL, etc.
• 1980 to 1990 – New data models, powerful query
languages, etc. Popular vendors are Oracle, SQL
Server, IBMs DB2, Informix, etc.
• OODBMS, ORDBMS and Advanced Data types..
• Various types of data: Images, Text, complex queries, Data Mining, etc.
• Enterprise Resource Planning (ERP)
• Management Resource Planning (MRP)
• Database in Web technologies
Current Database Trends:
 Multimedia databases
 Interactive video
 Streaming data
 Digital Libraries
 Spatial Data base
 Active Database
 Temporal Database
 Distributed Database
 Deductive Database
 Web Database
 Mobile Database
Database Systems: Then
Database Systems: Today
Other databases you may use
 Current Commercial Outlook
• A major part of the software industry:
– Oracle, IBM, Microsoft, Sybase
– also Informix (now IBM), Teradata
– smaller players: java-based dbms, devices, OO, …

• Lots of related industries

– data warehouse, document management, storage, backup, reporting,
business intelligence, app integration, cloud data services
• Relational products dominant and evolving
– adapting for extensibility (user-defined types), adding native XML
support.
• Open Source coming on strong
– MySQL, PostgreSQL, BerkeleyDB
 Examples of Commercial Systems
Vendor Product
Oracle Oracle
Microsoft SQL Server
Access
IBM DB2
Informix
Open source PostgreSQL
MySQL AB MySQL

41
Percona's DBMS Popularity Survey
 Database Systems
• Types of Database Systems
– Number of Users
• Single-user
– Desktop database
• Multiuser
– Workgroup database
– Enterprise database
– Scope
• Desktop
• Workgroup
• Enterprise
 Database Systems

• Types of Database Systems

– Location
• Centralized
• Distributed
– Use
• Transactional (Production)
• Decision support
• Data warehouse
 = Is the WWW a DBMS?
• Fairly sophisticated search available
– crawler indexes pages on the web
– Keyword-based search for pages
• But, currently
– data is mostly unstructured and untyped
– search only:
• can’t modify the data
• can’t get summaries, complex combinations of data
– few guarantees provided for freshness of data, consistency across
data items, fault tolerance, …
– Web sites typically have a DBMS in the background to provide these
functions.
• The picture is changing
– New standards e.g., XML, Semantic Web can help data modeling
– Research groups (e.g., at Berkeley) are working on providing some of
this functionality across multiple web sites.
 “Search” vs. Query
• What if you
wanted to find
out which actors
donated to John
Kerry’s
presidential
campaign?

• Try “actors
donated to john
kerry” in your
favorite search
A “Database Query” Approach
Application Development with a DBMS
SQL Queries

Data

Database Tables
Forms, Reports,
Programs Database Server

Application Server

Users
Developers and Application Forms
Administrators

57
 Web Databases
Database

Developers
Web Server
Users
data

Web forms
and reports
Reports

58
Goal: Build a Business Application
Tools:
Database
Design
SQL (queries)
Programming

Program
Design

SQL
Best:
Spend your time
on design and SQL.
Program
Design

SQL

Worst:
Compensate for poor design
and limited SQL with programming.

59
DBMS Application Design
1. Identify business rules.

2. Define tables and relationships.

3. Create input forms
and reports.

4. Combine as
applications for users.

60
System Structure
 Database Architecture
• two-tier architecture
• three-tier architecture

• Client Machine - remote database users work

• Server Machine - the database system runs

Database Architecture
 Two Tier Architecture
• The application resides at the client machine,
where it invokes database system functionality
at the server machine through query language
statements.
• Application program interface standards like
ODBC and JDBC are used for interaction
between the client and the server
 Three-tier architecture
• client machine acts as front end and does not
contain any direct database calls.
• the client end communicates with an application
server, through a forms interface.
• The application server in turn communicates
with a database system.
• The business logic of the application, which says
what actions to carry out under what conditions,
is embedded in the application server, instead of
being distributed across multiple clients