Transcript 無投影片標題

DATABASE SYSTEMS
OVERVIEW
國立東華大學
資訊管理系
楊維邦 教授
Wei-Pang Yang, Information Management, NDHU
0-1
Database System: Introduction

Database Management System (DBMS)
 Contains a large bodies of information
 Collection of interrelated data (database)
 Set of programs to access the data

Goal of a DBMS:
 provides a way to store and retrieve database information
• convenient and
• efficient
Wei-Pang Yang, Information Management, NDHU
0-2
Database System: Introduction (cont.)

Functions of DBMS: Management of Data (MOD)
 Defining structure for storage data
 Proving mechanisms for manipulation of data
 Ensure safety of data (system crashes, unauthorized access,
misused, …)
 Concurrent control in multi-user environment

Computer Scientists:
 Developed a lot of concepts and techniques for MOD
 Concepts and techniques form the focus of this course
Wei-Pang Yang, Information Management, NDHU
0-3
Contents
 Introduction
 Why Database Systems?
 An Architecture for a Database System
 Data Models
 Components of a Database System
 How to establish a Database System?
 Discussion and Remarks
 Extending Database Technology
 Data Structure and Database 課程流程圖
 Research on Database
Wei-Pang Yang, Information Management, NDHU
0-4
0.1 Why Database Systems?
 Conventional Data Processing techniques:
Enterprise:
Application
System A
Application
System B
Application
Program A
Application
Program B
File System A
Customer
File System B
Invoice
File System N
Inventory
…
Application
System N
Wei-Pang Yang, Information Management, NDHU
Application
Program N
0-5
0.1 Why Database Systems?
 File System
Customer
Customer No.
Customer Name
Customer Addr.
Social Security ID
Inventory
Part No.
Part Description
Unit Price
Supplier
Quantities Remain
Quantities Ordered
Wei-Pang Yang, Information Management, NDHU
Invoice
Customer No.
Customer Name
Part No.
Quantities
Unit Price
Parts
Part No.
Part Description
Supplier
Quantities Ordered
Customer Name
Unit Price
0-6
File
0.1 Why Database Systems?
System: Advantages and Disadvantages
 Advantages: File Systems are simple in design
 Disadvantages:
• Data Redundancy:
• a waste of memory
• high update cost
• data inconsistency
• Data Incompleteness
• Data Insecure
• Application Program Unstable
• file system (application program)
data changed
data structure changed
program changed
Wei-Pang Yang, Information Management, NDHU
0-7
0.1 Why Database Systems?
Solution: Database Systems!!
 Objectives of Database Systems:
•
•
•
•
•
eliminate unnecessary data redundancy
maintain data integrity
control security
achieve data independence
add program stability
Wei-Pang Yang, Information Management, NDHU
0-8
0.2 Architecture for a Database System
View 1: DBMS Components
Query
DBMS
SQL: Select S#, P# from S, P
Language Processor
where…..
Optimizer
Operation Processor
Access Method
File Manager
Database
Wei-Pang Yang, Information Management, NDHU
0-9
0.2 Architecture for a Database System
View 2: Three Tiers
User A1
Host
Language
+ DSL
User A2
Host
Language
+ DSL
User B1
Host
Language
+ DSL
User B2
User B3
Host
Host
C, C++
Language
Language
+ DSL
+ DSL
DSL (Data Sub. Language)
e.g. SQL
1
2
External View
@
#
&
3
External
schema
A
External/conceptual
mapping A
Conceptual
schema
<
External View
B
External/conceptual
mapping B
Conceptual
View
Database
management
system
Dictionary
(DBMS) e.g. system
catalog
Conceptual/internal
mapping
DBA
(Build and
maintain
schemas
and
mappings)
External
schema
B
Storage
structure
definition
(Internal
schema)
Wei-Pang Yang, Information Management, NDHU
1
Stored database (Internal View)
2
3
#
...
100
&
@
0-10
0.2 Architecture for a Database System:
View 3: User’s View vs. Internal
Scan figure
Wei-Pang Yang, Information Management, NDHU
0-11
0.2 Architecture for a Database System:
View 4: DBA vs. Programmers
Scan figure
Wei-Pang Yang, Information Management, NDHU
0-12
0.3 Data Models:

Hierarchical Data Model

Network Data Model

Relational Data Model

Object-Oriented Data Model

…
Wei-Pang Yang, Information Management, NDHU
0-13
Hierarchical Data Model
“Data Pile”
course# name prereq.1 prereq.2 off.1 92/9/6 EE108
T003 Yang
course# name
prereq
off. 1 off. 2
Course
Course#
92/9/6
Title
prereq.1
Offering
PreReq
Prereq.#
Off.#
Date
Location
Teacher
Emp.#
prereq.2
Student
Name
Wei-Pang Yang, Information Management, NDHU
Emp.#
Name
Grade
0-14
Network Data Model
S
S#
P
SNAME
STATUS
CITY
P-SP
S-SP
SP
Wei-Pang Yang, Information Management, NDHU
(Links)
QTY
0-15
S
S1 Smith 20 London
Wei-Pang Yang, Information Management, NDHU
S2
S3
S4
S5
400
300
P6
200
P5
200
P4
400
P3
300
100
400
200
300
SP
P2
P1
200
P
100
The Network Model: Sample Values
S1 P1
……
S2 P1
S2 P2
-- --
0-16
300
300
400
---
Relational Data Model: [Codd70]
S: Supplier

System R

DB2

INGRES

Oracle

Informix

ACCESS

SP :Shipment
S#
City
P#
QTY
S# NAME
Status
S1 Smith
20
London
S1
P1
300
S2 Jones
10
Paris
S1
P2
200
S1
P3
400
.
.
.
S2
P1
400
S2
P2
200
S3
:
:
:
S4
:
:
:
…
P: Parts
P#
P-Name
Color
P1
P2
P3
Nut
Screw
:
Red
Blue
:
12
17
:
……
.
:
:
:
:
:
Wei-Pang Yang, Information Management, NDHU
:
Weight …………
0-17
Object-Oriented Data Model
Person
name
Employee
dept.
owns
photo
Vehicle
video
salary
face-image
name
model
manu.
car-video
Company
hair-color
aggregation
inheritance
Wei-Pang Yang, Information Management, NDHU
name
location
0-18
0.4 Components of a Database System




DDL (Data Definition Language)
DML (Data Manipulation Language)
Data Dictionary
Utility Routines
Wei-Pang Yang, Information Management, NDHU
0-19
0.4 Components of a Database System:
DDL (Data Definition Language)
S: Supplier
CREATE TABLE S
(S#
CHAR(5)
SNAME CHAR(20)
STATUS SMALLINT
CITY
CHAR(15)
PRIMARY KEY (S#));
NOT NULL,
NOT NULL,
NOT NULL,
NOT NULL,
S# NAME
Status
City
ALTER TABLE S ADD DISCOUNT SMALLINT;
DROP TABLE S
CREATE INDEX XSC ON S (CITY);
CREATE UNIQUE INDEX X ON S (S#);
DROP INDEX XSC;
Wei-Pang Yang, Information Management, NDHU
0-20
0.4 Components of a Database System:
DML (Data Manipulation Language)
SELECT S#, STATUS
FROM S
WHERE CITY=‘PARIS’
S: Supplier
S# NAME
Status
City
UPDATE S
SET STATUS= 2*STATUS
WHERE CITY=‘LONDON’;
DELETE
FROM S
WHERE S#= ‘S5’
INSERT
INTO S (S#, SNAME, STATUS, CITY)
VALUES(‘S6’,’TSENG’,100,’HSINCHU’)
Wei-Pang Yang, Information Management, NDHU
0-21
0.4 Components of a Database System:
Data Dictionary
Request
Application Program
9
Working Area
return
1
8
7
6
2
DBMS
System Buffer
Data
Dictionary
3
5
OS
Secondary Memory
4
Database
Wei-Pang Yang, Information Management, NDHU
0-22
0.4 Components of a Database System:
Utility Routines:
•
•
•
•
•
•
•
Wei-Pang Yang, Information Management, NDHU
Loading Routines
Reorganization Routines
Journalizing routines (log)
Database Dump Routines
Recovery Routines
Statistical Analysis Routines
…
0-23
0.5 How to Establish a Database System?
 STEP 1: Database Design
• Logical database vs. physical database
• Collect data of applications
• Analyze data to eliminate redundancy (using normalization
theory and E-R Model…)
• Describe data in the specific Data Model the DBMS use.
• Describe each schema in DDL


STEP 2: Implementation
• schema
• data
SETP3:Evaluation and Correction (by DBA)
• tuning
• statistical analysis
Wei-Pang Yang, Information Management, NDHU
0-24
0.6 Discussion and Remarks
 Advantages of database systems
•
•
•
•
•
•
•
redundancy can be reduced
Inconsistency can be avoid
Data can be shared
Standards can be enforced
Security restrictions can be applied
Integrity can be maintained
Provision of data independence
 Disadvantages of database systems
• Database design and control is a complicated matter.
Wei-Pang Yang, Information Management, NDHU
0-25
0.7 Extending Database Technology
• Expert Database Systems
Knowledge-base Management Systems
AI + DB
• Image Database Systems
Intelligent Pictorial Databases
Image + DB
• Object-Oriented Database Systems
OO Programming + DB
• Multimedia Database
Text + Voice + Image + …..+ DB
• Multidatabases
Integrate heterogeneous /homogeneous
database systems
Wei-Pang Yang, Information Management, NDHU
0-26
Extending Database Technology
• Real-time Database Systems
Real-time Tech. + DB
• Video Database Systems
MPEG + DB
• Digital Library
Library + DB
• Bioinformatic Database Systems
Biological + DB
• …
Wei-Pang Yang, Information Management, NDHU
0-27
0.8 DS and Database 課程流程圖
一上:計算機概論
大
一
一上:程式設計I
一下:程式設計II
二上: 資料結構
大
二
基本, 入門, 觀念
二下: 資料庫管理
三四上:資料庫進階
Sorting, Hashing, 檔案結
構, web, 實例應用
大三四
<大學可先修研究所課程>
研上:高等資料庫管理
研上:高等資料庫管理
原理, 研究
研究所
研下:多媒體資料庫 研下:影像資料庫
Wei-Pang Yang, Information Management, NDHU
研下:知識庫管理
0-28
資料庫管理 (大二下)
Chapter 1: Introduction
Chapter 2: Entity-Relationship Model
Chapter 3: Relational Model
Chapter 4: SQL
Chapter 5: Other Relational Languages
Chapter 6: Integrity Constraints
Chapter 7: Relational Database Design

References:
1. Silberschatz etc, Database System Concepts, 5th edition, McGraw Hill, 2006
Wei-Pang Yang, Information Management, NDHU
0-29
資料庫進階 (大三四上)
Unit 1: Sorting (DS. Chapter 7)
Unit 2: Hashing (DS. Chapter 8)
Unit 3: Heap Structure (DS. Chapter 9)
Unit 4: Searching Structure (DS. Chapter 10)
Unit 5: XML (DBMS Chapter 10)
Unit 6: Storage and File Structure (DBMS Chapter 11)
Unit 7: Indexing and Hashing (DBMS Chapter 12)

References:
1. A. Silberschatz, etc, Database System Concepts, 5th edition, McGraw Hill, 2006
2. E. Horowitz, etc. Fundamentals of Data Structures, …
Wei-Pang Yang, Information Management, NDHU
0-30
資料庫管理 (大二下)
今年
PART 0 Basic Concepts
Chapter 0: Overview
Chapter 1: Introduction
PART 1 RELATIONAL DATABASES
Chapter 2: Relational Model
Chapter 3: SQL
Chapter 4: Advanced SQL
PART 2 DATABASE DESIGN
Chapter 6: Database Design and the E-R Model
Chapter 7: Relational Database Design
Chapter 8: Application Design and Development
PART 4 DATA STORAGE AND QUERYING
Chapter 11: Storage and File Structure
Chapter 12: Indexing and Hashing

Text Book: A. Silberschatz etc, “Database System Concepts,” 5th edition, McGraw Hill, 2006
Wei-Pang Yang, Information Management, NDHU
0-31
高等資料庫管理 (研究所下)
Unit 1
Unit 2
Unit 3
Unit 4
Unit 5
Unit 6
Unit 7
Unit 8
Unit 9
Unit 10
Unit 11
Unit 12

Introduction to DBMS
DB2 and SQL
The Relational Model
The Hierarchical Model
The Network Model
Access Methods
Logical Database Design
Database Recovery
Concurrency Control
Security and Integrity
Query Optimization
Distributed Database
References:
1. C. J. Date, An Introduction to Database Systems, 8th edition, 2004.
2. J. D. Ullman, Principles of Database and Knowledge-Base, Vol.I, 1988.
3. Cited papers
Wei-Pang Yang, Information Management, NDHU
0-32
高等資料庫管理 II (研究所)
Unit 13 Object-oriented Database
Unit 14 Logic-Based Database
Unit 15 Image Database
Unit 16 Multimedia Database
Unit 17 Multidatabase
Unit 18 Real-time Database
Unit 19 Parallel Database
Unit 20 Temporal Database
Unit 21 Active Database
Unit 22 Bioinformatic Database
Unit 23 ….

References:
Cited papers from Journals and conferences
Wei-Pang Yang, Information Management, NDHU
0-33
GenBank Database Growth
Wei-Pang Yang, Information Management, NDHU
0-34
Protein Database Growth
Wei-Pang Yang, Information Management, NDHU
0-35
Entrez
 Developed at NCBI, freely available and allow for integrated access
to PubMed records, nucleotide and protein sequence data and 3D
structure information
 http://www.ncbi.nlm.nih.gov/Entrez
Wei-Pang Yang, Information Management, NDHU
0-36
Wei-Pang Yang, Information Management, NDHU
0-37
Wei-Pang Yang, Information Management, NDHU
0-38
0.9 Research on Database
Level 5: Doing Research
Level 4: Survey Papers: Special Topics (Unit 13 -
)
Level 3: DBMS: Advanced Topics
(Unit 7 – 12)
Date, Vol. 1, 2
Ullman
Level 2: DBMS: Fundamentals
(Unit 1 – 6)
Date, Vol. 1
Using DB2,
Project: Design a “mini” DBMS
Level 1: Using DBMS
Wei-Pang Yang, Information Management, NDHU
0-39
Where to find Database papers?

Journals:
•
•
•
•
•
•

ACM Trans. On Database Systems (TODS)
IEEE Trans. On Software Engineering (TOSE)
IEEE Trans. On Knowledge and Data Engineering
ACM Trans. On Office Information Systems (TOOIS)
Journal of Information Science and Engineering Taiwan
…
Conferences:
•
•
•
•
•
•
VLDB (Very Large Data Base)
ACM SIGMOD
PODS (Principal of Database Systems)
IEEE Data Engineering
NCS, ICS (National/International Computer Symposium) Taiwan
…
Wei-Pang Yang, Information Management, NDHU
0-40
DATABASE SYSTEMS: OVERVIEW









Why Database Systems?
An Architecture for a Database System
Data Models
Components of a Database System
How to establish a Database System?
Discussion and Remarks
Extending Database Technology
Data Structure and Database 課程流程圖
Research on Database
Wei-Pang Yang, Information Management, NDHU
0-41