Database Architecture

1
Chapter 2

• Database Architecture

2
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 comprises
• A structural part with constructs that defines a
  DB structure
• A language part that manipulates a DB
• Possibly a set of integrity rules or constraints
  that specify some restrictions on valid data

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

4
Schemas versus Instances

• Database Schema
• The description of a database.
• Includes descriptions of the database structure,
  data types, and the constraints on the database.
• 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
• It changes very frequently (every update of the
  DB)

5
Example of a database schema
6
Example of a database state
7
Three-Schema Architecture

• General architecture for database systems
• All users should be able to change each views of
  the data without affecting others.
• Users should not need to know physical database
  storage details.
• DBA should be able to change database storage
  structures without affecting the users' views.
• DBA should be able to change conceptual structure
  of database without affecting all users.
• It allows the mapping among schema levels during
  requests of data

8
Three-schema Architecture (Fig. 2.2)
9
Three-schema Architecture

• External schema Users' view of the DB
• Describes that part of database that is relevant
  to a particular user.
• It usually uses a conceptual data model
• Conceptual schema Community view of the DB
• Describes what data is stored in database and
  relationships among the data.
• Using a conceptual or implementation data model
• Internal schema Physical representation of the
  DB
• Describes how the data is stored internally.

10
Three-schema architecture (Example)
11
Data Independence

• Logical Data Independence
• Refers to immunity of external schemas to changes
  in conceptual schema.
• Should not require changes to external schema or
  rewrites of application programs.
• Physical Data Independence
• Refers to immunity of conceptual schema to
  changes in the internal schema.
• Should not require change to conceptual or
  external schemas.
• Only mappings between schemas need to be changed

12
Database Languages

• Data Definition Language (DDL)
• Allows a DBA to describe the database metadata
  required for the application
• Data Manipulation Language (DML)
• Provides basic data manipulation operations on
  data held in the database.
• Procedural DML
• Allows user to tell system how to manipulate
  data.
• Non-Procedural DML
• allows user to state what data is needed rather
  than how it is to be retrieved.

13
Characteristics of DML languages

• High Level or Non-procedural Language
• For example, the SQL relational language
• May be used standalone or embedded in a PL
• 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.

14
DBMS Interfaces

• Stand-alone query language interfaces
• Example Entering SQL queries at the DBMS
  interactive SQL interface (e.g. T-SQL in MS SQL
  Server)
• Programmer interfaces for embedding DML in
  programming languages
• Embedded approach embedded SQL in C or Java
  Procedure Call approach using ODBC or JDBC
• User-friendly interfaces
• Menu-based, forms-based, graphics-based, etc.

15
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.
• Report generation utilities.
• Performance monitoring utilities.
• Other functions, such as sorting, user
  monitoring, data compression, etc.

16
Typical DBMS component modules
17
Multi-User 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.
• Client-Server DBMS
• Specialized server with client (machine)
  accessing the DBMS server.
• Usually in a two-tier architecture

18
Two-tier Client-server Architecture
19
Client machines

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

20
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
• Client and server must install appropriate client
  module and server module software for ODBC or
  JDBC
• See Chapter 9

21
Three-tier Client-server Arch.

• 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

22
Classification of DBMS

• Based on the data model used
• Traditional Relational, Network, Hierarchical.
• Emerging Object-oriented, Object-relational.
• Other classifications
• Single-user (typically used with personal
  computers)vs. multi-user (most DBMSs).
• Centralized (uses a single computer with one
  database) vs. distributed (uses multiple
  computers, multiple databases)
• Distributed DBMS (database and software
  distributed over many sites

23
Variations of Distributed DBMSs

• Homogeneous DDBMS
• DDBMS uses the same DBMS over many sites.
• Heterogeneous DDBMS
• Applications accessing databases on different
  DBMS.
• Federated or Multidatabase Systems
• Participating DBMS are loosely coupled.
• Distributed Database Systems have become
  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.