Chapter 2
Database System Architecture
An “architecture” for a database system.
A specification of how it will work, what it will “look
like.”
The “ANSI/SPARC” architecture provides a model
for a general description of database concepts.
A m erican N atio n al S tan d ard s
In stitu te
(A N S I)
N atio n al C o m m ittee o n
In fo rm atio n
T ech n o lo g y S tan d ard s
(N C IT S )
S tan d ard s P lan n in g an d
R eq u irem en ts C o m m ittee
(S P A R C )
“ANSI”
“X3”
“ANSI/SPARC”
S tu d y G ro u p o n D atab ase
M an ag em en t
S ystem s
R ep o rt
“D B M S F ram ew o rk ”
1972, 1978
“ANSI/SPARC”
Architecture
The Architecture
• Three “levels” of the architecture:
– Internal: How it is physically stored.
– External: How it is used, what does it “look
like” to the user (a user).
– Conceptual: An abstract specification--how
does it “look” in general, in its entirety.
Fig. 2.1 The three levels of the architecture
User 1
User 2
User n
Each user (or user
program) has a
different, and
partial view of the
data.
The “view” of the
entire database (as
seen by the
designer(s) and
DBA.
How the data is
actually
(physically) stored.
Fig. 2.1 The three levels of the architecture
Internal level, physical level: the way the data is
actually stored.
External level, user logical level: the way the
data is seen by each individual user or user
program.
Conceptual level, community logical level: the
logical view of the entire data structure.
Definitions only--no consideration of how used or
stored.
E x tern a l:
(J a v a )
class Employee {
String employeeNumber;
String departmentNumber;
float salary;
}
C o n cep tu a l:
E m p lo y ee
E m p lo y ee_ N u m b er ch a r (6 )
D ep a rtm en t_ N u m b er ch a r (4 )
S a la ry n u m eric(5 )
In tern a l:
STORED_EMP
PREFIX
EMP#
DEPT#
PAY
LENGTH = 18
TYPE=BYTE(6), OFFSET=0
TYPE=BYTE(6), OFFSET=6, INDEX=EMPX
TYPE=BYTE(4), OFFSET=12,
TYPE=FULLWORD, OFFSET=16
Same data,
different
names
The DBMS
ties it all
together
At the external level, application programs
are written in Java, C++, COBOL, PL/1,
Ada, or … (the “host” language).
A data sublanguage is embedded in or
interfaced with the program, or used
interactively with the DBMS.
The data sublanguage (DSL) of the DBMS
is almost universally SQL.
Fig. 2.3 Detailed system architecture
The DBMS provides:
Data Definition Language (DDL)
for the
internal
external
“schemas”
conceptual
The schemas (plans) are the definitions of how the
data is to be stored and how it is to be viewed.
The DBMS provides:
Data Manipulation Language (DML)
for
“planned” use
“unplanned” use
.
Planned: operational, production
Unplanned: ad hoc queries, DSS
Embedding:
PL/1 program, DB2 DBMS, DB2-PL/1 pre-compiler
Interfacing:
Java program, any DBMS, JDBC interface
DBMS provides for:
Data Definition
Data Manipulation
with a program or
interactively
Query Capability
interactively
SQL is usually the language for all of these
The DBMS provides:
Security enforcement
Integrity checks
Recovery
Concurrency control
Transaction management
Data dictionary
Performance optimization
Data Dictionary
Catalog
synonyms
Repository
The “metadata”--data about the data--part
of the database (itself a database).
Fig. 2.4 Major DBMS Functions and Components
Logical to Physical Mapping
record 142
record 142
file
block
record 142
record 142
disk
block
disk
record 142
file
total
disk
space
Logical to Physical Mapping
record 142
record 142
file
block
record 142
record 142
disk
block
disk
record 142
file
total
disk
space
a table,
part of a table,
multiple tables
Access Methods
• Sequential Access
• Direct Access
• Indexed Access
Access Methods
• Sequential Access
read next
write next (append)
(typically either read existing file or write new one)
rewind (go back to the beginning)
generally no read after write
may be able to skip ahead or back
Sequential-access File
Access Methods
• Direct Access
– Relative Access, Random Access
read n
write n
or, position to n
read next
write next
(n = relative block number)
must know n (e.g., n is employee number)
or calculate n (e.g., n is hashed from employee name)
Access Methods
• Indexed Access
use of an index to find what n is
index is itself a file
if small, can be read and kept in memory
if large, can have an index to the
blocks of the large index
Example of Index and Relative Files
Access Methods
• Sequential Access vs. Direct Access
– Sequential preferable when entire file is
to be accessed
– Direct access preferred when access is
needed to a few records
– Application determines
– Both may be desirable—more difficult to
achieve
The DBMS vendor provides utilities for:
Loading the database (from files).
Unloading/reloading the database for backup.
Reorganization (to improve performance).
Gathering statistics (on usage or performance).
Statistical analysis.
Client/Server Architecture
Fig. 2.5 Client/server architecture
Backends and Frontends
“back”
Database
CPU
“front”
Network,
Users
“backend”
“frontend”
(DB manager)
(DC manager)
Distributed Processing
Fig. 2.6 Client and server running on different machines
Backends and Frontends
Server
(DBMS)
“backend”
Network
Client
“frontend”
Distributed
processing,
centralized
database
Fig. 2.7 One server machine, many client machines
Distributed
database
Fig. 2.8 Each machine runs both client(s) and server
“Two-tier” Systems
Database
Server
Application
Server
same or different
computers
Client
network
Server
(DBMS)
Database
Server
network
Application
Server
network
Client
“fat”
client
Client
“thin”
client
2.2 Define the following terms:
back end
front end
client
host language
conceptual DDL, schema, view
load
conceptual/internal mapping
logical database design
data definition language
internal DDL, schema, view
data dictionary
physical database design
data manipulation language
planned request
data sublanguage
reorganization
DB/DC system
server
DC manager
storage structure definition
distributed database
unload/reload
distributed processing
unplanned request
external DDL, schema, view
user interface
external/conceptual mapping
utility
Descargar

Document