< DBMS contains inIormation about a particular enterprise

ollection oI interrelated data

Set oI programs to access the data
An environment that is both convenient and efficient to use
< Database Applications:
Banking: all transactions
Airlines: reservations, schedules
Universities: registration, grades
Sales: customers, products, purchases
Online retailers: order tracking, customized recommendations
ManuIacturing: production, inventory, orders, supply chain
Human resources: employee records, salaries, tax deductions
< Databases touch all aspects oI our lives
Database Management System
(DBMS)
Basic DeIinitions
Database: A collection oI related data.
Data: Known Iacts that can be recorded and have an
implicit meaning.
Mini-world: Some part oI the real world about which
data is stored in a database. For example, student
grades and transcripts at a university.
Database Management System (DBMS): A soItware
package/ system to Iacilitate the creation and
maintenance oI a computerized database.
Database System: The DBMS soItware together with
the data itselI. Sometimes, the applications are also
included.
<n the early days, database applications were built
directly on top oI Iile systems
<Drawbacks oI using Iile systems to store data:
Data redundancy and inconsistency
N Multiple Iile Iormats, duplication oI inIormation in diIIerent Iiles
DiIIiculty in accessing data
N eed to write a new program to carry out each new task
Data isolation multiple Iiles and Iormats
ntegrity problems
N ntegrity constraints (e.g. account balance ~ 0)
N Hard to add new constraints or change existing ones
Purpose oI Database Systems
n the early days, database applications were built on top oI Iile
systems
Data is stored in separate Iiles
Data is manipulated by a set oI application programs
As time goes by, the number oI Iiles and application programs
increases
increasing complexity oI database application management
Purpose oI Database Systems
Limitations oI File Systems (1/3)
Drawbacks oI using Iile systems to store data
Data redundancy and inconsistency
Redundancy: duplication oI inIormation in diIIerent Iiles
nconsistency: the various copies oI the same data may be diIIerent
DiIIiculty in accessing data
eed to write a new program to carry out each new task
Data isolation multiple Iiles and Iormats
Because data are scattered in various Iiles, and Iiles may be in diIIerent Iormats,
writing the appropriate inIormation to retrieve the appropriate data is diIIicult
ntegrity problems
Data values stored in the database must satisIy certain types oI .onsisten.y
.onstraints
ntegrity constraints (e.g. account balance ~ 0) become part oI program code
Those constraints are Iorced by the database system developer
Hard to add new constraints or change existing ones
Purpose oI Database Systems
Limitations oI File Systems (2/3)
Drawbacks oI using Iile systems to store data
Atomicity oI updates
Failures may leave database in an inconsistent state with partial updates
carried out
E.g. transIer oI Iunds Irom one account to another should either complete or
not happen at all
oncurrent access by multiple users
oncurrent accessed needed Ior perIormance
Uncontrolled concurrent accesses can lead to inconsistencies
E.g. two people reading a balance and updating it at the same time
Security problems
ot every user oI the database should be able to access all the data
Purpose oI Database Systems
Limitations oI File Systems (3/3)
Provide a way Ior eIIicient data management
storage and retrieval oI the inIormation
Manage large bodies oI inIormation
DeIining structures Ior the storage oI inIormation
Providing the mechanisms Ior the manipulation oI the
inIormation
Ensure the saIety oI the inIormation stored
Avoid abnormal results when sharing the DB among
several users
Objective oI DBMS
Banking: all transactions
Airlines: reservations, schedules
Universities: registration, grades, student inIormation
Sales: customers, products, purchases
ManuIacturing: production, inventory, orders, supply chain
Human resources: employee records, salaries, tax
deductions
Telecommunications: keeping record oI calls made,
generating monthly bills
Database System Applications
Recall that
a database management system (DBMS) is a collection oI
interrelated data and a set oI programs to access and modiIy those
data.
A major purpose oI a database system is to provide users with an
abstra.t view oI data
The system hides certain details oI how the data are stored and
maintained
View oI Data in a Database
The objective oI data abstraction is to simpliIy
the interaction with the database Irom the user
perspective
Most oI database users are not computer trained
However, more complexity Irom the design
perspective
omplex data structure to represent data
View oI Data in a Database
The eed Ior Data Abstraction
Slide 1-11
Typical DBMS Functionality
DeIine a database : in terms oI data types,
structures and constraints
onstruct or Load the Database on a
secondary storage medium
Manipulating the database : querying,
generating reports, insertions, deletions and
modiIications to its content
oncurrent Processing and Sharing by a set
oI users and programs yet, keeping all data
valid and consistent
Slide 1-12
Typical DBMS Functionality
Other Ieatures:
Protection or Security measures to
prevent unauthorized access
'Active processing to take internal
actions on data
Presentation and Visualization oI data
Example oI a Database
(with a onceptual Data Model)
Mini-world for the example: Part oI a
UVERSTY environment.
Some mini-world 039908:
STUDETs
OURSEs
SETOs (oI OURSEs)
(academic) DEPARTMETs
STRUTORs
Slide 1-14
Main haracteristics oI the
Database Approach
SelI-describing nature oI a database system: A
DBMS .atalog stores the /escription oI the
database. The description is called meta-data).
This allows the DBMS soItware to work with
diIIerent databases.
nsulation between programs and data: alled
program-data independen.e. Allows
changing data storage structures and operations
without having to change the DBMS access
programs.
Slide 1-15
Main haracteristics oI the
Database Approach
Data Abstraction: A data model is used to
hide storage details and present the users with
a conceptual view oI the database.
Support oI multiple views oI the data: Each
user may see a diIIerent view oI the
database, which describes only the data oI
interest to that user.
Slide 1-16
Main haracteristics oI the
Database Approach
Sharing oI data and multiuser transaction
processing : allowing a set oI concurrent users to
retrieve and to update the database. oncurrency
control within the DBMS guarantees that each
transa.tion is correctly executed or completely
aborted. OLTP (Online Transaction Processing) is
a major part oI database applications.
Slide 1-17
Advantages oI Using the
Database Approach
ontrolling redundancy in data storage and
in development and maintenance eIIorts.
Sharing oI data among multiple users.
Restricting unauthorized access to data.
Advantages oI Using the
Database Approach
Providing backup and recovery
services.
EnIorcing integrity constraints on the
database.
Slide 1-19
Additional mplications oI Using
the Database Approach
Flexibility to change data structures: database
structure may evolve as new requirements
are deIined.
Availability oI up-to-date inIormation very
important Ior on-line transaction systems
such as airline, hotel, car reservations.
Economies oI scale: by consolidating data
and applications across departments wasteIul
overlap oI resources and personnel can be
avoided.
Slide 1-20
Extending Database apabilities
ew fun.tionality is being added to
DBMSs in the following areas:
ScientiIic Applications
mage Storage and Management
Audio and Video data management
Data Mining
Spatial data management
Time Series and Historical Data Management
Slide 1-21
When not to use a DBMS
Main inhibitors (.osts) of using a DBMS:
High initial investment and possible need Ior
additional hardware.
Overhead Ior providing generality, security,
concurrency control, recovery, and integrity
Iunctions.
When a DBMS may be unne.essary:
I the database and applications are simple, well
deIined, and not expected to change.
I there are t real-time requirements that may not
be met because oI DBMS overhead.
I access to data by multiple users is not required.
Slide 1-22
When not to use a DBMS
When no DBMS may suffi.e:
I the database system is not able to handle the
complexity oI data
I the database users need special operations not
supported by the DBMS.
2-1 2-23
DataBase DataBase System Ar.hite.ture System Ar.hite.ture
2.1 Data Models, Schemas, and nstances
2.2 DBMS Architecture and Data ndependence
2.3 Database Languages and nterIaces
2.4 The Database System Environment
2.5 lassiIication oI Database Management Systems
2.6 Summary
Physical Level: describes ow the data is
actually stored
Describes low level data structures
Logical Level: describes wat data are stored in
the database and what relationships exist
between data
View Level: describes only part oI the entire
database
View oI Data in a Database
Data Abstraction Levels
View oI Data in a Database
Data Abstraction Levels
Physical Level: describes ow the
data is actually stored
Describes low level data
structures
Logical Level: describes wat
data are stored in the database and
what relationships exist between
data
View Level: describes only part
oI the entire database
View oI Data
An architecture Ior a database system
S.hema the logical structure oI the database
Example: The database consists oI inIormation about a set oI customers and accounts
and the relationship between them)
Physi.al s.hema: database design at the physical level
Logi.al s.hema: database design at the logical level
Instan.e the actual content oI the database at a particular point in time
Physi.al Data Independen.e the ability to modiIy the physical schema without changing
the logical schema
Applications depend on the logical schema
n general, the interIaces between the various levels and components should be well
deIined so that changes in some parts do not seriously inIluence others.
nstances and Schemas
2-2 2-28
Data Model: A set oI concepts to describe the structure oI a
database, and certain constraints that the database should obey.
data types
relationships
Provide data abstraction
Data Model Operations: Operations Ior speciIying database
retrievals and updates by reIerring to the concepts oI the data
model.
generic operation: insert, delete, modiIy, retrieve
user-deIined operations
Data Models, S.hemas, and Instan.es Data Models, S.hemas, and Instan.es
2-3 2-29
S.hemas, Instan.es and Database State S.hemas, Instan.es and Database State
Database S.hema (meta-data): The description oI a database. ncludes
descriptions oI the database structure and the constraints that should hold
on the database.
S.hema Diagram: A diagrammatic display oI (some aspects oI ) a
database schema. (reIer to Fig 2.1 2-5)
Database Instan.e: The actual data stored in a database at a particular
moment in time. Also called database state ( or occurrence, snapshot)
(reIer to Fig 1.2 2-6)
The database s.hema changes very inIrequently. The database state
changes every time the database is updated. S.hema is also called
intension
Each schema construct has its own current set oI instances.
cI database
2-4a 2-30
Figure 2.1 Schema diagram Ior UVERSTYdatabase
schema construct
Known data:
name oI record types, data items
2-4 2-31
Figure 1.2
UVERSTY Database
2-3 2-32
deIine
empty state
initial state
load
state
update
update
valid state
satisIy database schema
2-5 2-33
DBMS Ar.hite.ture and Data Independen.e DBMS Ar.hite.ture and Data Independen.e
Proposed to support DBMS characteristics oI:
- nsulation oI programs and data/program and operations
(program-data and program-operation independence)
- Support oI multiple views oI the data.
- Use oI catalog (database description)
DeIines DBMS schema at three levels: (see 2-9)
- Internal s.hema at the internal level to describe data storage structures and access
paths.
- Con.eptual s.hema at the conceptual level to describe the structure and constraints
Ior the wole database.
- xternal s.hema at the external level to describe the various user views.
Mappings among schema levels are also needed. Programs reIer to an external schema,
and are mapped by the DBMS to the internal schema Ior execution
Three Three- -S.hema Ar.hite.ture S.hema Ar.hite.ture
2-6 2-34
Figure 2.2 The Three-schema architecture
2-6
2-7 2-35
Data Independen.e Data Independen.e
Logi.al Data Independen.e: The capacity to change the conceptual schema without
having to change the external schemas and their application programs.
Physi.al Data Independen.e: The capacity to change the internal schema without
having to change the conceptual schema.
By adding or removing a record type or data
item to
expand the database (2-11)
reduce the database
Database storage structure
2-8 2-36
Database Languages and Interfa.es Database Languages and Interfa.es
Data Definition Language (DDL): Used by the DBA and database designers to
speciIy the conceptual scema oI a database.
Data Manipulation Language (DML): Used to speciIy database retrievals and
updates (insertion, deletion, modiIications)
provide appropriate languages and interIaces Ior each category oI users. provide appropriate languages and interIaces Ior each category oI users.
DBMS Languages DBMS Languages
2-10 2-37
DBMS Interfa.es DBMS Interfa.es
- Stand-alone query language interIaces. (casual end user)
- Programmer interIaces Ior embedding DML in programming
languages: (programmer)
-Pre-compiler Approach
-Procedure
- User-Iriendly interIaces:
-Menu-based nterIaces Ior Browsing.
-Forms-based nterIaces.
-Graphical User nterIaces.
-atural language nterIaces
-ombination oI the above
- nterIaces Ior the DBA:
-reating accounts, granting authorizations
-Setting system parameters
-hanging schemas or access path
2-11 2-38
DBMS Component Modules DBMS Component Modules
The Database System nvironment The Database System nvironment
Figure 2.3
2-12 2-39
Database System Utilities Database System Utilities
To perIorm certain Iunctions such as:
- oa/ing data stored in Iiles into a database.
- Backing up the database periodically on storage.
- File reorgani:ing database Iile structures.
- Report generation utilities.
- Performance monitoring utilities.
- Oter functions, such as sorting, user monitoring
2-12 2-40
Data dictionary utility:
- Used to store schema descriptions and other inIormation such as design
decisions, application program descriptions, user inIormation, usage
standards, etc. (comment)
-Active data dictionary is accessed by DBMS soItware and users/DBA.
-Passive data dictionary is accessed by users/DBA only.
Tools, Appli.ation nvironments, and Tools, Appli.ation nvironments, and
Communi.ations Fa.ilities Communi.ations Fa.ilities
2-13 2-41
Classifi.ation of Database Management Systems Classifi.ation of Database Management Systems
Based on the data model used:
Data models
-Traditional: Relational, etwork (see 2-19), Hierarchical
- Emerging: Object-oriented, Semantic, Entity- Relationship, other.
Other .lassifi.ations:
umber oI users : Single-user (typically used with personal computers) vs.
multi-user (most DBMSs)
umber oI sites:
entralized (uses a single computer) vs. distributed (uses multiple computers).
Homogeneous vs. Heterogeneous
ost oI DBMS soItware. $10,000~100,000
$100~3,000
Purpose general purpose
special purpose
e.g. airline reservations, telephone directory, on-line transaction
processing system
2-14 2-42
Figure 2.4 A etwork Schema
Database Application Architectures
(web browser)
Old
Modern
Four types oI database users
ave users: invoke one oI the permanent application programs that have been written
previously
E.g. Bank cashier, people accessing a database over the web
The typical user interIace Ior nave user is a Iorm interIace where the user just Iill in the
appropriate Iields oI the Iorm
Appli.ation programmers: computer proIessional that write application programs
hoose a tool Irom many tools to build a user interIace to the database
nteract with the system through DML calls
Sophisti.ated users: nteract with the system without writing programs
Form their requests using a database query language (e.g. Analysts)
Spe.ialized users: write specialized database applications that do not Iit into the
traditional data processing Iramework
E.g. computer-aided design systems, expert systems, environement modeling systems
Database Users
The Database Administrator (DBA) oordinates all the activities oI the
database system
The DBA has a good understanding oI the enterprise`s inIormation resources
and needs
The DBA has central control over the system
Database administrator's duties include:
Schema deIinition, speciIying integrity constraints
Storage structure and access method deIinition
Schema and physical organization modiIication
Granting user authority to access the database
Acting as liaison with users
Monitoring perIormance and responding to changes in requirements
Routine maintenance (DB backup, Ensuring Iree space Ior the DB, etc .)
Database Administrators
< (Most oI) Database systems are accessed remotely
through a network
< The lient/Server Model
lient machines, on which remote database users work
Server machines, on which the database system runs
Application Architectures
Database
Client machines
Server machines
Network
Application Architectures
< Two-Tiered Ar.hite.ture
The application is partitioned into a
component that resides at the client
machine
t remotely invokes the database
server machine through query
languages statements
E.g. using ODB, JDB Ior
interaction between the client and the
server
< Three-Tiered Ar.hite.ture
The client machine acts as a Iront-end
and does not contain any direct
database calls
The client machine communication
with the server appli.ation through
an interIace oI Iorms (not direct
queries)
The server application communicate
with a database system to access data
application
User
Database systems
Network Network
TwoTiered Architecture
Application client
User
Database systems
Network Network
ThreeTiered Architecture
Application server
< 1950s and early 1960s:
Data processing using magnetic tapes Ior storage
N Tapes provide only sequential access
Punched cards Ior input
< Late 1960s and 1970s:
Hard disks allow direct access to data
etwork and hierarchical data models in widespread use
Ted odd deIines the relational data model
N Would win the AM Turing Award Ior this work
N BM Research begins System R prototype
N U Berkeley begins ngres prototype
High-perIormance (Ior the era) transaction processing
History oI Database Systems
< 1980s:
Research relational prototypes evolve into commercial systems
N SQL becomes industry standard
Parallel and distributed database systems
Object-oriented database systems
< 1990s:
Large decision support and data-mining applications
Large multi-terabyte data warehouses
Emergence oI Web commerce
< 2000s:
ML and Query standards
Automated database administration
ncreasing use oI highly parallel database systems
Web-scale distributed data storage systems
History (cont.)
The architecture oI a database systems is greatly
inIluenced by
the underlying computer system on which the database
is running:
< entralized
< lient-server
< Parallel (multiple processors and disks)
< Distributed
Database Architecture
Extend the relational data model by including
object orientation and constructs to deal with
added data types.
Allow attributes oI tuples to have complex types,
including non-atomic values such as nested
relations.
Preserve relational Ioundations, in particular the
declarative access to data, while extending
modeling power.
Provide upward compatibility with existing
relational languages.
Object-Relational Data Models
<DeIined by the WWW onsortium (W3)
<Originally intended as a document markup
language not a database language
<The ability to speciIy new tags, and to
create nested tag structures made ML a
great way to exchange data, not just
documents
<ML has become the basis Ior all new
generation data interchange Iormats.
<A wide variety oI tools is available Ior
parsing, browsing and querying ML
documents/data
ML: Extensible Markup Language