Course Name: Database Management System

Course Code: 18CS53

Module 1 – Chapter 2
Database System
Concepts and Architecture
 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

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

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)

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

Slide 2- 6
 Schemas versus Instances
 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.

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

Slide 2- 8
 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 state when it is initially
loaded into the system.
 Valid State:
 A state that satisfies the structure and constraints
of the database.

Slide 2- 9
 Database Schema
vs. Database State (continued)
 Distinction
 The database schema changes very infrequently.
 The database state changes every time the
database is updated.

 Schema is also called intension.

Slide 2- 10
Example of a Database Schema

Slide 2- 11
Example of a database state

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

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

Slide 2- 14
The three-schema architecture

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)

Slide 2- 16
 Data Independence
 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

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.

Slide 2- 18
 DBMS Languages
 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

Slide 2- 19
 DBMS Languages
 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

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

Slide 2- 21