Copyright © 2016 Ramez Elmasri and Shamkant B.

Navathe
 CHAPTER 2

Database System Concepts

and Architecture

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 1- 2

Outline
 Data Models and Their Categories
 History of Data Models
 Schemas, Instances, and States
 Three-Schema Architecture
 Data Independence
 DBMS Languages and Interfaces
 Database System Utilities and Tools
 Centralized and Client-Server Architectures
 Classification of DBMSs

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 3

Data Models
 Data Model:
 A set of concepts to describe the structure of a database,
the operations for manipulating these structures, and
certain constraints that the database should obey.
 Data Model Structure and Constraints:
 Constructs are used to define the database structure
 Constructs typically include elements (and their data
types) as well as groups of elements (e.g. entity, record,
table), and relationships among such groups
 Constraints specify some restrictions on valid data; these
constraints must be enforced at all times

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 4

Data Models (continued)
 Data Model Operations:
 These operations are used for specifying database
retrievals and updates by referring to the
constructs of the data model.
 Operations on the data model may include basic
model operations (e.g. generic insert, delete,
update) and user-defined operations (e.g.
compute_student_gpa, update_inventory)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 5

Categories of Data Models
 Conceptual (high-level, semantic) data models:
 Provide concepts that are close to the way many users
perceive data.
 (Also called entity-based or object-based data models.)
 Physical (low-level, internal) data models:
 Provide concepts that describe details of how data is stored
in the computer. These are usually specified in an ad-hoc
manner through DBMS design and administration manuals
 Implementation (representational) data models:
 Provide concepts that fall between the above two, used by
many commercial DBMS implementations (e.g. relational
data models used in many commercial systems).
 Self-Describing Data Models:
 Combine the description of data with the data values.
Examples include XML, key-value stores and some NOSQL
systems.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 6
Schemas versus Instances
 Database Schema: t
 The description of a database.
 Includes descriptions of the database structure,
data types, and the constraints on the database.
 Schema Diagram:es I
 An illustrative display of (most aspects of) a 25W
database schema.
 Schema Construct:
 A component of the schema or an object within
the schema, e.g., STUDENT, COURSE.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 7

Schemas versus Instances
 Database State:
 The actual data stored in a database at a
particular moment in time. This includes the
collection of all the data in the database.
 Also called database instance (or occurrence or
snapshot).
 The term instance is also applied to individual
database components, e.g. record instance, table
instance, entity instance

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 8

Database Schema
vs. Database State
 Database State: 5
 Refers to the content of a database at a moment
in time. 53
 Initial Database State:
 Refers to the database state when it is initially
loaded into the system.
 Valid State: a
 A state that satisfies the structure and constraints
of the database.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 9

Database Schema
vs. Database State (continued)
 Distinction
iii
 The database schema changes very infrequently.
-
 The database state changes every time the
database is updated.
S
 Schema is also called intension.
 State is also called extension.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 10

Example of a Database Schema

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 11

Example of a database state

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 12

Three-Schema Architecture
 Proposed to support DBMS characteristics of:
 Program-data independence.
 Support of multiple views of the data.
 Not explicitly used in commercial DBMS products,
but has been useful in explaining database
system organization

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 13

 Three-Schema Architecture
 Defines DBMS schemas at three levels:
 Internal schema at the internal level to describe physical
storage structures and access paths (e.g indexes).
physical
 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 schema.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 14

 The three-schema architecture

the
draw
EEE
8
To

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 15

Three-Schema Architecture
 Mappings among schema levels are needed to
transform requests and data.
 Programs refer to an external schema, and are
mapped by the DBMS to the internal schema for
execution.
 Data extracted from the internal DBMS level is
reformatted to match the user’s external view (e.g.
formatting the results of an SQL query for display
in a Web page)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 16

Data Independence
 Logical Data Independence: III.r
 The capacity to change the conceptual schema
without having to change the external schemas
and their associated application programs.
 Physical Data Independence: tfIDsm1
 The capacity to change the internal schema
without having to change the conceptual schema.
 For example, the internal schema may be changed
when certain file structures are reorganized or new
indexes are created to improve database
performance

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 17

Data Independence (continued)
 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.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 18

DBMS Languages SQL
structure quiney
structure Language
 Data Definition Language (DDL)
s Jew as a
 Data Manipulation Language (DML)
 High-Level or Non-procedural Languages: These
include the relational language SQL
 May be used in a standalone way or may be
embedded in a programming language 5
Low Level or Procedural Languages:
Ian


 These must be embedded in a programming

language

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 19

DBMS Languages
 Data Definition Language (DDL):
 Used by the DBA and database designers to
specify the conceptual schema
Structure of
of a
database
database.
 In many DBMSs, the DDL is also used to define
internal and external schemas (views).
 In some DBMSs, separate storage definition
language (SDL) and view definition language
(VDL) are used to define internal and external
schemas.
 SDL is typically realized via DBMS commands
provided to the DBA and database designers

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 20

DBMS Languages
 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,
C++, or Java.
 A library of functions can also be provided to access
the DBMS from a programming language
 Alternatively, stand-alone DML commands can be
applied directly (called a query language).

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 21

Types of DML
 High Level or Non-procedural Language:
 For example, the SQL relational language
 Are “set”-oriented and specify what data to retrieve
rather than how to retrieve it.
bulla Jim
 Also called declarative languages.
 Low Level or Procedural Language:
 Retrieve data one record-at-a-time;
 Constructs such as looping are needed to retrieve
multiple records, along with positioning pointers.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 22

DBMS Interfaces
 Stand-alone query language interfaces
 Example: Entering SQL queries at the DBMS
interactive SQL interface (e.g. SQL*Plus in
ORACLE)
at I
 Programmer interfaces for embedding DML in
programming languages
 User-friendly interfaces
 Menu-based, forms-based, graphics-based, etc.
 Mobile Interfaces:interfaces allowing users to
perform transactions using mobile apps
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 23
DBMS Programming Language Interfaces

 Programmer interfaces for embedding DML in a

programming languages:
 Embedded Approach: e.g embedded SQL (for C, C++, etc.),
SQLJ (for Java)
 Procedure Call Approach: e.g. JDBC for Java, ODBC (Open
Databse Connectivity) for other programming languages as API’s
(application programming interfaces)
 Database Programming Language Approach: e.g. ORACLE
has PL/SQL, a programming language based on SQL; language
incorporates SQL and its data types as integral components
 Scripting Languages: PHP (client-side scripting) and Python
(server-side scripting) are used to write database programs.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 24

User-Friendly DBMS Interfaces

 Menu-based (Web-based), popular for browsing

on the web
 Forms-based, designed for naïve users used to
filling in entries on a form
 Graphics-based
 Point and Click, Drag and Drop, etc.
 Specifying a query on a schema diagram
 Natural language: requests in written English
 Combinations of the above:
 For example, both menus and forms used
extensively in Web database interfaces
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 25
Other DBMS Interfaces
us 2555
 Natural language: free text as a query
 Speech : Input query and Output response
 Web Browser with keyword search
 Parametric interfaces, e.g., bank tellers using
function keys.
 Interfaces for the DBA:
 Creating user accounts, granting authorizations
 Setting system parameters
Changing schemas or access paths
if 

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 26

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.
 Performance monitoring utilities.
 Report generation utilities. 1 w̅
 Other functions, such as sorting, user monitoring,
data compression, etc.
s
I
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 27
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.
 Active data dictionary is accessed by DBMS
software and users/DBA.
 Passive data dictionary is accessed by
users/DBA only. 2 96 DBMS

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 28

Other Tools
 Application Development Environments and
CASE (computer-aided software engineering)
tools:
 Examples:
 PowerBuilder (Sybase)
 JBuilder (Borland)
 JDeveloper 10G (Oracle)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 29

Typical DBMS Component Modules

13

I
I v16 J
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 30
Centralized and 00
Client-Server DBMS Architectures
 Centralized DBMS:
 Combines everything into single system including-
DBMS software, hardware, application programs,
and user interface processing software.
 User can still connect through a remote terminal –
however, all processing is done at centralized site.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 31

A Physical Centralized Architecture

I'm

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 32

 WJMN.IQ w's is É veins
AJIN Is divisi 118
Basic 2-tier Client-Server Architectures

 Specialized Servers with Specialized functions

 Print server
 File server
 DBMS server
 Web server
 Email server
 Clients can access the specialized servers as
needed

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 33

Logical two-tier client server architecture

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 34

Clients
 Provide appropriate interfaces through a client
software module to access and utilize the various
server resources.
 Clients may be 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.)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 35

DBMS Server
 Provides database query and transaction services to the
clients
 Relational DBMS servers are often called SQL servers,
query servers, or transaction servers
 Applications running on clients utilize an Application
Program Interface (API) to access server databases via
standard interface such as:
 ODBC: Open Database Connectivity standard
 JDBC: for Java programming access

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 36

Two Tier Client-Server Architecture
i is a is
II IF e
 Client and server must install appropriate client
module and server module software for ODBC or
JDBC
 A client program may connect to several DBMSs,
sometimes called the data sources.
 In general, data sources can be files or other
non-DBMS software that manages data.
 See Chapter 10 for details on Database
Programming

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 37

Three Tier Client-Server Architecture
 Common for Web applications
 Intermediate Layer called Application Server or Web
Server:
 Stores the web connectivity software and the business logic
part of the application used to access the corresponding
data from the database server
 Acts like a conduit for sending partially processed data
between the database server and the client.
 Three-tier Architecture Can Enhance Security:
 Database server only accessible via middle tier
 Clients cannot directly access database server

 Clients contain user interfaces and Web browsers

 The client is typically a PC or a mobile device connected to

the Web Slide 2- 38

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Three-tier client-server architecture

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 39

 Classification of DBMSs
 Based on the data model used
 Legacy: Network, Hierarchical.
 Currently Used: Relational, Object-oriented, Object-
relational
 Recent Technologies: Key-value storage systems,
NOSQL systems: document based, column-based,
graph-based and key-value based. Native XML
DBMSs.
 Other classifications
 Single-user (typically used with personal computers)
vs. multi-user (most DBMSs).
 Centralized (uses a single computer with one
database) vs. distributed (multiple computers, multiple
DBs) Slide 2- 40
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Variations of Distributed DBMSs
(DDBMSs)
Ñ
 Homogeneous DDBMS
I
 Heterogeneous DDBMS
 Federated or Multidatabase Systems
 Participating Databases are loosely coupled with
high degree of autonomy.
 Distributed Database Systems have now come to
be known as client-server based database
systems because:
 They do not support a totally distributed
environment, but rather a set of database servers
supporting a set of clients.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 41
Cost considerations for DBMSs
 Cost Range: from free open-source systems to
configurations costing millions of dollars
 Examples of free relational DBMSs: MySQL, PostgreSQL,
others
 Commercial DBMS offer additional specialized modules,
e.g. time-series module, spatial data module, document
module, XML module
 These offer additional specialized functionality when
purchased separately
 Sometimes called cartridges (e.g., in Oracle) or blades

 Different licensing options: wi iswjijj.in

site license,
as IT
maximum number
of concurrent users (seat license), single user, etc.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 42

Other Considerations
 Type of access paths within database system
 E.g.- inverted indexing based (ADABAS is one
such system).Fully indexed databases provide
access by any keyword (used in search engines)
 General Purpose vs. Special Purpose
 E.g.- Airline Reservation systems or many others-
reservation systems for hotel/car etc. Are special
purpose OLTP (Online Transaction Processing
Systems)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 43

Chapter Summary
 Data Models and Their Categories
 Schemas, Instances, and States
 Three-Schema Architecture
 Data Independence
 DBMS Languages and Interfaces
 Database System Utilities and Tools
 Database System Environment
 Centralized and Client-Server Architectures
 Classification of DBMSs
 History of Data Models
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 2- 53