unit 1 - libvolume6.xyzlibvolume6.xyz/mechanical/btech/semester8/databasemanagement...wei-pang yang,...
TRANSCRIPT
1-1
Unit 1
Introduction to DBMS
(Database Management Systems)
Application
program End-userDBMS
Wei-Pang Yang, Information Management, NDHU 1-2
Outline of Unit 1
1.1 Information Systems
1.2 An Overview of a Database System
1.3 An Architecture for a Database System
1.4 Database Technology Trends
1-3
1.1 Information Systems
Wei-Pang Yang, Information Management, NDHU 1-4
Stages of Information System
� Stage 0: Manual Information System
• Records
• Files
• Index Cards
� Stage 1: Sequential Information Systems
• Tapes
• Files
• slow, non-interactive, redundancy,... .
� Stage 2: File Based Information Systems
• Disk (direct access)
• application program has its own file data dependence
• data redundancy
� Stage 3: DBMS based Information Systems
• Generalized data management software
• Transaction processing
Wei-Pang Yang, Information Management, NDHU 1-5
� Conventional Data Processing techniques:
Enterprise:
Application
System A
Application
Program AFile System A Customer
Application
System B
Application
Program BFile System B Invoice
Application
System NApplication
Program N File System N Inventory
…
File Based Information Systems
Wei-Pang Yang, Information Management, NDHU 1-6
Customer Invoice
InventoryParts
Customer No.
Customer Name
Customer Addr.
Social Security ID
Customer No.
Customer Name
Part No.
Quantities
Unit Price
Part No.
Part Description
Unit Price
Supplier
Quantities Remain
Quantities Ordered
Part No.
Part Description
Supplier
Quantities Ordered
Customer Name
Unit Price
File Based Information Systems (cont.)
Wei-Pang Yang, Information Management, NDHU 1-7
Host Language
+ DSL
Host Language
+ DSL
Host Language
+ DSL
Host Language
+ DSL
Host Language
+ DSL
User A1 User A2 User B1 User B2 User B3
External View@ # &
External ViewB
External/conceptual
mapping A
ConceptualView
External/conceptual
mapping B
Conceptual/internal
mapping
Stored database (Internal View)
Databasemanagement
system dictionary
(DBMS) e.g. systemcatalog
<
DBA
Storagestructuredefinition(Internalschema)
Conceptualschema
Externalschema
A
Externalschema
B
(Build andmaintainschemas
andmappings)
# @&
DSL (Data Sub Language)
C, Pascal
e.g. SQLSQLSQLSQL
1 2 3
1 2 3 ... 100
Wei-Pang Yang, Information Management, NDHU 1-8
DBMS based Information Systems:
Basic Approach - Integration
� (1) Integration of Information
• Description of the integrated view of data is the "Conceptual
Schema" of the database
Application
programEnd-user
DBMS
Wei-Pang Yang, Information Management, NDHU 1-9
DBMS based Information Systems:
Basic Approach – Simple views and High level language
� (2) Provide simple views (External Schema) and high level language (e.g.
SQL) for users to manipulate (handle) data
• High level language: e.g. SQL (Structured Query Language)
<e.g.>: SELECT SNAMEFROM SWHERE S#= 'S4';
• Description of user's view of data is the "external schema" or
"subschema" or "view".
• High-level languages (Query Language): SQL(1) Data Definition Language:
define format(2) Data Manipulation Language:
retrieve, insert, delete, update
• Emphasize: EASE OF USE !!
S
S# name
Wei-Pang Yang, Information Management, NDHU 1-10
DBMS based Information Systems:
Basic Approach - Storage/Access Method
� (3) Efficient Storage/Access Techniques:
• implemented once rather than duplicated in all application
programs.
Language Processor
Access Method(B+ tree, Dynamic Hashing)
DBMS
User: query in SQL
Access Methods Calls
I/O calls
Wei-Pang Yang, Information Management, NDHU 1-11
DBMS based Information Systems:
Basic Approach - Transaction Management
� (4) Provide Transaction Management:
• Concurrency Control
• Recovery
• Security
• .:
Wei-Pang Yang, Information Management, NDHU 1-12
Example: A Simple Query Processing
Query in SQL:SELECT CUSTOMER. NAMEFROM CUSTOMER, INVOICEWHERE REGION = 'N.Y.' ANDAMOUNT > 10000 ANDCUTOMER.C#=INVOICE.C
Internal Form :
Π(σ (S SP)
Operator :
SCAN C using region index, create CSCAN I using amount index, create ISORT C?and I?on C#JOIN C?and I?on C#EXTRACT name field
Calls to Access Method:OPEN SCAN on C with region indexGET next tuple
.
.
.
Calls to file system:GET10th to 25th bytes fromblock #6 of file #5
Language Processor
Optimizer
Operator Processor
Access Method
File System
database
Language
Processor
Access
Method
e.g.B-tree; Index;
Hashing
DBMS
1-13
1.2 An Overview of a
Database System
Wei-Pang Yang, Information Management, NDHU 1-14
An Example
� The Wine Cellar Database:
Cellar:
� Retrieval:
• DML (Data Manipulation Language):SELECT Wine, Bin, ProducerFROM CellarWHERE Ready = 85;
• Result:
2 Chardonnay Buena Vista 83 1 85
3 Chardonnay Louis Martini 81 5 846 Chardonnay Chappellet 82 4 85 Thanksgiving
12 Jo. Riesling Buena Vista 82 1 83 Late Harvest
16 Jo. Riesling Sattui 82 1 83 Very dry
43 Cab. Sauvignon Robt. Mondavi 77 12 8750 Pinot Noir Mirassou 77 3 85 Harvest
51 Pinot Noir Ch. St. Jean 78 2 86
Bin Wine Producer Year Bottle Ready Comments
Wine Bin ProducerChardonnay 2 Buena VistaChardonnay 6 Chappellet
Pinot Noir 50 Mirassou
Wei-Pang Yang, Information Management, NDHU 1-15
An Example (cont.)
� Deletion:
• DML: DELETE FROM CellarWHERE Ready < 86;
• Result:
� Insertion:
• DML: INSERT INTO CellarVALUES (53, 'Pinot Noir', 'Franciscan', 79, 1, 86, 'for Joan');
• Result:
43 Cab. Sauvignon Robt. Mondavi 77 12 8751 Pinot Noir Ch. St. Jean 78 2 86
Bin Wine Producer Year Bottle Ready Comments
43 Cab. Sauvignon Robt. Mondavi 77 12 8751 Pinot Noir Ch. St. Jean 78 2 86
53 Pinot Noir Franciscan 791 86 for Joan
Bin Wine Producer Year Bottle Ready Comments
Wei-Pang Yang, Information Management, NDHU 1-16
An Example (cont.)
� Update
• DML: UPDATE Cellar
SET Bottles = 4
WHERE Bin = 51;
• Result:
43 Cab. Sauvignon Robt. Mondavi 77 12 87
51 Pinot Noir Ch. St. Jean 78 4 8653 Pinot Noir Franciscan 79
1 86 for Joan
Bin Wine Producer Year Bottle Ready Comments
Wei-Pang Yang, Information Management, NDHU 1-17
What is a Database System?
Application
program
End-user
DBMS
Wei-Pang Yang, Information Management, NDHU 1-18
What is a Database System? (cont.)
� Major components of a database system:• Data: integrated and shared.
• Hardware: disk, CPU, Main Memory, ...
• Software: DBMS
• Users:
1. Application programmers
2. End users
3. Database administrator (DBA)
• Defining external schema
• Defining conceptual schema
• Defining internal schema
• Liaison with users
• Defining security and integrity checks
• Defining backup and recovery procedures
• Monitoring performance and changing requirements
Wei-Pang Yang, Information Management, NDHU 1-19
Why Database ?
� Redundancy can be reduced
� Inconsistency can be avoided
� The data can be shared
� Standards can be enforced
� Security restrictions can be applied
� Integrity can be maintained
� Provision of data independence
objective !
Wei-Pang Yang, Information Management, NDHU 1-20
Data Independence
� Application Program
� Data Structure
� Immunity of application to change in
storage structure and access strategy.
Wei-Pang Yang, Information Management, NDHU 1-21
Data Dependence vs. Data Independence
� Data Dependent
e.g. SELECT CITYFROM SWHERE ITEM = 'X';
• Linked list: TOP
if item = TOPPP. item then .........
• Tree:
.
if item < root.data then root := rootP.left ..........
• Array:
if A[I] = item then ............
• Storage structure changed ���� program changed
S
S#S1
S2
:Sn
STop
s1 s2 sn
1-22
1.3 An Architecture for a
Database System
Wei-Pang Yang, Information Management, NDHU 1-23
Host Language
+ DSL
Host Language
+ DSL
Host Language
+ DSL
Host Language
+ DSL
Host Language
+ DSL
User A1 User A2 User B1 User B2 User B3
External View@ # A &
External ViewB
External/conceptual
mapping A
ConceptualView
External/conceptual
mapping B
Conceptual/internal
mapping
Stored database (Internal View)
Databasemanagement
system
(DBMS)
<
DBA
Storagestructuredefinition(Internalschema)
Conceptualschema
Externalschema
A
Externalschema
B
(Build andmaintainschemas
andmappings)
# @&
(Data Sub Language)
#A@ &
e.g. SQL, QUEL
Wei-Pang Yang, Information Management, NDHU 1-24
An Example of the Three Levels
� Internal level:STORED_EMP length = 18
PREFIX TYPE = BYTE(6), OFFSET = 0, INDEX = EMPXEMP# TYPE = BYTE(6), OFFSET=0,DEPT# TYPE = BYTE(4), OFFSET = 12PAY TYPE = FULLWORD, OFFSET = 16
� Conceptual level:EMPLOYEE
EMPLOYEE_NUMBER CHARACTER(6)DEPARTMENT_NUMBER CHARACTER(4) SALARY NUMERIC(5)
� External level:
• ( PL /I )DCL 1 EMP
2 EMP# CHAR(6)2 SAL FIXED BIN(31)
• (COBOL)01 EMPC
02 EMPNO PIC X(4)02 DEPTNO PIC X(4)
Wei-Pang Yang, Information Management, NDHU 1-25
Functions of the DBMS
� Data Definition Language (DDL)
� Data Manipulation Language (DML)
� Data Security and Integrity
� Data Recovery and Concurrency
� Data Dictionary
� Performance
Wei-Pang Yang, Information Management, NDHU 1-26
1960s to
Mid-1970s
1970s to
Mid-1980sLate1980s
Future
Data Model
Database Hardware
UserInterface
ProgramInterface
Presentationand display
processing
NetworkHierarchical
Mainframes
NoneForms
Procedural
ReportsProcessingdata
Relational
MainframesMinisPCs
Query languages- SQL, QUEL
Embeddedquerylanguage
ReportgeneratorsInformation
and transactionprocessing
SemanticObject-orientedLogic
Faster PCsWorkstationsDatabase machines
GraphicsMenusQuery-by-forms
4GLLogic programming
Business graphicsImage outputKnowledge
processing
Merging data models, knowledge representation,and programming
languages
Parallel processingOptical memories
Natural languageSpeech input
Integrated databaseand programminglanguage
Generalized displaymanagersDistributed knowledge
processing
1.4 Database Technology Trends
Wei-Pang Yang, Information Management, NDHU 1-27
Distributed Databases
Database Distributed System
� Distributed database is a database that is not stored
in its entirety at a single physical location, but
rather is spread across a network of computer.
Hsinchu
Taipei
Kaoshiung
computers
Taichung
communication
links
< e.g.>
Wei-Pang Yang, Information Management, NDHU 1-28
Distributed Databases (cont.)
� Advantages: � efficiency of local processing
� data sharing
� Disadvantages:� communication overhead
� implementation difficulties
� Reference: S. Ceri and G. Pelagatti
"Distributed Databases: principles and systems"
Wei-Pang Yang, Information Management, NDHU 1-29
Multi-Database/Heterogeneous Database
Multidatabase
IMS INGRES ORION. . .
Hierarchical
Model
Relational
Model
Object-
Oriented
Model• semantic inconsistency
• data incompleteness
• global schema
Wei-Pang Yang, Information Management, NDHU 1-30
DB + AI Database AI
Language Processor
Query Optimizer
Operator Processor
Access Method
File Manager
Query
Distributed
DB design
Logical
DB design
Knowledge
Base
Knowledge
BaseKnowledge
Base
DBMS
Wei-Pang Yang, Information Management, NDHU 1-31
KBMS
� A Combined Model :
Logic Programming + Relational DB
� Three layers :
Database Logic
User Program
Knowledge
management
program
Relational DB
management
program
IDB
EDB
relational
interface
Query :
? :- ancestor (taro, Y)
? :- grandfather (?, c)
IDB:
ancestor(X,Y):- parent(X,Y)
ancestor(X,Y) :- parent(X,Z), ancestor(Z,Y)
parent(X,Y):-edb(father(X,Y))
parent(X,Y):-edb(mother(X,Y))
grandfather(X,Z):- father(X,Y) ̂father(Y,Z)
EDB: father son
A B X Y . . . .
B C
fathe
r
mother
Wei-Pang Yang, Information Management, NDHU 1-32
OODB
� A typical Document : MEMO [Woelk86, SIGMOD]
Database Object-Oriented
MCCTo: W. Kim
From: D. Woolk
Date: September 18, 1992 can be heard
Subject: Workstations
In the computer center of National Chiao-Tung University, there are
a lot of workstations. There are HP RS serials, SUNs, Apollo, and so
on. The students in NCTU learn to use workstation since they are fresh-
men. The configuration of the workstations follows:
. . .
In the course introduction to Computer Science? students do their
homework's on workstations.
Workstation Workstation
Database
Server
speakervoice message associated
}text
}graphics
}image
Wei-Pang Yang, Information Management, NDHU 1-33
Use of a Database Management System in
Design and Application
Design
Analysis
Design
VerificationEvaluation Synthesis Release
Manufacturing
PlanningProduction
ControlProject
Management
APPLICATION
DBMS
Database Management
System
Graphic Interface Language InterfaceINTERFACE
Preliminary-
design
Analysis
Models
Detailed
Design
Design
Release
ControlFabrication
Assembly
Info
Test /
Inspection
Database Manufacturing
Wei-Pang Yang, Information Management, NDHU 1-34
Fuzzy Database
� Fuzzy Query
Database Fuzzy Set Theory
<e.g.> SELECT STUDENT.NAME
FROM STUDENT
WHERE SEX = MAND HEIGH = TALLER
AND WEIGH = SLIMMER
STUDENT:
NAME SEX HEIGHT WEIGHT IQ
Mary F 158 55 High
Linda F 165 55 Medium
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
<e.g.> SELECT STUDENT.NAME
FROM STUDENT
WHERE IQ >= 130
Wei-Pang Yang, Information Management, NDHU 1-35
More?
DB AI
DB VR
?
?
DB
?
WWW
1993
1995
1997
DB
?
Mobile/video1999
?2003
DB
911/ Anthrax
?2001
DB
DNA/BioInfo
.
?2004
DB ??? SARS
?2006
DB ???