Chapter 2 Database System Concepts and Architecture

1
Chapter 2 Database System Concepts and
Architecture

• Dr. Bernard Chen Ph.D.
• University of Central Arkansas
• Fall 2008

2
Outline

• Data Models, Schemas, and Instances
• Three-Schema Architecture
• Database language and Interfaces
• The database system environment
• Centralized and client/server architecture

3
Categories of data models

• One fundamental characteristic of the database
  approach is that it provides some level of data
  abstraction
• High-level or Conceptual data models
• Provide concept that are close to the way many
  users perceive data
• Low-level or Physical data model
• Provide concepts that describe the details of how
  data is stored in the computer

4
Conceptual data models

• It uses concepts such as entities, attributes and
  relationships.
• Entity represents a real-world object or concept,
  such as employee or project
• Attribute represents some property of interest
  that further describes an entity, such as
  employees name or salary
• Relation among two or more entities represents an
  association among two or more entitles

5
Example of a Relation
6
Schemas and Database State

• In any data model, it is important to distinguish
  between the description of the data and database
  itself
• The description of the database is called the
  database schema
• A displayed Schema is called a schema diagram

7
Database V.S. File

• In the database approach, a single repository of
  data is maintained that is defined once then
  accessed by various users
• The major differences between DB and File are
• Self-describing of a DB
• Insulation between programs and data
• Support of multiple views of the data
• Sharing of data and multiuser transaction
  processing

8
University Database
9
Example of a Database Schema
10
Schemas and Database State

• The data in the database at a particular moment
  in time is called a database state
• The distinction between database schema and
  database state is very important
• When we define a new database, we specify its
  database schema only to the DBMS
• At this point, the corresponding database state
  is the empty state with no data
• We get the initial state of the database when the
  database is first loaded
• From then on, every time an update operation is
  applied to the database, we get another database
  state

11
Schemas and Database State

• Valid State a state that satisfies the structure
  and constrains specified in the schema.
• 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.

12
Outline

• Data Models, Schemas, and Instances
• Three-Schema Architecture
• Database language and Interfaces
• The database system environment
• Centralized and client/server architecture

13
Three-Schema Architecture

• Three of four important characteristics of the
  database approach, listed in Ch.1 are
• Self-describing of a DB (Schema)
• Insulation between programs and data
• Support of multiple views of the data
• Three-Schema Architecture it was proposed to
  help achieve and visualize these characteristics

14
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).
• Conceptual schema at the conceptual level to
  describe the structure and constraints for the
  whole database for a community of users.
• External schemas at the external level to
  describe the various user views.

15
The three-schema architecture
16
Outline

• Data Models, Schemas, and Instances
• Three-Schema Architecture
• Database language and Interfaces
• The database system environment
• Centralized and client/server architecture

17
DBMS Languages

• The first step to create a database through DBMS
  is to specify conceptual and internal schemas for
  the database
• Data Definition Language (DDL) is used by
  database designers to define schemas
• Data Manipulation Language (DML)
• View Definition Language (VDL) is to specify
  user views
• In current DBMS, the preceding types of languages
  are usually not considered distinct languages

18
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 DB connectivity) for other programming
  languages
• 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

19
User-Friendly DBMS Interfaces

• Menu-based, popular for browsing on the web
  (URSA)
• Forms-based, designed for naïve users
• Graphics-based
• Natural language requests in written English
• Combinations of the above

20
Outline

• Data Models, Schemas, and Instances
• Three-Schema Architecture
• Database language and Interfaces
• The database system environment
• Centralized and client/server architecture

21
DBMS Component Modules

• A DBMS is a complex software system
• The figure showed in next slide is divide into
  two halves.
• The top half refers to the various users of the
  database system
• The lower half shows the internals of the DBMS
  responsible for storage of data and processing of
  transactions

22
Typical DBMS Component Modules
23
Outline

• Data Models, Schemas, and Instances
• Three-Schema Architecture
• Database language and Interfaces
• The database system environment
• Centralized and client/server architecture

24
Centralized DBMS Architecture

• A centralized DBMS in which all the DBMS
  functionality, application program execution, and
  user interface processing were carried out on a
  single machine

25
A Physical Centralized Architecture
26
Basic Client/Server Architectures

• The client/server architecture was developed to
  deal with computer environment in which a large
  number of PCs, workstation, file server
• A client in this framework is typically a user
  machine that provides user interface capabilities
  and local processing
• A server is a system containing both hardware and
  software that can provide services to the client
  machines.

27
Logical two-tier client server architecture
28
Two-tier Architecture

• This is called two-tire architectures because the
  software components are distributed over two
  systems client and server
• The emergence of the Web changed the roles of
  client and server, leading to the three-tier
  architecture

29
Three-tier architecture

• The intermediate layer or middle layer is
  sometimes called the application server or Web
  server
• Three-tier Architecture Can Enhance Security
• Database server only accessible via middle tier
• Clients cannot directly access database server

30
Three-tier client-server architecture
31
Three-tier architecture

• The presentation layer displays information to
  the user
• The business logic layer handles intermediate
  rules and constrains before data is passed up to
  the user or down to the DBMS
• If the bottom layer is split into two layers (a
  web server and a database server), then it is a
  4-tire architecture (possible to the n-tier)