Transcript Chapter 1: Introduction

Introduction to Database
CHAPTER 1
INTRODUCTION










Edited: Wei-Pang Yang, IM.NDHU, 2009
Database-System Applications
Purpose of Database Systems
View of Data
Database Languages
Relational Databases
Database Design
Data Storage and Querying
Transaction Management
Database Architecture
Database Users and Administrators
Source: Database System Concepts, Silberschatz etc. 2006
1-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 that is both
• convenient and
• efficient.

Functions of DBMS: Management of Data (MOD)
 Defining structure for storage data
 Providing 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 technique for MOD
 concepts and technique form the focus of this book, and this course
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-2
1.1 Database-System Applications


Database Applications:
 Banking: all transactions

Airlines: reservations, schedules

Universities: registration, grades, student profile, ..

Sales: customers, products, purchases

Manufacturing: production, inventory, orders, supply chain

Human resources: employee records, salaries, tax deductions
Databases touch all aspects of our lives
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-3
1.2 Purpose of Database Systems


In the early days, database applications were built on top of file
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) become part
of program code
Hard to add new constraints or change existing ones
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-4
Drawbacks of using file systems (cont.)

Drawbacks of using file systems to store data: (cont.)
原子性, 單一性
 Atomicity of updates
• Failures may leave database in an inconsistent state with

partial updates carried out
• E.g. transfer of funds from one account to another should
either complete or not happen at all
Concurrent access by multiple users
• Concurrent accessed needed for performance
• Uncontrolled concurrent accesses can lead to inconsistencies
 E.g. two people reading a balance and updating it at the same
time

Security problems
Solution
Database systems offer solutions to all the above problems
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-5
1.3 View of Data and Data Abstraction

Physical level: describes how a record (e.g., customer information)
is stored in disk.
 By sequential file, pointer, or hash structure, …

Logical level: describes data stored in database, and the
relationships among the data.
type customer = record
name : string;
street : string;
city : string;
income : integer;
end;

View level: application programs hide details of data types. Views
can also hide information (e.g., income) for security purposes.
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-6
View of Data -1: Three Levels
An architecture for a database system
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-7
View of Data -2: Three Levels
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)
Edited: Wei-Pang Yang, IM.NDHU, 2009
1
2
Stored database (Internal View)
Source: Database System Concepts, Silberschatz etc. 2006
3
#
...
100
&
@
1-8
1.3.2 Instances and Schemas

Schema – the logical structure of the database
 e.g., the database consists of information about a set of customers and
accounts and the relationship between them
 Analogous to type information of a variable in a program
 Physical schema: database design at the physical level
 Logical schema: database design at the logical level
account
create table account
(account-number char(10),
balance integer)
type customer = record
name : string;
street : string;
city : integer;
end;
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
customer
1-9
Instances and Schemas (cont.)

Instance – the actual content of the database at a particular point in time
 Analogous to the value of a variable
Instance
Schema
create table account
(account-number char(10),
balance integer)

Physical Data Independence – the ability to modify the physical schema
without changing the logical schema
 Applications depend on the logical schema
 In general, the interfaces between the various levels and components
should be well defined so that changes in some parts do not seriously
influence others.
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-10
View of Data: Three Levels
An architecture for a database system
Physical Data Independence
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-11
1.3.3 Data Models


A collection of conceptual tools for describing
 data (entities, objects)

data relationships

data semantics

data consistency constraints
Data Models Provide:
 A way to describe the design of a database at 3 levels
• Physical level
• Logical level
• View level
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-12
Category of Data Models

Category of Data Models:
 Entity-Relationship model

Relational model

Object-oriented model

Semi-structured data models
• Extensible Markup Language (XML)

Older models:
• Network model and
• Hierarchical model
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-13
1.4 Database Languages

Data Definition Language (DDL):
 Specification notation for defining the database schema

E.g.
create table account
(account-number char(10),
balance integer)

Data Manipulation Language (DML)

To express database queries or updates

E.g.
Select account-number
from account
where balance >1000

SQL (Structured Query Language): a single language for both
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-14
1.4.1 Data-Manipulation Language (DML)

Language for accessing and manipulating the data organized
by the appropriate data model
 DML also known as query language


For retrieval, insertion, deletion, modification (update)
Two classes of languages
 Procedural DMLs – user specifies what data is required and
how to get those data
• E.g. … in C

Declarative DMLs (Nonprocedural DMLs) – user specifies
what data is required without specifying how to get those data
• E.g.

In SQL:
Select account-number
from account
where balance > 700
SQL is the most widely used query language
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-15
1.4.2 Data-Definition Language (DDL)

Specification notation for defining the database schema
 E.g.
create table account
(account-number char(10),
balance integer)

Define:
• Attributes name
• Data type
• Consistency constraints (integrity constraints)
 Domain constraints:
e.g. assets are integer type
 Assertions: e.g. assets >= 0
 Authorization: for different users
 ….
Edited: Wei-Pang Yang, IM.NDHU, 2009
create table branch
(branch-name
char(15),
branch-city
char(30),
assets
integer,
primary key (branch-name),
check (assets >= 0))
Source: Database System Concepts, Silberschatz etc. 2006
1-16
Data Dictionary and Storage Definition

Data Dictionary:
 DDL compiler generates a set of tables stored in a data dictionary
 contains metadata (i.e., data about data)
• Database schema
• System tables
• Users
•…
 Database system consults the Data dictionary before reading or
modifying actual dada.

Data storage and definition language
• To specify the storage structure and access methods
• Usually an extension of the data definition language
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
(ch. 11,12)
1-17
1.5 Relational Databases

Definition 1: A Relational Database is a database that is perceived
by the users as a collection of time-varying, normalized relations
(tables).
•
•
•

Perceived by the users: the relational model apply at the view level and
logical levels.
Time-varying: the set of tuples changes with time.
Normalized: contains no repeating group (only contains atomic value).
The relational model represents a database system at a level of
abstraction that removed from the details of the underlying machine,
like high-level language.
C, PASCAL ,PL/1
assembler
machine
Edited: Wei-Pang Yang, IM.NDHU, 2009
DBMS environments
Relational DBMS
Relational
Data Model
Source: Database System Concepts, Silberschatz etc. 2006
1-18
1.5.1 Tables

Definition 2: A Relational Database is a database that is perceived by
its users as a collection of tables (and nothing but tables).
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-19
1.5.2 Data-Manipulation Language

SQL (Structured Query Language) : widely used
 E.g. find the name of the customer with customer-id 192-83-7465
select customer.customer-name
from customer
where customer.customer-id = ‘192-83-7465’
customer
Output:
customer-name
Johnson
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-20
SQL (Structured Query Language)

E.g. find the balances of all accounts held by the customer with
customer-id 192-83-7465
select account.balance
from depositor, account
where depositor.customer-id = ‘192-83-7465’ and
depositor.account-number = account.account-number
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-21
1.5.3 Data-Definition Language

SQL provides DDL to define database schema:
 Tables
• E.g.
create table account
(account-number char(10),
balance integer)

Assertions (ref. p.132)
•

E.g. create assertion balance-constraint
check account.balance >= 1000
integrity Constraints (ref. p.129)
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-22
Referential Integrity Constraint
create table account
(account-number char(10),
branch-name
char(15),
balance
integer,
primary key (account-number),
3. account
存款帳
references
create table depositor
(customer-name
char(20),
account-number char(10),
primary key (customer-name, account-number),
foreign key (account-number) references account,
Edited: Wei-Pang Yang, IM.NDHU, 2009
4. depositor
存款戶
Source: Database System Concepts, Silberschatz etc. 2006
1-23
1.5.4 Data Access from Application Programs

Application programs generally access databases through one of
 Language extensions to allow embedded SQL

Application program interface (e.g. ODBC/JDBC) which allow
SQL queries to be sent to a database

ODBC: Open Database Connectivity for C

JDBC: Java Database Connectivity for Java language
ODBC/JDBC
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-24
1.6 Database Design


Database Design - The process of designing the general structure of
the database:
 Logical Design
 Physical Design
Logical Design – Deciding on the database schema.
 To find a “good” collection of relation schemas.



Business decision – What attributes should we record in the
database?
Computer Science decision – What relation schemas should we
have and how should the attributes be distributed among the
various relation schemas?
Physical Design – Deciding on the physical layout of the database
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-25
1.6.1 Design Process

Phase I
 Specification of user requirement (with domain experts)

Phase II





Phase III


Conceptual design (ch. 6)
Choose a data model
Design tables
Normalization (ch. 7)
Specification of functional requirements
Phase IV



Implementation
Logical-design
Physical-design (ch. 11, 12)
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-26
1.6.2 Database Design for Banking

Banking Database: consists 6 relations:
1. branch (branch-name, branch-city, assets)
2. customer (customer-name, customer-street, customer-only)
3. account (account-number, branch-name, balance)
4. loan (loan-number, branch-name, amount)
5. depositor (customer-name, account-number)
6. borrower (customer-name, loan-number)
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-27
Example: Banking Database
1. branch 分公司
4. borrower
貸款戶
Edited: Wei-Pang Yang, IM.NDHU, 2009
2. customer 客戶(存款戶,貸款戶) 3. depositor
5. account
存款帳
6. loan
Source: Database System Concepts, Silberschatz etc. 2006
存款戶
貸款帳
1-28
1.6.3 Entity-Relationship Model (ch.6)
 Example: Schema in the Entity-Relationship model
客戶
存款帳
存款帳
客戶(存款戶,貸款戶,信用卡戶)
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
存款戶
1-29
E-R Diagram for a Banking Enterprise, p.240
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-30
Entity Relationship Model (cont.)

E-R model of real world
 Entities (objects)
• E.g. customers, accounts, bank branch

Relationships between entities
• E.g. Account A-101 is held by customer Johnson
• E.g. Relationship set depositor associates customers with
accounts

Widely used for database design



Database design in E-R model usually converted to design in the
Relational model (coming up next) which is used for storage and
processing
Relational Model (ch. 2)
E-R model (ch. 6)
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-31
1.6.4 Normalization

Definition: A Relational Database is a database that is perceived by
its users as a collection of tables (and nothing but tables).
<e.g.> Supplier-and-Parts Database
S
P
S#
S1
S2
S3
S4
S5
P#
P1
P2
P3
P4
P5
P6
SP S#
SNAME STATUS CITY
Smith
20
London
Jones
10
Paris
Blake
30
Paris
Clark
20
London
Adams
30
Athens
PNAME
Nut
Bolt
Screw
Screw
Cam
Cog
Edited: Wei-Pang Yang, IM.NDHU, 2009
COLOR
Red
Green
Blue
Red
Blue
Red
WEIGHT
12
17
17
14
12
19
CITY
London
Paris
Rome
London
Paris
London
S1
S1
S1
S1
S1
S1
S2
S2
S3
S4
S4
S4
P# QTY
P1 300
P2 200
P3 400
P4 200
P5 100
P6 100
P1 300
P2 400
P2 200
P2 200
P4 300
P5 400
Source: Database System Concepts, Silberschatz etc. 2006
1-32
Problem of Normalization
<e.g.>
S1, Smith, 20, London, P1, Nut, Red, 12, London, 300
S1, Smith, 20, London, P2, Bolt, Green, 17, Paris, 200
.
.
S4, Clark, 20, London, P5, Cam, Blue, 12, Paris, 400
S
P
S# SNAME STATUS CITY
P#
s1
.
.
.
.
.
.
.
London
.
S'
or
Normalization
SP
.
...
Smith
.
.
STATUS
.
.
.
P#
.
.
Redundancy
Edited: Wei-Pang Yang, IM.NDHU, 2009
S#
...
.
.
.
.
.
.
.
.
P#
QTY
.
.
.
.
SP'
P
S# SNAME
S1
S2
...
...
.
.
...
.
.
...
.
.
S#
P#
QTY
S1 London
P1
300
S1 London
P2
200
.
.
.
CITY
.
Update Anomalies!
(異常)
Source: Database System Concepts, Silberschatz etc. 2006
1-33
1.7 Object-Based and Semistructured Databases

Extend the relational data model
 by including object orientation and

constructs to deal with added data types. (video, image, …)

Allow attributes of tuples to have complex types, including
 non-atomic values such as nested relations. (repeated data, …)

Preserve relational foundations,
 in particular the declarative access to data, while extending
modeling power.
6. borrower
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-34
1.7.2 Semistructured Data Models

XML (Extensible Markup Language)
 Defined by the WWW Consortium (W3C)
聯合

Originally intended as a document markup language not a
database language

The ability to specify new tags, and to create nested tag
structures made XML a great way to exchange data, not just
documents

XML has become the basis for all new generation data
interchange formats.

A wide variety of tools is available for parsing, browsing
and querying XML documents/data
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-35
1.8 Data Storage and Querying

Components of Database System
 Query Processor
•
•
•

Helps to simplify to access data
High-level view
Users are not be burdened
unnecessarily with the physical
details
Query
DBMS
Language Processor
Optimizer
Query Processor
Operation Processor
Storage Manager
•
•
•
•
Require a large amount of space
Can not store in main memory
Disk speed is slower
Minimize the need to move data
between disk and main memory
Access Method
Storage Manager
File Manager
Goal of a DBMS: provides a way to store and
retrieve data that is both convenient and efficient.
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
Database
1-36
Overall System Structure
Overall
System
Structure
low-level data stored
databaseSource: Database System Concepts, Silberschatz etc. 2006
Edited: Wei-Pang Yang, IM.NDHU, 2009
1-37
1.8.1 Storage Management

Storage Manager
 is a program module
 that provides the interface between the low-level data stored and the
application programs and queries submitted to the system.

Tasks of the Storage Manager:
 interaction with the file manager (part of Operating System)
 Translates DML into low-level file-system commands,
 i.e. responsible for storing, retrieving and updating of data in database

Data Structures of the Storage Manager
 Data files: store database itself
 Data Dictionary: store metadata
 Indices: provide fast access to data items that hold particular values
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-38
Storage Management (cont.)

Components of Storage manager:
 Authorization and Integrity Manager
• Tests for the satisfaction of integrity constraints
• Checks the authority of users to access data
 Transaction Manager
• Ensure the database in a consistent state (correct) after failures
• Ensure that concurrent transaction executions proceed without
conflicting
 File Manager
• Manages the allocation of space on disk
• Manages the data structures used to representation data stored
 Buffer manager
• Fetches data from disk into main memory
• Decides what data to cache in main memory
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-39
1.8.2 The Query Processor

DDL Interpreter
 Interprets DDL statements


DML Compiler
 Translates DML statements into an evaluation plan (or some
evaluation plans) which consists low-level instructions


write the definitions (schema, view, ..) into the data dictionary
Query Optimization: picks the lowest cost evaluation plan
Query Evaluation Engine:
 execute low-level instructions generated by the DML Compiler
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-40
Flow of Query Processing
1. Parsing and translation
2. Optimization
3. Evaluation
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-41
Query Optimizer

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
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-42
Example: A Simple Query Processing (補)
Query in SQL：
DBMS
SELECT CUSTOMER. NAME
FROM CUSTOMER, INVOICE
WHERE REGION = 'N.Y.' AND
AMOUNT > 10000 AND
CUTOMER.C#=INVOICE.C
Language Processor
Internal Form :
( (S
SP)
Optimizer
Operator :
SCAN C using region index, create C
SCAN I using amount index, create I
SORT C?and I?on C#
JOIN C?and I?on C#
EXTRACT name field
Language
Processor
Query
Processor
Access
Method
Storage
Manager
Operator Processor
Calls to Access Method：
OPEN SCAN on C with region index
GET next tuple
.
.
.
Calls to file system：
GET10th to 25th bytes from
block #6 of file #5
Access Method
e.g.B-tree; Index;
Hashing
File System
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
database
1-43
1.9 Transaction Management

Transaction:
 A transaction is a collection of operations that performs a single
logical function in a database application


Atomicity: all or nothing
Failure recovery manager
 ensures that the database remains in a consistent (correct) state,

Failure:
• system failures (e.g., power failures and operating system
•

crashes)
transaction failures.
Concurrency-control manager
 controls the interaction among the concurrent transactions, to
ensure the consistency of the database.
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-44
1.10 Data Mining and Analysis

Data Analysis and Mining
 Decision Support Systems

Data Analysis and OLAP (Online analytical processing),

Data Warehousing

Data Mining
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-45
Decision Support Systems

Decision-support systems
 are used to make business decisions,



often based on data collected by on-line transaction
systems.
Examples of business decisions:
 What items to stock?

What insurance premium to change?

To whom to send advertisements?
Examples of data used for making decisions

Retail sales transaction details

Customer profiles (income, age, gender, etc.)
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-46
Data Mining (ch.18)

Data mining:
 seeks to discover knowledge automatically in the form of statistical rules
and patterns from large databases. E.g. p.23: Young women buy cars.


Prediction based on past history
 Predict if a credit card applicant poses a good credit risk, based on some
attributes (income, job type, age, ..) and past history


is the process of semi-automatically analyzing large databases to find
useful patterns
Predict if a pattern of phone calling card usage is likely to be fraudulent
欺騙的
Descriptive Patterns
 Associations
• Find books that are often bought by “similar” customers.

If a new
such customer buys one such book, suggest the others too. (library)
Associations may be used as a first step in detecting causation 引起;因果關係
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-47
1.11 Database Architecture

System Structure of a Database System
 Fig. 1.6 (p.25)

Application Structure
 User uses database at the site

Users uses database through a network
• Client: remote database users work
• Sever: database system runs here

Partition of Database Application
 Two-tier architecture

Three-tier architecture
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-48
Application Architectures
ODBC/JDBC


Two-tier Architecture: e.g. client programs using ODBC/JDBC
to communicate with a database
Three-tier Architecture: e.g. web-based applications, and
applications built using “middleware”
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-49
1.12 Database Users and Administrators
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)
Edited: Wei-Pang Yang, IM.NDHU, 2009
1
2
Stored database (Internal View)
Source: Database System Concepts, Silberschatz etc. 2006
3
#
...
100
&
@
1-50
1.12.1 Database Users and User Interfaces



單純的

Application programmers
 interact with system through DML calls
Sophisticated users 複雜, 多用途
 Submit query without write program
 E.g. OLAP (Online analytical processing), data mining tools
Specialized users
 write specialized database applications that do not fit into the
traditional data processing framework
 E.g. CAD, expert system, complex data type (graphics, audio)
Naive users (end user)
 invoke one of the permanent application programs that have
been written previously
 E.g. people accessing database over the web, bank tellers,
clerical staff 辦事員
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-51
1.12.2 Database Administrator


Database Administrator:
 Coordinates all the activities of the database system;
 has a good understanding of the enterprise’s information
resources and needs.
Database Administrator's Duties:
 Schema definition
 Storage structure and access method definition
 Schema and physical organization modification
 Granting of authorization for data access
 Routine maintenance
• Periodically backup database
• Upgrade system e.g. disk
• Monitoring performance

…
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-52
1.13 History of Database Systems





1950s – early 1960:

Tapes: sequentially

Application: Payroll,

Input: punched decks, Output: printer
Late 1960s -- 1970s:

Disk: direct access

Codd proposed Relational Model, …  Turing Award
1980s:

System R: IBM Res. Lab.  IBM DB2, Oracle, Ingress, DEC Rdb

Replaced Network/Hierarchical model

Research: parallel database, distributed database, object-oriented, …
Early 1990s:

Parallel database

Object-Relational
Late 1990s:

World Wide Web was explosive growth

Database were used much more than ever before

Database had to support Web interfaces to data
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-53
History of Database Systems (補)
1950-1965
Data Model
Network
Hierarchical
Database
Hardware
Mainframes
User
Interface
None
Forms
Program
Interface
Procedural
Presentation
and display
processing
Reports
Processing
data
Edited: Wei-Pang Yang, IM.NDHU, 2009
1965-1979
1980-1989
Semantic
Objectoriented
Relation proposed Logic
Relation
Mainframes
Minis
Mainframes
PCs
Network
Hierarchical
DL/I
COBOL+DL/I
Procedural
Reports
Processing
data
Graphics, Menus
SQL, QUEL
Query-by-forms
Embedded
Query
non-Procedural
Report
generators
Information
and transaction
processing
1990-1995
Merging data
models,
knowledge-base
Relation
1995-present
Object-Oriented
OO-relation
XML
Relation
Faster PCs
Workstations
Parallel
Database machines Optical memories
Natural language
Speech input
WWW
Web interface
Integrated database
4GL
and programming
Logic programming
language
Business graphics
Image output
Knowledge
processing
Source: Database System Concepts, Silberschatz etc. 2006
Multimedia
1-54
計算機科學的諾貝爾獎 – 杜林獎 (趙坤茂)




象徵最崇高學術桂冠的諾貝爾獎，從1901年開始頒發，根據瑞典發
明家諾貝爾的遺囑，設有物理、化學、生理醫學、文學及和平等五
個獎項；自1969年起，增設了經濟學諾貝爾獎。
疑問: 為什麼諾貝爾獎沒有數學獎項呢？坊間流傳的說法是，當初
諾貝爾的夫人，曾經和瑞典一位很有成就的數學家米塔雷符勒有過
一段婚外情，所以諾貝爾決定不設數學獎項。
英國數學家亞蘭杜林(Alan Turing，1912-1954)，雖然無緣在有生之
年得到諾貝爾獎，但後人為了紀念他在數位計算理論貢獻而設立的
杜林獎(Turing Award)，已被公認是計算機科學領域最崇高的獎項。
杜林獎從1966年開始頒發，受獎人都是對計算機科學有深遠影響的
大師級學者。例如，在計算複雜度理論上有卓越貢獻的庫克(Cook)、
C程式語言的創始人理奇(Ritchie)、Unix作業系統製作人湯普生
(Thompson)及資料庫管理系統的先驅卡德(Codd)等。
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-55
計算機科學的諾貝爾獎 – 杜林獎 (cont.)





1936年時，杜林提出了一個假想性的計算工具，稱為杜林機器
(Turing machine)，這個機器有一個長條型、無窮多格的儲存磁帶，
每一格位置是空白或一個符號；附帶在磁帶上的是一個可讀寫的磁
頭，它可以在磁帶的格子往左或往右，並在每次移動時讀、寫或擦
拭該格子；還有一個有限狀態控制機，可運用狀態的改變，配合目
前磁頭所在的位置，來決定這些移動讀寫的動作。
這樣一個簡單的機器，它的運算功力竟然相當於今天的數位計算機，
換句話說，目前數位計算機可以運算的方法，我們都可以在杜林機
器上實現！
杜林也提出了如何決定電腦是否會“思考”的方法，也被視為人工
智慧研究領域的基石。
在二次世界大戰時，杜林曾發展一個可以破解德軍密碼的機器，不
過世人在戰爭結束二十五年後才知曉。
他也是馬拉松運動的好手，真是多才多藝的科學家。可惜他在1954
年時就過世，只享年42歲。
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-56
Mail: 台大醫院資訊室國防役徵才
楊老師您好:
台大醫院資訊室國防役徵才啟事
資格：符合國防役甄選資格之資訊、電機、醫工相關領域碩士及博士各一名
待遇：比照大學講師，表現良好者提供在職進修機會
工作內容：
目前台大醫院的醫療資訊系統 (HIS) 是二十年前開始規劃開發的，運作的平台是IBM mainframe 和它的
hierarchical database。所謂的 HIS 處理的是門診、急診、住院等的掛號、看診、檢查、處方、批價、領藥，乃至
於向健保局的申報等等作業。二十年來經過多手的維護，因應新的法規、業務等的增修等等因素，已經使得現有
HIS變成龐然巨物，幾乎沒有人可以從容的掌控了。
另外因為mainframe 的維護費用居高不下，實在有必要在開放式平台上用新的架構，如relational database，
開發新的醫療資訊系統。過去這類案子，大部分的醫院都是採用委外開發的方式進行。不過過份的依賴委外開發使
得臺大醫院資訊室無法掌握程式碼，以致於較難做有效的維護。所以這次我們組織新的開發團隊負責程式撰寫，搭
配臺大醫院資訊室現有人員的系統分析，共同完成開放式平台上新的醫療資訊系統。
今年預計可以完成門診系統，隨後將進行住院、急診以及行政系統。醫療資訊系統有其專業，而且永遠有市場
需求。台大醫院的複雜度，將使這些 know how 可以涵蓋台灣大多數的醫院。目前臺大醫院除了總院 (含公館院區)
外還有、雲林分院、北護分院等，涵蓋了醫學中心、區域醫院以及地區醫院。將來還將擴展到結盟的基層診所，組
合成一個完整的醫療體系。進而可以把研發的成果推廣到各醫療機構 (含國軍醫院體系)。
有關團隊人員的長期生涯規劃，我們將會朝著鼓勵他們協助他們成立育成公司的方向前進。醫療資訊系統的
know how 將是該育成公司的最大資產。
意者請於 94/3/15 前將履歷及自傳 eMail 至 [email protected] (翟家屏組長)
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-57
Oracle database下載
我已經將 Oracle database 10g的軟體放在FTP站上面了,供給每位同學下載,
方法一: 使用FTP軟體
IP: 134.208.27.191 Port: 21
making.csie.ndhu.edu.tw
username: oracle
password: im07
方法二: 使用MicrosoftInternet Explorer軟體
可直接使用這行URL:
ftp://oracle:[email protected]/
下載 10g_win32_db.zip (576MB)這個檔案來安裝,就可以了!!
方法三: 光碟
五片光碟交給老師,麻煩老師發給班上的幹部(ex:班代,副班代,總務.....等),
以方便他們軟體安裝(或許有些人家中沒有網路的,比較可以安裝好軟體)
詹博聞 3/8
電話: 0933-201920
E-mail: [email protected]
Edited: Wei-Pang Yang, IM.NDHU, 2009
Source: Database System Concepts, Silberschatz etc. 2006
1-58