Chapter 2: DDBMS Architecture
• Deﬁnition of the DDBMS Architecture
• ANSI/SPARC Standard
• Global, Local, External, and Internal Schemas, Example
• DDBMS Architectures
• Components of the DDBMS
Acknowledgements: I am indebted to Arturas Mazeika for providing me his slides of this course.
DDB 2008/09 J. Gamper Page 1Deﬁnition
• Architecture: The architecture of a system deﬁnes its structure:
– the components of the system are identiﬁed;
– the function of each component is speciﬁed;
– the interrelationships and interactions among the components are deﬁned.
• Applies both for computer systems as well as for software systems, e.g,
– division into modules, description of modules, etc.
– architecture of a computer
• There is a close relationship between the architecture of a system, standardisation
efforts, and a reference model.
DDB 2008/09 J. Gamper Page 2Motivation for Standardization of DDBMS Architecture
• DDBMS might be implemented as homogeneous or heterogeneous DDBMS
• Homogeneous DDBMS
– All sites use same DBMS product
– It is much easier to design and manage
– The approach provides incremental growth and allows increased performance
• Heterogeneous DDBMS
– Sites may run different DBMS products, with possibly different underlying data models
– This occurs when sites have implemented their own databases ﬁrst, and integration is
– Translations are required to allow for different hardware and/or different DBMS
– Typical solution is to use gateways
⇒ A common standard to implement DDBMS is needed
DDB 2008/09 J. Gamper Page 3Standardization
• The standardization efforts in databases developed reference models of DBMS.
• Reference Model: A conceptual framework whose purpose is to divide standardization
work into manageable pieces and to show at a general level how these pieces are
related to each other.
• A reference model can be thought of as an idealized architectural model of the system.
• Commercial systems might deviate from reference model, still they are useful for the
• A reference model can be described according to 3 different approaches:
DDB 2008/09 J. Gamper Page 4Standardization . . .
– Components of the system are deﬁned together with the interrelationships between
– Good for design and implementation of the system
– It might be difﬁcult to determine the functionality of the system from its components
DDB 2008/09 J. Gamper Page 5Standardization . . .
– Classes of users are identiﬁed together with the functionality that the system will
provide for each class
– Typically a hierarchical system with clearly deﬁned interfaces between different layers
– The objectives of the system are clearly identiﬁed.
– Not clear how to achieve the objectives
– Example: ISO/OSI architecture of computer networks
DDB 2008/09 J. Gamper Page 6Standardization . . .
– Identify the different types of the data and specify the functional units that will realize
and/or use data according to these views
– Gives central importance to data (which is also the central resource of any DBMS)
→ Claimed to be the preferable choice for standardization of DBMS
– The full architecture of the system is not clear without the description of functional
– Example: ANSI/SPARC architecture of DBMS
DDB 2008/09 J. Gamper Page 7Standardization . . .
• The interplay among the 3 approaches is important:
– Need to be used together to deﬁne an architectural model
– Each brings a different point of view and serves to focus on different aspects of the
DDB 2008/09 J. Gamper Page 8ANSI/SPARC Architecture of DBMS
• ANSI/SPARC architecture is based on data
• 3 views of data: external view, conceptual view, internal view
• Deﬁnes a total of 43 interfaces between these views
DDB 2008/09 J. Gamper Page 9Example
• Conceptual schema: Provides enterprise view of entire database
RELATION EMP RELATION PROJ
KEY = ENO KEY = PNO
ATTRIBUTES = ATTRIBUTES =
ENO : CHARACTER(9) PNO : CHARACTER(7)
ENAME: CHARACTER(15) PNAME : CHARACTER(20)
TITLE: CHARACTER(10) BUDGET: NUMERIC(7)
LOC : CHARACTER(15)
KEY = TITLE RELATION ASG
ATTRIBUTES = KEY = ENO,PNO
TITLE: CHARACTER(10) ATTRIBUTES =
SAL : NUMERIC(6) ENO : CHARACTER(9)
PNO : CHARACTER(7)
DUR : NUMERIC(3)
DDB 2008/09 J. Gamper Page 10Example . . .
• Internal schema: Describes the storage details of the relations.
– Relation EMP is stored on an indexed ﬁle
– Index is deﬁned on the key attribute ENO and is called EMINX
– A HEADER ﬁeld is used that might contain ﬂags (delete, update, etc.)
INTERNAL REL EMPL
INDEX ON E CALL EMINX
KEY = ENO
ENO : CHARACTER(9)
E : BYTE(9)
ENAME : BYTE(15)
TIT : BYTE(10)
DDB 2008/09 J. Gamper Page 11Example . . .
• External view: Speciﬁes the view of different users/applications
– Application 1: Calculates the payroll payments for engineers
CREATE VIEW PAYROLL (ENO, ENAME, SAL) AS
FROM EMP, PAY
WHERE EMP.TITLE = PAY.TITLE
– Application 2: Produces a report on the budget of each project
CREATE VIEW BUDGET(PNAME, BUD) AS
SELECT PNAME, BUDGET
DDB 2008/09 J. Gamper Page 12Architectural Models for DDBMSs
• Architectural Models for DDBMSs (or more generally for multiple DBMSs) can be
classiﬁed along three dimensions:
DDB 2008/09 J. Gamper Page 13Architectural Models for DDBMSs . . .
• Autonomy: Refers to the distribution of control (not of data) and indicates the degree to
which individual DBMSs can operate independently.
– Tight integration: a single-image of the entire database is available to any user who
wants to share the information (which may reside in multiple DBs); realized such that
one data manager is in control of the processing of each user request.
– Semiautonomous systems: individual DBMSs can operate independently, but have
decided to participate in a federation to make some of their local data sharable.
– Total isolation: the individual systems are stand-alone DBMSs, which know neither of
the existence of other DBMSs nor how to comunicate with them; there is no global
• Autonomy has different dimensions
– Design autonomy: each individual DBMS is free to use the data models and
transaction management techniques that it prefers.
– Communication autonomy: each individual DBMS is free to decide what information
to provide to the other DBMSs
– Execution autonomy: each individual DBMS can execture the transactions that are
submitted to it in any way that it wants to.
DDB 2008/09 J. Gamper Page 14Architectural Models for DDBMSs . . .
• Distribution: Refers to the physical distribution of data over multiple sites.
– No distribution: No distribution of data at all
– Client/Server distribution:
∗ Data are concentrated on the server, while clients provide application
∗ First attempt to distribution
– Peer-to-peer distribution (also called full distribution):
∗ No distinction between client and server machine
∗ Each machine has full DBMS functionality
DDB 2008/09 J. Gamper Page 15Architectural Models for DDBMSs . . .
• Heterogeneity: Refers to heterogeneity of the components at various levels
– operating system
– DB components (e.g., data model, query language, transaction management
DDB 2008/09 J. Gamper Page 16Architectural Models for DDBMSs . . .
DDB 2008/09 J. Gamper Page 17Client-Server Architecture for DDBMS (Data-based)
• General idea: Divide the functionality into two
– server functions
∗ mainly data management, including
query processing, optimization, transac-
tion management, etc.
– client functions
∗ might also include some data manage-
ment functions (consistency checking,
transaction management, etc.) not just
• Provides a two-level architecture
• More efﬁcient division of work
• Different types of client/server architecture
– Multiple client/single server
– Multiple client/multiple server
DDB 2008/09 J. Gamper Page 18Peer-to-Peer Architecture for DDBMS (Data-based)
• Local internal schema (LIS)
– Describes the local physical data or-
ganization (which might be different
on each machine)
• Local conceptual schema (LCS)
– Describes logical data organization
at each site
– Required since the data are frag-
mented and replicated
• Global conceptual schema (GCS)
– Describes the global logical view of
– Union of the LCSs
• External schema (ES)
– Describes the user/application view
on the data
DDB 2008/09 J. Gamper Page 19Multi-DBMS Architecture (Data-based)
• Fundamental difference to peer-to-peer DBMS is in the deﬁnition of the global
conceptual schema (GCS)
– In a MDBMS the GCS represents only the collection of some of the local databases
that each local DBMS want to share.
• This leads to the question, whether the GCS should even exist in a MDBMS?
• Two different architecutre models:
– Models with a GCS
– Models without GCS
DDB 2008/09 J. Gamper Page 20