. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Faculty of Computer Science
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
University of Indonesia
CSF2600700
Basis Data
Term 1 - 2012/2013
1
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Faculty of Computer Science
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
University of Indonesia
Database System
Concepts and
Architecture
2
Acknowledgement
• All materials in these slides are from:
✤ Elmasri, et al. Database Systems, 6th
Edition, Addison-Wesley, 2011
• Do not reproduce these slides for
commercial use and / or other purpose
outside Database 1 course.
3
Outline
• Data Models
✤ Categories of Data Models
✤ History of Data Models
• Schema
✤ Three-Schema Architecture
• DBMS Component
• DBMS Architecture
4
Data Models
• Data Model:
✤ A set of concepts to describe the structure
of a database, and certain constraints that
the database should obey.
• Data Model Operations:
✤ Operations for specifying database
retrievals and updates by referring to the
concepts of the data model. Operations
on the data model may include basic
operations and user-defined operations.
5
Categories Data Models
• Conceptual (high-level, semantic) data models:
Provide concepts that are close to the way many
users perceive data. Such as: entity, attribute,
relationship among entities (will explain more
detail in ER model)
• Physical (low-level, internal) data models:
Provide concepts that describe details of how data
is stored in the computer. Ex. Tree, Graph, dsb
• Implementation (representational) data models:
Provide concepts that fall between the above two,
balancing user views with some computer storage
details. Such as: relational, network or hierarchical
data model
6
History of Data Models - 1
• Network Model:
✤ the first one to be implemented by Honeywell in
1964-65 (IDS System).
✤ Adopted heavily due to the support by
CODASYL (CODASYL - DBTG report of 1971).
✤ Later implemented in a large variety of systems
- IDMS (Cullinet - now CA), DMS 1100 (Unisys),
IMAGE (H.P.), VAX -DBMS (Digital Equipment
Corp.).
✤ Data in a Network in terms of Interdependencies
and Connections Among Data Items
✤ Graphs
7
History of Data Models - 2
• Hierarchical Data Model:
✤ implemented in a joint effort by IBM and
North American Rockwell around 1965.
✤ Resulted in the IMS family of systems. The
most popular model. Other system based
on this model: System 2k (SAS inc.)
✤ Data in Hierarchies in terms of
Interdependencies and Connections
Among Data Items
✤ Tree
8
History of Data Models - 3
• Relational Model:
✤ proposed in 1970 by E.F. Codd (IBM),
✤ first commercial system in 1981-82.
✤ Now in several commercial products (DB2, ORACLE,
SQL Server, SYBASE, INFORMIX).
• Object-oriented Data Model(s):
✤ several models have been proposed for implementing in
a database system.
✤ One set comprises models of persistent O-O
Programming Languages such as C++ (e.g., in
OBJECTSTORE or VERSANT), and Smalltalk (e.g., in
GEMSTONE).
✤ Additionally, systems like O2, ORION (at MCC - then
ITASCA), IRIS (at H.P.- used in Open OODB).
9
History of Data Models - 3
• Object-Relational Models:
✤ Most Recent Trend.
✤ Started with Informix Universal Server.
✤ Exemplified in the latest versions of
Oracle-10g, DB2, and SQL Server etc.
systems.
10
Network Graphical Representation
Courses
Course#* Title Descrip
Requirements COfferings QtrOfferings
Prereq Formats
PCourse#* Title Section#* Quarter Campus
Takes Teaches
Student Faculty
SSN#* Name GPA SSN#* Name Phone
11
Hierarchy Graphical Representation
Courses
Course#* Title Descrip
1 1
n
n
Prereq Formats
PCourse#* Title Section#* Quarter Campus
1
1
n
1
Student Faculty
SSN#* Name GPA SSFaN#* Name Phone
12
Relational Model
• Relational Model of Data Based on the Concept of a
Relation
• Relation - a Mathematical Concept Based on Sets
• Strength of the Relational Approach to Data
Management Comes From the Formal Foundation
Provided by the Theory of Relations
• RELATION: A Table of Values
✤ A Relation May Be Thought of as a Set of Rows
✤ A Relation May Alternately be Though of as a Set of
Columns
✤ Each Row of the Relation May Be Given an Identifier
✤ Each Column Typically is Called by its Column Name or
Column Header or Attribute Name
13
Relational Tables - Rows/Columns/Tuples
14
Entity Relationship (ER) Data Model
• Originally Proposed by P. Chen, ACM TODS,
Vol. 1, No. 1, March1976
• Conceptual Modeling of Database Requirements
• Allows an Application's Information to be
Characterized
• Basic Building Blocks are Entities and
Relationships
• Well-Understood and Studied Technique
• Well-Suited for Relational Database
Development
• Did Not Originally Include Inheritance!!
15
ER Diagram
16
Schemas
• Database Schema: The description of a
database. Includes descriptions of the database
structure and the constraints that should hold on
the database.
• Schema Diagram: A diagrammatic display of
(some aspects of) a database schema.
• Schema Construct: A component of the schema
or an object within the schema, e.g., STUDENT,
COURSE.
• Database State/Snapshot: The actual data
stored in a database at a particular moment in
time. Also called the current set of
occurrences/instances).
17
Schema Diagram
18
Database Schema Vs. Database State
• Database State: Refers to the content of a
database at a moment in time.
• Initial Database State: Refers to the database
when it is loaded
• Valid State: A state that satisfies the structure
and constraints of the database.
• Distinction
✤ The database schema changes very
infrequently. The database state changes every
time the database is updated.
✤ Schema is also called intension, whereas state
is called extension.
19
Three-Schema Architecture - 1
• Proposed to support DBMS
characteristics of:
✤ Program-data independence.
✤ Support of multiple views of the data.
20
Three-Schema Architecture - 2
21
Another view: Three Schema Architecture
22
Three-Schema Architecture - 3
• Defines DBMS schemas at three levels:
✤ Internal schema at the internal level to
describe physical storage structures and
access paths. Typically uses a physical data
model.
✤ Conceptual schema at the conceptual level to
describe the structure and constraints for the
whole database for a community of users.
Uses a conceptual or an implementation data
model.
✤ External schemas at the external level to
describe the various user views. Usually uses
the same data model as the conceptual level.
23
Conceptual Schema
• Describes the Meaning of Data in the
Universe of Discourse
✤ Emphasizes on General, Conceptually
Relevant, and Often Time Invariant
Structural Aspects of the Universe of
Discourse
• Excludes the Physical Organization and
Access Aspects of the Data
24
External Schema
• Describes Parts of the Information in
the Conceptual Schema in a form
Convenient to a Particular User Group’s
View
• Derived from the Conceptual Schema
25
Internal Schema
• Describes How the Information
Described in the Conceptual Schema is
Physically Represented in a Database
to Provide the Overall Best
Performance
26
Unified Example of Three Schemas
27
Data Independence
• Ability that Allows Application Programs Not Being
Affected by Changes in Irrelevant Parts of the
Conceptual Data Representation, Data Storage
Structure and Data Access Methods
• Invisibility (Transparency) of the Details of Entire
Database Organization, Storage Structure and Access
Strategy to the Users
✤ Both Logical and Physical
• Recall Software Engineering Concepts:
✤ Abstraction the Details of an Application's Components
Can Be Hidden, Providing a Broad Perspective on the
Design
✤ Representation Independence: Changes Can Be Made
to the Implementation that have No Impact on the
Interface and Its Users
28
Data Independence
• Logical Data Independence: The
capacity to change the conceptual
schema without having to change the
external schemas and their application
programs.
• Physical Data Independence: The
capacity to change the internal schema
without having to change the
conceptual schema.
29
Data Independence
• When a schema at a lower level is
changed, only the mappings between
this schema and higher-level schemas
need to be changed in a DBMS that
fully supports data independence. The
higher-level schemas themselves are
unchanged. Hence, the application
programs need not be changed since
they refer to the external schemas.
30
Physical Data Independence
Physical
31
Logical Data Independence
Logical
32
DBMS Languages - 1
• Data Definition Language (DDL): Used by
the DBA and database designers to specify the
conceptual schema and internal schema of a
database and any mapping between the two.
• In many DBMSs where a clear separation of
conceptual and internal schema, DDL is used
to define conceptual schema only. Storage
definition language (SDL) define the internal
schema and view definition language (VDL)
are used to define user view and their mapping
to the conceptual schemas.
• Most DBMSs, the DDL is used to define both
conceptual and external schemas
33
DBMS Language - 2
• Data Manipulation Language (DML):
Used to specify database retrievals and
updates.
✤ DML commands (data sublanguage) can
be embedded in a general-purpose
programming language (host language),
such as COBOL, C or an Assembly
Language.
✤ Alternatively, stand-alone DML commands
can be applied directly (query language).
34
DBMS Language - 3
• High Level or Non-procedural
Languages:
✤ e.g., SQL, are set-oriented and specify
what data to retrieve than how to retrieve.
Also called declarative languages.
• Low Level or Procedural Languages:
record-at-a-time; they specify how to
retrieve data and must be embedded in
programming language
35
DBMS Interfaces
• Menu-based, popular for browsing on
the web
• Forms-based, designed for naïve users
• Graphics-based (Point and Click, Drag
and Drop etc.)
• Natural language: requests in written
English “Show the student that have
GPA above 3.0”
• Combinations of the above
36
Other DBMS Interfaces
• Speech as Input and Output
• Parametric interfaces (e.g., bank tellers)
using function keys.
• Interfaces for the DBA:
✤ Creating accounts, granting
authorizations
✤ Setting system parameters
✤ Changing schemas or access path
37
The Database System Environment
• Main DBMS Modules
✤ DDL Compiler
✤ DML Compiler
✤ Ad-hoc (Interactive) Query Compiler
✤ Run-time Database Processor
✤ Stored Data Manager
✤ Concurrency/Back-Up/Recovery Subsystem
• DBMS Utility Modules
✤ Loading Routines
✤ Backup Utility
✤ …
38
Components modules of a DBMS and Their
Interactions
39
Database System Utilities
• To perform certain functions such as:
✤ Loading data stored in files into a
database. Includes data conversion tools.
✤ Backing up the database periodically on
tape.
✤ Reorganizing database file structures.
✤ Report generation utilities.
✤ Performance monitoring utilities.
✤ Other functions, such as sorting, user
monitoring, data compression, etc.
40
Other Tools
• Data dictionary/repository:
✤ Used to store schema descriptions and
other information such as design decisions,
application program descriptions, user
information, usage standards, etc.
• Application Development
Environments and CASE (computer-
aided software engineering) tools:
✤ Power builder, Builder, VB, Java, C, C++,
etc
✤ Ms. Visio, ER-Win, DBDesigner, etc
41
Centralized Architectures
• Centralized DBMS: combines
everything into single system (PC)
including- DBMS software, hardware,
application programs and user interface
processing software.
42
Client-Server Architectures - 1
• Servers:
✤ Specialized Servers with Specialized
functions
✤ Ex. Database Server, File Server, Web
Server, Email Server
43
Client-Server Architectures - 2
• Client:
✤ Provide appropriate interfaces and a
client-version of the system to access and
utilize the server resources.
✤ Clients maybe diskless machines or PCs
or Workstations with disks with only the
client software installed.
✤ Connected to the servers via some form
of a network.(LAN: local area network,
wireless network, etc.)
44
Two Tier Client-Server Architecture
• User Interface Programs and
Application Programs run on the
client side
• Interface called ODBC (Open
Database Connectivity) provides an
Application program interface (API)
allow client side programs to call the
DBMS. Most DBMS vendors provide
ODBC drivers.
45
Logical two-tier client/server architecture
46
Three Tier Client-Server Architecture
• Common for Web applications
• Intermediate Layer called Application Server
or Web Server:
✤ stores the web connectivity software and the
rules and business logic (constraints) part of
the application used to access the right amount
of data from the database server
✤ acts like a conduit for sending partially processed
data between the database server and the client.
• Additional Features- Security:
✤ encrypt the data at the server before transmission
✤ decrypt data at the client
47
Logical three-tier client/server architecture
48
Database Classification
49
