10-Sep-20

Database System Concepts

and Architecture

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.

1
 10-Sep-20

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.
 Implementation (representational) data models:
Provide concepts that fall between the above two,
balancing user views with some computer storage
details.

Schemas versus Instances

• 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 Instance: The actual data stored in a
database at a particular moment in time. Also
called database state (or occurrence).

2
 10-Sep-20

Schema diagram for the database

discussed earlier

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.

3
 10-Sep-20

Example of a Database State

COURSE

Course_name Course_number Credit_hours Department

Intro to
Computer CS1310 4 CS
Science
Data Structures CS3320 4 CS
Discrete
MATH2410 3 MATH
Mathematics
Database CS3380 3 CS

Three-Schema Architecture
• Proposed to support DBMS
characteristics of:
• Program-data independence.
• Support of multiple views of the data.

4
 10-Sep-20

Three-Schema Architecture
• 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.

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.

5
 10-Sep-20

The three-schema architecture.

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.

6
 10-Sep-20

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.

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

7
 10-Sep-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 C or an Assembly Language.
• Alternatively, stand-alone DML commands
can be applied directly (query language).

DBMS Interfaces
• Stand-alone query language interfaces.
• Programmer interfaces for embedding DML
in programming languages
• User-friendly 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
• Combinations of the above

8
 10-Sep-20

Centralized and Client-Server

Architectures
• Centralized DBMS: combines
everything into single system including-
DBMS software, hardware, application
programs and user interface processing
software.

Basic Client-Server
Architectures
• Specialized Servers with
Specialized functions
• Clients
• DBMS Server

9
 10-Sep-20

Specialized Servers with

Specialized functions:
• File Servers
• Web Servers
• E-mail Servers

Clients:
• 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.)

10
 10-Sep-20

DBMS Server
• Provides database query and
transaction services to the clients
• Sometimes called query and transaction
servers

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.

11
 10-Sep-20

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

Classification of DBMSs
• Based on the data model used:
• Relational, Network, Hierarchical.
• Object-oriented, Object-relational.
• Other classifications:
• Single-user (typically used with micro- computers) vs.
multi-user (most DBMSs).
• Centralized (uses a single computer with one
database) vs. distributed (uses multiple computers,
multiple databases)

12
 10-Sep-20

Acknowledgement
Reference for this lecture is

 Ramez Elmasri and Shamkant B. Navathe,

Fundamentals of Database Systems,
Pearson Education.

The authors and the publishers are

gratefully acknowledged.

13