Title: Database Environment
1Chapter 17
1
2Chapter 17 - Objectives
- The purpose and origin of the three-level
database architecture. - The contents of the external, conceptual and
internal levels. - The purpose of the external/conceptual and the
conceptual/internal mappings.
2
3Chapter 17 - Objectives
- The meaning of logical and physical data
independence. - The distinction between a Data Definition
Language (DDL) and a Data Manipulation Language
(DML). - A classification of data models.
3
4Chapter 17 - Objectives
- The purpose and importance of conceptual
modeling. - The typical functions and services a DBMS should
provide. - The components of a DBMS.
4
5Chapter 17 - Objectives
- The meaning of the clientserver architecture and
the advantages of this type of architecture for a
DBMS. - The function and importance of the system catalog.
5
6Objectives of Three-Level Architecture
- All users should be able to access same data.
- A user's view is immune to changes made in other
views. - Users should not need to know physical database
storage details.
6
7Objectives of Three-Level Architecture
- DBA should be able to change database storage
structures without affecting the users' views. - Internal structure of database should be
unaffected by changes to physical aspects of
storage. - DBA should be able to change conceptual structure
of database without affecting all users.
7
8ANSI-SPARC Three-level Architecture
8
9ANSI-SPARC Three-level Architecture
- External Level
- Users' view of the database. Describes that part
of database that is relevant to a particular
user. - Conceptual Level
- Community view of the database. Describes what
data is stored in database and relationships
among the data.
9
10ANSI-SPARC Three-level Architecture
- Internal Level
- Physical representation of the database on the
computer. Describes how the data is stored in
the database.
10
11An example of the three levels
-
- External level
- 01 employee,
- 02 employee_city pic
x(20), - 02 employee_state
pic x(2). -
- conceptual level
- employee
- employee_city
varchar(20) - employee_state
char(2) - Internal Level
- employee
- employee_city varchar(20) NULl
- employee_state
char(2) NOT NULL
12Differences between Three Levels of ANSI-SPARC
Architecture
11
13Data Independence
- Logical Data Independence
- Refers to immunity of external schemas to changes
in conceptual schema. - Conceptual schema changes e.g. addition/removal
of entities. - Should not require changes to external schema or
rewrites of application programs.
12
14Data Independence
- Physical Data Independence
- Refers to immunity of conceptual schema to
changes in the internal schema. - Internal schema changes e.g. using different file
organizations, storage structures/devices. - Should not require change to conceptual or
external schemas.
13
15Data Independence and the ANSI-SPARC Three-level
Architecture
14
16Data Model
- Collection of concepts for describing data,
relationships between data and constraints on the
data in an organization. - Data Model comprises
- A structural part (a set of rules according to
- Which databases can be constructed)
- A manipulative part (defining the types of
operations that are allowed on the data) - Possibly a set of integrity rules
18
17Data Model
- Purpose
- To represent the data in an understandable way.
- Categories of data models include
- Conceptual
- Logical (Functional)
- Physical
19
18Conceptual modeling
- The process of developing a conceptual data model
that is - a complete and accurate representation of an
organization's data requirements. - independent of implementation details.
22
19Functions of a DBMS
- Data Storage, Retrieval and Update.
- Must furnish users with the ability to store,
retrieve, and update data in the database. - A User-Accessible Catalog.
- Must furnish a catalog in which descriptions of
data items are stored and which is accessible to
users.
23
20Functions of a DBMS
- Transaction Support
- Must furnish a mechanism to ensure that either
all the updates corresponding to a given
transaction are made or that none of them are
made. - Concurrency Control Services
- Must furnish a mechanism to ensure that database
is updated correctly when multiple users are
updating the database concurrently.
24
21Functions of a DBMS
- Recovery Services
- Must furnish a mechanism for recovering the
database in the event that the database is
damaged in any way. - Authorization Services
- Must furnish a mechanism to ensure that only
authorized users can access the database.
25
22Functions of a DBMS
- Support for Data Communication
- Must be capable of integrating with communication
software. - Integrity Services
- Must furnish a means to ensure that both the data
in the database and changes to the data follow
certain rules.
26
23Functions of a DBMS
- Services to Promote Data Independence
- Must include facilities to support the
independence of programs from the actual
structure of the database. - Utility Services
- Should provide a set of utility services.
27
24Components of a DBMS
28
25Components of a DBMS
- Query processor
- Database manager (DM)
- File manager
- DML preprocessor
- DDL compiler
- Catalog manager
29
26Components of Database Manager (DM)
30
27Components of Database Manager (DM)
- Authorization control
- Command processor
- Integrity checker
- Query optimizer
- Transaction manager
- Scheduler
- Recovery manager
- Buffer manager
31
28Multi-user DBMS Architectures
- Teleprocessing
- File-server
- Client-server
32
29Teleprocessing
- Traditional architecture.
- Single mainframe with a number of terminals
attached. - Trend is now towards downsizing.
33
30Teleprocessing Topology
34
31File-server
- File-server is connected to several workstations
across a network. - Database resides on file-server.
- DBMS and applications run on each workstation.
35
32File-server
- Disadvantages include
- Significant network traffic.
- Copy of DBMS on each workstation.
- Concurrency, recovery and integrity control more
complex.
36
33File-server Architecture
37
34Client-server
- Server holds the database and the DBMS.
- Client manages the user interface and runs
applications.
38
35Client-server
- Advantages include
- Wider access to existing databases.
- Increased performance.
- Possible reduction in hardware costs.
- Reduction in communication costs.
- Increased consistency.
39
36Client-server Architecture
40
37System Catalog
- A repository of information (metadata) describing
the data in the database. - Typically stores
- Names of authorized users.
- Names of data items in the database.
- Constraints on each data item.
- Data items accessible by a user and the type of
access.
43
38System Catalog
- It is used by modules such as
- Authorization Control.
- Integrity Checker.
44