Database System Concepts

and Architecture
▪ Data Models and Their Categories
▪ History of Data Models
▪ Schemas, Instances, and States
▪ Three-Schema Architecture
▪ Data Independence
▪ DBMS Languages
▪ Centralized and Client-Server Architectures
▪ Classification of DBMSs
▪ 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

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

▪ 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)

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

 ▪ 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
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
 ▪ 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.
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
 ▪ Database Schema:
▪ The description of a database.
▪ Includes descriptions of the database
structure, data types, and the constraints on
the database.
▪ Schema Diagram:
▪ An illustrative display of (most aspects of) a
database schema.
▪ Schema Construct:
▪ A component of the schema or an object
within the schema, e.g., STUDENT, COURSE.
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
▪ 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

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

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

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

 Database Schema vs. Database State

▪ Distinction
▪ The database schema changes very
infrequently.
▪ The database state changes every time the
database is updated.
▪ Schema is also called intension.
▪ State is also called extension.

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
 ▪ 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

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

 ▪ Defines DBMS schemas at three levels:
▪ Internal schema at the internal level to describe
physical storage structures and access paths (e.g
indexes).
▪ 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.
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
 ▪ Logical Data Independence:
▪ The capacity to change the conceptual schema
without having to change the external schemas
and their associated application programs.
▪ Physical Data Independence:
▪ 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
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
▪ Data Definition Language (DDL)
▪ 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
▪ Low Level or Procedural Languages:
▪ These must be embedded in a
programming language

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

▪ Data Definition Language (DDL):
▪ Used by the DBA and database designers to specify the
conceptual schema of a 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

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

 ▪ 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).

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

 ▪ 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.
▪ 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.

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

 ▪ 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.

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
 ▪ Specialized Servers with Specialized functions
▪ Print server
▪ File server
▪ DBMS server
▪ Web server
▪ Email server

▪ Clients can access the specialized servers as needed

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

▪ 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.)

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

▪ 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

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
▪ 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)
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
▪ Homogeneous DDBMS
▪ 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.

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)

▪ 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: site license, maximum
number of concurrent users (seat license), single
user, etc.
Adapted from Fundamentals of Database Systems (Elmasri and Navathe)
▪ 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)

Adapted from Fundamentals of Database Systems (Elmasri and Navathe)